Need Help in Privileges

[Shantha]

Hi,

We are using Oracle 10g.

We want to implement certain security setup for the schemas to be accessed by the developers.

The design is as follows:

MAIN_MGR:- will hold the complete tables, data and stored procedures
ACCESS_MGR:- will have the synonyms to the tables created in MAIN_MGR and appropriate privileges to view, manipulate data. Also, it will hold EXECUTE privilege of Stored procedures created in MAIN_MGR.

The problem is:

Certain Stored procedures creates tables,procedure dynamically while executing in MAIN_MGR. Since this procedure is being executed from ACCESS_MGR and it doesn't have privlege to create tables in MAIN_MGR it throws error stating "insufficient privilege".

Need your help in resolving this or any other better design can be suggested.

Thanks & Regards,
Shantha.

Talent is what you possess;
genius is what possesses you

 

[Turkbear]

Hi,
Take a look at the 'definer's rights' explanation here:

http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#i1007675

The definer's rights are used by the SP when called from an account with only execute rights to the SP.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Shantha]

Hi,

Thanks for your reply.

Now I got the procedure executing in ACCESS_MGR and creating tables in MAIN_MGR.

After creating of the table i am trying to create synonym for the newly created table back to ACCESS_MGR within the same procedure.

For this, the same procedure after creating the table, frames a dynamic statement - CREATE OR REPLACE PUBLIC SYNONYM XXXXX FOR MAIN_MGR.XXXXX.

While executing this statement, it throws an error "ORA-01031: insufficient privileges"

But the same statement if executed from SQL PLUS client @ACCESS_MGR schema works fine.

Please guide.

Thanks & Regards,
Shantha.


Talent is what you possess;
genius is what possesses you

 

[Beilstwh]

You can make a private synonym in ACCESS_MGR. There is NO need to make a public synonym. However, why are you creating these new tables. We might be able to suggest a different method.

Bill
Lead Application Developer
New York State, USA