Chapter 10
Pages 250-52
Example 10-2

First Attempt at a Package to Implement Data Access Security

CREATE OR REPLACE PACKAGE k_payroll AS
my_dept payroll.dept%TYPE;
mgr     BOOLEAN;

PROCEDURE del (p_emp_id INTEGER);
PROCEDURE ins (p_emp_id INTEGER, p_name VARCHAR2
              ,p_dept INTEGER, p_payment_period VARCHAR2
              ,p_salary INTEGER);
PROCEDURE upd (p_emp_id INTEGER, p_name VARCHAR2
              ,p_payment_period VARCHAR2 ,p_salary INTEGER);
END k_payroll;
/

CREATE OR REPLACE PACKAGE BODY k_payroll AS
mgr_flag payroll.mgr_flag%TYPE;
CURSOR  c_me IS
       SELECT dept,
              mgr_flag
       FROM   mysys_users
       WHERE  username = USER;

FUNCTION checkdept (p_emp_id INTEGER) RETURN BOOLEAN IS
dept payroll.dept%TYPE;
  CURSOR c_payroll IS
         SELECT pay.dept
         FROM   payroll pay
         WHERE  id = p_emp_id;
BEGIN
  OPEN  c_payroll;
  FETCH c_payroll INTO dept;
  CLOSE c_payroll;

  IF dept <> my_dept THEN
    RETURN FALSE;
  END IF;

  RETURN TRUE;
END checkdept;

PROCEDURE del (p_emp_id INTEGER) IS
- Only Departmental Managers can delete their employees
- Payroll records
BEGIN
  IF checkdept(p_emp_id) AND mgr THEN
    DELETE payroll
    WHERE  id = p_emp_id;
  ELSE
    raise_application_error (-20001, 'Insufficient Privilege');
  END IF;

END del;

PROCEDURE ins (p_emp_id INTEGER, p_name VARCHAR2
              ,p_dept INTEGER, p_payment_period VARCHAR2
             ,p_salary INTEGER) IS
- Can only insert Payroll records in your own dept
- Only manager can set salary (otherwise set to null)
  l_salary payroll.salary%TYPE;
BEGIN
  IF NOT checkdept(p_emp_id) THEN
     raise_application_error (-20001, 'Insufficient Privilege');
  END IF;

  IF NOT mgr THEN
    l_salary := NULL;
  ELSE
    l_salary := p_salary;
  END IF;

  INSERT INTO payroll (id,name,dept,payment_period,salary)
  VALUES (p_emp_id,p_name,p_dept,p_payment_period,l_salary);
END ins;

PROCEDURE upd (p_emp_id INTEGER, p_name VARCHAR2
              ,p_payment_period VARCHAR2 ,p_salary INTEGER) IS
- Can only update Payroll records in your own dept
- Only manager can update salary (otherwise left as it was)
- Cannot change dept

l_salary payroll.salary%TYPE;

CURSOR c_old_salary IS
         SELECT pay.salary
         FROM   payroll pay
         WHERE  id = p_emp_id;

BEGIN
  IF NOT checkdept(p_emp_id) THEN
     raise_application_error (-20001, 'Insufficient Privilege');
  END IF;

  IF NOT mgr THEN
    OPEN c_old_salary;
    FETCH c_old_salary INTO l_salary;
    CLOSE c_old_salary;
  ELSE
    l_salary := p_salary;
  END IF;

  UPDATE payroll
  SET    name = p_name
        ,payment_period = p_payment_period
        ,salary = l_salary
  WHERE id = p_emp_id;
END upd;
- Package initialization code
BEGIN
  OPEN  c_me;
  FETCH c_me
  INTO  my_dept
       ,mgr_flag;
  CLOSE c_me;
  IF mgr_flag = 'Y' THEN
    mgr := TRUE;
  ELSE
    mgr := FALSE;
  END IF;

END k_payroll;
/