安全性示例
在以下示例中,将创建一个新数据库以及两个用户 – 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。在红色粗体括号内显示每个程序执行过程中的当前用户。
从 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 表的访问权限。
运行 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。
现在以用户 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)
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论