Oracle Schema Privileges Other Users

oraclepermissionsschemausers

I have a user with only CONNECT privileges and I'd like to know if RESOURCE would be enough to query objects in other schemas and create objects in my own schema based on that.

i.e.: create a view in my own schema by selecting tables from other schemas.

Would a SELECT ANY TABLE privilege be enough or does my user have to be granted specific object privileges for any action on another user?

Best Answer

RESOURCE role has the following system privileges.

SQL> select privilege from role_sys_privs where  role='RESOURCE';

PRIVILEGE
----------------------------------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 rows selected.

And has no table privileges.

SQL> select privilege from role_tab_privs where  role='RESOURCE';

no rows selected

After the observation we can know that the user, with RESOURCE role, can create certain objects such as tables and procedures.

But in order to SELECT from tables belong to another schema, another user needs to grant SELECT object privilege on tables explicitly. 

SQL> conn user1/password
SQL> grant select on table to user2

Now user2 can create view(user should have CREATE VIEW system privilege) by selecting from the tables of user1 schema.

Demo(Based on Oracle 11.2.0.4):

SQL> create user user2 identified by user2;

User created.

SQL> grant resource, connect to user2;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create view v1 as select * from user1.mytest;
create view v1 as select * from user1.mytest
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn user1/user1
Connected.
SQL> grant select on mytest to user2;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create view v1 as select * from user1.mytest;
create view v1 as select * from user1.mytest
            *
ERROR at line 1:
ORA-01031: insufficient privileges --Now the user has no `CREATE VIEW` system privilege

SQL> conn / as sysdba
Connected.
SQL> grant create view to user2;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create view v1 as select * from user1.mytest;

View created.
Related Question