This content has been marked as final. Show 4 replies
GR wrote:There is no single command to grant privileges at that level. There are either ANY privileges or privileges on an object.
I need to grant DML privileges to all tables in few schemas to a role. How can I achieve that?
I thought it's below syntax but it doesn't work. Please advice.
grant ALL ON ALL TABLES IN SCHEMA <Schema_name> TO <role_name>;
You can write a bit of code to generate and execute what you want, but you would have to rerun it if any new tables were created.
Veeresh.S wrote:or 'GRANT ALL...' if that is what is really wanted.
select 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' to ROLE_NAME;' as grant_select FROM dba_tables WHERE owner in ('SCOTT','X_USER','Y_USER');
In 11GR2, 'ALL' implies 'ALTER,DEBUG,DELETE,FLASHBACK,INSERT,ON COMMIT REFRESH,QUERY REWRITE,SELECT,UPDATE'.
You can also list more than one privilege at a time: 'grant DELETE,INSERT,SELECT,UPDATE ON...'
You can't list the tables and you can't use wildcards.