Re: What am I missing???

I know the issue you are asking about.

A user can only create views against a table that he has been granted rights to directly.

for instance.

UserA owns TableA.
grant select on UserA.TableA to SomeRole; grant SomeRole to UserB

UserB can do this:
select * from UserA.TableA
but can NOT do this:
create view ViewB as select * from UserA.TableA

If the privs are granted directly to UserB then the create view statement will work.

"Doug C" <dcowles_at_i84.net> wrote in message news:smno9tcpd9f6j7rg319sletkj9o4pipahm_at_4ax.com...
> 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 Sun Mar 04 2001 - 08:27:44 CST