Home » RDBMS Server » Security » Applying ROLE-based security to columns in a View in Oracle
icon5.gif  Applying ROLE-based security to columns in a View in Oracle [message #648302] Fri, 19 February 2016 16:50 Go to next message
rajsinha
Messages: 4
Registered: February 2016
Junior Member
Hi,
How can I create a view by applying ROLE-based security on specific columns in Oracle. I want to suppress the data in a particular column in a view based on the user ROLE. In Teradata I used CASE statements to suppress or display specific columns of a table/view. Here is an example in Teradata -

CREATE VIEW MYVIEWDB.EMP
AS
SELECT EMPID,
CASE WHEN ROLE = 'EMP_SENSITIVE' THEN EMP_NAME ELSE 'XXXXXX' END AS ENAME,
CASE WHEN ROLE = 'EMP_SENSITIVE' THEN EMP_SSN ELSE 'XXXXXX' END AS SSN,
AGE
FROM MYTABDB.EMPLOYEE;


So only the users having ROLE = EMP_SENSITIVE will be able to see actual data in ENAME and SSN columns when used in a query. Else, 'XXXXXX' will be displayed.

SELECT * FROM MYVIEWDB.EMP;


How can I simulate the same in Oracle views based on ROLE ? Please advise.
Thanks
Re: Applying ROLE-based security to columns in a View in Oracle [message #648303 is a reply to message #648302] Fri, 19 February 2016 17:14 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

For example,
orclz>
orclz> select sys_context('sys_session_roles','DBA') from dual;

SYS_CONTEXT('SYS_SESSION_ROLES
------------------------------
TRUE

orclz> set role none;

Role set.

orclz> select sys_context('sys_session_roles','DBA') from dual;

SYS_CONTEXT('SYS_SESSION_ROLES
------------------------------
FALSE

orclz>

Re: Applying ROLE-based security to columns in a View in Oracle [message #648358 is a reply to message #648303] Mon, 22 February 2016 03:22 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

A secure solution is Fine-Grained Access Control or Virtual Private Database (VPD). But be sure you have the license for it.
Re: Applying ROLE-based security to columns in a View in Oracle [message #648364 is a reply to message #648358] Mon, 22 February 2016 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65210
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do not use FGAC or VPD when you can do it with a simple view which is as safe as the former and requires no license.

Re: Applying ROLE-based security to columns in a View in Oracle [message #655571 is a reply to message #648364] Fri, 02 September 2016 15:47 Go to previous messageGo to next message
Bill B
Messages: 1697
Registered: December 2004
Senior Member
As long as the user can not directly access the table that the view uses then you are fine. However FGAC OR VPD is a much more robust method, however they are ONLY available in Enterprise edition and they do cost extra. If your a small shop then use views and block access to the base tables except from the view. If you are a large shop then spend the money and do it right.
Re: Applying ROLE-based security to columns in a View in Oracle [message #655575 is a reply to message #655571] Sat, 03 September 2016 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65210
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How VPD is more robust than a view?

Re: Applying ROLE-based security to columns in a View in Oracle [message #656356 is a reply to message #655575] Mon, 03 October 2016 15:53 Go to previous messageGo to next message
Bill B
Messages: 1697
Registered: December 2004
Senior Member
with a view, a simple select from dba/all/user_views will return you the base tables being selected so you can query the data directly.
Re: Applying ROLE-based security to columns in a View in Oracle [message #656363 is a reply to message #656356] Tue, 04 October 2016 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65210
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you have the privileges ONLY on the view you cannot query the base tables.

Re: Applying ROLE-based security to columns in a View in Oracle [message #656412 is a reply to message #648302] Wed, 05 October 2016 07:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
-- users, role, and privileges:
SCOTT@orcl_12.1.0.2.0> create user MYTABDB identified by MYTABDB quota 1m on users
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> grant create session, create table to MYTABDB
  2  /

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> create user MYVIEWDB identified by MYVIEWDB
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> grant create session, create view to MYVIEWDB
  2  /

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> create user USER1 identified by USER1
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> create user USER2 identified by USER2
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> grant create session to USER1, USER2
  2  /

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> create role EMP_SENSITIVE
  2  /

Role created.

SCOTT@orcl_12.1.0.2.0> grant EMP_SENSITIVE to USER1
  2  /

Grant succeeded.

-- MYTABDB:
SCOTT@orcl_12.1.0.2.0> connect MYTABDB/MYTABDB
Connected.
MYTABDB@orcl_12.1.0.2.0> create table employee
  2    (empid	  number,
  3  	emp_name  varchar2(15),
  4  	emp_ssn   varchar2(11),
  5  	age	  number)
  6  /

Table created.

MYTABDB@orcl_12.1.0.2.0> insert into employee values (1, 'name1', '111-11-1111', 1)
  2  /

1 row created.

MYTABDB@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

MYTABDB@orcl_12.1.0.2.0> grant select on MYTABDB.EMPLOYEE to MYVIEWDB with grant option
  2  /

Grant succeeded.

--MYVIEWDB:
MYTABDB@orcl_12.1.0.2.0> connect MYVIEWDB/MYVIEWDB
Connected.
MYVIEWDB@orcl_12.1.0.2.0> CREATE VIEW MYVIEWDB.EMP AS
  2  SELECT EMPID,
  3  	    CASE WHEN sys_context ('sys_session_roles', 'EMP_SENSITIVE') = 'TRUE' THEN EMP_NAME ELSE 'XXXXXX' END AS ENAME,
  4  	    CASE WHEN sys_context ('sys_session_roles', 'EMP_SENSITIVE') = 'TRUE' THEN EMP_SSN ELSE 'XXXXXX' END AS SSN,
  5  	    AGE
  6  FROM   MYTABDB.EMPLOYEE
  7  /

View created.

MYVIEWDB@orcl_12.1.0.2.0> grant select on MYVIEWDB.EMP to USER1, USER2
  2  /

Grant succeeded.

-- USER1 has role EMP_SENSITIVE:
MYVIEWDB@orcl_12.1.0.2.0> connect USER1/USER1
Connected.
USER1@orcl_12.1.0.2.0> select * from MYVIEWDB.EMP
  2  /

     EMPID ENAME           SSN                AGE
---------- --------------- ----------- ----------
         1 name1           111-11-1111          1

1 row selected.

-- USER2 does does not have role EMP_SENSITIVE:
USER1@orcl_12.1.0.2.0> connect USER2/USER2
Connected.
USER2@orcl_12.1.0.2.0> select * from MYVIEWDB.EMP
  2  /

     EMPID ENAME           SSN                AGE
---------- --------------- ----------- ----------
         1 XXXXXX          XXXXXX               1

1 row selected.
Re: Applying ROLE-based security to columns in a View in Oracle [message #656413 is a reply to message #656412] Wed, 05 October 2016 08:48 Go to previous message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
I've been meaning to investigate whether one could use CREATE VIEW...BEQUEATH CURRENT_USER AS... to achieve similar results. If anyone isn't familiar with this, it is the same idea as AUTHID CURRENT_USER in a procedure. I have yet to find a usage case for it.
Previous Topic: Verify sec_protocol_error_further_action
Next Topic: Grant ROLE to user takes longer time
Goto Forum:
  


Current Time: Sat Nov 25 03:23:09 CST 2017

Total time taken to generate the page: 0.01110 seconds