[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

would like to know what the Admin Option allows for those users. Are they granted privilege status and if so, we may need to review.

In auditing when i run this query then got i these results

 select
  2     grantee,
  3     granted_role,
  4     admin_option,
  5     default_role
  6  from
  7     sys.dba_role_privs
  8  where
  9     admin_option = 'YES'
 10  and
 11     grantee not in ('sys','system');



results..

GRANTEE       GRANTED_ROLE                           ADMIN_OP  DEFAULT_ROLE                    
------------    -------------------------                             --------   ------------                    
DBA               AQ_ADMINISTRATOR_ROLE                  YES      YES                            
TESTuser        'OEM_MONITOR'                                    YES      YES                            
SYS                  'OLAP_DBA'                                         YES      YES                            
TESTuser           WKFEE_USER                                     YES      YES                

would like to know what the Admin Option allows for those users.  Are they granted privilege status and if so, we may need to review.
0
Cha1tu
Asked:
Cha1tu
  • 7
  • 5
  • 5
4 Solutions
 
sdstuberCommented:
ADMIN allows the grantee to regrant that privilege to someone else
0
 
slightwv (䄆 Netminder) Commented:
It does a little more than just allow the user to grant to others.  It also allows the user to revoke and any other permission that role grants.  Basically 'administer' that role to any other user.

I suggest you always start with the docs.  Since items is dba_role_privs must be granted, check out the GRANT command:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_9013.htm#SQLRF01603

WITH ADMIN OPTION

Specify WITH ADMIN OPTION to enable the grantee to:

    *      Grant the privilege or role to another user or role, unless the role is a GLOBAL role
    *      Revoke the privilege or role from another user or role
    *      Alter the privilege or role to change the authorization needed to access it
    *      Drop the privilege or role
0
 
sdstuberCommented:
right,  I apologize if that wasn't clear.

There is no "revoke" or "alter" or "drop" option for privileges.  

For instance, you can't have alter with admin option.  - there is no such privilege

There is only "grant",  if you can grant then you get everything that "grant" can do, which includes alter,revoke,drop as mentioned


sorry for any confusion

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Cha1tuAuthor Commented:
@sdstuber:

==> There is no "revoke" or "alter" or "drop" option for privileges.  

Can i get those results in query which user has alter permisiion and which one has revoke,grant,...etc privileges..
0
 
slightwv (䄆 Netminder) Commented:
I'm not sdstuber but:   What are you asking?

If you are asking how to find out who has the 'revoke privilege' option, it doesn't exist.

If you are asking who has 'revoke, grant' on database objects, yes but that is a different question and needs to be asked in a new question.
0
 
sdstuberCommented:
no,

 because there is nothing to query


>>  There is no "revoke" or "alter" or "drop" option for privileges

If you have the ADMIN OPTION  then you have the ability to grant.

If you can grant a privilege then you can also revoke it or alter it

similar to objects.  There is no DROP TABLE privilege If you can create your own table then you also can drop your own table.

If somebody wants to contest with "DROP ANY TABLE" privilege,  that doesn't apply, it's something different




0
 
Cha1tuAuthor Commented:
thanks for your replay..


I Want to see results like this

GRANTEE       GRANTED_ROLE                           ADMIN_OP  DEFAULT_ROLE          ROLE      
------------    -------------------------                             --------   ------------                    ------------
DBA               AQ_ADMINISTRATOR_ROLE                  YES      YES                             ALTER
TESTuser        'OEM_MONITOR'                                    YES      YES                             REVOKE
SYS                  'OLAP_DBA'                                         YES      YES                             DELETE
TESTuser           WKFEE_USER                                     YES      YES                             DROP
 user1                  role_role1                                            YES       YES                          create
0
 
sdstuberCommented:
what is the "ROLE" column  supposed to represent in your output?
0
 
Cha1tuAuthor Commented:
Role column is the cloumn just i gave the name in that column. i need to get the which kind of granting privileges they have....

IF THEY user has CREATE granting privillege it needs to display there (ROLE COLUMN). if the user has delete,drop privilege ..needs to display
0
 
slightwv (䄆 Netminder) Commented:
A 'GRANTED_ROLE' can have MANY privileges and other roles granted to it.

There really isn't a way to provide that in a single column unless you are thinking like a CSV of all privs/roles granted to that role but I would advise against that and just issue a different query as part of your report.
0
 
sdstuberCommented:
If you are trying to determine if the grantee has some kind of special privileges to grant/revoke/alter the given role based on the ADMIN option then you're missing the point.  There are no such privileges.  Either you have the admin option are you do not.  If you have it then you can grant,revoke and alter.  If you do not, then you can't.


Maybe you mean you're trying to find system privileges associated with a given role.  If so do you mean something like this?  (11g syntax, if correct, it can be modified to support 10g as well but will require additional functions)





SELECT   drp.grantee,
         drp.granted_role,
         drp.admin_option,
         drp.default_role,
         listagg(dsp.privilege, ',') WITHIN GROUP (ORDER BY privilege) privs
    FROM sys.dba_role_privs drp, sys.dba_sys_privs dsp
   WHERE     drp.admin_option = 'YES'
         AND drp.grantee NOT IN ('sys', 'system')
         AND drp.granted_role = dsp.grantee
GROUP BY drp.grantee, drp.granted_role, drp.admin_option, drp.default_role;
0
 
slightwv (䄆 Netminder) Commented:
>>Maybe you mean you're trying to find system privileges associated with a given role.  

If that is what you are after, it needs to be a different question.
0
 
Cha1tuAuthor Commented:
==>
SELECT   drp.grantee,
         drp.granted_role,
         drp.admin_option,
         drp.default_role,
         listagg(dsp.privilege, ',') WITHIN GROUP (ORDER BY privilege) privs
    FROM sys.dba_role_privs drp, sys.dba_sys_privs dsp
   WHERE     drp.admin_option = 'YES'
         AND drp.grantee NOT IN ('sys', 'system')
         AND drp.granted_role = dsp.grantee
GROUP BY drp.grantee, drp.granted_role, drp.admin_option, drp.default_role;   <==

This query is giving problem here ****WITHIN GROUP (ORDER BY privilege) privs
****
0
 
sdstuberCommented:
listagg requires 11gR2
0
 
Cha1tuAuthor Commented:
oh...mineis 11gR1.........:(

is there anyway to get those Results in 11gR1
0
 
slightwv (䄆 Netminder) Commented:
>>is there anyway to get those Results in 11gR1

Yes.  By asking another question.

This question is "would like to know what the Admin Option allows for those users".

Now you appear to be asking how to report on all privileges and roles for a user.
0
 
sdstuberCommented:
agreed,  if you want to pursue the additional system privileges  those are UNRELATED to the admin option.  So they should be a new question.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now