Re: What am I missing???

Just for the record.. I had no problem with this scenario on Personal Oracle 7.

SQL> connect sys/sys
Connected.
SQL> create user audunj identified by stuff default tablespace user_Data   2 quota unlimited on user_Data;

User created.

SQL> grant connect to audunj;

Grant succeeded.

SQL> connect audunj/stuff;
Connected.
SQL> CREATE TABLE testtable (a VARCHAR2(4), b NUMBER(5));

Table created.

SQL> INSERT INTO testtable VALUES('srrt', 45);

1 row created.

SQL> CREATE OR REPLACE VIEW testview AS SELECT * FROM testtable;

View created.

SQL> connect sys/sys
Connected.

SQL> create role atestrole;

Role created.

SQL> connect audunj/stuff
Connected.
SQL> grant select on testview to atestrole;

Grant succeeded.

SQL> connect sys/sys
Connected.
SQL> grant atestrole to scott;
SQL> select * from role_tab_privs where role = 'ATESTROLE';

ROLE                           OWNER                          TABLE_NAME
------------------------------ ------------------------------
---------------------
COLUMN_NAME                    PRIVILEGE                                GRA
------------------------------ ---------------------------------------- ---
ATESTROLE                      AUDUNJ                         TESTVIEW
                               SELECT                                   NO

SQL> connect scott/tiger
Connected.
SQL> select * from audunj.testview;

A B
---- ---------
srrt 45

SQL> On 21 Feb 2001 07:01:03 GMT, audun_j_at_yahoo.no (Audun Jensen) wrote:

>Hi all,
>I read somewhere (I think) that grants on views has to be given directly to
>the user and not via roles (is this true?).
>In order to test this I did the following (logged on as dba):
>
>CREATE USER testuser IDENTIFIED BY testuser;
>ALTER USER testuser DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
>GRANT CONNECT TO testuser;
>
>CREATE TABLE testtable (a VARCHAR2(4), b NUMBER(5));
>INSERT INTO testtable VALUES('srrt', 45);
>CREATE OR REPLACE VIEW testview AS SELECT * FROM testtable;
>
>CREATE ROLE testrole;
>GRANT SELECT ON testview TO testrole;
>
>then connect as testuser/testuser and run
>SELECT * FROM audunj.testview;
>
>gives "ORA-00942: table or view does not exist"
>
>I'm pretty sure this should work or???
>
>AJ
Received on Tue Feb 27 2001 - 20:37:46 CST