-
Security
Given the tables below i need to implement security so that the employee can only VIEW employee details in the same organisational unit. Anyone give me some hints on how to do this. Do i create a new role and do i use the original employee table or create a synonym?
CREATE TABLE employee
(
emp_id NUMBER(6) CONSTRAINT emp_pk PRIMARY KEY,
emp_name VARCHAR2(40) CONSTRAINT emp_name_nn NOT NULL,
emp_hiredate DATE CONSTRAINT emp_hiredate_nn NOT NULL,
ou_id NUMBER(4) CONSTRAINT emp_ou_fk REFERENCES org_unit
);
CREATE TABLE org_unit
(
ou_id NUMBER(4) CONSTRAINT ou_pk PRIMARY KEY,
ou_name VARCHAR2(40) CONSTRAINT ou_name_uq UNIQUE
CONSTRAINT ou_name_nn NOT NULL,
ou_type VARCHAR2(30) CONSTRAINT ou_type_nn NOT NULL,
ou_parent_org_id NUMBER(4) CONSTRAINT ou_parent_org_unit_fk
REFERENCES org_unit
);
-
Create a View .............
HTH
Srini
-
FGAC is your answer. Which database version?
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
sorry, whats FGAC?
so create a view and then grant select to that employee
but how can you tell whether the employee is in the same organisational unit?
oh and im using oracle 9i
Last edited by Arnielover; 01-14-2004 at 07:34 AM.
-
-
Fine Grain Access Control.
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
I think FGAC wuld be most suitable.......
fine grained access control....
check out the documentation.....
SS
-
I havent used FGAC because we havent coveed it at uni yet, could i use a view or synonym?
Also would Roles come into play here? and grants?
-
With a view u how will u determine what org_unit the user belongs to.
so if user x logs on u want to display only details of user x when one queries the view. so one needs to know what org_unit x belongs to.
check out this link
http://osi.oracle.com/~tkyte/article2/index.html
might give u an idea on FGAC
SS
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|