How to know that a specific procedure or function has been executed by which user and when?
Lot many times we would like to track the execution of a fuction or SP. For example, who executed the fuction / SP? What time this was executed? and many more. You need to do that with the help of auditing database. Here I have shown an example of how to enable audit on function.
Please go through the below steps. Hope this helps you.
C:\Users\Ashish>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 7 15:17:34 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter audit
NAME TYPE VALUE
----------------------------- ----------- ------------------------------
audit_file_dest string C:ASHISH\ADMIN\PRIMARY\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> set pages 1000
SQL> set lines 180
SQL>
SQL>
SQL> conn ashish/ashish
Connected.
SQL>
SQL>
SQL> create or replace function TEST_AUDIT_BY_ACCESS
2 return date
3 is
4 begin
5 return sysdate;
6 end;
7 /
Function created.
SQL>
SQL>
SQL>
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> audit execute on ashish.TEST_AUDIT_BY_ACCESS by access;
Audit succeeded.
SQL> select os_username,username,obj_name,owner from dba_audit_trail where obj_name='TEST_AUDIT_BY_ACCESS';
no rows selected
SQL> conn ashish/ashish
Connected.
SQL> select TEST_AUDIT_BY_ACCESS from dual;
TEST_AUDI
---------
07-AUG-14
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> select os_username,username,obj_name,owner from dba_audit_trail where obj_name='TEST_AUDIT_BY_ACCESS';
OS_USERNAME USERNAME OBJ_NAME OWNER
--------------- ------------- --------------------- -------------
Ashish ASHISH TEST_AUDIT_BY_ACCESS ASHISH
SQL>
You can fetch more columns from dba_audit_trail as per your requirement.
SQL> desc dba_audit_trail
Name Null? Type
----------------------------------------------------------------------------------------------------- --------
--------------------------------------------------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
OWNER VARCHAR2(30)
OBJ_NAME VARCHAR2(128)
ACTION NOT NULL NUMBER
ACTION_NAME VARCHAR2(28)
NEW_OWNER VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJ_PRIVILEGE VARCHAR2(16)
SYS_PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(1)
GRANTEE VARCHAR2(30)
AUDIT_OPTION VARCHAR2(40)
SES_ACTIONS VARCHAR2(19)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
COMMENT_TEXT VARCHAR2(4000)
SESSIONID NOT NULL NUMBER
ENTRYID NOT NULL NUMBER
STATEMENTID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER
PRIV_USED VARCHAR2(40)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
SESSION_CPU NUMBER
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME
ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
SCN NUMBER
SQL_BIND NVARCHAR2(2000)
SQL_TEXT NVARCHAR2(2000)
OBJ_EDITION_NAME VARCHAR2(30)
DBID NUMBER
Lot many times we would like to track the execution of a fuction or SP. For example, who executed the fuction / SP? What time this was executed? and many more. You need to do that with the help of auditing database. Here I have shown an example of how to enable audit on function.
Please go through the below steps. Hope this helps you.
C:\Users\Ashish>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 7 15:17:34 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter audit
NAME TYPE VALUE
----------------------------- ----------- ------------------------------
audit_file_dest string C:ASHISH\ADMIN\PRIMARY\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> set pages 1000
SQL> set lines 180
SQL>
SQL>
SQL> conn ashish/ashish
Connected.
SQL>
SQL>
SQL> create or replace function TEST_AUDIT_BY_ACCESS
2 return date
3 is
4 begin
5 return sysdate;
6 end;
7 /
Function created.
SQL>
SQL>
SQL>
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> audit execute on ashish.TEST_AUDIT_BY_ACCESS by access;
Audit succeeded.
SQL> select os_username,username,obj_name,owner from dba_audit_trail where obj_name='TEST_AUDIT_BY_ACCESS';
no rows selected
SQL> conn ashish/ashish
Connected.
SQL> select TEST_AUDIT_BY_ACCESS from dual;
TEST_AUDI
---------
07-AUG-14
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> select os_username,username,obj_name,owner from dba_audit_trail where obj_name='TEST_AUDIT_BY_ACCESS';
OS_USERNAME USERNAME OBJ_NAME OWNER
--------------- ------------- --------------------- -------------
Ashish ASHISH TEST_AUDIT_BY_ACCESS ASHISH
SQL>
You can fetch more columns from dba_audit_trail as per your requirement.
SQL> desc dba_audit_trail
Name Null? Type
----------------------------------------------------------------------------------------------------- --------
--------------------------------------------------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
OWNER VARCHAR2(30)
OBJ_NAME VARCHAR2(128)
ACTION NOT NULL NUMBER
ACTION_NAME VARCHAR2(28)
NEW_OWNER VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJ_PRIVILEGE VARCHAR2(16)
SYS_PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(1)
GRANTEE VARCHAR2(30)
AUDIT_OPTION VARCHAR2(40)
SES_ACTIONS VARCHAR2(19)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
COMMENT_TEXT VARCHAR2(4000)
SESSIONID NOT NULL NUMBER
ENTRYID NOT NULL NUMBER
STATEMENTID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER
PRIV_USED VARCHAR2(40)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
SESSION_CPU NUMBER
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME
ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
SCN NUMBER
SQL_BIND NVARCHAR2(2000)
SQL_TEXT NVARCHAR2(2000)
OBJ_EDITION_NAME VARCHAR2(30)
DBID NUMBER
