8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Database Security Enhancements in Oracle Database 10g
- Virtual Private Database (VPD) Enhancements
- Fine-Grained Auditing Enhancements
- Uniform Audit Trail
- Audit Trail Contents
- DBMS_CRYPTO
Related articles.
- Security Enhancements In Oracle 9i
- Auditing in Oracle 10g Release 2
- Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2
- Virtual Private Databases (VPD)
- Oracle Label Security (OLS) in Oracle 9i
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:
STATIC
- The return value of the policy function is cached and reused repeatedly for an individual object. By definition the return value of the policy function must be static.SHARED_STATIC
- The same asSTATIC
but the resulting predicate can be applied to several objects.CONTEXT_SENSITIVE
- Used when policy is based around local application context. The result of the policy function is cached and reused. The policy function is only executed again when the value of the application context changes.SHARED_CONTEXT_SENSITIVE
- The same asCONTEXT_SENSITIVE
but the resulting predicate can be applied to several objects.DYNAMIC
- The policy function is executed for every SQL statement.
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.
DBA_AUDIT_TRAIL
- Standard auditing only (fromAUD$
).DBA_FGA_AUDIT_TRAIL
- Fine-grained auditing only (fromFGA_LOG$
).DBA_COMMON_AUDIT_TRAIL
- Both standard and fine-grained auditing.
Audit Trail Contents
Several fields have been added to both the standard and fine-grained audit trails.
EXTENDED_TIMESTAMP
- A more precise value than the exisingTIMESTAMP
column.PROXY_SESSIONID
- Proxy session serial number when an enterprise user is logging in via the proxy method.GLOBAL_UID
- Global Universal Identifier for an enterprise user.INSTANCE_NUMBER
- TheINSTANCE_NUMBER
value from the actioning instance.OS_PROCESS
- Operating system process id for the oracle process.TRANSACTIONID
- Transaction identifier for the audited transaction. This column can be used to join to theXID
column on theFLASHBACK_TRANSACTION_QUERY
view.SCN
- System change number of the query. This column can be used in flashback queries.SQL_BIND
- The values of any bind variables if any.SQL_TEXT
- The SQL statement that initiated the audit action.
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.
- Cryptographic algorithms - DES, 3DES, AES, RC4, 3DES_2KEY
- Padding forms - PKCS5, zeroes
- Block cipher chaining modes - CBC, CFB, ECB, OFB
- Cryptographic hash algorithms - MD5, SHA-1, MD4
- Keyed hash (MAC) algorithms - HMAC_MD5, HMAC_SH1
- Cryptographic pseudo-random number generator - RAW, NUMBER, BINARY_INTEGER
- Database types - RAW, CLOB, BLOB
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:
- What's New in Oracle Database Security?
- Adding Policies for Column-Level VPD
- DBMS_RLS.ADD_POLICY Procedure Policy Types
- Using SYS_CONTEXT in a Parallel Query
- New Features in Auditing
- Configuring and Administering Auditing
- DBA_AUDIT_TRAIL
- Developing Applications Using Data Encryption
- DBMS_CRYPTO
- Security Enhancements In Oracle 9i
- Auditing in Oracle 10g Release 2
- Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2
- Virtual Private Databases (VPD)
- Oracle Label Security (OLS) in Oracle 9i
Hope this helps. Regards Tim...