Label Security

1
Label Security DB Auditfor Oracle 9i

• DATABASE SECURITY
• ?????? 032ISI01
• ? ? ?

2
? ?

• Label Security
• ? What is Oracle9i Label Security?
• ? Label Security Access Mediation
• ? Security Label Components
• ? Oracle 9i Label Security Implementation
• ? SQLPLUS
• ? POLICY_MANAGER
• AUDITING
• ? AUDITING ???
• ? AUDITING ? ??
• ? INFORMIX AUDITING
• References

3
What is Oracle9i Label Security?

• Oracle9i Label Security leverages the Oracle9i
  Enterprise Edition virtual private database (VPD)
  security features to provide a comprehensive,
  highly customizable, out-of-the-box solution for
  row level security. 
• Oracle9i Label Security is a security option for
  the Oracle9i Enterprise Edition and dramatically
  reduces the need to isolate information, build
  complex application code, and rely on manual or
  physical controls to protect your data. 
• Oracle9i Label Security mediates access using
  sensitivity labels assigned to individual table
  rows combined with user label authorizations

4
Label Security Access Mediation
Users
Access Mediation
Data
Labels
5
Security Label Components
define the areas to which data access is
restricted
define who is the owner of the data and provides
define increasing data sensitivity
6
Oracle 9i Label Security Implementation
7
Part ISQLPLUS
8
Label Security ??
ss_test? Label ??
ss_test? Table ???
9
Part IIPOLICY MANAGER
10
Policy Manager ??

• ???? Lable

• Data Lable

11
AUDITING ???

• ??
• ???? ??? ????? ??????? ?? ????? ?? ???? ??
• ??
• AUDIT ??? initltSIDgt.ora ??? AUDIT_TRAIL ?????
  ???? DB? shutdown/startup ?? ????.
• (1) ORACLE_HOME/dbs/initSID.ora file
• AUDIT_TRAILDB lt ??
• (2) ORACLE_HOME/rdbms/admin directory ?
• cataudit.sql lt sys user? ??
• ???
• AUDIT_TRAILDB ? ?? gt SYS.AUD ???? ??? ??
  AUDIT_TRAILOS ? ?? gt Flie? ??? ??.

12
AUDITING ??

• STATEMENT AUDITING
• ??? ??? ??? AUDITING ??
• EX)AUDIT TABLE BY SCOTT BY ACCESS WHENEVER
  SUCCESSFUL
• PRIVILEGE AUDITING
• ???? PRIVILEGE ? ?? AUDITING ??
• EX)AUDIT CREATE TABLE BY SCOTT BY SESSION
• OBJECT AUDITING
• ???? OBJECT ? ?? AUDITING ??
• EX)AUDIT ALL ON SCOTT.EMP
• ??? AUDIT ??? NOAUDIT ???? ?? ? ??.
• EX)NOAUDIT ALL ON SCOTT.EMP

13
Statement Auditing

• Statement Audit ??
• SQLgt audit not exists by ss_test
• SQLgt select from dba_stmt_audit_opts
• USER_NAME AUDIT_OPTION SUCCESS
  FAILURE
• -----------------------------------------------
  ----------
• SS_TEST NOT EXISTS BY ACCESS
  BY ACCESS
• Statement Audit ??
• SQLgt grant select on not_exist_tab to wookpark
• grant select on not_exist_tab to wookpark
• ERROR at line 1
• ORA-00942 table or view does not exist
• SQLgt select os_username,username,timestamp,obj_
  name,action_name, obj_privilege, grantee
• 2 from dba_audit_statement

14
Privilege Auditing

• Privilege Audit ??
• SQLgt audit create session by ss_test
• SQLgt select from dba_priv_audit_opts
• USER_NAME PRIVILEGE SUCCESS
  FAILURE
• -----------------------------------------------
  -------
• SS_TEST CREATE SESSION BY ACCESS
  BY ACCESS
• Privilege Audit ??
• SQLgt connect ss_test/password
• SQLgt disconnect
• SQLgt connect system/manager
• SQLgt select os_username, timestamp,
  logoff_time, logoff_lread, logoff_pread
• 2 from dba_audit_session
• 3 where username SS_TEST'
• OS_USERNAME TIMESTAMP
  LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD
• -----------------------------------------------
  ----------------------------

15
Object Auditing

• SQLgt create table ss_test.aud1 (a char(1))
• SQLgt audit insert on ss_test.aud1 by session
  whenever not successful
• SQLgt select from dba_obj_audit_opts
• where owner SS_TEST' and object_name
  'AUD1'
• OWNER OBJECT_NAME
  OBJECT_TY
• -----------------------------------------------
  ----------------
• ALT AUD COM DEL GRA IND INS LOC REN
  SEL UPD REF EXE
• -----------------------------------------------
  ----------------
• SS_TEST AUD1
  TABLE
