What if you have some confusion about some of your database users and want to trace the users or even be notified about their actions in the database or just want to warn them about probable mistakes can be made by db users. Oracle Database software has some solutions about likely cases. Auditing is one of the most used option with tracing. DDL triggers can also be used. There are also some solutions at the opsys level.
1- Logon Triggers to Trace UsersOracle database has logon triggers to run on any users login to the database. DBA can use this feature to set a trace file for the logged in user. tkprof utility can be used to examine users activity.
CREATE OR REPLACE TRIGGER SYS.TRG_SESSION_LOGON after logon on database
declare
s_username varchar2(20);
s_sessid varchar2(20);
s_stamp varchar2(20);
begin
-- obtain sid of the session
select distinct(substr(sid,1,20)) into s_sessid from v$mystat;
-- obtain username of the session
select username into s_username from v$session where sid=s_sessid;
-- create a timestamp to uniquely identify the trace file
select to_char(sysdate,'DDMMYYYY_HH24MISS') into s_stamp from dual;
-- set the tracefile size to unlimited. careful about that (!)
execute immediate 'alter session set max_dump_file_size=unlimited';
-- define an identifier to the tracefile to distinguish the trace files
execute immediate 'alter session set tracefile_identifier=' || s_username ||
'_' || s_sessid || '_' || s_stamp;
-- i will take 10046 trace level 8 to see the waits
-- (check timed_statistics init parameter!)
execute immediate 'alter session set events ''10046 trace name context forever, level 8''';
-- finally start the trace
execute immediate 'alter session set sql_trace=true';
exception
when others then
raise;
end TRG_LOGON;
You can find the trace files from user_dump_dest parameter
SQL> show parameter user_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string D:\ORACLEDB10G\ADMIN\ORCL\UDUMP
To examine the dump file tkprof utility can be used.
tkprof orcl_ora_5412_epeker_148_22052008_162752.trc tkprof.out
tkprof.out file.2- Database AuditTo enable audit option, audit_trail init parameter should be set.
select name, value from v$parameter where name = 'audit_trail';
alter system set audit_trail='DB_EXTENTED' scope=spfile;
--After changing audit_trail parameter bounce the database.
audit select table, insert table, update table, delete table by epeker;
audit session by epeker;
Now login to the database with the audited user and make some ddl.
select * from EPEKER.T_TABLE_DBA_TABLES;
insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10;
commit;
select * from EPEKER.T_TABLE_DBA_TABLES;
All the audit information should be in the SYS.AUD$ table
select
SESSIONID, USERID, USERHOST,TERMINAL, OBJ$CREATOR, OBJ$NAME, NTIMESTAMP#, SCN, SQLTEXT
from
SYS.AUD$
order by NTIMESTAMP# desc;
/*
SESSIONID USERID OBJ$CREATOR OBJ$NAME NTIMESTAMP# SCN SQLTEXT
1249 EPEKER SYS, USER$ 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER SYS, TS$ 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER SYS, SEG$ 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER SYS, X$KSPPCV 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER SYS, TAB$ 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER SYS, OBJ$ 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER SYS, DBA_TABLES 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER SYS, X$KSPPI 5/23/2008 11:45:00 AM 935996 insert into EPEKER.T_TABLE_DBA_TABLES select * from DBA_TABLES where rownum<10
1249 EPEKER EPEKER, T_TABLE_DBA_TABLES 5/23/2008 11:44:10 AM 935944 select * from T_TABLE_DBA_TABLES
1268 EPEKER 5/23/2008 12:01:56 PM
*/
You can revoke the audit by noaudit command.
noaudit select table, insert table, update table, delete table by epeker;
noaudit session by epeker;
3- DDL TriggersOracle database has many different trigger options. You have just read about "logon triggers". There is also ddl triggers can be used to disable specific users from mistakenly running dangerous ddl statements. Any user wants to execute a ddl statment (create, truncate.. etc) can be logged and the user can also be warned by the administrator. The following trigger simply disables a user to drop and/or truncate database tables.
CREATE OR REPLACE TRIGGER SYS.TRG_DDL before ddl
ON DATABASE
declare
s_sysevent varchar2(32);
s_owner varchar2(32);
s_objname varchar2(32);
s_user varchar2(32);
begin
select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, USER
into s_sysevent, s_owner, s_objname, s_user from dual;
insert into epeker.t_trg_ddl_test values (ora_sysevent);
if s_sysevent in ('DROP','TRUNCATE') then
if s_user = 'EPEKER' then
raise_application_error(-20010,'You are not authorized to drop a segment!.. ');
null;
end if;
end if;
end TRG_DDL;
4- database privilegesPrivileges is important (maybe the most important) checklist to be careful about. Privileges and Roles should be carefully assgined and also revoked from users to minimize the mistakenly made data loss in the databases.
5- opsys suggested tasks (alias, sudo, execute privileges, op.sys groups)Beside database auditing tasks, some securtiy hardening can be made in operating system side. Aliases can be used as of direct op. sys. commands for auditing and preventing mistakes by end users or application users. Some commands need root privileges, this kind of commands can be run through sudo in unix. Application users should not be in dba group and Oracle Home and datafiles should be protected with op. sys. file permissions. Grid agent like management and monitoring tools can be used to audit or proactively solve the likely problems.
