In the following example, a new database is created with two users. One user is hr_mgr, who owns the hr_mgr schema that contains a copy of the entire sample app. The other user is sales_mgr, who owns the sales_mgr schema that contains a copy of the emp table. The table provides a list 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 app is installed are removed. Then, required permissions are re-granted to the sample app.

The list_emp and hire_clerk programs are changed from the default definer's permission to the caller's permission. When the sales_mgr user runs the programs, the programs act on the emp table in the sales_mgr schema. This occurs because search path and permissions of the sale_mgr user are used for name resolution and authorization checks.

Then, the sale_mgr user executes the get_dept_name and hire_emp programs that are included in the emp_admin package. 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's permission.

Step 1: Create a database and two users

Use the user identity polardb to create the hr database.
CREATE DATABASE hr;
Switch to the hr database and create the required users:
\c hr polardb
CREATE USER hr_mgr IDENTIFIED BY password;
CREATE USER sales_mgr IDENTIFIED BY password;

Step 2: Create the sample app

Create the sample app owned by the hr_mgr user in the hr_mgr schema.
\c - hr_mgr
\i C:/Program Files/PostgresPlus/9.3AS/installer/server/polar-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 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 so that the user can make a copy of the emp table owned by the hr_mgr user. This step is only required in POLARDB compatible with Oracle and is incompatible with Oracle databases, which regard the schema and its user as the same entity.

Step 4: Remove the default permissions

Remove all 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's 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 compatible with Oracle. When you perform this step, make sure that you have logged on as the hr_mgr user. Otherwise, the modified program may be saved 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's permission to the hire_clerk program and qualify the calling of the new_empno function to the hr_mrg schema

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 with the caller's permission. If you call the new_empno function but do not qualify it, the new_empno function in the search path of the caller is executed, rather than that in the hr_mrg schema.

When you save the program, make sure that you have logged on as the hr_mgr user. Otherwise, the modified program may be saved 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 required permissions to the sales_mgr user for accessing the list_emp stored procedure, hire_clerk function, and emp_admin package. Note that the emp table in the sales_mgr schema is the only data object that the sales_mgr user can access. sales_mgr 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 diagram shows the tables and sequences accessed by the anonymous code block. The gray ovals represent the sales_mgr and hr_mgr schemas. The current user during the execution of each program is displayed in bold red font within parenthesis.
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 diagram 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's permission and is owned by hr_mgr.

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 program in the emp_admin package.
EXEC hr_mgr.emp_admin.hire_emp(9001, 'ALICE','SALESMAN',8000,TRUNC(SYSDATE),1000,7369,40);
The following diagram 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's permission.
Connect to the database by using the user identity hr_mgr. Use the following SELECT command to check whether the information of new employees has been added to the emp table owned by the hr_mgr user. The emp_admin package has the definer's permission and hr_mgr 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)