安全应用角色
1.可以解决其他客户端程序越权访问数据的问题。
2.比隐藏密码机制更好。
3.使用SYS_CONTEXT机制和VPD效果一样。
4.启用角色时通过包,而不是通过密码。
1.建立一个名为secure_user的应用用户,只有create session权限或其他权限,但不具有查询ldy用户下表的权限。
create user secure_user identified by oracle;
grant create session to secure_user;
2.创建1个安全角色,此时认证使用的过程包不需要已经存在。赋予对ldy.emp表的查询权限。
CREATE ROLE secure_role IDENTIFIED USING ldy.auth_role;
grant select on ldy.emp to secure_role;
3.创建权限信息表。如果权限比较简单,也可以不使用权限信息表,直接在验证包里写验证信息。
目的是为了限制应用用户从指定IP连接上来才具有安全角色权限。
表结构如下
create table ldy.AUTH_ROLES
(
USERNAME varchar2(50),
ROLE varchar2(50),
IP_ADDRESS
ENABLED
);
表内容如下:
insert into ldy.auth_roles values ('SECURE_USER','SECURE_ROLE','192.168.161.37',1);
4.创建验证的包和包体
需要包含AUTHID CURRENT_USER子句:
create or replace procedure ldy.auth_role
AUTHID CURRENT_USER
as
cursor vc is
SELECT role
FROM ldy.AUTH_ROLES
WHERE username = upper(sys_context('userenv','current_user'))
AND ip_address = upper(sys_context('userenv','ip_address'))
AND enabled=1;
v_role ldy.auth_roles.role%TYPE;
begin
open vc;
loop
end loop;
exception
END;
/
5.分配这个包的执行权限,权限表的查询权限以及角色给应用用户。注权限分配表的权限给了应用用户,应用用户可以知道哪些IP具有权限,如果不使用权限分配表,写在存储过程中,应用用户也可以从all_sources视图中查询到存储过程的内容。
grant execute on ldy.auth_role to secure_user;
grant select on ldy.auth_roles to secure_user;
grant secure_role to secure_user;
ALTER USER secure_user DEFAULT ROLE ALL EXCEPT secure_role;
6.测试连接
从IP 192.168.161.37连接
$ sqlplus secure_user/oracle@cdct
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 21 09:44:18 2012
Copyright (c) 1982, 2007, Oracle.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exec ldy.auth_role;
PL/SQL procedure successfully completed.
SQL> select count(*) from ldy.emp;
----------
从其他IP连接
$ sqlplus secure_user/oracle@cdct
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 21 09:44:18 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exec ldy.auth_role;
PL/SQL procedure successfully completed.
SQL> select count(*) from ldy.emp;
select count(*) from ldy.emp
ERROR at line 1:
ORA-00942: table or view does not exist
一个简单的例子:
create user secuser identified by oracle;
grant create session to secuser;
create role secrole identified using sec_proc;
grant select on test.load_tab1 to secrole;
create or replace procedure sec_proc
authid current_user
as
begin
if sys_context('userenv','ip_address')='192.168.72.11' then
dbms_session.set_role('secrole');
end if;
end;
/
grant execute on sec_proc to secuser;
grant secrole to secuser;
alter user secuser default role all except secrole;