Home » RDBMS Server » Security » Role-based Sensitive Column Masking by VPD (ORA-28112) (Oracle 11g)
Role-based Sensitive Column Masking by VPD (ORA-28112) [message #649011] Wed, 09 March 2016 13:23 Go to next message
rajsinha
Messages: 4
Registered: February 2016
Junior Member
Hi,

I have a requirement to mask sensitive columns in a table/view based on the current enabled Role of the user. If the user has the role SECURITY_SSN only then will he be allowed to view the ssn column in the view V_EMPLOYEE. Any user who does not have the role SECURITY_SSN must not see the values in the ssn column of the view.
I tried achieving the same with VPD but it fails with the error - ORA-28112.

CREATE TABLE HR.EMPLOYEE 
(
	EID VARCHAR(10), 
	FNAME VARCHAR(50), 
	LNAME VARCHAR(50), 
	SSN VARCHAR(9)
);

INSERT INTO HR.EMPLOYEE VALUES ('123456', 'JOHN', 'DOE', '487328967');
INSERT INTO HR.EMPLOYEE VALUES ('928347', 'PETER', 'FOSTER', '472983398');

CREATE ROLE SECURITY_SSN;

GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEE TO SECURITY_SSN;
GRANT SECURITY_SSN, CREATE SESSION TO HR;

CREATE OR REPLACE VIEW HR.V_EMPLOYEE
AS
SELECT * FROM HR.EMPLOYEE;

create or replace
function SYS.APPLY_SSN( p_owner in varchar2, p_name in 
varchar2 ) return varchar2
is

sqltext VARCHAR2(200):= 'SELECT 1 FROM DUAL WHERE ''SECURITY_SSN'' in (SELECT ROLE FROM SESSION_ROLES)';

FLG NUMBER;

                        begin
                        EXECUTE IMMEDIATE sqltext INTO FLG;
                        
                        if FLG = 1
                        then
                                          RETURN NULL;
                        ELSE  
                                          RETURN '1=0';
                        END IF;
END;
/

BEGIN 
DBMS_RLS.ADD_POLICY(	object_schema=>'HR', 
			object_name=>'V_EMPLOYEE',
                        policy_name=>'MASK_SSN',
                        function_schema=>'SYS',
                        policy_function=>'APPLY_SSN',
                        sec_relevant_cols=>'SSN',
                        sec_relevant_cols_opt=>dbms_rls.ALL_ROWS
			);
END;

COMMIT;

However, the command -
SELECT * FROM HR.EMPLOYEE;
fails with the error -
ORA-28112 : FAILED TO EXECUTE POLICY FUNCTION.The policy function has one or more error(s) during execution.

I am very new to Oracle. Can anyone please help me sort this out.

Thanks.
Re: Role-based Sensitive Column Masking by VPD (ORA-28112) [message #649014 is a reply to message #649011] Wed, 09 March 2016 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need any VPD:
SQL> create table t (
  2     EID VARCHAR(10),
  3     FNAME VARCHAR(50),
  4     LNAME VARCHAR(50),
  5     SSN VARCHAR(9)
  6  );

Table created.

SQL> CREATE ROLE SECURITY_SSN;

Role created.

SQL> grant SECURITY_SSN to michel;

Grant succeeded.

SQL> create or replace view v as
  2  select eid, fname, lname,
  3         decode(role, null,null, ssn) ssn
  4  from t left outer join
  5       (select role from session_roles where role='SECURITY_SSN')
  6       on 1 = 1
  7  /

View created.

SQL> show user
USER is "MICHEL"
SQL> set role none
  2  ;

Role set.

SQL> select * from v;
EID        FNAME      LNAME      SSN
---------- ---------- ---------- ---------
123456     JOHN       DOE
928347     PETER      FOSTER

2 rows selected.

SQL> set role SECURITY_SSN;

Role set.

SQL> select * from v;
EID        FNAME      LNAME      SSN
---------- ---------- ---------- ---------
123456     JOHN       DOE        487328967
928347     PETER      FOSTER     472983398

2 rows selected.

Re: Role-based Sensitive Column Masking by VPD (ORA-28112) [message #649015 is a reply to message #649011] Wed, 09 March 2016 14:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I ran all your code (an excellent test case) and I get no errors:
orclz> SELECT * FROM HR.EMPLOYEE;

EID        FNAME
---------- --------------------------------------------------
LNAME                                              SSN
-------------------------------------------------- ---------
123456     JOHN
DOE                                                487328967

928347     PETER
FOSTER                                             472983398


orclz>
so I can;t suggest anything other than do it all again.
Re: Role-based Sensitive Column Masking by VPD (ORA-28112) [message #649016 is a reply to message #649015] Wed, 09 March 2016 15:46 Go to previous messageGo to next message
rajsinha
Messages: 4
Registered: February 2016
Junior Member
@Michel & @John,

Thanks for your response. Your solution will work fine. However, I still want to know why my approach (using VPD) isn't working. Is there anything wrong with the function ?

Thanks.
Re: Role-based Sensitive Column Masking by VPD (ORA-28112) [message #649022 is a reply to message #649016] Thu, 10 March 2016 01:07 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As John said (I did not verify it but I believe him), your approach does not raise an error.
So follow his advice and do it all again (and post it as I did it).

Previous Topic: Grant ROLE to user takes longer time
Next Topic: ORA-01017: invalid username/password; logon denied
Goto Forum:
  


Current Time: Thu Mar 28 15:02:54 CDT 2024