Oracle: Grant privileges to multiple schema in one line

Question

I want to write one line in oracle so that I can grant select privilege on a table to multiple schema at once.

Here's what I have but I get a syntax error:

GRANT SELECT ON OWNER.TABLE_NAME to (select distinct owner from dba_objects where owner like 'SCHEMA_%');

Because the parameter is not expected to be a list nor a result set. Check Littlefoot's answer. — Alfabravo, Nov 23 '18 at 20:12
Also it opens up a few nuances around privileges if use with PLSQL code, for just straight querying you could consider creating roles , assigning the roles to users , then you just assign the privilege to the role, and users within that role inherit the privs. — TenG, Nov 24 '18 at 11:36

score 3 · Answer 1 · answered Nov 23 '18 at 20:10

You'll need dynamic SQL to do that, e.g.

SQL> declare
  2    l_str varchar2(200);
  3  begin
  4    for cur_r in (select 'MIKE' username from dual union all
  5                  select 'IMPORTER'      from dual union all
  6                  select 'DIP'           from dual
  7                 )
  8    loop
  9      l_str := 'grant select on emp to ' || cur_r.username;
 10      dbms_output.put_line(l_str);
 11      execute immediate l_str;
 12    end loop;
 13  end;
 14  /
grant select on emp to MIKE
grant select on emp to IMPORTER
grant select on emp to DIP

PL/SQL procedure successfully completed.

SQL>

I used several users from my database to fetch usernames in the cursor FOR loop; you'd substitute it with your own select distinct owner from dba_objects where owner like 'SCHEMA_%'.

Oracle: Grant privileges to multiple schema in one line

1 Answers1