Virtual Private Database VPD to hide/secure table column data | Not Encryption

CREATE OR REPLACE

FUNCTION FN_LDVPD (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)

RETURN VARCHAR2

AS

BEGIN

RETURN 'rowid = ''0''';

END FN_LDVPD;

/

 

begin

      dbms_rls.drop_policy (

          object_schema     => 'LDBO',

          object_name      => 'ACCOUNTADDRESSDETAIL',

          policy_name      => 'LD_PAN_POLICY'

      );

END;

/

 

 

SELECT OOWNCODE,PANGIR FROM ACCOUNTADDRESSDETAIL WHERE TRIM(PANGIR) IS NOT NULL;

 

BEGIN

DBMS_RLS.ADD_POLICY(object_schema=> 'LDBO',

object_name=> 'ACCOUNTADDRESSDETAIL',

policy_name=> 'LD_PAN_POLICY',

function_schema=> 'LDBO',

policy_function=> 'FN_LDVPD',

statement_types   => 'SELECT',

sec_relevant_cols=> 'PANGIR',

policy_type => DBMS_RLS.SHARED_STATIC,

sec_relevant_cols_opt=> dbms_rls.ALL_ROWS);

END;

/

 

SELECT OOWNCODE,PANGIR FROM ACCOUNTADDRESSDETAIL WHERE TRIM(PANGIR) IS NOT NULL;