Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

GRANT SELECT on all tables in a schema

Tmicheli-OracleOct 13 2014 — edited Jun 26 2018

User often are asking for a single statement to Grant privileges in a single step.

there are multiple workarounds for not have a GRANT SELECT on all table

FOR x IN (SELECT * FROM user_tables)

LOOP  

EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <<someone>>';

END LOOP;

 

or

declare
cursor c1 is select table_name from user_tables;
cmd varchar2(200);
begin
for c in c1 loop
cmd := 'GRANT SELECT ON '||c.table_name|| <<TO YOURUSERNAME>>;
execute immediate cmd;
end loop;
end;

Oracle could implement a schema level grant

GRANT SELECT ON <schema name> to <<user>>

This request is now referenced with ER: 16899440 - SCHEMA WIDE PRIVILEGES

Comments
Post Details
Added on Oct 13 2014
89 comments
472,525 views