詳解使用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/，如需轉載，請註明出處，否則將追究法律責任。