LaGiPusHinGWitH ---- IT

Navigation

Home |

Categories

• Computer Hacking
• Computer Diary
• Computer DataBase
• Computer Propaganda
• Computer Prophile
• Computer Security
• Computer Unix

Monthly Archives

• September 2010
• October 2009
• December 2008
• November 2008
• October 2008
• September 2008
• July 2008
• May 2008
• March 2008
• December 2007
• October 2007
• September 2007
• August 2007

Most recent entries

• Create Oracle Tablespace on SAP with BRTools
• Audit on Oracle Database
• Membuat Oracle Data Guard
• Cerita Tentang Kamu
• Membuat ulang/re-create controlfile di oracle
• Membuat RMAN dengan crontab
• Saat kau hadir kembali
• Memindahkan controlfile dan redologfile pada Oracle
• Oracle Dataguard on Standart Edition
• Adakah aku dihatimu
• Instalasi 10g di HP-UX Itanium
• Instalasi OpenOffice di Slackware 12
• Syncronize archive log
• Merubah ukuran archived Log
• Alter Block Size for tablespace

Syndicate

• RSS 2.0
• Atom

Site Credits

Powered by:
ExpressionEngine

Design by:
BlogMoxie

Kali ini saya membuat artikel untuk melakukan audit pada oracle, audit ini menggunakan feature yang sudah ada pada oracle dengan mengaktifkan feature audit.

Oracle menyediakan aplikasi untuk management audit ini yaitu Audit Vault Server, tapi lisensi nya mahal ... sekitar 52 ribu US dollar (tentu saja dengan banyak kelebihan2nya dan cocok untuk enterprise berskala besar).

Disini saya hanya memanfaatkan feature2 yang sudah ada pada oracle dan menampilkan hasil auditnya dengan sangat sederhana..tapi cukup lumayan untuk mengetahui aktifitas yang terjadi pada oracle..

-- Audit Trail not yet activated

SQL> select * from sys.aud$;

no rows selected

-- Activated audit trail

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1218556 bytes
Variable Size 62916612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

-- Create user to audit

SQL> CREATE USER audit_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

User created.

-- Make grant to user

SQL> GRANT connect TO audit_user;

Grant succeeded.

SQL> GRANT create table, create procedure TO audit_user;

Grant succeeded.

-- Make policy for audit to user audit

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_user BY ACCESS;

Audit succeeded.

-- Testing audit

SQL> CONN audit_user/password
Connected.

SQL> CREATE TABLE user_tab (id NUMBER);

Table created.

SQL> INSERT INTO user_tab (id) VALUES (1);
1 row created.

SQL> UPDATE user_tab SET id = id;
1 row created.

SQL> SELECT * FROM user_tab;
ID
----------
1

SQL> DELETE FROM user_tab;
1 row deleted.

SQL> SELECT view_name
FROM dba_views
WHERE view_name LIKE ‘DBA&#xAU;DIT%’
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

14 rows selected.

SQL> COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

-- View audit result

SQL> SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = ‘audit_user’
ORDER BY timestamp;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- -------- -----------
audit_user 29-SEP-09 04.10.29.873811 AM +07:00 audit_user user_tab INSERT
audit_user 29-SEP-09 04.10.29.905141 AM +07:00 audit_user user_tab DELETE
audit_user 29-SEP-09 04.10.29.881962 AM +07:00 audit_user user_tab SELECT
audit_user 29-SEP-09 04.10.29.878485 AM +07:00 audit_user user_tab UPDATE

-- For Specific audit use FGA

---- FGA -----

SQL> CONN audit_user/password
Connected.
SQL> CREATE TABLE emp (
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);

Table created.

SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Tim’, 1);
1 row created.

SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Larry’, 50001);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> BEGIN
DBMS_FGA.add_policy(
object_schema => ‘audit_user’,
object_name => ‘EMP’,
policy_name => ‘SALARY_CHK_AUDIT’,
audit_condition => ‘SAL > 50000’,
audit_column => ‘SAL’);
END;
/
PL/SQL procedure successfully completed.

SQL> CONN audit_user/password
Connected.
SQL> SELECT sal FROM emp WHERE ename = ‘Tim’;

SAL
----------
1

SQL> SELECT sal FROM emp WHERE ename = ‘Larry’;

SAL
----------
50001

SQL> SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------------------------------------------------
SELECT sal FROM emp WHERE ename = ‘Larry’

SQL> select db_user,sql_text from dba_fga_audit_trail;

DB_USER SQL_TEXT
------- --------
audit_user SELECT sal FROM emp WHERE ename = ‘Larry’

-- Clear down the audit trail

SQL> TRUNCATE TABLE fga_log$;

Table truncated.

SQL> SELECT sql_text FROM dba_fga_audit_trail;

no rows selected

-- Apply the policy to the SAL column of the EMP table.

SQL> BEGIN
DBMS_FGA.add_policy(
object_schema => ‘audit_user’,
object_name => ‘EMP’,
policy_name => ‘SAL_AUDIT’,
audit_condition => NULL,—Equivalent to TRUE
audit_column => ‘SAL’,
statement_types => ‘SELECT,INSERT,UPDATE,DELETE’);
END;
/

PL/SQL procedure successfully completed.

-- Test the auditing.

SQL> CONN audit_user/password
Connected.
SQL> SELECT * FROM emp WHERE empno = 9998;

no rows selected

SQL> INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1);

1 row created.

SQL> UPDATE emp SET sal = 10 WHERE empno = 9998;

1 row updated.

SQL> DELETE emp WHERE empno = 9998;

1 row deleted.

SQL> ROLLBACK;

Rollback complete.

-- Check the audit trail as sysdba.

SQL> SELECT db_user,sql_text FROM dba_fga_audit_trail;

DB_USER SQL_TEXT
---------- ------------------------------------------------------------------------
audit_user SELECT * FROM emp WHERE empno = 9998
audit_user INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1)
audit_user UPDATE emp SET sal = 10 WHERE empno = 9998
audit_user DELETE emp WHERE empno = 9998

-- Drop the policy.
SQL> BEGIN
DBMS_FGA.drop_policy(
object_schema => ‘audit_user’,
object_name => ‘EMP’,
policy_name => ‘SAL_AUDIT’);
END;
/

PL/SQL procedure successfully completed.