Home » RDBMS Server » Security » how to implement row level and column level vpd simultaneously (Oracle 10gr2, windows 32)
how to implement row level and column level vpd simultaneously [message #505598] Wed, 04 May 2011 02:40 Go to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
Hi,

--here's my set up

CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;

CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;

CREATE USER user2 IDENTIFIED BY user2
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user2;

GRANT EXECUTE ON DBMS_RLS TO PUBLIC;

CONN schemaowner/schemaowner@service

CREATE TABLE users 
(id          NUMBER(10)   NOT NULL, 
 ouser       VARCHAR2(30) NOT NULL,
 first_name  VARCHAR2(50) NOT NULL,
 last_name   VARCHAR2(50) NOT NULL);

INSERT INTO users VALUES (1,'USER1','User','One');
INSERT INTO users VALUES (2,'USER2','User','Two');



CREATE TABLE user_data 
(column1     VARCHAR2(50) NOT NULL,
 user_id     NUMBER(10)   NOT NULL,
column 2 VARCHAR2(50));


INSERT INTO user_data (column1, user_id, column2)VALUES (1,'USER1','tester1);
INSERT INTO user_data (column1, user_id, column2)VALUES (2,'USER2','tester2);



my desired outcome for user 1

user1> select * from schemaowner.user_data;

user_id column1
1           USER1


my desired outcome for user2 are as follow:
user1> select * from schemaowner.user_data;

user_id column2  
2          TESTER 2



the nearest solution is from
with reference to http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvcntx.htm#i1009519

Quote:

Adding Policies for Column-Level VPD

....

SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
WHERE d.deptno = e.deptno;

the database returns a subset of rows as follows:

ENAME DNAME JOB SAL COMM
-------------- -------------- ------------ ------------ -------------
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
TURNER SALES SALESMAN 1500 0
JAMES SALES CLERK 950



so how do I implement roll level and column level simultaneously?

thanks a lot!
Re: how to implement row level and column level vpd simultaneously [message #505599 is a reply to message #505598] Wed, 04 May 2011 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not answer to your question but did you first try to achieve your goal using views instead of VPD?

Regards
Michel
Re: how to implement row level and column level vpd simultaneously [message #505600 is a reply to message #505598] Wed, 04 May 2011 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also it should be great if the test case worked.

Regards
Michel
Re: how to implement row level and column level vpd simultaneously [message #505601 is a reply to message #505598] Wed, 04 May 2011 03:18 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not possible to get your desire output anyway.
The column level VPD is a column masking replacing the data by NULL it does not remove the column from the output.

The only way to do it is with views.

Regards
Michel
Previous Topic: Doubt in DBMS_RLS!
Next Topic: Oracle Internet Directory - Tables
Goto Forum:
  


Current Time: Thu Mar 28 14:09:29 CDT 2024