Grant Select on all Tables Owned By Specific User

Question

I need to grant select permission for all tables owned by a specific user to another user. Can I do this with a single command along the lines of:

Grant Select on OwningUser.* to ReceivingUser

Or do I have to generate the sql for each table with something along the lines of:

 Select 'GRANT SELECT ON OwningUser.'||Table_Name||'TO ReceivingUser' 
 From All_Tables Where Owner='OWNINGUSER'

score 70 · Accepted Answer · answered Oct 09 '08 at 22:49

70

Well, it's not a single statement, but it's about as close as you can get with oracle:

BEGIN
   FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='TheOwner') LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to TheUser';
   END LOOP;
END;

answered Oct 09 '08 at 22:49

DCookie

42,630
11
83
92

4

Also `for v in (select OWNER, VIEW_NAME from ALL_VIEWS where OWNER = 'XXX') loop` – gavenkoa May 07 '14 at 15:00
3

just an observation that the challenge with this approach is that it is "point in time" - any subsequent tables added will be excluded. It would be MUCH nicer to have a conceptual "grant read-only access to any table owned by someSchema to someUser" statement as one could guarantee that any table owned by someSchema would be readable by someUser – Pancho Jul 31 '19 at 18:00
this is ending with error "table or view does not exists" even if i try to "SELECT owner, table_name FROM all_tables WHERE owner='TheOwner'" - it gaves me a rows, so the select is OK, but the whole loop ends with mentioned error. – Klára Janstová Oct 25 '21 at 08:18
@klara, you probably don't have permission to grant the privileges on the tables returned by the query. – DCookie Oct 26 '21 at 15:15

score 3 · Answer 2 · answered Sep 09 '16 at 06:14

tables + views + error reporting

SET SERVEROUT ON
DECLARE
  o_type VARCHAR2(60) := '';
  o_name VARCHAR2(60) := '';
  o_owner VARCHAR2(60) := '';
  l_error_message VARCHAR2(500) := '';
BEGIN
  FOR R IN (SELECT owner, object_type, object_name
            FROM all_objects 
            WHERE owner='SCHEMANAME'
            AND object_type IN ('TABLE','VIEW')
            ORDER BY 1,2,3) LOOP
    BEGIN
    o_type := r.object_type;
    o_owner := r.owner;
    o_name := r.object_name;
    DBMS_OUTPUT.PUT_LINE(o_type||' '||o_owner||'.'||o_name);
    EXECUTE IMMEDIATE 'grant select on '||o_owner||'.'||o_name||' to USERNAME';
    EXCEPTION
      WHEN OTHERS THEN
        l_error_message := sqlerrm;
        DBMS_OUTPUT.PUT_LINE('Error with '||o_type||' '||o_owner||'.'||o_name||': '|| l_error_message);
        CONTINUE;
    END;
  END LOOP;
END;
/

score 2 · Answer 3 · edited Oct 05 '17 at 06:13

2

yes, its possible, run this command:

lets say you have user called thoko

grant select any table, insert any table, delete any table, update any table to thoko;

note: worked on oracle database

edited Oct 05 '17 at 06:13

J. Chomel

8,193
15
41
69

answered Aug 29 '13 at 13:03

user2729366

65
1

5

This will grant it on all schemas, and will require a system privilege in order to grant this – Petr Jan 31 '14 at 11:59

score 0 · Answer 4 · answered Oct 05 '17 at 06:28

From http://psoug.org/reference/roles.html, create a procedure on your database for your user to do it:

CREATE OR REPLACE PROCEDURE GRANT_SELECT(to_user in varchar2) AS

  CURSOR ut_cur IS SELECT table_name FROM user_tables;

  RetVal  NUMBER;
  sCursor INT;
  sqlstr  VARCHAR2(250);

BEGIN
    FOR ut_rec IN ut_cur
    LOOP
      sqlstr := 'GRANT SELECT ON '|| ut_rec.table_name || ' TO ' || to_user;
      sCursor := dbms_sql.open_cursor;
      dbms_sql.parse(sCursor,sqlstr, dbms_sql.native);
      RetVal := dbms_sql.execute(sCursor);
      dbms_sql.close_cursor(sCursor);

    END LOOP;
END grant_select;

score 0 · Answer 5 · answered Jan 18 '23 at 15:40

DECLARE
    v_owner VARCHAR2(50) DEFAULT 'TEST1';
    v_grantee VARCHAR2(50) DEFAULT 'TEST2';
BEGIN
    FOR c IN (SELECT object_name FROM all_objects WHERE owner = v_owner and object_type = 'TABLE')
    LOOP
        BEGIN
            EXECUTE IMMEDIATE 'GRANT SELECT ON ' || v_owner || '.' || c.object_name||' TO ' || v_grantee;
        EXCEPTION when OTHERS THEN
            DBMS_OUTPUT.PUT_LINE (c.object_name);
        END;
    END LOOP;
END;
/

Grant Select on all Tables Owned By Specific User

5 Answers5

Linked

Related