If you are an employee,
you can see your own row in HR.EMPLOYEES; if you are a manager, you can see the rows for all
the employees who report directly to you.
TIP
If you do not have the sample schemas installed in your database, you
can create them using the scripts found in $ORACLE_HOME/demo/
schema.
Chapter 9: Database Security and Auditing 321
Once the sample schemas are in place, we need to create some users in the database who
want to see rows from the table HR.EMPLOYEES.
create user smavris identified by smavris702;
grant connect, resource to smavris;
create user dgrant identified by dgrant507;
grant connect, resource to dgrant;
create user kmourgos identified by kmourgos622;
grant connect, resource to kmourgos;
The user KMOURGOS is the manager for all the stocking clerks, and DGRANT is one of
KMOURGOS??™s employees. The user SMAVRIS is the HR_REP for the company.
In the following three steps, we will grant SELECT privileges on the HR.EMPLOYEES table to
everyone in the database, and we will create a lookup table that maps employee ID numbers to
their database account. The procedure that sets the context variables for the user session will use
this table to assign the employee ID number to the context variable that will be used in the policy
function to generate the predicate.
grant select on hr.employees to public;
create table hr.emp_login_map (employee_id, login_acct)
as select employee_id, email from hr.
Pages:
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532