ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 详解使用Role来保护Oracle数据库的安全性

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

原创 Linux操作系统 作者:landf 时间:2012-02-10 09:13:55 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-14

  • 博文量
    68
  • 访问量
    488521