Oracle Schema Privileges Other Users

Question

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?

Answer 1

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.

Answer 2

A little late but this block will surely help someone.

1. Replace OWNER_SCHEMA_NAME with desired table owner schema.
2. Replace NEW_TARGET_SCHEMA_NAME in below script to the schema you want to give permissions.
3. Please take a look at the permissions below (which are placed as sample) before you execute this script.
4. Similarly, you can build a script for revoking permissions.

here goes the pl/sql block.

BEGIN
    FOR t IN (
        SELECT
            object_name,
            object_type
        FROM
            all_objects
        WHERE
            owner = 'OWNER_SCHEMA_NAME'
            AND   object_type IN (
                'TABLE',
                'VIEW',
                'PROCEDURE',
                'FUNCTION',
                'PACKAGE',
                'SEQUENCE'
            )
    ) LOOP
        IF
            t.object_type IN (
                'TABLE',
                'VIEW'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF
            t.object_type IN (

                'SEQUENCE'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF t.object_type IN (
            'PROCEDURE',
            'FUNCTION',
            'PACKAGE'
        ) THEN
            EXECUTE IMMEDIATE 'GRANT EXECUTE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        END IF;
    END LOOP;
END;