Privileges to select on a view – Issue

oracleoracle-12cpermissionsschema

I have two schemas: A and B.

A has one table with the name 'TEST'. B has created a view 'BASE' which does select on table 'TEST'.

Now I create another user C to which I grant, select on the view 'BASE'.

When the user C runs the following statement, he gets insufficient privileges error:

select * on B.BASE;

What privileges is required to select the contents of the view?

Best Answer

You need to grant select on the tables in schema A to the user B WITH GRANT OPTION.

From the documentation:

To grant SELECT on a view to another user, either you must own all of the objects underlying the view or you must have been granted the SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has SELECT privileges on those underlying objects.

Related Solutions

Oracle SQL create view privileges

To grant privilege to create a view:

GRANT CREATE VIEW TO STD01;

To grant the DML privileges:

GRANT SELECT,UPDATE,INSERT,DELETE ON STD00.CUSTOMER TO STD01;

But that's not all of the object privileges. If you did:

GRANT ALL ON STD00.CUSTOMER TO STD01;

you would also give other privileges such as ALTER, INDEX, FLASHBACK, etc.

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL>

Best Answer

Related Solutions

Oracle SQL create view privileges

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Related Question