Home » SQL & PL/SQL » SQL & PL/SQL » Roles and Objects
Roles and Objects [message #223549] Fri, 09 March 2007 07:23 Go to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hi

Need some help on writing a query.

I want to select all objects which "belongs" to a particulary role and have been explicited granted(select) to a user.

short scenario to clarify

USER1 has got ROLE1,ROLE2
USER1 is given explicit grants on all objects in ROLE2
USER1 get's ROLE2 Revoked
USER1 find all explicit grants which "belongs" to ROLE2 and revoke those for USER1.

Any brilliant ideas to pull this of in 1 sql statment ?


Cheers*


Re: Roles and Objects [message #223551 is a reply to message #223549] Fri, 09 March 2007 07:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
May i ask why are you doing this?
Are you trying to create a user like 'another user'?
Regards
Re: Roles and Objects [message #223553 is a reply to message #223551] Fri, 09 March 2007 07:37 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Sure

Some users needs explicit grants on some objects.
You are probably aware :
"Role Limlitation.
In general, a user cannot acquire a DML privilege needed to perform a DDLoperation via role.The user must be explicitly granted the necessary object privilege.A user creating a view on another user's table, cannot receive the privilege to select from the table through a role."

But those explicit grants should be removed if the users role gets revoked.
Previous Topic: HOW TO SELECT NOT NULL COLUMNS FROM A TABLE
Next Topic: Select statment required
Goto Forum:
  


Current Time: Thu Dec 08 12:49:14 CST 2016

Total time taken to generate the page: 0.13687 seconds