一个关于安全机制的示例-阿里云帮助中心

在下面的示例中，将创建一个新的数据库，这个数据库带有两个用户。一个是hr_mgr，在模式hr_schema中拥有整个示例程序的拷贝。另外一个是sales_mgr，拥有名称为sales_mgr的模式，它拥有在表emp中进行销售工作的雇员记录的拷贝。

在这个示例中，将使用到存储过程list_emp，函数hire_clerk，还有包emp_admin。为在这个示例中展现一个更安全的环境。会首先删除所有在安装示例程序时授予的缺省权限，然后重新授予必要的权限。

应用程序list_emp和hire_clerk将从缺省的定义者权限变为调用者权限。下面这些场景将被展现：用户sales_mgr使用这些程序时，这些程序是对sales_mgr模式中对表emp操作。这是因为使用了 sale_mgr的搜索路径和权限来进行命名解析和认证检查。

然后用户sale_mgr将会执行在包emp_admin中的程序get_dept_name和hire_emp。在这种情况下，因为hr_mgr是包emp_admin的所有者，且包emp_admin正在使用定义者权限，所以可以访问hr_mgr的模式中的表dept和emp。

步骤 1 - 创建数据库和用户

用PolarDB用户的身份创建数据库hr。

CREATE DATABASE hr;

然后切换到hr数据库，并且创建相关用户：

\c hr polardb
CREATE USER hr_mgr IDENTIFIED BY password;
CREATE USER sales_mgr IDENTIFIED BY password;

步骤2 - 创建示例程序

在模式hr_mgr中创建为用户hr_mgr所拥有的示例程序：

\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

步骤3 - 在sales_mgr模式中创建表emp

在用户sales_mgr所属模式中创建属于用户sales_mgr的表emp的子集：

\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';

在上面的示例中，使用GRANT USAGE ON SCHEMA命令来允许用户sales_mgr访问模式hr_mgr，这样能够产生用户hr_mgr所拥有表emp的拷贝。这个步骤只是在PolarDB要求的，与Oracle不兼容，因为Oracle没有把模式和它的用户当作两个不同的实体。

步骤4 - 删除缺省的权限

删除所有的权限，随后对所需要的最低限度的权限加以阐述说明：

\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;

步骤5 - 将存储过程list_emp改成调用者权限

当以用户hr_mgr的身份连接数据库时，在PolarDB中为程序list_emp 增加AUTHID CURRENT_USER子句，然后保存。在执行上面这个步骤时，需要确保以用户hr_mgr登录，否则被修改的程序将在模式public，而不是在模式hr_mgr中结束。

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;

步骤6 - 将程序hire_clerk改变调用者权限，并且限定到new_empno的调用

当以用户hr_mgr的身份连接到数据库后，为程序hire_clerk增加AUTHID CURRENT_USER子句。

在关键字BEGIN后面，将new_empno完全限定为hr_mgr.new_empno，为了使函数hire_clerk在模式 hr_mgr中调用函数new_empno，调用必须改为完全限定名称。因为hire_clerk现在是一个调用者权限的程序，对new_empno的非限定调用会导致对new_empno的搜索是在调用hire_clerk用户的搜索路径的模式中进行，而不是在指定程序实际存在的模式hr_mgr中进行的。

当重新保存程序时，确保以hr_mgr用户身份登录，否则被修改的程序将在public 模式而不是在 hr_mgr模式中结束。

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;

步骤7 - 授予被要求的权限

当以用户hr_mgr的身份连接时，为了使用户sales_mgr能够执行存储过程list_emp、函数hire_clerk和包emp_admin ，需要为这个用户授予所需要的权限。需要注意的是只有在模式sales_mgrs中的表emp才是sales_mgr可访问的数据对象。sales_mgr没有任何权限访问hr_mgr模式中的表。

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;

步骤 8 - 运行程序list_emp和hire_clerk

以用户sales_mgr的身份连接到数据库，然后运行下面的匿名代码块：

\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

在下面的图表中，演示了被匿名代码块访问的表和序列。灰色的椭圆部份表示模式sales_mgr和hr_mgr。在括号中用红色字体表示在每一个程序执行期间的当前用户。

在模式sales_mgr中的表emp上执行查询操作，结果显示在这个表上已经执行了更新操作。

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)

在下面的图表中显示了SELECT命令在模式sales_mgr中引用表emp。包emp_admin有定义者权限，由用户hr_mgr所拥有，被包emp_admin中的函数get_dept_name所引用的表dept来自于hr_mgr模式。

步骤9-在emp_admin包中运行程序hire_emp

当以用户sales_mgr的身份连接数据库时，在包emp_admin中运行存储过程hire_emp。

EXEC hr_mgr.emp_admin.hire_emp(9001, 'ALICE','SALESMAN',8000,TRUNC(SYSDATE),1000,7369,40);

这个图表显示了使用带有定义者权限包emp_admin中的存储过程hire_emp更新了属于模式hr_mgr中的表emp。

现在以用户hr_mgr身份连接到数据库，当emp_admin拥有定义者权限并且用户hr_mgr是emp_admin的所有者时，使用下面的SELECT命令检查是否已经将新的雇员信息添加到hr_mgr所拥有的表emp中。

\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)