In the following example, a new database that has two users is created. One user is hr_mgr. This user owns the hr_mgr schema that contains a copy of the entire sample program. The other user is sales_mgr. This user owns the sales_mgr schema that contains a copy of the emp table. The table contains the records of sales employees.
In this example, the list_emp stored procedure, hire_clerk function, and emp_admin package are used. To present a more secure environment, all the default permissions that are granted when the sample program is installed are first removed. Then, required permissions are re-granted to the sample program.
The list_emp and hire_clerk programs are changed from the default definer permission to caller permissions. The following scenario is to be presented: When the sales_mgr user uses the programs, the programs act on the emp table in the sales_mgr schema. This occurs because the search path and permissions of the sale_mgr user are used for name resolution and authorization checks.
Then, the sale_mgr user runs the get_dept_name and hire_emp programs that are included in the emp_admin package. In this case, the dept and emp tables in the hr_mgr schema can be accessed because the hr_mgr user is the owner of the emp_admin package that is using the definer permission.
Step 1: Create a database and users
CREATE DATABASE hr;
\c hr polardb
CREATE USER hr_mgr IDENTIFIED BY password;
CREATE USER sales_mgr IDENTIFIED BY password;
Step 2: Create a sample program
\c - hr_mgr
\i C:/Program Files/PostgresPlus/9.3AS/installer/server/polardb-sample.sql
BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
.
.
.
CREATE PACKAGE
CREATE PACKAGE BODY
COMMIT
Step 3: Create the emp table in the sales_mgr schema
\c - hr_mgr
GRANT USAGE ON SCHEMA hr_mgr TO sales_mgr;
\c - sales_mgr
CREATE TABLE emp AS SELECT * FROM hr_mgr.emp WHERE job = 'SALESMAN';
In the preceding example, the GRANT USAGE ON SCHEMA command is used to authorize the sales_mgr user to access the hr_mgr schema. This way, the user can make a copy of the emp table that is owned by the hr_mgr user. This step is required in only PolarDB and is incompatible with Oracle. This is because the schema and its user are not regarded as two different entities in Oracle.
Step 4: Delete the default permissions
\c - hr_mgr
REVOKE USAGE ON SCHEMA hr_mgr FROM sales_mgr;
REVOKE ALL ON dept FROM PUBLIC;
REVOKE ALL ON emp FROM PUBLIC;
REVOKE ALL ON next_empno FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION new_empno() FROM PUBLIC;
REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION hire_clerk(VARCHAR2,NUMBER) FROM PUBLIC;
REVOKE EXECUTE ON PACKAGE emp_admin FROM PUBLIC;
Step 5: Grant the caller permission to the list_emp stored procedure
CREATE OR REPLACE PROCEDURE list_emp
AUTHID CURRENT_USER
IS
v_empno NUMBER(4);
v_ename VARCHAR2(10);
CURSOR emp_cur IS
SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
OPEN emp_cur;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE emp_cur;
END;
Step 6: Grant the caller permission to the hire_clerk program and qualify the call to hr_mrg
When you connect to the database by using the user identity hr_mgr, add the AUTHID CURRENT_USER clause to the hire_clerk program.
To ensure that the hire_clerk function calls the new_empno function in the hr_mgr schema, you must fully qualify new_empno to hr_mgr.new_empno after the BEGIN keyword. The high_clerk function is a program of the caller permission. If you call the new_empno function but do not qualify it, the new_empno function is searched in the search path schema of the hire_clerk caller rather than the actual hr_mrg schema of a specified program.
CREATE OR REPLACE FUNCTION hire_clerk (
p_ename VARCHAR2,
p_deptno NUMBER
) RETURN NUMBER
AUTHID CURRENT_USER
IS
v_empno NUMBER(4);
v_ename VARCHAR2(10);
v_job VARCHAR2(9);
v_mgr NUMBER(4);
v_hiredate DATE;
v_sal NUMBER(7,2);
v_comm NUMBER(7,2);
v_deptno NUMBER(2);
BEGIN
v_empno := hr_mgr.new_empno;
INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782,
TRUNC(SYSDATE), 950.00, NULL, p_deptno);
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO
v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm);
RETURN v_empno;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
RETURN -1;
END;
Step 7: Grant the required permissions
GRANT USAGE ON SCHEMA hr_mgr TO sales_mgr;
GRANT EXECUTE ON PROCEDURE list_emp TO sales_mgr;
GRANT EXECUTE ON FUNCTION hire_clerk(VARCHAR2,NUMBER) TO sales_mgr;
GRANT EXECUTE ON FUNCTION new_empno() TO sales_mgr;
GRANT EXECUTE ON PACKAGE emp_admin TO sales_mgr;
Step 8: Run the list_emp and hire_clerk programs
\c - sales_mgr
DECLARE
v_empno NUMBER(4);
BEGIN
hr_mgr.list_emp;
DBMS_OUTPUT.PUT_LINE('*** Adding new employee ***');
v_empno := hr_mgr.hire_clerk('JONES',40);
DBMS_OUTPUT.PUT_LINE('*** After new employee added ***');
hr_mgr.list_emp;
END;
EMPNO ENAME
----- -------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
*** Adding new employee ***
Department : 40
Employee No: 8000
Name : JONES
Job : CLERK
Manager : 7782
Hire Date : 08-NOV-07 00:00:00
Salary : 950.00
*** After new employee added ***
EMPNO ENAME
----- -------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
8000 JONES
SELECT empno, ename, hiredate, sal, deptno, hr_mgr.emp_admin.get_dept_name(deptno) FROM sales_mgr.emp;
empno | ename | hiredate | sal | deptno | get_dept_name
-------+--------+--------------------+---------+--------+---------------
7499 | ALLEN | 20-FEB-81 00:00:00 | 1600.00 | 30 | SALES
7521 | WARD | 22-FEB-81 00:00:00 | 1250.00 | 30 | SALES
7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | 30 | SALES
7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | 30 | SALES
8000 | JONES | 08-NOV-07 00:00:00 | 950.00 | 40 | OPERATIONS
(5 rows)
Step 9: Run the hire_emp program in the emp_admin package
EXEC hr_mgr.emp_admin.hire_emp(9001, 'ALICE','SALESMAN',8000,TRUNC(SYSDATE),1000,7369,40);
\c - hr_mgr
SELECT empno, ename, hiredate, sal, deptno, hr_mgr.emp_admin.get_dept_name(deptno) FROM hr_mgr.emp;
empno | ename | hiredate | sal | deptno | get_dept_name
-------+--------+--------------------+---------+--------+---------------
7369 | SMITH | 17-DEC-80 00:00:00 | 800.00 | 20 | RESEARCH
7499 | ALLEN | 20-FEB-81 00:00:00 | 1600.00 | 30 | SALES
7521 | WARD | 22-FEB-81 00:00:00 | 1250.00 | 30 | SALES
7566 | JONES | 02-APR-81 00:00:00 | 2975.00 | 20 | RESEARCH
7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | 30 | SALES
7698 | BLAKE | 01-MAY-81 00:00:00 | 2850.00 | 30 | SALES
7782 | CLARK | 09-JUN-81 00:00:00 | 2450.00 | 10 | ACCOUNTING
7788 | SCOTT | 19-APR-87 00:00:00 | 3000.00 | 20 | RESEARCH
7839 | KING | 17-NOV-81 00:00:00 | 5000.00 | 10 | ACCOUNTING
7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | 30 | SALES
7876 | ADAMS | 23-MAY-87 00:00:00 | 1100.00 | 20 | RESEARCH
7900 | JAMES | 03-DEC-81 00:00:00 | 950.00 | 30 | SALES
7902 | FORD | 03-DEC-81 00:00:00 | 3000.00 | 20 | RESEARCH
7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 | 10 | ACCOUNTING
9001 | ALICE | 08-NOV-07 00:00:00 | 8000.00 | 40 | OPERATIONS
(15 rows)