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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] Grant Select on all Tables Owned By Specific User

Examples related to oracle