Home » SQL & PL/SQL » SQL & PL/SQL » Script to create synonyms for users granted a particular role
Script to create synonyms for users granted a particular role [message #271018] Fri, 28 September 2007 14:05 Go to next message
nandithareddy
Messages: 3
Registered: September 2007
Junior Member
Hi,

Help.

I have a role in the database, that is assigned to some of the users.

I want to create a generic script that will query the data dictionary view dba_role_privs and create a synonym for all the users that query results.

I have tried to do this in a package I have created in the system schema. however I keep getting the error
PL/SQL: ORA-00942: table or view does not exist.

Here is the code from my package body:

CREATE OR REPLACE Package body synCr_NM
AS


PROCEDURE SYNONYM_CREATE

AS

BEGIN

FOR vrec in (select grantee from dba_role_privs where granted_role like 'KBW_READER')
LOOP
BEGIN

EXECUTE IMMEDIATE('Create SYNONYM '||vrec.grantee||'.BM_RATING_ITEM FOR KBW.BM_RATING_ITEM');

END;

END LOOP;

END;

END SYNCR_NM;
/


TIA

nanditha
Re: Script to create synonyms for users granted a particular role [message #271019 is a reply to message #271018] Fri, 28 September 2007 14:08 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>I have tried to do this in a package I have created in the system schema.
BAD! BAD! BAD! Don't create any object in SYSTEM or SYS schemas!

Privs acquired via ROLE do NOT apply within PL/SQL procedures.

Explicit GRANT on OBJECT must be issued
Re: Script to create synonyms for users granted a particular role [message #271027 is a reply to message #271019] Fri, 28 September 2007 14:21 Go to previous messageGo to next message
nandithareddy
Messages: 3
Registered: September 2007
Junior Member
Appreciate the answer.
OK. Point noted. Iunderstand this is generally a bad idea.




However I need to create 20 synonyms each time a create a table. sothought this might be a way out instead of doing it manually.

Also this will keep the extra work of keeping track of new users who have been created this role off my back.

If you will check my pacakge it is executing the individual create commands within the loop.

I have granted select on the table manually to this role.

any other suggestion on how to do this?






Re: Script to create synonyms for users granted a particular role [message #271031 is a reply to message #271018] Fri, 28 September 2007 14:27 Go to previous messageGo to next message
nandithareddy
Messages: 3
Registered: September 2007
Junior Member
Sorry for the hasty reply.

Here is the thing though....when I am compiling this package why do I get the error that table or view does not exist. Can we even use the dba_* views within packages?
This is my first attempt at using them within packages so I am really not sure.

I can query them from the sqlprompt as well as within toads sqleditor using the same login

Re: Script to create synonyms for users granted a particular role [message #271034 is a reply to message #271018] Fri, 28 September 2007 14:33 Go to previous message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member

>I can query them from the sqlprompt as well as within toads sqleditor using the same login
SQL> SET ROLE NONE
& then do your SQL

Privs acquired via ROLE do NOT apply within PL/SQL procedures.
Privs acquired via ROLE do NOT apply within PL/SQL procedures.
Privs acquired via ROLE do NOT apply within PL/SQL procedures.
Privs acquired via ROLE do NOT apply within PL/SQL procedures.
Privs acquired via ROLE do NOT apply within PL/SQL procedures.
Previous Topic: how can I user trigger to implement Referential Integrity in distributed database.
Next Topic: Linking Perl procedure with Oracle Package
Goto Forum:
  


Current Time: Sat Dec 03 15:53:18 CST 2016

Total time taken to generate the page: 0.13645 seconds