This forum is currently locked. You can't register or post questions at this time. (read more)

VPD setup

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

VPD setup

Postby me_lucky » Wed May 08, 2013 8:06 am

Hi Tim,

I am trying to setup VPD for my database.

I have created policy on employee table such that
CASE USER
WHEN 'USER10'
THEN 'DEPTNO = 10'
WHEN 'USER20'
THEN 'DEPTNO = 20'
WHEN 'USER30'
THEN 'DEPTNO = 30'

When Database user USER10 connects and executes a query select * form employee, then he will be able to see the data of deptno=10.
But I don't want user10, user20 as my database users, I want select even the user name from a master table say X. All my users will be defined in that.

Can u pl help me in achieving this.

Thanks & Regards,
Lucky

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: VPD setup

Postby Tim... » Wed May 08, 2013 8:51 am

Hi.

You've already described the solution, so why don't you just do it?

- Create the table.
- Put in some data.
- Replace the CASE statement with a query, like:

Code: Select all

SELECT 'DEPTNO = ' || deptno
INTO predicate
FROM X
WHERE username = USER;


You can read about basic VPD setup here:

http://www.oracle-base.com/articles/8i/ ... abases.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD setup

Postby me_lucky » Fri May 17, 2013 9:22 am

Hi Tim,

Thanks for the information. I tried the below with sys_context and am able to get the user data according to context.

But the problem though I didn't set any context my table data is not getting displayed.

Below is the predicate function:

CREATE OR REPLACE FUNCTION F_DEPT_POLICY (p_schema VARCHAR2, p_obj VARCHAR2)
RETURN varchar2 IS
BEGIN
RETURN 'DEPARTMENT_ID='||'sys_context(''DEPT_CONTEXT'',''DEPARTMENT_ID'')';
END;

Can u tell me how to get the complete employee table data if predicate is null or if it is not set.

Thanks & Regards,
Lucky

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: VPD setup

Postby Tim... » Fri May 17, 2013 1:37 pm

Hi.

You mean like,

Code: Select all

RETURN 'DEPARTMENT_ID='||'sys_context(''DEPT_CONTEXT'',''DEPARTMENT_ID'') OR sys_context(''DEPT_CONTEXT'',''DEPARTMENT_ID'') IS NULL';


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD setup

Postby me_lucky » Mon May 20, 2013 8:17 am

Yeap Thanks Tim, it worked for me :-)

Thanks & Regards,
Lucky

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: VPD setup

Postby Tim... » Mon May 20, 2013 8:49 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 9 guests

cron