安全性示例_程序安全性_存储过程语言_兼容Oracle数据库开发指南_云数据库PolarDB_阿里云技术文档

在以下示例中，将创建一个新数据库以及两个用户 – hr_mgr，其将拥有 schema hr_mgr 中的整个示例应用程序的副本；和 sales_mgr，其将拥有名为 sales_mgr 的 schema，其中将仅具有只包含在销售部工作的员工的 emp 表的副本。

在此示例中，将使用存储过程 list_emp、函数 hire_clerk 和包 emp_admin。在此示例中，将删除在安装示例应用程序时授予的所有默认特权，然后重新显式授予以提供更安全的环境。

程序 list_emp 和 hire_clerk 将从默认的定义者的权限更改为调用者的权限。然后将说明，当 sales_mgr 运行这些程序时，它们处理 sales_mgr 的 schema 中的 emp 表，因为将使用 sales_mgr 的搜索路径和特权来解析名称和检查授权。

然后，sales_mgr 将执行 emp_admin 包中的程序 get_dept_name 和 hire_emp。在这种情况下，将访问 hr_mgr 的 schema 中的 dept 表和 emp 表，因为 hr_mgr 是使用定义者的权利的 emp_admin 包的所有者。由于使用 $user 占位符的默认搜索路径已生效，因此将使用与用户（在本例中为 hr_mgr）匹配的 schema 查找表。

创建数据库和用户

作为用户 enterprisedb，创建 hr 数据库：

CREATE DATABASE hr;

切换到 hr 数据库并创建用户：

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

创建示例应用程序

在 hr_mgr 的 schema 中，创建由 hr_mgr 拥有的整个示例应用程序。

\c - hr_mgr
\i /usr/edb/as11/share/edb-sample.sql

BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
        .
        .
        .
CREATE PACKAGE
CREATE PACKAGE BODY
COMMIT

在 schema sales_mgr 中创建 emp 表

在 sales_mgr 的 schema 中创建由 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 的 schema 以复制 hr_mgr 的 emp 表。此步骤在 POLARDB for Oracle 中是必需的，但与 Oracle 数据库不兼容，因为 Oracle 没有与其用户不同的 schema 的概念。

删除默认特权

删除所有特权以稍后说明所需的最低必需特权。

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

将 list_emp 更改为调用者的权限

在以用户 hr_mgr 身份连接时，将 AUTHID CURRENT_USER 子句添加到 list_emp 程序中并在 POLARDB for Oracle 中重新保存它。在执行此步骤时，请确保您以 hr_mgr 身份登录，否则修改后的程序可能位于 public schema 中，而不是位于 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;

将 hire_clerk 更改为调用者的权限并将调用资格授予 new_empno

在以用户 hr_mgr 身份连接时，将 AUTHID CURRENT_USER 子句添加到 hire_clerk 程序中。

此外，在 BEGIN 语句之后，将引用 new_empno 完全限定为 hr_mgr.new_empno，以确保 hire_clerk 函数对 new_empno 函数的调用解析为 hr_mgr schema。

在重新保存程序时，请确保您以 hr_mgr 身份登录，否则修改后的程序可能位于 public schema 中，而不是位于 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;

授予必需特权

在以用户 hr_mgr 身份连接时，请授予所需的特权，以便 sales_mgr 可以执行 list_emp 存储过程、hire_clerk 函数和 emp_admin 包。请注意，sales_mgr 有权访问的唯一数据对象是 sales_mgr schema 中的 emp 表。sales_mgr 对 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;

运行程序 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 的 schema。在红色粗体括号内显示每个程序执行过程中的当前用户。

安全性示例_程序安全性_存储过程语言_兼容Oracle数据库开发指南_云数据库PolarDB 阿里云技术文档第1张

从 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 schema 中的 emp 表，但 emp_admin 包中的 get_dept_name 函数所引用的 dept 表来自 hr_mgr schema，因为 emp_admin 包具有定义者的权限并由 hr_mgr 拥有。具有 $user 占位符的默认搜索路径设置解析用户 hr_mgr 对 hr_mgr schema 中的 dept 表的访问权限。

安全性示例_程序安全性_存储过程语言_兼容Oracle数据库开发指南_云数据库PolarDB 阿里云技术文档第2张

运行 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 的对象特权，并且具有 $user 占位符的默认搜索路径设置解析为 hr_mgr 的 schema。

安全性示例_程序安全性_存储过程语言_兼容Oracle数据库开发指南_云数据库PolarDB 阿里云技术文档第3张

现在以用户 hr_mgr 身份连接。以下 SELECT 命令验证新员工是否已添加到 hr_mgr 的 emp 表中，因为 emp_admin 包具有定义者的权限，并且 hr_mgr 是 emp_admin 的所有者。

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

安全性示例_程序安全性_存储过程语言_兼容Oracle数据库开发指南_云数据库PolarDB

安全性示例