测试real application security的security class

security class是application security的集合。
首先创建一个security class

real application security这里好多的地方都没有进行verify，我其中的一个privilege写错了modify_sesion，没有进行检测
08:16:51 SQL> declare
	p_list xs$privilege_list;
begin
	p_list := xs$privilege_list(xs$privilege('create_session'),xs$privilege('modify_sesion'));
	xs_security_class.create_security_class(name=>'wgz_sc_session_test1',priv_list=>p_list);
end;
/
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

08:18:04 SQL> select * from dba_xs_security_classes;

NAME							     OWNER	DESCRIPTION
------------------------------------------------------------ ---------- ------------------------------------------------------------
DML							     SYS	DML Privileges Security Class
SYSTEM							     SYS	System Security Class
ALL							     SYS	All Security Class
SESSION_SC						     SYS	Session Security Class
NSTEMPLATE_SC						     SYS	Namespace Template Security Class
NETWORK_SC						     SYS	Network Security Class
DBSFWUSER_SC						     DBSFWUSER
OlapPrivileges						     SYS	OLAP Data Security Class
WGZ_SC_SESSION_TEST1					     SYS

9 rows selected.

这一步是将错误的权限名称删除掉，然后添加一个正确的权限名称

08:42:53 SQL> declare
	p_list xs$name_list;
	pr_list xs$privilege_list;
begin
	p_list := xs$name_list('modify_sesion');
	xs_security_class.remove_privileges(sec_class=>'wgz_sc_session_test1',priv_list=>p_list);
	pr_list:= xs$privilege_list(xs$privilege('modify_session'));
	xs_security_class.add_privileges(sec_class=>'wgz_sc_session_test1',priv_list=>pr_list);

end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

08:44:14 SQL> select name,security_class,security_class_owner from dba_xs_privileges where security_class='WGZ_SC_SESSION_TEST1';

NAME							     SECURITY_CLASS	  SECURITY_C
------------------------------------------------------------ -------------------- ----------
CREATE_SESSION						     WGZ_SC_SESSION_TEST1 SYS
MODIFY_SESSION						     WGZ_SC_SESSION_TEST1 SYS

Elapsed: 00:00:00.01

添加一个aggregate privilege 到security class
aggregate privilege是依附于security class的一个概念

08:52:14 SQL> declare
 	wgz_aggregate_priv1_list xs$name_list;
 begin
 	wgz_aggregate_priv1_list := xs$name_list('attach_session','terminate_session');
 	xs_security_class.add_privileges(sec_class=>'wgz_sc_session_test1',priv=>'wgz_aggregate_priv1',implied_priv_list=>wgz_aggregate_priv1_list);
 end;
 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10


08:53:07 SQL> select name,security_class from dba_xs_privileges where security_class='WGZ_SC_SESSION_TEST1';

NAME							     SECURITY_CLASS
------------------------------------------------------------ --------------------
CREATE_SESSION						     WGZ_SC_SESSION_TEST1
MODIFY_SESSION						     WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1					     WGZ_SC_SESSION_TEST1

08:55:46 SQL> select privilege,implied_privilege,security_class from dba_xs_implied_privileges where security_class='WGZ_SC_SESSION_TEST1';

PRIVILEGE		       IMPLIED_PRIVILEGE	      SECURITY_CLASS
------------------------------ ------------------------------ --------------------
WGZ_AGGREGATE_PRIV1	       TERMINATE_SESSION	      WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1	       ATTACH_SESSION		      WGZ_SC_SESSION_TEST1

Elapsed: 00:00:00.01

对aggregate privilege进行添加删除privilege操作

对aggregate privilege添加权限

08:52:42 SQL> declare
 	pr_list xs$name_list;
 begin
 	pr_list := xs$name_list('assign_user','set_dynamic_roles');
 	xs_security_class.add_implied_privileges(sec_class=>'wgz_sc_session_test1',priv=>'wgz_aggregate_priv1',implied_priv_list=>pr_list);
 end;
 /
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

查看添加权限是否成功
08:56:27 SQL> select privilege,implied_privilege,security_class from dba_xs_implied_privileges where security_class='WGZ_SC_SESSION_TEST1';

PRIVILEGE		       IMPLIED_PRIVILEGE	      SECURITY_CLASS
------------------------------ ------------------------------ --------------------
WGZ_AGGREGATE_PRIV1	       TERMINATE_SESSION	      WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1	       ATTACH_SESSION		      WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1	       ASSIGN_USER		      WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1	       SET_DYNAMIC_ROLES	      WGZ_SC_SESSION_TEST1

Elapsed: 00:00:00.00

从aggregate privilege里删除一个privilege
09:01:49 SQL> exec xs_security_class.remove_implied_privileges(sec_class=>'wgz_sc_session_test1',priv=>'wgz_aggregate_priv1',implied_priv=>'assign_user');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

检查删除privilege是否成功
09:03:04 SQL> select privilege,implied_privilege,security_class from dba_xs_implied_privileges where security_class='WGZ_SC_SESSION_TEST1';

PRIVILEGE		       IMPLIED_PRIVILEGE	      SECURITY_CLASS
------------------------------ ------------------------------ --------------------
WGZ_AGGREGATE_PRIV1	       TERMINATE_SESSION	      WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1	       ATTACH_SESSION		      WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1	       SET_DYNAMIC_ROLES	      WGZ_SC_SESSION_TEST1

Elapsed: 00:00:00.01

从security class里删除一个权限

09:08:19 SQL> exec xs_security_class.remove_privileges(sec_class=>'WGZ_SC_SESSION_TEST1',priv=>'modify_session');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

检查权限是否已经删除
09:13:12 SQL> select name,security_class from dba_xs_privileges where security_class='WGZ_SC_SESSION_TEST1';

NAME							     SECURITY_CLASS
------------------------------------------------------------ --------------------
CREATE_SESSION						     WGZ_SC_SESSION_TEST1
WGZ_AGGREGATE_PRIV1					     WGZ_SC_SESSION_TEST1

Elapsed: 00:00:00.00