Whoever Resembles a People is One of Them. He is not one of us who resembles other than us,
nor who resembles the Jews nor the Christians.(jami-at-tirmidhi-vol-5-ahadith-2695)
O you who believe (who wish to reach ALLAH C.C. before death)! Do not take my and
your enemies for friends!
And although they have denied what has come to you
from the Truth, you still love them (treating them as your friends).
If you go forth to struggle hard in My Way, seeking My Pleasure, (then why) you manifest
love to them and share with them your secrets.
And I know what you conceal and what you manifest. And whoever of you does that, then he
indeed has gone astray from the right Way. 60/Al-Mumtahanah-1
They are driving out the Messenger and yourselves from your land because you
believe in ALLAH C.C., your Lord.
Recite (read and explain to others) that which has been revealed to you of the Book
and keep up the Prayer.
Surely the Prayer keeps away from Al-Fahsh (to do what ALLAH C.C. prohibits) and Al-Munkar
(to deny what ALLAH C.C. commands).
Whoever obeys me, obeys ALLAH C.C., and whoever disobeys me, disobeys ALLAH C.C.
Whoever obeys the ruler, obeys me, and whoever disobeys the ruler, disobeys me. " (Sahih)

Audit

{code class=”brush: xml;”}alter system set audit_trail=TRUE scope=spfile;{/code}
{code class=”brush: xml;”}AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }{/code}
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
{code class=”brush: xml;”}shutdown immediate
startup{/code}
{code class=”brush: xml;”}AUDIT DELETE ON SCOTT.EMP1 BY ACCESS{/code}
Scott şemasının tüm tablolarının audit edilmesi
{code class=”brush: xml;”}select ‘audit delete on SCOTT.’||table_name||’ BY ACCESS ;’ from user_tables{/code}
Disable Edilmesi
{code class=”brush: xml;”}select ‘NOAUDIT delete on SCOTT.’||table_name||’ ;’ from user_tables ;{/code}
Örnek
{code class=”brush: xml;”}SQL> select ‘audit delete on SCOTT.’||table_name||’ BY ACCESS ;’ from user_tables;{/code}
‘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 ;
{code class=”brush: xml;”}delete from scott.emp1 where empno=7521;{/code}
audit lenen işlemleri görmek için
{code class=”brush: xml;”}SELECT “OWNER”, “OBJ_NAME”, “USERNAME”, “ACTION_NAME”, “TIMESTAMP”
FROM “SYS”.”DBA_AUDIT_OBJECT” {/code}

{code class=”brush: xml;”}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;{/code}

{code class=”brush: xml;”}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;{/code}

örnek ||
{code class=”brush: xml;”}select ‘test on test ‘||table_name||’ ‘ from user_tables{/code}
{code class=”brush: xml;”} 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{/code}

Örnek
{code class=”brush: xml;”}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;{/code}

{code class=”brush: xml;”}AUDIT ALL BY omer BY ACCESS;{/code}

{code class=”brush: xml;”}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;{/code}

{code class=”brush: xml;”}SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
where owner=’omer’
ORDER BY timestamp desc;{/code}
{code class=”brush: xml;”}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 {/code}

Veri tabanı yöneticilerinin izlenmesi
{code class=”brush: xml;”}alter system set AUDIT_SYS_OPERATIONS = TRUE scope=spfile;{/code}

{code class=”brush: xml;”}sqlplus / as sysdba
ALTER SYSTEM FLUSH SHARED_POOL;{/code}
{code class=”brush: xml;”}SQL> update scott.emp1 set sal=3333 where empno=7654 ;
1 row updated.
SQL> commit;
Commit complete.{/code}

{code class=”brush: xml;”}cat /u01/app/oracle/admin/orcl/adump/{/code}
{code class=”brush: xml;”}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’{/code}
Kayıtları silmek
{code class=”brush: xml;”}DELETE FROM SYS.AUD$;{/code}
Tek bir kullanıcıyı izlemek
{code class=”brush: xml;”}AUDIT SESSION
BY omer;{/code}
{code class=”brush: xml;”}SELECT “USERNAME”, “OS_USERNAME”, “USERHOST”, “EXTENDED_TIMESTAMP” FROM “SYS”.”DBA_AUDIT_SESSION” WHERE returncode != 0{/code}
{code class=”brush: xml;”}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’){/code}

Kullanıcının yaptığı işlemleri görme
{code class=”brush: xml;”}SELECT “USERNAME”, “ACTION_NAME”, “OWNER”, “OBJ_NAME”, “TIMESTAMP”, “SQL_TEXT”, “SESSIONID”, “USERHOST”, “TERMINAL”, “OS_USERNAME”
FROM “SYS”.”DBA_AUDIT_OBJECT”
Where username = ‘KULLANICI_ISMI’
and
owner = ‘SCHEMA_SAHIBI'{/code}
{code class=”brush: xml;”}SELECT * FROM “SYS”.”DBA_AUDIT_OBJECT”
Where username = ‘KULLANICI_ISMI’
and
owner = ‘SCHEMA_SAHIBI'{/code}

Kullanıcının Çalıştırdığı SQL cümlelerini görme
{code class=”brush: xml;”}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 = ‘KULLANICI_ISMI'{/code}

VT’ ye yapılan veri girişlerini görmek için

{code class=”brush: xml;”}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;{/code}

{jcomments on}

Copyright © 2015 Sysdba All Rights Reserved Web Designer