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 the hr database as a PolarDB user.
CREATE DATABASE hr;
Switch to the hr database and create the related users:
\c hr polardb
CREATE USER hr_mgr IDENTIFIED BY password;
CREATE USER sales_mgr IDENTIFIED BY password;

Step 2: Create a sample program

Create the sample program that is owned by the hr_mgr user in the hr_mgr schema.
\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

Create a subset of the emp table that is owned by the sales_mgr user 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

Delete all the permissions and illustrate the minimum required 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

When you connect to the database by using the user identity hr_mgr, add the AUTHID CURRENT_USER clause to the list_emp program and save the clause in PolarDB. When you perform the preceding step, make sure that you have logged on as the hr_mgr user. Otherwise, the modified program ends in the public schema rather than the hr_mgr schema.
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.

When you save the program, make sure that you have logged on as the hr_mgr user. Otherwise, the modified program ends in the public schema rather than the hr_mgr schema.
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

When you connect to the database by using the user identity hr_mgr, you must grant the user the required permissions so that the sales_mgr user can run the list_emp stored procedure, hire_clerk function, and the emp_admin package. Take note of the following item: The emp table in only the sales_mgr schema is the only data object that the sales_mgr user can access. The sales_mgr user has no permission to access tables in the hr_mgr schema.
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

Connect to the database by using the user identity sales_mgr and run the following anonymous code block:
\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
The following figure shows the tables and the sequences that are accessed by the anonymous code block. The gray ovals represent the sales_mgr and hr_mgr schemas. The content in red in parentheses represents the current user when each program is run.
The following query result on the emp table in the sales_mgr schema shows that the update is applied to the table.
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)
The following figure shows that the SELECT command references the emp table in the sales_mgr schema. The dept table referenced by the get_dept_name function in the emp_admin package is from the hr_mgr schema because the emp_admin package has the definer permission and is owned by the hr_mgr user.

Step 9: Run the hire_emp program in the emp_admin package

When you connect to the database by using the user identity sales_mgr, run the hire_emp stored procedure in the emp_admin package.
EXEC hr_mgr.emp_admin.hire_emp(9001, 'ALICE','SALESMAN',8000,TRUNC(SYSDATE),1000,7369,40);
The following figure shows that the emp table in the hr_mgr schema is updated by the hire_emp stored procedure in the emp_admin package that has the definer permission.
Connect to the database by using the user identity hr_mgr. Run the following SELECT command to check whether the information about new employees has been added to the emp table that is owned by the hr_mgr user. The emp_admin package has the definer permission and the hr_mgr user is the owner of the emp_admin package.
\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)