Example includes 2 roles -

1. READ_ONLY - for select access only
2. READ_WRITE - for update, insert, delete, drop, etc.

Now I want to fire pl/sql query to -

For loop to get all schema names: Grant select on BGREADONLY to for User_X;

Please help with the exact piece of code..

CodePudding user response：

You can use below sample code and make sure you can modify the SELECT statement inside the FOR loop. Similarly you can do for the READ_WRITE as well. Just you need to change the GRANT statement for READ_WRITE.

DECLARE
BEGIN
  FOR i IN (select distinct owner from dba_objects)
  LOOP
    execute immediate 'GRANT select any table to '||i.owner;
  END LOOP;
END;

CodePudding user response：

you can try this procedure

CREATE or replace PROCEDURE grant_select(
    in_var_username VARCHAR2, 
    in_var_grantee VARCHAR2)
AS   
BEGIN
    FOR rec IN (
        SELECT owner, table_name FROM all_tables WHERE owner = in_var_username
    )
    LOOP
        EXECUTE IMMEDIATE 
            'GRANT SELECT ON '||rec.owner||'.'||rec.table_name||' TO ' || in_var_grantee || '  with grant option ';
    END LOOP;
END;


-- Call the procedure

exec grant_select('OWNER_SCHEMA','ROLE_TO_BE_GRANTED');