Security

[Arnielover]

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
);

[Srinivas_Sharma]

Create a View .............

HTH

Srini

[kris109]

FGAC is your answer. Which database version?

[Arnielover]

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

[Arnielover]

*

[kris109]

Fine Grain Access Control.

[Srinivas_Sharma]

I think FGAC wuld be most suitable.......

fine grained access control....

check out the documentation.....

SS

[Arnielover]

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?

[Srinivas_Sharma]

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

[Arnielover]

great link, thanks