Home » SQL & PL/SQL » SQL & PL/SQL » DATA LEVEL SECURITY (ORACLE 10G)
DATA LEVEL SECURITY [message #443720] Wed, 17 February 2010 01:59 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

DEAR ALL,
Is it possible that we restrict user at data level? For Example 'A' user can only query employeess of deptno 10 only. He can not query employees of others dept.

Best Regards.
Asif.
Re: DATA LEVEL SECURITY [message #443722 is a reply to message #443720] Wed, 17 February 2010 02:02 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
read more about VIEWS in oracle..

sriram Smile
Re: DATA LEVEL SECURITY [message #443723 is a reply to message #443720] Wed, 17 February 2010 02:04 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

no. I don't want to make view because i will have to generate view for all deptno. we can we generate dynamic view or any other possible way.???
Re: DATA LEVEL SECURITY [message #443728 is a reply to message #443723] Wed, 17 February 2010 02:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
its not clear..
Quote:
i will have to generate view for all deptno.

Why?
which/what is stopping you ?
dynamic view?

what do you mean By that ?Oracle dynamic views ?
MV`s?
whats?
sriram Smile
Re: DATA LEVEL SECURITY [message #443732 is a reply to message #443723] Wed, 17 February 2010 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mamalik wrote on Wed, 17 February 2010 09:04
no. I don't want to make view because i will have to generate view for all deptno. we can we generate dynamic view or any other possible way.???

VDP (Virtual Private Database) but it is far harder to maintain than view.

Regards
Michel
Re: DATA LEVEL SECURITY [message #443736 is a reply to message #443720] Wed, 17 February 2010 02:35 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Simple solution would be maintain the config table and have check in in procedure based on cofing table but again it need some extra coding and maintenance.

Just example


my proc

....
....

select dept into x from config_tab where user = xyz

if user = xyz and x <> 10 then 
 return ;
end if 



Re: DATA LEVEL SECURITY [message #443739 is a reply to message #443723] Wed, 17 February 2010 02:47 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
no. I don't want to make view because i will have to generate view for all deptno.

No, you have not to do this, you can a "variable", for instance a context value.
SQL> create or replace package pkg is 
  2    procedure set_dept (p_deptno varchar2);
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg is 
  2    procedure set_dept (p_deptno varchar2)
  3    is
  4    begin 
  5      dbms_session.set_context('myctx','deptno',p_deptno);
  6    end;
  7  end;
  8  /

Package body created.

SQL> create context myctx using pkg;

Context created.

SQL> create or replace view myview
  2  as 
  3  select * from emp where deptno = sys_context('myctx','deptno')
  4  /

View created.

SQL> exec pkg.set_dept(10);

PL/SQL procedure successfully completed.

SQL> select * from myview;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10

1 row selected.

SQL> exec pkg.set_dept(20);

PL/SQL procedure successfully completed.

SQL> select * from myview;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20

5 rows selected.

Exveryone is using the same view.

Regards
Michel
Previous Topic: Missing numbers in a sequence
Next Topic: RETURN MONTH
Goto Forum:
  


Current Time: Sun Dec 04 12:39:26 CST 2016

Total time taken to generate the page: 0.04637 seconds