|
1 |
alter system set audit_trail=TRUE scope=spfile; |
|
1 |
AUDIT_TRAIL = < none | os | db | db,extended | xml | xml,extended >; |
AUDIT_TRAIL Seçenekleri
*none or false – Audit kapalı. (default ta kapalıdır)
*db or true – Audit aktif hale getirilir. kayıtlar (SYS.AUD$) tablosuna yazılır
*db,extended – sys.aud$ ve SQL_TEXT e yazılır.
*xml- Auditing işletim sistemi üzerindeki xml dosyalarına yazılır.
*xml,extended – xml dosyalarına ve sql_text e yazılır.
*os- işletim sisteminde AUDIT_FILE_DEST parametresine set edilen yere .aud uzantılı dosyalara yazar.
AUDIT_FILE_DEST parametresi os level auditler için ayarlanmış olması gerekir.
Obje bazında izlenebilecek statement lar ; alter, audit, comment, delete, flashback, grant, index, insert, lock, rename, select, update
|
1 2 3 |
shutdown immediate startup |
|
1 |
AUDIT DELETE ON SCOTT.EMP1 BY ACCESS |
Scott şemasının tüm tablolarının audit edilmesi
|
1 |
select 'audit delete on SCOTT.'||table_name||' BY ACCESS ;' from user_tables |
Disable Edilmesi
|
1 |
select 'NOAUDIT delete on SCOTT.'||table_name||' ;' from user_tables ; |
Example
|
1 |
select 'audit delete on SCOTT.'||table_name||' BY ACCESS ;' from user_tables; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
'AUDITDELETEONSCOTT.'||TABLE_NAME||'BYACCESS;' ---------------------------------------------------------------- audit delete on SCOTT.DEPT BY ACCESS ; audit delete on SCOTT.EMP BY ACCESS ; audit delete on SCOTT.BONUS BY ACCESS ; audit delete on SCOTT.SALGRADE BY ACCESS ; audit delete on SCOTT.GAME_PLAYER BY ACCESS ; audit delete on SCOTT.EMP1 BY ACCESS ; |
|
1 |
delete from scott.emp1 where empno=7521; |
audit lenen işlemleri görmek için
|
1 |
SELECT "OWNER", "OBJ_NAME", "USERNAME", "ACTION_NAME", "TIMESTAMP" FROM "SYS"."DBA_AUDIT_OBJECT" |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
COLUMN username FORMAT A10 COLUMN owner FORMAT A10 COLUMN obj_name FORMAT A10 COLUMN extended_timestamp FORMAT A35 SELECT username, extended_timestamp, owner, obj_name, action_name FROM dba_audit_trail WHERE owner = 'SCOTT' ORDER BY timestamp; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
COLUMN db_user FORMAT A10 COLUMN object_schema FORMAT A10 COLUMN object_name FORMAT A10 COLUMN extended_timestamp FORMAT A35 SELECT db_user, extended_timestamp, object_schema, object_name, action FROM v$xml_audit_trail WHERE object_schema = 'SCOTT' ORDER BY extended_timestamp; |
örnek ||
|
1 |
select 'test on test '||table_name||' ' from user_tables |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA%AUDIT%' ORDER BY view_name; VIEW_NAME ------------------------------ DBA_AUDIT_EXISTS DBA_AUDIT_OBJECT DBA_AUDIT_POLICIES DBA_AUDIT_POLICY_COLUMNS DBA_AUDIT_SESSION DBA_AUDIT_STATEMENT DBA_AUDIT_TRAIL DBA_COMMON_AUDIT_TRAIL DBA_FGA_AUDIT_TRAIL DBA_OBJ_AUDIT_OPTS DBA_PRIV_AUDIT_OPTS DBA_REPAUDIT_ATTRIBUTE DBA_REPAUDIT_COLUMN DBA_STMT_AUDIT_OPTS |
örnek ||
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE USER omer IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT connect TO omer; GRANT create table, create procedure TO omer; |
|
1 |
AUDIT ALL BY OMER BY ACCESS; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE heybe ( id NUMBER ); INSERT INTO heybe (id) VALUES (1); UPDATE heybe SET id = id; SELECT * FROM heybe; DELETE FROM heybe; DROP TABLE heybe; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT username, extended_timestamp, owner, obj_name, action_name FROM dba_audit_trail where owner='omer' ORDER BY timestamp desc; |
|
1 2 3 4 5 |
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME OMER 07.06.2011 15:34 OMER HEYBE DROP TABLE OMER 07.06.2011 15:34 OMER HEYBE CREATE TABLE |
Veri tabanı yöneticilerinin izlenmesi
|
1 |
alter system set AUDIT_SYS_OPERATIONS = TRUE scope=spfile; |
|
1 2 3 |
sqlplus / as sysdba ALTER SYSTEM FLUSH SHARED_POOL; |
|
1 2 3 4 5 6 7 |
update scott.emp1 set sal=3333 where empno=7654 ; 1 row updated. commit; Commit complete. |
|
1 |
cat /u01/app/oracle/admin/orcl/adump/ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Mon Jun 6 14:31:46 2011 LENGTH : '198' ACTION :[30] 'ALTER SYSTEM FLUSH SHARED_POOL' DATABASE USER:[3] 'sys' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[11] 'mdervisoglu' CLIENT TERMINAL:[10] 'IST-PCD068' STATUS:[1] '0' DBID:[10] '1257644776' ----------------------------- Mon Jun 6 14:31:49 2011 LENGTH : '216' ACTION :[48] 'update scott.emp1 set sal=3333 where empno=7654 ' DATABASE USER:[3] 'sys' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[11] 'mdervisoglu' CLIENT TERMINAL:[10] 'IST-PCD068' STATUS:[1] '0' DBID:[10] '1257644776' |
Kayıtları silmek
|
1 |
DELETE FROM SYS.AUD$; |
Tek bir kullanıcıyı izlemek
|
1 2 3 |
AUDIT SESSION BY omer; |
|
1 |
SELECT "USERNAME", "OS_USERNAME", "USERHOST", "EXTENDED_TIMESTAMP" FROM "SYS"."DBA_AUDIT_SESSION" WHERE returncode != 0 |
|
1 |
SELECT "PRIV_USED", "USERNAME", "ACTION_NAME", "TIMESTAMP" FROM SYS.DBA_AUDIT_TRAIL dat, SYS.DBA_PRIV_AUDIT_OPTS dpao WHERE dat.PRIV_USED = dpao.PRIVILEGE and to_char(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS') |
Kullanıcının yaptığı işlemleri görme
|
1 2 3 4 5 |
SELECT "USERNAME", "ACTION_NAME", "OWNER", "OBJ_NAME", "TIMESTAMP", "SQL_TEXT", "SESSIONID", "USERHOST", "TERMINAL", "OS_USERNAME"& FROM "SYS"."DBA_AUDIT_OBJECT"& Where username = 'USER_NAME' and owner = 'SCHEMA_OWNER' |
|
1 2 3 4 |
SELECT * FROM "SYS"."DBA_AUDIT_OBJECT" Where username = 'USER_NAME' and owner = 'SCHEMA_NAME' |
Kullanıcının Çalıştırdığı SQL cümlelerini görme
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT * FROM V_$SQLAREA WHERE FIRST_LOAD_TIME BETWEEN '2007-12-28/09:00:00' AND '2007-12-28/09:18:00' AND PARSING_SCHEMA_NAME = 'USER_NAME' |
VT’ ye yapılan veri girişlerini görmek için
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
select "OS_USERNAME","USERNAME","USERHOST","TERMINAL","TIMESTAMP","ACTION_NAME","LOGOFF_TIME","LOGOFF_LREAD","LOGOFF_PREAD","LOGOFF_LWRITE","LOGOFF_DLOCK","SESSIONID","RETURNCODE", CASE WHEN TO_CHAR(RETURNCODE) = '1017' THEN 'ORA-01017: invalid username password logon denied' WHEN TO_CHAR(RETURNCODE) = '28000' THEN 'ORA-28000: the account is locked' WHEN TO_CHAR(RETURNCODE) = '28009' THEN 'ORA-28009: connection as sys should be as sysdba or sysoper' WHEN TO_CHAR(RETURNCODE) = '604' THEN 'ORA-00604: error occurred at recursive sql level' ELSE TO_CHAR(RETURNCODE) END "RETURNMESSAGE", "CLIENT_ID","SESSION_CPU","EXTENDED_TIMESTAMP","PROXY_SESSIONID","GLOBAL_UID","INSTANCE_NUMBER","OS_PROCESS" from dba_audit_session where returncode > 0; |
Export-DataPumb – script
12 Mart 2019