Oracle – How to Grant All Privileges to a User Over All Other Users’ Objects

oraclepermissions

I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks

Best Answer

Which privileges? Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking. You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary. I suspect what you really need is something like this:

set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off

Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it. Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.

Related Question