今天在做看OCP的时候有道题是关于应用安全角色的,不是很明白,在网上找了个例子按照步骤验证了下.
QUESTION 48You 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: BSection: (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_roleAUTHID CURRENT_USERascursor vc isSELECT roleFROM ldy.AUTH_ROLESWHERE 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;beginopen 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_slnngkSQL> exec hxl.auth_role; PL/SQL procedure successfully completed. SQL> select count(*) from hxl.tb_test; COUNT(*)---------- 10 从其他IP连接$ sqlplus secure_user/oracle@three_slnngkSQL> 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 --