|
|
|
|
|
|
|
|
| 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   |
Bill B
Messages: 1971
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 #656412 is a reply to message #648302] |
Wed, 05 October 2016 07:46 |
 |
Barbara Boehmer
Messages: 9086
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
