Role-based Sensitive Column Masking by VPD (ORA-28112) [message #649011] |
Wed, 09 March 2016 13:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/cb82382addbe0ae952a7d135c0c4ac1c?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 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.
|
|
|
|
|