Home » RDBMS Server » Security » Column Masking on Table
Column Masking on Table [message #162399] Fri, 10 March 2006 01:11 Go to next message
anilhyd
Messages: 10
Registered: October 2005
Junior Member
We need to mask one column to group of users and allow to other users. The requirements are as below:-

Schema name - SCOTT
Table name - EMP
Users - u1,u2,u3

SQL>DESC EMP

Name Type
----------------- ------------
EMP_NAME VARCHAR2(30)
DEPT VARCHAR2(30)
ACCOUNT_NO NUMBER(9)
DATE_OF_JOINING DATE

Requirements:-
a) u1 and u2 cannot see the column Account_no
b) Only u3 can see the column Account_no
c) u1,u2,u3 shall run the queries as select * from scott.emp
and they cannot change this code (Schema name.table name)
4) For u1 and u2 if they run
select * from scott.emp - the output should be

EMP_NAME DEPT ACCOUNT_NO DATE_OF_JOINING
-----------------------------------------------------
Alex Sales 9999999999 01-Mar-2004

5) For user u3 if they run
select * from scott.emp - the output should be
EMP_NAME DEPT ACCOUNT_NO DATE_OF_JOINING
-----------------------------------------------------
Alex Sales 00480151560 01-Mar-2004

Please let me know how do we implement the above column masking. The user queries shall have schemaname.tablename and that cannot be changed.

Thanks in Advance..
Re: Column Masking on Table [message #162442 is a reply to message #162399] Fri, 10 March 2006 04:22 Go to previous messageGo to next message
aciolac
Messages: 242
Registered: February 2006
Senior Member
For hiding columns are used views. Create view that have hot show column account_no and give select privilege to u1 and u2.
Re: Column Masking on Table [message #162443 is a reply to message #162442] Fri, 10 March 2006 04:28 Go to previous messageGo to next message
anilhyd
Messages: 10
Registered: October 2005
Junior Member
Thanks for the reply.

But the current user queries cannot be changed.

All the users u1/u2 and u3 shall access the table using the query

select * from scott.emp

(Schemaname.tablename)

If we create view/synonyms,the name of the table has to be changed or we need to query without schema name.

Regards...
Re: Column Masking on Table [message #162468 is a reply to message #162443] Fri, 10 March 2006 06:56 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

EMP_NAME DEPT  ACCOUNT_NO DATE_OF_JOINING
-----------------------------------------------------
Alex     Sales 9999999999 01-Mar-2004

5) For user u3 if they run
select * from scott.emp - the output should be
EMP_NAME DEPT ACCOUNT_NO  DATE_OF_JOINING
-----------------------------------------------------
Alex    Sales 00480151560 01-Mar-2004

If i am understanding it right, you are masking the data. Not just the columns. The only way is to use FGAC / Row level security.
Previous Topic: user
Next Topic: Security and XA permissions
Goto Forum:
  


Current Time: Thu Mar 28 12:25:47 CDT 2024