OraFAQ Forum: Security » Column Masking on Table

Home » RDBMS Server » Security » Column Masking on Table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Column Masking on Table [message #162399]

Fri, 10 March 2006 01:11

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..

Report message to a moderator

Re: Column Masking on Table [message #162442 is a reply to message #162399]

Fri, 10 March 2006 04:22

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.

Report message to a moderator

Re: Column Masking on Table [message #162443 is a reply to message #162442]

Fri, 10 March 2006 04:28

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...

Report message to a moderator

Re: Column Masking on Table [message #162468 is a reply to message #162443]

Fri, 10 March 2006 06:56

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Previous Topic:	user
Next Topic:	Security and XA permissions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 04 08:04:06 CDT 2025