• -/- -/- -/- -/- -/- -/- -/S -/- -/-
  -/- -/- -/- -/-
• SQLgt insert into aud1 values ('5byte')
• insert into aud1 values ('5byte')
• ERROR at line 1
• ORA-01401 inserted value too large for
  column
• SQLgt insert into aud1 values ('a')
• SQLgt select username, timestamp, action_name,
  ses_actions
• 2 from dba_audit_object

16
AUDITING ??

• ??? ??? ROLLBACK ???? AUDIT ? ?? ??? ??? ??? ??
  ??.
• SYS ?? INTERNAL? ???? ??? ??? AUDIT? ??? ????
  ???.
• ???? AUDIT ??? ?? ???? ???? STARTUP, SHUTDOWN,
  CONNECT INTERNAL ? ?? ??? OS ?? ??? ??? ????.
• REMOTE DB ? ??? ???? ??? AUDIT ??? ?? ???. ???
  REMOTE DB ?? ??? ??? ??.
• ?? ??? AUDIT ? ??? ???? ???? ??? ???? ????.
• OBJECT AUDITING ? ?? ??? ??? ????. ??? ??? ????
  ???.
• BY SESSION ? ??? ??? ??? ??? ???? ???? BY ACCESS
  ? ?? ??? ??? ??? ??? ????.
• DDL? AUDIT ?? SYSTEM AUDIT, PRIVILEGE AUDIT? ???
  BY ACCESS? ????? ? ?? ??? BY SESSION ? ?????.

17
AUDITING INFORMIX (1)
onstat -g ses (????) session
RSAM total used id
user tty pid hostname threads
memory memory 225196 informix - 0
- 0 12288 11200 34
tuxedo - 17607 cstst1 1
61440 43896 33 tuxedo - 17606
cstst1 1 36864 35304
18
AUDITING INFORMIX (2)

• onstat -g sql 34
• Sess SQL Current Iso Lock
  SQL ISAM F.E.
• Id Stmt type Database Lvl Mode
  ERR ERR Vers
• 34 - ppcommon CR Not
  Wait 0 0 9.03
• Last parsed SQL statement
• update ppcommon st201 set unisys_down ? ,
  online_close ? Where rec_no 0
• ps -ef grep 17607
• tuxedo 17607 1 0 Apr 23 ? 000
  tcis01ys -g 610 -i 611 -u cstst1 -U
  /infordump/log.dir/ULOG -m

19
Trigger ? ??? DML AUDITING(1)

• CREATE OR REPLACE TRIGGER LogEmpChanges
• BEFORE INSERT OR DELETE OR UPDATE ON
  scott.emp
• FOR EACH ROW
• DECLARE
• v_ChangeType CHAR(1)
• BEGIN
• IF INSERTING THEN
• v_ChangeType 'I'
• ELSIF UPDATING THEN
• v_ChangeType 'U'
• ELSE
• v_ChangeType 'D'
• END IF

20
Trigger ? ??? DML AUDITING(2)

• INSERT INTO emp_audit
• (change_type, changed_by, timestamp,
  old_empno,
• old_ename, old_job, old_mgr,
  old_hiredate, old_sal,
• old_comm, old_deptno, new_empno,
  new_ename, new_job,
• new_mgr, new_hiredate, new_sal,
  new_comm, new_deptno)
• VALUES
• (v_ChangeType, USER, SYSDATE,
  old.empno, old.ename,
• old.job, old.mgr, old.hiredate,
  old.sal, old.comm,
• old.deptno, new.empno, new.ename,
  new.job, new.mgr,
• new.hiredate, new.sal, new.comm,
  new.deptno)
• END LogEmpChanges
• /

21
Trigger ? ??? DML AUDITING(3)

• AUDIT ?? ??
• SQLgt delete from emp where empno7934
• 1 row deleted.
• SQLgt commit
• Commit complete.
• SQLgt select from emp_audit
• OLD_EMPNO OLD_ENAME OLD_JOB OLD_MGR
  OLD_HIREDATE OLD_SAL
• ---------- ---------- --------- ----------
  ------------------- --------OLD_COMM OLD_DEPTNO
  NEW_EMPNO NEW_ENAME NEW_JOB NEW_MGR
• ---------- ---------- ---------- ----------
  --------- ----------
• NEW_HIREDATE NEW_SAL NEW_COMM
  NEW_DEPTNO CHANGED_ C TIMESTAMP
• 7934 MILLER CLERK 7782
  1982/01/23 000000 1300
• 10
• 
  SCOTT D
  2004/5/19 095441

22
References

• Oracle 9i Label Security data sheet
• Oracle 9i Label Security Controlling Access to
  Data
• Oracle 9i Label Security An Oracle White Paper
  January 202
• Security Solutions In Application Hosting
• Oracle 9i Database Administrator's Guide