Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Security

  1. #1
    Join Date
    Jan 2004
    Posts
    28

    Unanswered: 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
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    create different views with records of each department employees
    and grant the permission on the respective VIEWS

  3. #3
    Join Date
    Jan 2004
    Posts
    28
    thanks mate,

    i understand how to create views and grant priveliges, the only thing that confuses me is that the employee viewing the employee table can only view employees in the same organisational unit as them.

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Well that is what i mean..
    There is no way u can grant priviliges on ROWS.

    So for each unit create a view

    eg For UNIT 10
    create view UNIT_10 as select from employee table all employees of unit 10

    then for the people in UNIT 10 give priviliges on VIEW UNIT_10

    and do the same to others.

    DONT GIVE ANY RIGHTS ON THE BASE TABLE EMPLOYEE!!

    I hope i understood ur question and answered!!

  5. #5
    Join Date
    Jan 2004
    Posts
    28
    oh right, i see now what you mean thank you very much!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Security

    You need a way to link the user to his/her own employee record, e.g. by adding an extra column USERNAME VARCHAR2(30) to EMPLOYEE.

    Then you could create a single view like this:

    CREATE OR REPLACE VIEW emp_view AS
    SELECT e1.* FROM employee e1, employee e2
    WHERE e1.ou_id = e2.ou_id
    AND e2.username = USER;


    A more sophisticated solution would be to use Fine Grained Access Control (FGAC) aka Row-Level Security. This would allow the users to select directly from the EMPLOYEE table, with an additional security condition automatically and transparently applied to the query, so that:

    SELECT * FROM employee;

    is treated as something like:

    SELECT * FROM employee WHERE ou_id = SYS_CONTEXT('MyContext','OU_ID')

    If you want to know more, refer to the Oracle docs.

  7. #7
    Join Date
    Jan 2004
    Posts
    28
    thanks again andrew,

    can you give me a link to the appropriate oracle docs?

    thanks
    Arnielover

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try these:

    http://download-west.oracle.com/docs...privs.htm#4525

    http://download-west.oracle.com/docs...02.htm#1006142

    Also, Tom Kyte's book "Expert One on One Oracle" explains it all very well.

  9. #9
    Join Date
    Jan 2004
    Posts
    28
    i think the FGAS is beyond the scop of my assignement although i will add an explanation to suggest it as an alternative.

    so what ive done is this:

    this mean that the employee in org_unit 1100 can only view records of employees who are in org_unit 1100. This would mean i would have to grant all the employees in org_unit 1100 the same select privilige. Is there a better way i could be doing this that isnt too complicated?

    create or replace view emp1100 as
    select * from employee
    where ou_id = 1100;

    grant select on emp1100 to scott;

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What about the idea I suggested earlier, just before I mentioned FGAC?

  11. #11
    Join Date
    Jan 2004
    Posts
    28
    i tried that but it only lets that particular employee view his/her own records and not everyone in his/her organisational unit (i think)

    BTW im sorry to keep using the * to bump up the post, its just my assignment is in for tomorrow and im stressing.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, it lets him see any employee in the same OU.

  13. #13
    Join Date
    Jan 2004
    Posts
    28

    Smile

    ok, ill try it again cos i probably implemented it wrong cos i stupid

  14. #14
    Join Date
    Oct 2003
    Posts
    706

    Angry

    Originally posted by Arnielover
    ok, ill try it again cos i probably implemented it wrong cos i stupid
    C'mon, no one's really stupid, and yet, computers make us all feel stupid. After 25 years in this business, those "" machines still make me feel stupid! Show of hands, please? Thank you. See what I mean, Arnielover? Last time I saw that many hands raised at one time, they were doing "the wave" at a football game!

    Here's what to do: Come up with a couple of alternate suggestions, then go back to the person who gave you the assignment for further guidance. That's how we all learn this stuff...
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  15. #15
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Sundial - you've cracked me up laughing:-) And you're wrong, I *am* that stupid. And where I'm not yet that stupid I have time enough to practice!!!!

    You've created the vision of 100,000 Oracle DBAs at a football game, all doing the mexican wave :-)

    Cheers!
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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