Auditing - grant / revoke create session

All posts relating to Oracle database administration.

Moderator: Tim...

Auditing - grant / revoke create session

Postby wlourens » Wed Jun 19, 2013 8:59 am

Hi,

Can you please help me on how to retrieve grant / revoke of create session from the database audit trail, I can't find it:

/home/oracle > sqlplus TSSAtest/xxxxx

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 19 10:41:09 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> grant create session to scott;

Grant succeeded.

SQL> revoke create session from scott;

Revoke succeeded.


I tried numerous select statements, but could not find it. Example, the SYS_PRIVILEGE and OBJ_PRIVILEGE columns are blank:

1* select USERNAME, SYS_PRIVILEGE,OBJ_PRIVILEGE,SQL_TEXT,ADMIN_OPTION,COMMENT_TEXT from dba_audit_trail where USERNAME='TSSAtest'
SQL> /

USERNAME SYS_PRIVILEGE OBJ_PRIVILEGE
------------------------------ ---------------------------------------- ----------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A
-
COMMENT_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TSSAtest


Authenticated by: DATABASE



Assistance will be appreciated. I switched auditing on for all options in dba_priv_audit_opts and dba_stmt_audit_opts, so believe this should be audited.

Thanks,
Willem
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Auditing - grant / revoke create session

Postby wlourens » Wed Jun 19, 2013 9:15 am

Some more queries from dba_audit_trail that did not give me the results:

select USERNAME, PRIV_USED, ACTION_NAME , count(*) from dba_audit_trail where TIMESTAMP> sysdate - 2 group by USERNAME, PRIV_USED, ACTION_NAME order by 4 desc;

USERNAME PRIV_USED ACTION_NAME COUNT(*)
------------------------------ ---------------------------------------- ---------------------------- ----------
TSSAtest SELECT 4
TSSAtest CREATE SESSION LOGON 3
TSSAtest LOGOFF 2


SQL> select SQL_TEXT, USERNAME, PRIV_USED, to_char(TIMESTAMP, 'MM-DD-YYYY HH12:MI:SS') from dba_audit_trail where USERNAME='TSSAtest';

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERNAME PRIV_USED TO_CHAR(TIMESTAMP,'
------------------------------ ---------------------------------------- -------------------

TSSAtest CREATE SESSION 06-19-2013 10:17:40
....
USERNAME PRIV_USED TO_CHAR(TIMESTAMP,'
------------------------------ ---------------------------------------- -------------------

TSSAtest 06-19-2013 10:18:14

select USERNAME, SYS_PRIVILEGE,OBJ_PRIVILEGE,SQL_TEXT,ADMIN_OPTION,COMMENT_TEXT from dba_audit_trail where USERNAME='TSSAtest'
TSSAtest 06-19-2013 10:55:21
...
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Auditing - grant / revoke create session

Postby Tim... » Wed Jun 19, 2013 10:43 am

Hi.

This works for me.

Code: Select all
AUDIT SYSTEM GRANT BY ACCESS;


To test it, I'll create a new DBA user.

Code: Select all
CREATE USER tim IDENTIFIED BY tim;
GRANT DBA TO tim;


Now lets connect and try some grants and revokes.

We can see those messages in the audit trail.

Code: Select all
CONN tim/tim
GRANT CREATE SESSION TO test;
REVOKE CREATE SESSION FROM test;


Code: Select all
SET LINESIZE 150
SELECT username, action_name, sys_privilege, timestamp
FROM   dba_audit_trail
WHERE  timestamp > SYSDATE - (5/24/60)
ORDER BY timestamp;

USERNAME                       ACTION_NAME                  SYS_PRIVILEGE                            TIMESTAMP
------------------------------ ---------------------------- ---------------------------------------- --------------------
TIM                            SYSTEM REVOKE                CREATE SESSION                           19-JUN-2013 11:40:57
TIM                            LOGON                                                                 19-JUN-2013 11:40:57
TIM                            SYSTEM GRANT                 CREATE SESSION                           19-JUN-2013 11:40:57

3 rows selected.

SQL>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Auditing - grant / revoke create session

Postby wlourens » Wed Jun 26, 2013 1:35 pm

Hi,

Thanks!

Good news is that I did not pick up any ALTER SESSIONS in the audit_trail because there were none.

Auditing was enabled:

SQL> select * from dba_priv_audit_opts where privilege like '%AUDIT%';

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
AUDIT ANY BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS

Kind Regards,
Willem
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Auditing - grant / revoke create session

Postby Tim... » Wed Jun 26, 2013 2:11 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 6 guests

cron