{"id":3212,"date":"2021-04-19T09:00:00","date_gmt":"2021-04-19T06:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3212"},"modified":"2024-06-18T17:52:45","modified_gmt":"2024-06-18T14:52:45","slug":"pos-energopoioyme-to-auditing-se-oracle-database","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-energopoioyme-to-auditing-se-oracle-database\/","title":{"rendered":"How we enable auditing in Oracle Database"},"content":{"rendered":"

In this article we will see the steps and options we have to activate it auditing <\/strong>in one Oracle<\/strong> Database<\/strong>. Through auditing we can record who and how accesses or changes its data.<\/p>\n\n\n\n

Auditing can be activated on specific users, on specific objects or on the entire database.<\/p>\n\n\n\n

<\/p>\n\n\n\n

How is it activated?<\/h2>\n\n\n\n

First we have to see from a command window in the database if the parameter is enabled audit_trail<\/strong>:<\/p>\n\n\n\n

SQL>  show parameter audit_trail<\/pre>\n\n\n\n
NAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\naudit_trail                          string      DB, EXTENDED\n<\/code><\/pre>\n\n\n\n
If it is not, we activate it with the options db,extended<\/strong>. The extended is needed in order to keep not only who accessed the data but also the sql statement along with its parameters.<\/p>\n\n\n\n
To get the change, it needs to be done restart the instance<\/strong>:<\/p>\n\n\n\n
alter system set audit_trail = db,extended scope=spfile;\n\nshutdown immediate;\n\nstartup;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Audit on a specific object<\/h3>\n\n\n\n
If we want to audit a specific table, we run the following:<\/p>\n\n\n\n
audit select on SCOTT.DEPT by access;\n\n--noaudit select on SCOTT.DEPT;<\/pre>\n\n\n\n
*The by access <\/strong>defines that every time the same user from the same session goes to access the table to keep it as a separate record<\/em><\/p>\n\n\n\n
**With the same command starting<\/em> with the noaudit <\/strong>without the by access<\/strong> We do it disable<\/strong>.<\/em><\/p>\n\n\n\n
To see that it was activated, we run the following query in the view dba_obj_audit_opts<\/code> and we will see that it says A\/A in the select column:<\/p>\n\n\n\n
select * from DBA_OBJ_AUDIT_OPTS where object_name ='DEPT';<\/pre>\n\n\n\n
*we could add other actions such as insert, update, delete, alter, etc<\/em><\/p>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Where we find the results of the auditing<\/h3>\n\n\n\n
After we run a select on the object we activated the audit before, e.g.:<\/p>\n\n\n\n
select * from scott.dept;<\/pre>\n\n\n\n
We will see the auditing information at <\/strong>dba_audit_trail<\/code>, which is a table view sys.aud$<\/code>.<\/p>\n\n\n\n
Making the selection in the view we will see information such as from which username it was made, from which terminal, what time, in which object and exactly what sql query was run:<\/p>\n\n\n\n
--view\nselect username,os_username,userhost,terminal,timestamp,owner,obj_name,action_name,SQL_TEXT,SQL_BIND from dba_audit_trail; \n\n--table select * from sys.aud$;<\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Audit a specific user<\/h3>\n\n\n\n
We can respectively activate auditing only for a specific user and for specific actions such as select, insert, update, delete:<\/p>\n\n\n\n
audit select table,update table,insert table, delete table,execute procedure BY stratos by access;\n\n--noaudit select table,update table,insert table, delete table,execute procedure BY stratos;<\/pre>\n\n\n\n
Running select on the view dba_stmt_audit_opts <\/code>we'll see exactly what we set auditing to record:<\/p>\n\n\n\n
select * from dba_stmt_audit_opts where user_name='STRATOS';<\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
Making an update with this user\u2026:<\/p>\n\n\n\n
update scott.dept set LOC='FLORIDA' where LOC='BOSTON';\ncommit;<\/pre>\n\n\n\n
We will see that he also recorded this in the dba_audit_trail <\/code>view.<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
In case we have enabled auditing to record it CREATE SESSION<\/code> then we can see what time each user connected with the following query:<\/p>\n\n\n\n
--audit CREATE SESSION by STRATOS by session;\n\nselect * from dba_audit_session order by timestamp desc;<\/pre>\n\n\n\n
Audit on specific actions<\/h3>\n\n\n\n
Accordingly, we can set auditing to be activated in specific actions such as select, insert, update, delete:<\/p>\n\n\n\n
audit select table,update table,insert table, delete table,execute procedure by access;\n\n--noaudit select table,update table,insert table, delete table,execute procedure;\n<\/pre>\n\n\n\n
Running select on the view dba_stmt_audit_opts <\/code>we will see what we set to record with the username this time empty:<\/p>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Audit on what object is made in the future<\/h3>\n\n\n\n
If we want to activate auditing on specific actions, in any new object we create, we add the parameter on default<\/strong>, as below:<\/p>\n\n\n\n
audit select,insert,update,delete,alter,execute on default by access;\n\n--noaudit select,insert,update,delete,alter,execute on default;<\/pre>\n\n\n\n
So once we make a table and add a record like here:<\/p>\n\n\n\n
create table stratos.stratos (id int);\ninsert into stratos.stratos values (1);\ncommit;<\/pre>\n\n\n\n
It will be added to the view dba_obj_audit_opts<\/code>:<\/p>\n\n\n\n
select * from dba_stmt_audit_opts;<\/pre>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
And accordingly it will be recorded in the view dba_audit_trail<\/code>:<\/p>\n\n\n\n
select username,os_username,userhost,terminal,timestamp,owner,obj_name,action_name,SQL_TEXT,SQL_BIND from dba_audit_trail; <\/pre>\n\n\n\n
\"\"
07<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Audit everywhere<\/h3>\n\n\n\n
If we want to have audit enabled on everything, we run the following:<\/p>\n\n\n\n
audit all by access;\n\n--noaudit all;<\/pre>\n\n\n\n
If we then run select on the view dba_stmt_audit_opts<\/code>, we will see that it will contain all the existing actions:<\/p>\n\n\n\n
select * from dba_stmt_audit_opts;<\/pre>\n\n\n\n
\"\"
08<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
How we maintain auditing<\/h2>\n\n\n\n
Because these records will take up a lot of space in system tablespaces, we should periodically delete old records from the table sys.AUD$<\/code> which the view sees dba_audit_trail<\/code>.<\/p>\n\n\n\n
First of all, we must have done an init cleanup in which we define that the audits records are transferred from the schema SYSTEM <\/code>in the SYSAUX<\/code>:<\/p>\n\n\n\n
BEGIN\nDBMS_AUDIT_MGMT.INIT_CLEANUP(\n              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,\n      default_cleanup_interval   => 12 \/* hours *\/);\nEND;<\/pre>\n\n\n\n
Then we check that it has been activated:<\/p>\n\n\n\n
SET SERVEROUTPUT ON\nBEGIN\nIF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN\nDBMS_OUTPUT.put_line('YES');\nELSE\nDBMS_OUTPUT.put_line('NO');\nEND IF;\nEND;\n\/<\/pre>\n\n\n\n
To delete the old manual entries we run the following:<\/p>\n\n\n\n
alter table sys.AUD$ nologging;\ndelete from sys.aud$ \nwhere TIMESTAMP# <= sysdate-30;\ncommit;<\/pre>\n\n\n\n
If we want to create a job that will delete records older than 30 days automatically, we run the following:<\/p>\n\n\n\n
BEGIN\nDBMS_SCHEDULER.create_job (\njob_name => 'JOB_PURGE_AUDIT_RECORDS',\njob_type => 'PLSQL_BLOCK',\njob_action => 'BEGIN \nDBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30); \nDBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(\naudit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,\nuse_last_arch_timestamp  =>  TRUE); \nEND;',\nstart_date => SYSTIMESTAMP,\nrepeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',\nend_date => NULL,\nenabled => TRUE,\ncomments => 'JOB_PURGE_AUDIT_RECORDS');\nEND;\n\/<\/pre>\n\n\n\n
To see when the job ran last and when it will run again, run the following:<\/p>\n\n\n\n
select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name= 'JOB_PURGE_AUDIT_RECORDS';<\/pre>\n\n\n\n
How do I store the audit trail files in a separate tablespace<\/h2>\n\n\n\n
So that we don't fill up SYS \/ SYSTEM \/ SYSAUX <\/code>tablespace we have the possibility to create a new tablespace and define in it that the auditing is stored.<\/p>\n\n\n\n
To do this we first create this new tablespace:<\/p>\n\n\n\n
create tablespace AUDIT_TS datafile '\/oracle\/oradata\/orcl\/audit_ts_001.dbf' size 100m autoextend on next 64m maxsize 32767m;<\/pre>\n\n\n\n
And then we run the following command putting the name of the tablespace we just created:<\/p>\n\n\n\n
BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_TS');\nEND;\n\/<\/pre>\n\n\n\n
So by running the following query we can confirm that the table AUD$<\/code> belongs to the new tablespace:<\/p>\n\n\n\n
select * from dba_tables where table_name ='AUD$';<\/pre>\n\n\n\n
We reset the init cleanup to make sure that the records are transferred to the new tablespace:<\/p>\n\n\n\n
BEGIN\nDBMS_AUDIT_MGMT.DEINIT_CLEANUP(\n  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);\nEND;\n\nBEGIN\nDBMS_AUDIT_MGMT.INIT_CLEANUP(\n              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,\n      default_cleanup_interval   => 12 \/* hours *\/);\nEND;\n\nSET SERVEROUTPUT ON\nBEGIN\nIF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN\nDBMS_OUTPUT.put_line('YES');\nELSE\nDBMS_OUTPUT.put_line('NO');\nEND IF;\nEND;\n\/<\/pre>\n\n\n\n
In case of ORA-01403 error<\/h4>\n\n\n\n
In case we have upgraded from an older version, the tablespace change command may show us the following error:<\/p>\n\n\n\n
ORA-01403: no data found<\/code><\/pre>\n\n\n\n
In this case we check in which schema the table is AUD$<\/code>:<\/p>\n\n\n\n
select * from dba_tables where table_name ='AUD$';<\/pre>\n\n\n\n
If it is indeed in a different e.g. in the SYSTEM <\/code>instead of SYS<\/code> then we should keep a backup of the table DDL:<\/p>\n\n\n\n
select dbms_metadata.get_ddl('TABLE', 'AUD$','SYSTEM') from dual;<\/pre>\n\n\n\n
Then we delete the synonym and the table and rebuild them with the DDL we kept in the schema SYS<\/code>:<\/p>\n\n\n\n
drop table system.aud$\ndrop synonym sys.aud$;\n \n  CREATE TABLE \"SYS\".\"AUD$\" \n   (  \"SESSIONID\" NUMBER NOT NULL ENABLE, \n  \"ENTRYID\" NUMBER NOT NULL ENABLE, \n  \"STATEMENT\" NUMBER NOT NULL ENABLE, \n  \"TIMESTAMP#\" DATE, \n  \"USERID\" VARCHAR2(128), \n  \"USERHOST\" VARCHAR2(128), \n  \"TERMINAL\" VARCHAR2(255), \n  \"ACTION#\" NUMBER NOT NULL ENABLE, \n  \"RETURNCODE\" NUMBER NOT NULL ENABLE, \n  \"OBJ$CREATOR\" VARCHAR2(128), \n  \"OBJ$NAME\" VARCHAR2(128), \n  \"AUTH$PRIVILEGES\" VARCHAR2(16), \n  \"AUTH$GRANTEE\" VARCHAR2(128), \n  \"NEW$OWNER\" VARCHAR2(128), \n  \"NEW$NAME\" VARCHAR2(128), \n  \"SES$ACTIONS\" VARCHAR2(19), \n  \"SES$TID\" NUMBER, \n  \"LOGOFF$LREAD\" NUMBER, \n  \"LOGOFF$PREAD\" NUMBER, \n  \"LOGOFF$LWRITE\" NUMBER, \n  \"LOGOFF$DEAD\" NUMBER, \n  \"LOGOFF$TIME\" DATE, \n  \"COMMENT$TEXT\" VARCHAR2(4000), \n  \"CLIENTID\" VARCHAR2(128), \n  \"SPARE1\" VARCHAR2(255), \n  \"SPARE2\" NUMBER, \n  \"OBJ$LABEL\" RAW(255), \n  \"SES$LABEL\" RAW(255), \n  \"PRIV$USED\" NUMBER, \n  \"SESSIONCPU\" NUMBER, \n  \"NTIMESTAMP#\" TIMESTAMP (6), \n  \"PROXY$SID\" NUMBER, \n  \"USER$GUID\" VARCHAR2(32), \n  \"INSTANCE#\" NUMBER, \n  \"PROCESS#\" VARCHAR2(16), \n  \"XID\" RAW(8), \n  \"AUDITID\" VARCHAR2(64), \n  \"SCN\" NUMBER, \n  \"DBID\" NUMBER, \n  \"SQLBIND\" CLOB, \n  \"SQLTEXT\" CLOB, \n  \"OBJ$EDITION\" VARCHAR2(128), \n  \"RLS$INFO\" CLOB, \n  \"CURRENT_USER\" VARCHAR2(128)\n   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 \nNOCOMPRESS LOGGING\n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n  TABLESPACE \"SYSTEM\" \nLOB (\"SQLBIND\") STORE AS BASICFILE (\n  TABLESPACE \"SYSTEM\" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION \n  NOCACHE LOGGING \n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) \nLOB (\"SQLTEXT\") STORE AS BASICFILE (\n  TABLESPACE \"SYSTEM\" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION \n  NOCACHE LOGGING \n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) \nLOB (\"RLS$INFO\") STORE AS BASICFILE (\n  TABLESPACE \"SYSTEM\" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION \n  NOCACHE LOGGING \n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))<\/pre>\n\n\n\n
We give the right of delete to delete_catalog_role<\/code> and retry the change tablespace command:<\/p>\n\n\n\n
GRANT DELETE ON AUD$ TO DELETE_CATALOG_ROLE;\n\nBEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_TS');\nEND;\n\/<\/pre>\n\n\n\n
We reset the init cleanup to make sure that the records are transferred to the new tablespace:<\/p>\n\n\n\n
BEGIN\nDBMS_AUDIT_MGMT.DEINIT_CLEANUP(\n  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);\nEND;\n\nBEGIN\nDBMS_AUDIT_MGMT.INIT_CLEANUP(\n              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,\n      default_cleanup_interval   => 12 \/* hours *\/);\nEND;\n\nSET SERVEROUTPUT ON\nBEGIN\nIF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN\nDBMS_OUTPUT.put_line('YES');\nELSE\nDBMS_OUTPUT.put_line('NO');\nEND IF;\nEND;\n\/<\/pre>\n\n\n\n
By doing all the above steps correctly, the change of the tablespace that we see in the following view should have progressed normally:<\/p>\n\n\n\n
select * from dba_tables where table_name ='AUD$';<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n