oracle安全应用角色,oracle安全应用角色例子

今天在做看OCP的时候有道题是关于应用安全角色的,不是很明白,在网上找了个例子按照步骤验证了下.

QUESTION 48

You want to create a role to meet these requirements:

1. The role is to be protected from unauthorized usage.

2. The password of the role is not to be embedded in the application source code or stored in a table.

Which method would you use to restrict enabling of such roles?

A. Create the role with external authentication.

B. Create the role as a secure application role.

C. Create the role as a password-protected role.

D. Create a role and use Fine-Grained Access Control (FGAC) to secure the role.

Correct Answer: B

Section: (none)

Explanation

有点:启用角色时通过包，而不是通过密码。

1.建立一个名为secure_user的应用用户，只有create session权限或其他权限，但不具有查询ldy用户下表的权限。

create user secure_user identified by oracle;

grant create session to secure_user;

2.创建1个安全角色，此时认证使用的过程包不需要已经存在(auth_role)。赋予对hxl.tb_test01表的查询权限。

create role secure_role identified using hxl.auth_role;

grant select on hxl.tb_test01 to secure_role;

3.创建权限信息表。目的是为了限制应用用户从指定IP连接上来才具有安全角色权限。

表结构如下

create table hxl.auth_roles

(

username varchar2(50),

role varchar2(50),

ip_address  varchar2(50),

enabled  number

);

表内容如下：

insert into ldy.auth_roles values ('SECURE_USER','SECURE_ROLE','192.168.2.84',1);

192.168.2.84这个是我客户端机器的ip,下面的存储过程需要通过该ip限制授权

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

fetch vc into v_role;

IF vc%ROWCOUNT = 0 THEN

raise_application_error(-20123,'This IP has Invalid Privilege',false);

END IF;

exit when vc%notfound; /*客户端ip和用户都满足查询条件才设置权限*/

dbms_session.set_role(v_role);

end loop;

exception

when others then

dbms_output.put_line(dbms_utility.format_error_stack);

END;

5.分配权限

grant execute on hxl.auth_role to secure_user;

grant select on hxl.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.2.84连接

$ sqlplus secure_user/oracle@three_slnngk

SQL> exec hxl.auth_role;

PL/SQL procedure successfully completed.

SQL> select count(*) from hxl.tb_test;

COUNT(*)

----------

10

从其他IP连接

$ sqlplus secure_user/oracle@three_slnngk

SQL> exec hxl.auth_role;

PL/SQL procedure successfully completed.

SQL> select count(*) from hxl.tb_test;

select count(*) from hxl.tb_test

*

ERROR at line 1:

ORA-00942: table or view does not exist

-- The End --