OraFAQ Forum: Security » GRANT Privs TO ROLES

Home » RDBMS Server » Security » GRANT Privs TO ROLES (ORACLE DB)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

GRANT Privs TO ROLES [message #577555]

Mon, 18 February 2013 06:07

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

I am working on the roles and privs. I want to provide the privs on any object to any user through the roles. not a direct grant.

I am having 150 users and 50 roles, I want to do this using the script is few strokes. for this I have tried to create a script but I am not able to make that.

For example There is 2 user scott and test

A. test is having select privs on scott.emp.
B. Test is having insert,delete,update privs on scott.emp;
c. DB is having 2 roles
scott_edit - insert,delete,update privs on tables
scott_read - select privs on tables

I want to revoke separate privs from test and want to given only scott_edit and scott_read (which(roles) is having all privs)

I hope you are able to understand my problem.

If some one has any clue in this please help me.

Thanks in Advance

Pradeep Sharma

Report message to a moderator

Re: GRANT Privs TO ROLES [message #577560 is a reply to message #577555]

Mon, 18 February 2013 06:32

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can find:
- all privileges of test on scott objects in dba_tab_privs
- all tables of scott in dba_tables
With both it is easy to generate the script.
I give you one:

select 'grant select on scott.'||table_name||' to scott_read;' from dba_tables where owner='SCOTT';

You'll have to add some restriction to, for instance, exclude nested tables...

Regards
Michel

Report message to a moderator

Re: GRANT Privs TO ROLES [message #577561 is a reply to message #577555]

Mon, 18 February 2013 06:33

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

for this I have tried to create a script but I am not able to make that.

What does your script look like? It should be straightforward: some REVOKE statements, the CREATE EROLE statements, and some GRANT statements. Then perhaps some SELECTs to confirm that the changes were made.

Report message to a moderator

Previous Topic:	how to create user under schema (should access all objects of schema)
Next Topic:	Oracle Wallet Autologin - can't move it to another machine?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 21:27:23 CST 2025