158.You want to create a role to meet these requirements: 1: The role is to be protected from unauth
来源:互联网 发布:js时间段选择控件 编辑:程序博客网 时间:2024/06/06 02:23
158.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 global authentication.
B.Create the role with external authentication.
C.Create the role as a secure application role.
D.Create the role as a password-protected role.
E.Create a role and use Fine-Grained Access Control (FGAC) to secure the role.
答案:C
解析:
A:参考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6012.htm#SQLRF01311
B:参考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6012.htm#SQLRF01311
C:参考:http://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG99906
http://docs.oracle.com/database/121/DBSEG/app_devs.htm#DBSEG30399
第一步:创建安全应用角色
CREATE ROLE hr_admin IDENTIFIED USING sec_mgr.hr_admin_role_check;
--授予权限给该角色
GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO hr_admin;
第二部:创建plsql包来定义访问策略
CREATE OR REPLACE PROCEDURE hr_admin_role_check
AUTHID CURRENT_USER
AS
BEGIN
IF (SYS_CONTEXT ('userenv','ip_address') BETWEEN '192.0.2.10' and '192.0.2.20' AND TO_CHAR (SYSDATE, 'HH24') BETWEEN 8 AND 17) THEN
EXECUTE IMMEDIATE 'SET ROLE hr_admin';
END IF;
END;
/
--授予过程的执行权限给对应的用户
GRANT EXECUTE ON hr_admin_role_check TO psmith;
测试:
CONNECT PSMITH@hrpdb
Enter password: password
EXECUTE sec_admin.hr_admin_role_check;
--这样就可以了
--这里可以通过存储过程自己控制,不需要再代码中写密码,因此满足要求题目的要求
D:带密码的role
--创建一个代密码的角色
create role overall_manage identified by password;
--将该角色授予对应的用户
grant overall_manage to hr;
--使用的时候需要激活
set role overall_manage identified by password;
E:这个时对应用程序的一种行级别的访问控制
create function authorized_human(p_schema_name in varchar2,p_object_name in varchar2)
--策略必须是单独的或者包里的函数
--必须返回varchar2类型,也就意味着不能超过32767
--必须有两个输入参数 模式和对象名
return varchar2
is
l_return_val varchar2(2000);
begin
l_return_val := 'humanid<10000';
return l_return_val;
end;
--开始创建一个策略
begin
dbms_rls.add_policy(object_schema => 'STS',
object_name => 'TBHUMAN',
policy_name => 'TBHUMAN_POLICY',
function_schema => sts
policy_function => 'AUTHORIZED_HUMAN',
statement_types => 'SELECT',--这里可以是insert update delete select index
update_check => TRUE --这个的作用是用于检查更新后与策略相违背的,比如策略定义为a<10,
--我在这里更新a =11,那么跟新后应该是看不见的了,加上这个选项后
--这里就会报错
sec_relevant_cols => 'SAL,COMM' --这里是指定列
);
end;
select * from tbhuman; --这里只有admin了,因为对应的设置了select的策略
select * from dba_policies; --这里可以看到具体的策略
--删除对应的策略
begin
dbms_rls.drop_policy(object_schema => 'STS',
object_name =>'TBHUMAN' ,
policy_name =>'TBHUMAN_POLICY' );
end;
----------动态策略
如果想让拥有者具有全部查看的权限,而每个部门职能查看自己部门的人员,那么可以使用两个方法
1.grant exempt access policy to STS;
但是这样就带来了一个问题,对于STS来说,所有的策略都不起作用了
2.另外一种方式就是就该策略函数
function authorized_human(p_schema_name in varchar2,p_object_name in varchar2)
return varchar2
is
l_unitid number;
l_return_val varchar2(2000);
begin
if (p_schema_name = USER) then
l_return_val := null;
else
select unitid into l_unitid from tbunit where unitname=user;
l_return_val := 'unitid=' ||l_unitid;
end if;
return l_return_val;
end;
--上下文敏感策略
当我们使用上下文敏感策略的时候性能一般比不上静态策略,但是静态策略很可能有很大的危险,因为他是
静态的,而上下文的是只有在会话中的应用程序发生变化的时候,就会重新执行,如果static_policy参数设置成
true,那么默认policy_type就是static,如果是false那么会被设置为dynamic
policy_type种类 : dbms_rls.DYNAMIC,dbms_rls.CONTEXT_SENSITIVE,dbms_rls.SHARED_CONTEXT_SENSITIVE,
dbms_rls.SHARED_STATIC,dbms_rls.STATIC
--对不显示的列进行隐藏,而不是对整个行进行隐藏
sec_relevant_cols_opt 设置为dbms_rls.all_rows 然后就会都显示,并且不应该显示的都为null,因此
可以设置set null ?,对于这个一定要慎重使用
--rls调试
rls给出了详细的跟踪文件,这个文件位于数据库初始化参数USER_DUMP_DEST指定的目录中
--查看被重写的sql语句
1.select sql_text,predicate,policy,object_name from v$sqlarea,v$vpd_policy where hash_value=sql_hash;
2.设置一个事件
alter session set events '10730 trace name context forever,level 12';
select * from tbhuman;
--这里可以从日志文件中看到
Logon user : STS
Table/View : STS.TBHUMAN
Policy name : TBHUMAN_POLICY
Policy function: STS.AUTHORIZED_HUMAN
RLS view :
SELECT "HUMANID","HUMANNAME","GENDER","STATUS","PASSWORD","UNITID","TELEPHONE","CELLPHONE","EMAIL","DISPORDER","ADDRESS","CANTONCODE","ADMINENABLE","HUMANCODE","LOGIN
FAILDATE","LOGINFAILCOUNT","ZIPCODE","JOBTITLE","HOMEPHONE","UUID" FROM "STS"."TBHUMAN" "TBHUMAN" WHERE (humanid<10000)
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 global authentication.
B.Create the role with external authentication.
C.Create the role as a secure application role.
D.Create the role as a password-protected role.
E.Create a role and use Fine-Grained Access Control (FGAC) to secure the role.
答案:C
解析:
A:参考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6012.htm#SQLRF01311
B:参考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6012.htm#SQLRF01311
C:参考:http://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG99906
http://docs.oracle.com/database/121/DBSEG/app_devs.htm#DBSEG30399
第一步:创建安全应用角色
CREATE ROLE hr_admin IDENTIFIED USING sec_mgr.hr_admin_role_check;
--授予权限给该角色
GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO hr_admin;
第二部:创建plsql包来定义访问策略
CREATE OR REPLACE PROCEDURE hr_admin_role_check
AUTHID CURRENT_USER
AS
BEGIN
IF (SYS_CONTEXT ('userenv','ip_address') BETWEEN '192.0.2.10' and '192.0.2.20' AND TO_CHAR (SYSDATE, 'HH24') BETWEEN 8 AND 17) THEN
EXECUTE IMMEDIATE 'SET ROLE hr_admin';
END IF;
END;
/
--授予过程的执行权限给对应的用户
GRANT EXECUTE ON hr_admin_role_check TO psmith;
测试:
CONNECT PSMITH@hrpdb
Enter password: password
EXECUTE sec_admin.hr_admin_role_check;
--这样就可以了
--这里可以通过存储过程自己控制,不需要再代码中写密码,因此满足要求题目的要求
D:带密码的role
--创建一个代密码的角色
create role overall_manage identified by password;
--将该角色授予对应的用户
grant overall_manage to hr;
--使用的时候需要激活
set role overall_manage identified by password;
E:这个时对应用程序的一种行级别的访问控制
create function authorized_human(p_schema_name in varchar2,p_object_name in varchar2)
--策略必须是单独的或者包里的函数
--必须返回varchar2类型,也就意味着不能超过32767
--必须有两个输入参数 模式和对象名
return varchar2
is
l_return_val varchar2(2000);
begin
l_return_val := 'humanid<10000';
return l_return_val;
end;
--开始创建一个策略
begin
dbms_rls.add_policy(object_schema => 'STS',
object_name => 'TBHUMAN',
policy_name => 'TBHUMAN_POLICY',
function_schema => sts
policy_function => 'AUTHORIZED_HUMAN',
statement_types => 'SELECT',--这里可以是insert update delete select index
update_check => TRUE --这个的作用是用于检查更新后与策略相违背的,比如策略定义为a<10,
--我在这里更新a =11,那么跟新后应该是看不见的了,加上这个选项后
--这里就会报错
sec_relevant_cols => 'SAL,COMM' --这里是指定列
);
end;
select * from tbhuman; --这里只有admin了,因为对应的设置了select的策略
select * from dba_policies; --这里可以看到具体的策略
--删除对应的策略
begin
dbms_rls.drop_policy(object_schema => 'STS',
object_name =>'TBHUMAN' ,
policy_name =>'TBHUMAN_POLICY' );
end;
----------动态策略
如果想让拥有者具有全部查看的权限,而每个部门职能查看自己部门的人员,那么可以使用两个方法
1.grant exempt access policy to STS;
但是这样就带来了一个问题,对于STS来说,所有的策略都不起作用了
2.另外一种方式就是就该策略函数
function authorized_human(p_schema_name in varchar2,p_object_name in varchar2)
return varchar2
is
l_unitid number;
l_return_val varchar2(2000);
begin
if (p_schema_name = USER) then
l_return_val := null;
else
select unitid into l_unitid from tbunit where unitname=user;
l_return_val := 'unitid=' ||l_unitid;
end if;
return l_return_val;
end;
--上下文敏感策略
当我们使用上下文敏感策略的时候性能一般比不上静态策略,但是静态策略很可能有很大的危险,因为他是
静态的,而上下文的是只有在会话中的应用程序发生变化的时候,就会重新执行,如果static_policy参数设置成
true,那么默认policy_type就是static,如果是false那么会被设置为dynamic
policy_type种类 : dbms_rls.DYNAMIC,dbms_rls.CONTEXT_SENSITIVE,dbms_rls.SHARED_CONTEXT_SENSITIVE,
dbms_rls.SHARED_STATIC,dbms_rls.STATIC
--对不显示的列进行隐藏,而不是对整个行进行隐藏
sec_relevant_cols_opt 设置为dbms_rls.all_rows 然后就会都显示,并且不应该显示的都为null,因此
可以设置set null ?,对于这个一定要慎重使用
--rls调试
rls给出了详细的跟踪文件,这个文件位于数据库初始化参数USER_DUMP_DEST指定的目录中
--查看被重写的sql语句
1.select sql_text,predicate,policy,object_name from v$sqlarea,v$vpd_policy where hash_value=sql_hash;
2.设置一个事件
alter session set events '10730 trace name context forever,level 12';
select * from tbhuman;
--这里可以从日志文件中看到
Logon user : STS
Table/View : STS.TBHUMAN
Policy name : TBHUMAN_POLICY
Policy function: STS.AUTHORIZED_HUMAN
RLS view :
SELECT "HUMANID","HUMANNAME","GENDER","STATUS","PASSWORD","UNITID","TELEPHONE","CELLPHONE","EMAIL","DISPORDER","ADDRESS","CANTONCODE","ADMINENABLE","HUMANCODE","LOGIN
FAILDATE","LOGINFAILCOUNT","ZIPCODE","JOBTITLE","HOMEPHONE","UUID" FROM "STS"."TBHUMAN" "TBHUMAN" WHERE (humanid<10000)
0 0
- 158.You want to create a role to meet these requirements: 1: The role is to be protected from unauth
- So you want to be a CIO
- You want to be a Graphics Programmer ...
- 47 You want to use RMAN to create compressed backups. Which statement is true about the compression
- Introduction to Role Transitions
- So you want to be a Game Developer?
- hdu1145.So you want to be a 2^n-aire?
- uva10900 So you want to be a 2n-aire?
- So you want to be a 2n-aire?
- So you want to be a zookeeper翻译
- So you want to be a 2n-aire? UVA
- 147.View the Exhibit. You want to create a tablespace to contain objects with block size 16 KB. But
- ArgumentException: The Thing You Want To Instantiate Is Null.
- Spyder is already running. If you want to open a new instance, please pass to it the --new-instance
- Server 2008 R2: “You must use the Role Management Tool to install or configure Microsoft .NET”
- Server 2008 R2: “You must use the Role Management Tool to install or configure Microsoft .NET”
- You must use the Role Management Tool to install or configure Microsoft .NET Framework 3.5
- Journey from a Python noob to a Kaggler on Python So, you want to become a data scientist or may be
- 认识CoreData-使用进阶
- STA 进程内Com组件剖析
- 博弈_______Stone Game(hdu 4387)
- linux进程编程(三)-- execl()函数使用
- 分布式系统MIT 6.824学习资源
- 158.You want to create a role to meet these requirements: 1: The role is to be protected from unauth
- Object类
- test
- 玲珑学院-1010-Alarm【打表】【找规律】【思维】
- 使用spring容器管理和配置netty
- C++中Polymorphism的实现
- Obiect
- 键值对在架构设计里的应用
- java异常