1

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

6

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.

| improve this answer | |
  • Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me. – miguel ramires Feb 18 '19 at 21:05
  • "it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege. – EdStevens Feb 18 '19 at 21:56
  • @EdStevens - 12c+ allows you to grant role_pkg_x to package invoker_rights_pkg_x eliminating the need to grant unnecessary privileges to the user. – Michael Kutz Feb 18 '19 at 23:02
  • @MichaelKutz - thanks for the pointer. That should work even better for the OP – EdStevens Feb 19 '19 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.