asked on

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

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

mrjoltcola

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')
)

cklautau

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.

Cha1tu

ASKER

@mrjoltcola: here its showing all but its not showing the
2 grantee,
3 granted_role,
4 admin_option,
5 default_role

OP_Zaharin

- 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

Cha1tu

ASKER

@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

ASKER CERTIFIED SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial