Thanks for the question, Priscila.
Asked: March 06, 2017 - 5:55 pm UTC
Last updated: June 13, 2020 - 9:37 am UTC
Version: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Viewed 100K+ times! This question is
You Asked
Hello!!
I did some research, but I still need help, please.
I have eight schemas: A, B, C, E, F, G and H.
I need to give schema A, full access (select, insert, update, delete) to schema B, C, D, E, F, G and H (all objects: TABLE, VIEW, PACKAGE, PROCEDURE, FUNCTION, SEQUENCE, SYNONYM).
1. How do I get all the objects in a schema?
2. Will this query work? I mean, will schema A be able to select, insert, update and delete on all objects in schema B?
set lines 300
set pages 0
spool grant.sql
select 'grant select, insert, update, delete on B.'||objecT_name||' to A;' from dba_objects where object_type in ('TABLE','VIEW','PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'SYNONYM') and owner='B';
spool off
Thank you!
and Connor said...
It will work, but you need to edit your privs based on object type, eg
select
case
when object_type = 'SEQUENCE' then 'SELECT'
when object_type = 'TABLE' then 'SELECT,INSERT,UPDATE,DELETE'
when object_type in ('PACKAGE','FUNCTION','PROCEDURE') then 'EXECUTE'
etc
etc
and you dont need to do grants on synonyms.
Rating
(8 ratings)
Is this answer out of date? If it is, please let us know via a Comment