?
Solved

Non-DBA who is granted DBA and RESOURCE,SELECT_CATALOG_ROLE...etc Roles

Posted on 2011-04-28
6
Medium Priority
?
916 Views
Last Modified: 2013-12-18
assuming the grantee is the Oracle user.  If so, would like to identify when they login and from what location.  Also, do we need to look at any cleanup?


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

GRANTEE      GRANTED_ROLE              ADMIN_OP   DEFAULT_ROLE                    
------------     -------------------------            --------    ------------                    
ISMOM                       DBA                          NO       YES                            
HEAT                      RESOURCE                  NO       YES                            
WMSYS                RESOURCE                    NO       YES                            
FICARCAT              RESOURCE                  YES      YES                  
0
Comment
Question by:Cha1tu
6 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35486802
Try something like this:

SELECT * FROM  dba_audit_trail where ACTION_NAME = 'LOGON'
   AND USERNAME IN (select
      grantee
   from
      sys.dba_role_privs
   where
      granted_role in ('RESOURCE','DBA','SELECT_CATALOG_ROLE')
   and
      grantee not in ('SYS','SYSTEM')
)
0
 
LVL 3

Expert Comment

by:cklautau
ID: 35488912
The suggestion above works if the parameter audit_trail=DB is set.
Another way to do this is to create a logon trigger and collect all the info you need.
0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35492119
@mrjoltcola:  here its showing all but its not showing the
     2 grantee,
  3     granted_role,
  4     admin_option,
  5     default_role
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35642103
- use join instead:

SELECT b.grantee, b.granted_role, b.admin_option, b.default_role 
FROM  dba_audit_trail a 
JOIN dba_role_privs b ON b.grantee = a.username
WHERE a.action_name = 'LOGON'
AND   b.granted_role IN ('RESOURCE','DBA','SELECT_CATALOG_ROLE')
AND   b.grantee NOT IN ('SYS','SYSTEM')

Open in new window

0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35722688
@OP_Zaharin:


I am getting the reults like this for the query which you gave me

 grantee     granted_role    admin_option      default_role


BUT I NEED TO GET when they login(LOGIN COLUMN) AND the server name column from where they are logging



 
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35725091
hi Cha1tu,
- try this:

SELECT b.grantee, b.granted_role, b.admin_option, b.default_role, a.os_username, a.userhost, a.terminal, a.timestamp, a.action
FROM  dba_audit_trail a 
JOIN dba_role_privs b ON b.grantee = a.username
WHERE a.action_name = 'LOGON'
AND   b.granted_role IN ('RESOURCE','DBA','SELECT_CATALOG_ROLE')
AND   b.grantee NOT IN ('SYS','SYSTEM')

Open in new window

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question