OraFAQ Forum: Security » Oracle Virtual Private Database

Home » RDBMS Server » Security » Oracle Virtual Private Database (11g)

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

Oracle Virtual Private Database [message #638055]

Mon, 01 June 2015 17:00

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

Create Table Designation (Designation_Code char(5),Designation_Name Varchar2(50));

insert into Designation ('00001','MANAGER');
insert into Designation ('00002','DEPUTY MANAGER');
insert into Designation ('00003','ASSISTANT MANAGER');
insert into Designation ('00004','HR MANAGER');
insert into Designation ('00005','OFFICER');
insert into Designation ('00006','SUPERVISOR');
insert into Designation ('00007','WORKER');

Create Table emp(empno char(5),Employee_Name varchar2(70),Designation_Code char(5));
insert into emp ('00001','HARRIS','00001');
insert into emp ('00002','PAUL','00002');
insert into emp ('00003','NOEL','00002');
insert into emp ('00004','JAMES','00003');
insert into emp ('00005','DONALD','00004');
insert into emp ('00006','STEVE','00005');
insert into emp ('00007','MARTIN','00006');
insert into emp ('00008','SIMON','00005');
insert into emp ('00009','ANDREW','00006');
insert into emp ('00010','MAC','00007');
insert into emp ('00011','SUSZAN','00005');

-------------------------
I have 2 users who will access these record USER EMP1 and EMP2

my query is when USER EMP1 get login he will get only managerial Data
-----------
00001 HARRIS 00001
00002 PAUL 00002
00003 NOEL 00002
00004 JAMES 00003
00005 DONALD 00004

When USER EMP2 get login he will get only non managerial Data

00006 STEVE 00005
00007 MARTIN 00006
00008 SIMON 00005
00009 ANDREW 00006
00010 MAC 00007
00011 SUSZAN 00005

I want that security using Oracle Virtual Private Database or if any other solution kindly shared with me.

Thanks in Advance

Report message to a moderator

Re: Oracle Virtual Private Database [message #638059 is a reply to message #638055]

Tue, 02 June 2015 01:16

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why don't you create a view you give access to the users and forbid accesses to the table itself (but for those who are allowed to see all data)?
Something like:

alter table emp rename to emp_tab;
create view emp
as
select * from emp_tab
where (   user in (<manager list>)
      and Designation_Code in (select Designation_Code from Designation 
                               where Designation_Name like '%MANAGER%')
      )
   or (   user not in (<manager list>)
      and Designation_Code in (select Designation_Code from Designation 
                               where Designation_Name not like '%MANAGER%')
      )
/

You can replace "user in (<manager list>)" by anything you want that characterizes a manager (context variable, package variable, pattern in user account...).
You can replace the condition on Designation_Code by anything more efficient than the subquery I gave (which is the only way from what you posted).

Report message to a moderator

Re: Oracle Virtual Private Database [message #638099 is a reply to message #638059]

Tue, 02 June 2015 05:29

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

Thanks a lot

Report message to a moderator

Re: Oracle Virtual Private Database [message #638100 is a reply to message #638099]

Tue, 02 June 2015 05:35

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

But User can Update and insert record too should i write Instead of trigger for updation and insertion on views

Report message to a moderator

Re: Oracle Virtual Private Database [message #638102 is a reply to message #638100]

Tue, 02 June 2015 05:58

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It depends on your specific case.
Is the user that is able to update only a "master" which should have access to the whole table or not?
If yes, then it is better to do the update directly on the table.
If not, it depends on the actual view definition. With mine you can do it on the view. Note that a user can only update or delete a row he can see but he can insert anything. If you want he can insert only a row he can see, you have to add a constraint to the view definition: "with check option".

Report message to a moderator

Re: Oracle Virtual Private Database [message #638104 is a reply to message #638102]

Tue, 02 June 2015 06:07

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

Ok Thanks A Lot

Report message to a moderator

Previous Topic:	SYSDBA Login without password
Next Topic:	Oracle Advaned Security TDE Configuration Set Up Question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 02:09:25 CST 2025