Security
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Security

  1. #1
    Join Date
    Jan 2004
    Posts
    25

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

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Create a View .............

    HTH

    Srini

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    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

  4. #4
    Join Date
    Jan 2004
    Posts
    25
    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.

  5. #5
    Join Date
    Jan 2004
    Posts
    25
    *

  6. #6
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    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

  7. #7
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    I think FGAC wuld be most suitable.......

    fine grained access control....

    check out the documentation.....

    SS

  8. #8
    Join Date
    Jan 2004
    Posts
    25
    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?

  9. #9
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    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

  10. #10
    Join Date
    Jan 2004
    Posts
    25
    great link, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width