Rights needed to connect oracle7 DB

From: Romain A <romain.anselin_at_REMOVETHISclub-internet.fr>
Date: Wed, 26 May 2004 17:57:27 +0200
Message-ID: <c92eea$h37$1@s1.read.news.oleane.net>

Hello all,

I'm creating a procedure in order to mount an Oracle 7.3.4 database on NT4 in less than 45 minutes with batch scripts (preparing my examinations). I'm a beginner in using Oracle, and I'm discovering the privileges of the database.
The role I created have the right to manipulate the datas (select on all tables ; insert, update, delete, select on PHOTO which has to be modified by the user), and I added the right of CREATE SESSION. But even through that, when I connect to the database with the user login, I cannot do
anything (even a select give me the following error). The error number is ORA-00942 - Table or view does not exist Here is the role and user script... comments are in french sorry

/*
Script de gestion des droits utilisateurs. On crée ici un rôle UPDFOTO, auquel on attribue les droits de lecture sur les tables
de la base PHOTO, et d'écriture sur la table Photo On crée l'utilisateur ROMAIN. On lui définit ensuite un mot de passe */
connect internal/****** as SYSDBA
-- Suppression des rôles et utilisateurs existants
DROP ROLE UPDFOTO;
DROP USER romain;
-- Creation du rôle et de l'utilisateur

CREATE ROLE UPDFOTO;
CREATE USER romain

   IDENTIFIED BY anselin
   DEFAULT TABLESPACE foto_user_data
   TEMPORARY TABLESPACE foto_temporary_data;
-- Attribution des droits au rôle

GRANT CREATE SESSION TO UPDFOTO;
GRANT SELECT ON MARQUE TO UPDFOTO;
GRANT SELECT ON GAMME TO UPDFOTO;
GRANT SELECT ON SERIE TO UPDFOTO;
GRANT SELECT ON MODELE TO UPDFOTO;
GRANT SELECT ON VEHICULE TO UPDFOTO;
GRANT SELECT, INSERT, UPDATE, DELETE ON PHOTO TO UPDFOTO;
-- Insertion de l'utilisateur dans le groupe
GRANT UPDFOTO TO romain;

Here is the result when searching informations on the system.

SVRMGR> select * from dba_roles;

ROLE                           PASSWORD
------------------------------ --------
CONNECT                        NO
RESOURCE                       NO
DBA                            NO
EXP_FULL_DATABASE              NO
IMP_FULL_DATABASE              NO
SNMPAGENT                      NO
UPDFOTO                        NO

7 lignes sÚlectionnÚes
SVRMGR> select * from dba_role_privs where granted_role = 'UPDFOTO';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
ROMAIN                         UPDFOTO                        NO  YES
SYS                            UPDFOTO                        YES YES

14 lignes sÚlectionnÚes
SVRMGR> select * from dba_tab_privs where grantee = 'UPDFOTO';

GRANTEE                        OWNER                          TABLE_NAME
GRANTOR
    PRIVILEGE                                GRA
------------------------------ ------------------------------ --------------
---------------- ---------------------------
--- ---------------------------------------- ---
UPDFOTO                        SYS                            VEHICULE
SYS
    SELECT                                   NO
UPDFOTO                        SYS                            MARQUE
SYS
    SELECT                                   NO
UPDFOTO                        SYS                            GAMME
SYS
    SELECT                                   NO
UPDFOTO                        SYS                            SERIE
SYS
    SELECT                                   NO
UPDFOTO                        SYS                            MODELE
SYS
    SELECT                                   NO
UPDFOTO                        SYS                            PHOTO
SYS
    DELETE                                   NO
UPDFOTO                        SYS                            PHOTO
SYS
    INSERT                                   NO
UPDFOTO                        SYS                            PHOTO
SYS
    SELECT                                   NO
UPDFOTO                        SYS                            PHOTO
SYS
    UPDATE                                   NO

Received on Wed May 26 2004 - 10:57:27 CDT