8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

Database Security Enhancements in Oracle Database 10g

Related articles.

Virtual Private Database (VPD) Enhancements

Column-Level VPD Policy

In conventional Virtual Private Database the VPD Policy is applied to the whole row. By default a Column-Level VPD Policy allows you to restrict the rows displayed only if specified columns are accessed.

CONN sys/password@db10g AS SYSDBA
GRANT EXECUTE ON dbms_rls TO scott;

CONN scott/tiger@db10g

-- Create the policy function to restrict access to SAL and COMM columns
-- if the employee is not part of the department 20.
CREATE OR REPLACE FUNCTION pf_job (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
  con VARCHAR2 (200);
BEGIN
  con := 'deptno = 20';
  RETURN (con);
END pf_job;
/

-- Apply the policy function to the table.
BEGIN
  DBMS_RLS.ADD_POLICY (object_schema     => 'scott',
                       object_name       => 'emp',
                       policy_name       => 'sp_job',
                       function_schema   => 'scott',
                       policy_function   => 'pf_job',
                       sec_relevant_cols => 'sal,comm');
END;
/

-- We see all records if SAL and COMM are not referenced
SELECT empno, ename, job FROM emp;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
.
.
      7934 MILLER     CLERK

14 rows selected.

-- Rows are restricted if SAL or COMM are referenced.
SELECT empno, ename, job, sal, comm FROM emp;

     EMPNO ENAME      JOB              SAL       COMM
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK          10000
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7876 ADAMS      CLERK           1100
      7902 FORD       ANALYST         3000

5 rows selected.

-- Remove the policy function from the table.
BEGIN
  DBMS_RLS.DROP_POLICY (object_schema     => 'scott',
                        object_name       => 'emp',
                        policy_name       => 'sp_job');
END;
/

Column Masking

Column masking behaviour is implemented by using the "sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS" parameter. This allows you to display all rows but mask the values of the specified columns for the restricted rows.

-- Using the same policy function as before.
BEGIN
  DBMS_RLS.ADD_POLICY (object_schema         => 'scott',
                       object_name           => 'emp',
                       policy_name           => 'sp_job',
                       function_schema       => 'scott',
                       policy_function       => 'pf_job',
                       sec_relevant_cols     => 'sal,comm',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

-- All rows are returned but the SAL and COMM values are only
-- shown for employees in department 20.
SELECT empno, ename, job, sal, comm FROM emp;

     EMPNO ENAME      JOB              SAL       COMM
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK          10000
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER         2975
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST         3000
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK           1100

     EMPNO ENAME      JOB              SAL       COMM
---------- ---------- --------- ---------- ----------
      7900 JAMES      CLERK
      7902 FORD       ANALYST         3000
      7934 MILLER     CLERK

14 rows selected.

-- Remove the policy function from the table.
BEGIN
  DBMS_RLS.DROP_POLICY (object_schema     => 'scott',
                        object_name       => 'emp',
                        policy_name       => 'sp_job');
END;
/

Policy Types

The correct use of policy types can increase the performance of VPD by caching the output of the policy function and applying it to subsequent queries without executing the policy function again. The POLICY_TYPE parameter of the DBMS_RLS.ADD_POLICY procedure is used to set one of the five policy types:

An example of it's usage is shown below.

BEGIN
  DBMS_RLS.ADD_POLICY (object_schema         => 'scott',
                       object_name           => 'emp',
                       policy_name           => 'sp_job',
                       function_schema       => 'scott',
                       policy_function       => 'pf_job',
                       policy_type           => DBMS_RLS.STATIC,
                       sec_relevant_cols     => 'sal,comm',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

Application Context Support for Parallel Queries

When SYS_CONTEXT is called from a SQL function within a parallel query the value of the context is passed to all the parallel execution servers (query slave processes) allowing the query to function properly.

Fine-Grained Auditing Enhancements

Fine-grained auditing now includes support for DML statements in addition to queries.

-- Clear down the audit trail.
CONN sys/password@db10g AS SYSDBA
TRUNCATE TABLE fga_log$;
SELECT sql_text FROM dba_fga_audit_trail;

no rows selected.

-- Apply the policy to the SAL columnof the EMP table.
BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'SCOTT',
    object_name     => 'EMP',
    policy_name     => 'SAL_AUDIT',
    audit_condition => NULL, -- Equivalent to TRUE
    audit_column    => 'SAL',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/

-- Test the auditing.
CONN scott/tiger@db10g
SELECT * FROM emp;
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
UPDATE emp SET sal = 10 WHERE empno = 9999;
DELETE emp WHERE empno = 9999;
ROLLBACK;

-- Check the audit trail.
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------
SELECT * FROM emp
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1)
UPDATE emp SET sal = 10 WHERE empno = 9999
DELETE emp WHERE empno = 9999

4 rows selected.

-- Drop the policy.
CONN sys/password@db10g AS SYSDBA
BEGIN
  DBMS_FGA.drop_policy(
    object_schema   => 'SCOTT',
    object_name     => 'EMP',
    policy_name     => 'SAL_AUDIT');
END;
/

Uniform Audit Trail

The DBA_COMMON_AUDIT_TRAIL view has been added to display the complete audit trail.

Audit Trail Contents

Several fields have been added to both the standard and fine-grained audit trails.

The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED initialization parameter is set.

-- Configure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;
AUDIT ALL BY scott BY ACCESS;

-- Perform an auditable action.
CONN scott/tiger
UPDATE emp SET ename = ename;

-- Check the audit trail.
SELECT sql_text FROM dba_common_audit_trail;

SQL_TEXT
----------------------------
UPDATE emp SET ename = ename

1 row selected.

-- Unconfigure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=none SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;

DBMS_CRYPTO

The DBMS_CRYPTO package is a replacement for the DBMS_OBFUSCATION_TOOLKIT package available in Oracle 8i and 9i. The new package is easier to use and contains more cryptographic algorithms.

A simple example of it's usage is.

SET SERVEROUTPUT ON
DECLARE
  l_credit_card_no    VARCHAR2(19) := '1234 5678 9012 3456';
  l_ccn_raw           RAW(128) := UTL_RAW.cast_to_raw(l_credit_card_no);
  l_key               RAW(128) := UTL_RAW.cast_to_raw('abcdefgh');

  l_encrypted_raw     RAW(2048);
  l_decrypted_raw     RAW(2048);
BEGIN
  DBMS_OUTPUT.put_line('Original  : ' || l_credit_card_no);

  l_encrypted_raw := DBMS_CRYPTO.encrypt(src => l_ccn_raw, 
                                         typ => DBMS_CRYPTO.des_cbc_pkcs5, 
                                         key => l_key);

  DBMS_OUTPUT.put_line('Encrypted : ' || RAWTOHEX(UTL_RAW.cast_to_raw(l_encrypted_raw)));

  l_decrypted_raw := DBMS_CRYPTO.decrypt(src => l_encrypted_raw, 
                                         typ => DBMS_CRYPTO.des_cbc_pkcs5, 
                                         key => l_key);

  DBMS_OUTPUT.put_line('Decrypted : ' || UTL_RAW.cast_to_varchar2(l_decrypted_raw));
END;
/
Original  : 1234 5678 9012 3456
Encrypted : 3041423134363932354234374545463631304337384433354443433736323331354244454237324635314545
Decrypted : 1234 5678 9012 3456

PL/SQL procedure successfully completed.

For further information see:

Hope this helps. Regards Tim...

Back to the Top.