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;

SELECT * FROM HR.EMPLOYEE;

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.

orclz> SELECT * FROM HR.EMPLOYEE;

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

928347     PETER
FOSTER                                             472983398


orclz>