Granting select, insert, update, delete on all tables of one user to a role
an, January 30, 2003 - 10:53 am UTC
Hi Tom:
if i am not worry about new tables that will be created in the future from a user, now i need to create a role, that should have all select, update, insert, delete privs from the user, if it's no syntax as grant select, update, ... from the user to the role, how can i create a procedure that will grant select, update, insert, delete privs from the user to the role?
thanks,
January 30, 2003 - 11:30 am UTC
begin
for x in ( select tname from tab )
loop
execute immediate 'grant select, insert, update, delete on ' || x.tname ||
' to whatever_role';
end loop;
end;
need to add some more info.
Bhanu Prashanthi M, March 25, 2004 - 5:35 am UTC
The query is of great help. It will be more useful if we add that the grant should be on valid objects only.
A reader, June 02, 2004 - 4:10 pm UTC
This is exactly what I needed...(I am not sure if this is suitable for ORACLE)...I modified it to user USER_TABLES and it worked like a charm!
thanks,
Thanks for this!
Wools83, March 27, 2019 - 9:46 pm UTC
Excellent! Just what I needed.
I modified it to work in Oracle 12c for just one schema:
begin
for x in ( select VIEW_NAME from DBA_VIEWS WHERE OWNER = '<SCHEMA_NAME>' )
loop
execute immediate 'grant select on <SCHEMA_NAME>.' || x.VIEW_NAME || ' to <ROLL_NAME>';
end loop;
end;
March 29, 2019 - 5:07 pm UTC
Glad we could help, thanks for sharing.
Good news: In Oracle 23 schema level privileges have been introduced!!!!!!!!!!
Anke Erdenberger, August 22, 2024 - 7:46 am UTC
August 23, 2024 - 8:00 am UTC
Indeed - its a much awaited feature