详解使用Role来保护Oracle数据库的安全性

Oracle数据库内引入role来管理系统权限、对象权限，因为role提供了容易、动态、有选择的权限可用性等优点；本文主要通过案例来演示role的有选择的权限可用性。
1，创建role：hr_mgr，包括权限select,update on hr.employees，可以使用passwd 或单独的pl/sql Procedure启用角色
sys@EXAM> create role hr_mgr identified by oracle;

Role created.

sys@EXAM> grant select,update on hr.employees to hr_mgr;

Grant succeeded.

2，把role：hr_mgr授予给user：scott，默认未启用状态
sys@EXAM> grant hr_mgr to scott;

Grant succeeded.

sys@EXAM> alter user scott default role all except hr_mgr;

User altered.

3，测试
sys@EXAM> conn scott/tiger
Connected.
scott@EXAM> select * from hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

scott@EXAM> set role hr_mgr identified by oracle;

Role set.

scott@EXAM> select * from hr.employees;
EMPLOYEE_ID FIRST_NAME                               LAST_NAME
----------- ---------------------------------------- --------------------------------------------------
EMAIL                                              PHONE_NUMBER                             HIRE_DATE    JOB_ID
-------------------------------------------------- ---------------------------------------- ------------ --------------------
    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
        198 Donald                                   OConnell
DOCONNEL                                           650.507.9833                             21-JUN-99    SH_CLERK
   4187.33                       124            50


创建role时，除了可以使用passwd 来保护role中的权限使用，还可以用单独的pl/sql procedure启用角色
sys@EXAM> create role hr_mgr identified using p_secure_role;

Role created.

sys@EXAM> create or replace procedure p_secure_role authid current_user is
  2  begin
  3   if sys_context('userenv','ip_address') ='192.168.1.100' then
  4      dbms_session.set_role('hr_mgr');
  5   else
  6      null;
  7   end if;
  8  end;
  9  /

Procedure created.

sys@EXAM> grant execute on p_secure_role to scott;

Grant succeeded.

sys@EXAM> grant hr_mgr to scott;

Grant succeeded.

sys@EXAM> alter user scott default role all except hr_mgr;

User altered.

测试：
SQL> select sys_context('userenv','ip_address') from dual;
 
SYS_CONTEXT('USERENV','IP_ADDR
--------------------------------------------------------------------------------
192.168.1.100
 
SQL> select * from hr.employees;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE   JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
        198 Donald               OConnell                  DOCONNEL                  650.507.9833         1999/6/21   SH_CLERK      4187.33                       124            50

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14359/viewspace-716018/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-716018/