0

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?

0

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.
  • Would it be better to grant the ANY privileges to user2 (SELECT ANY TABLE, CREATE ANY PROCEDURE, etc)? – rodrigortz Mar 7 '17 at 10:22
  • If you grant SELECT ANY TABLE to a user, he/she can select all the tables of that particular schema. And CREATE ANY PROCEDURE system privilege permits a user to create a procedure anywhere in the database. So it depends upon your requirements. – JSapkota Mar 7 '17 at 10:28
  • So if I would just want to create views, materialized views and procedures in my *own* schema based on other users tables, would I just require the SELECT ANY TABLE privilege? I see it from the standpoint that the CREATE VIEW command is really just pulling data from other tables (for which I need permission) but creating the view in my schema, not user1's schema, correct? – rodrigortz Mar 7 '17 at 10:35
  • Yes, SELECT ANY TABLE can let you create views by selecting any of the tables belong to user1. – JSapkota Mar 7 '17 at 10:40
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.