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
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.
-
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_xeliminating 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
