Re: What am I missing???

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

I gather you were connected as audunj when creating the table? A few remarks
From this transcript it looks like you never granted the testrole to your user testuser. So that's one possible reason why it doesn't work as expected.
Also: views are compiled. Roles are being disregarded during compilation as roles are volatile. So except for granting select to public (which still seems to be a special case) do not expect views to work when connected as a different user. So that's a second possible reason why it doesn't work.

Hth

Sybrand Bakker, Oracle DBA Received on Wed Feb 21 2001 - 01:56:32 CST