一个关于安全机制的示例
在下面的示例中,将创建一个新的数据库, 这个数据库带有两个用户。一个是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 - 创建数据库和用户
CREATE DATABASE hr;
\c hr polardb
CREATE USER hr_mgr IDENTIFIED BY password;
CREATE USER sales_mgr IDENTIFIED BY password;
步骤2 - 创建示例程序
\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
\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改成调用者权限
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中进行的。
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 - 授予被要求的权限
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
\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
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)
步骤9-在emp_admin包中运行程序hire_emp
EXEC hr_mgr.emp_admin.hire_emp(9001, 'ALICE','SALESMAN',8000,TRUNC(SYSDATE),1000,7369,40);
\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)
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论