Home » RDBMS Server » Security » GRANT Privs TO ROLES (ORACLE DB)
GRANT Privs TO ROLES [message #577555] Mon, 18 February 2013 06:07 Go to next message
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

Re: GRANT Privs TO ROLES [message #577560 is a reply to message #577555] Mon, 18 February 2013 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: GRANT Privs TO ROLES [message #577561 is a reply to message #577555] Mon, 18 February 2013 06:33 Go to previous message
John Watson
Messages: 8922
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.
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:
  


Current Time: Thu Mar 28 07:36:39 CDT 2024