| Oracle® Database Real Application Security管理者および開発者ガイド 12cリリース1 (12.1) B71274-08 |
|
 前 |
 次 |
この付録には、ソース・ファイルとログ・ファイルの両方が含まれます。HRデモの詳細な説明は、Real Application Security HRデモにあります。
セキュリティHRデモを実行するには、次のスクリプトを示されている順に実行します。
hrdemo_setup.logを作成する設定スクリプトhrdemo_setup.sqlを実行します。hrdemo_run.logを作成するデモ・スクリプトhrdemo_run.sqlを直接ログオンで実行します。hrdemo_run_sess.logを作成するデモ・スクリプトを実行して、Real Application Securityセッションhrdemo_run_sess.sqlを明示的に作成して連結します。HRDemo.logを作成するJavaデモHRDemo.javaファイルを実行します。hrdemo_clean.logを作成するクリーンアップ・スクリプトhrdemo_clean.sqlを実行します。表C-1に、スクリプトおよび生成されるログ・ファイルのリストを各ファイルの内容へのリンクとともに示します。
表C-1 HRデモ・スクリプトとログ・ファイル
| スクリプト | ログ・ファイル |
|---|---|
設定スクリプトhrdemo_setup.sqlのソース・ファイル。
SET ECHO OFF
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO ON
-- Create a database user as RAS administrator
connect sys/password as sysdba
grant dba, xs_session_admin to rasadm identified by rasadm;
pause;
----------------------------------------------------------------------
-- Introduction
----------------------------------------------------------------------
-- The HR Demo shows how to use basic Real Application Security features.
-- The demo secures HR.EMPLOYEES table by creating a data security
-- policy that grants the table access to:
-- 1) DAUSTIN, an application user in IT department. He has role EMP_ROLE
-- and IT_ROLE. He can view employee records in IT department,
-- but he cannot view the salary column except for his own.
-- 2) SMAVRIS, an application user in HR department. She has role EMP_ROLE
-- and HR_ROLE. She can view and update all the employee records.
----------------------------------------------------------------------
-- 1. SETUP - User and Roles
----------------------------------------------------------------------
-- Connect as RAS administrator
connect rasadm/rasadm;
-- Create database role DB_EMP and grant necessary table privileges.
-- This role will be used to grant the required object privileges to
-- application users.
create role db_emp;
grant select, insert, update, delete on hr.employees to db_emp;
pause;
-- Create an application role EMP_ROLE for common employees.
exec sys.xs_principal.create_role(name => 'emp_role', enabled => true);
-- Create an application role IT_ROLE for IT department.
exec sys.xs_principal.create_role(name => 'it_role', enabled => true);
-- Create an application role HR_ROLE for HR department.
exec sys.xs_principal.create_role(name => 'hr_role', enabled => true);
-- Grant DB_EMP to the three application roles, so they have the required
-- object privilege to access the table.
grant db_emp to emp_role;
grant db_emp to it_role;
grant db_emp to hr_role;
-- Create two application users:
-- DAUSTIN (in IT department), granted EMP_ROLE and IT_ROLE.
exec sys.xs_principal.create_user(name => 'daustin', schema => 'hr');
exec sys.xs_principal.set_password('daustin', 'welcome1');
exec sys.xs_principal.grant_roles('daustin', 'emp_role');
exec sys.xs_principal.grant_roles('daustin', 'it_role');
-- SMAVRIS (in HR department), granted EMP_ROLE and HR_ROLE.
exec sys.xs_principal.create_user(name => 'smavris', schema => 'hr');
exec sys.xs_principal.set_password('smavris', 'welcome1');
exec sys.xs_principal.grant_roles('smavris', 'emp_role');
exec sys.xs_principal.grant_roles('smavris', 'hr_role');
pause;
----------------------------------------------------------------------
-- 2. SETUP - Security class and ACL
----------------------------------------------------------------------
-- Create a security class HRPRIVS based on the predefined DML security class.
-- HRPRIVS has a new privilege VIEW_SALARY, which is used to control the
-- access to SALARY column.
declare
begin
sys.xs_security_class.create_security_class(
name => 'hrprivs',
parent_list => xs$name_list('sys.dml'),
priv_list => xs$privilege_list(xs$privilege('view_salary')));
end;
/
pause;
-- Create three ACLs to grant privileges for the policy defined later.
declare
aces xs$ace_list := xs$ace_list();
begin
aces.extend(1);
-- EMP_ACL: This ACL grants EMP_ROLE the privileges to view an employee's
-- own record including SALARY column.
aces(1) := xs$ace_type(privilege_list => xs$name_list('select','view_salary'),
principal_name => 'emp_role');
sys.xs_acl.create_acl(name => 'emp_acl',
ace_list => aces,
sec_class => 'hrprivs');
-- IT_ACL: This ACL grants IT_ROLE the privilege to view the employee
-- records in IT department, but it does not grant the VIEW_SALARY
-- privilege that is required for access to SALARY column.
aces(1) := xs$ace_type(privilege_list => xs$name_list('select'),
principal_name => 'it_role');
sys.xs_acl.create_acl(name => 'it_acl',
ace_list => aces,
sec_class => 'hrprivs');
-- HR_ACL: This ACL grants HR_ROLE the privileges to view and update all
-- employees' records including SALARY column.
aces(1):= xs$ace_type(privilege_list => xs$name_list('all'),
principal_name => 'hr_role');
sys.xs_acl.create_acl(name => 'hr_acl',
ace_list => aces,
sec_class => 'hrprivs');
end;
/
pause;
----------------------------------------------------------------------
-- 3. SETUP - Data security policy
----------------------------------------------------------------------
-- Create data security policy for EMPLOYEES table. The policy defines three
-- realm constraints and a column constraint that protects SALARY column.
declare
realms xs$realm_constraint_list := xs$realm_constraint_list();
cols xs$column_constraint_list := xs$column_constraint_list();
begin
realms.extend(3);
-- Realm #1: Only the employee's own record.
-- EMP_ROLE can view the realm including SALARY column.
realms(1) := xs$realm_constraint_type(
realm => 'email = xs_sys_context(''xs$session'',''username'')',
acl_list => xs$name_list('emp_acl'));
-- Realm #2: The records in the IT department.
-- IT_ROLE can view the realm excluding SALARY column.
realms(2) := xs$realm_constraint_type(
realm => 'department_id = 60',
acl_list => xs$name_list('it_acl'));
-- Realm #3: All the records.
-- HR_ROLE can view and update the realm including SALARY column.
realms(3) := xs$realm_constraint_type(
realm => '1 = 1',
acl_list => xs$name_list('hr_acl'));
-- Column constraint protects SALARY column by requiring VIEW_SALARY
-- privilege.
cols.extend(1);
cols(1) := xs$column_constraint_type(
column_list => xs$list('salary'),
privilege => 'view_salary');
sys.xs_data_security.create_policy(
name => 'employees_ds',
realm_constraint_list => realms,
column_constraint_list => cols);
end;
/
pause;
-- Apply the data security policy to the table.
begin
sys.xs_data_security.apply_object_policy(
policy => 'employees_ds',
schema => 'hr',
object =>'employees');
end;
/
pause;
----------------------------------------------------------------------
-- 4. SETUP - Validate the objects we have set up.
----------------------------------------------------------------------
set serveroutput on;
begin
if (sys.xs_diag.validate_workspace()) then
dbms_output.put_line('All configurations are correct.');
else
dbms_output.put_line('Some configurations are incorrect.');
end if;
end;
/
-- XS$VALIDATION_TABLE contains validation errors if any.
-- Expect no rows selected.
select * from xs$validation_table order by 1, 2, 3, 4;
pause;
----------------------------------------------------------------------
-- 5. SETUP - Mid-Tier related configuration.
----------------------------------------------------------------------
exec sys.xs_principal.create_user(name=>'dispatcher', schema=>'HR');
exec sys.xs_principal.set_password('dispatcher', 'welcome1');
exec sys.xs_principal.grant_roles('dispatcher', 'xscacheadmin');
exec sys.xs_principal.grant_roles('dispatcher', 'xssessionadmin');
exit
hrdemo_run.sqlスクリプトのソース・ファイル。このスクリプトは直接ログオンでデモを実行します。
SET ECHO OFF SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 COLUMN EMAIL FORMAT A10 COLUMN FIRST_NAME FORMAT A15 COLUMN LAST_NAME FORMAT A15 COLUMN DEPARTMENT_ID FORMAT 9999 COLUMN MANAGER_ID FORMAT 9999 COLUMN SALARY FORMAT 999999 SET ECHO ON -- Connect as DAUSTIN, who has only EMP_ROLE and IT_ROLE conn daustin/welcome1; SET SECUREDCOL ON UNAUTH ******* -- DAUSTIN can view the records in IT department, but can only view his own -- SALARY column. select email, first_name, last_name, department_id, manager_id, salary from employees order by email; SET SECUREDCOL OFF pause; -- DAUSTIN cannot update the record. update employees set manager_id = 102 where email = 'DAUSTIN'; -- Record is not changed. select email, first_name, last_name, department_id, manager_id, salary from employees where email = 'DAUSTIN'; pause; -- Connect as SMAVRIS, who has both EMP_ROLE and HR_ROLE role. conn smavris/welcome1; -- SMAVRIS can view all the records including SALARY column. select email, first_name, last_name, department_id, manager_id, salary from employees where department_id = 60 or department_id = 40 order by department_id, email; -- EMPLOYEES table has 107 rows, we expect to see all of them. select count(*) from employees; pause; -- SMAVRIS can update the record. update employees set manager_id = 102 where email = 'DAUSTIN'; -- Record is changed. select email, first_name, last_name, department_id, manager_id, salary from employees where email = 'DAUSTIN'; -- change the record back to the original. update employees set manager_id = 103 where email = 'DAUSTIN'; exit
hrdemo_run_sess.sqlスクリプトのソース・ファイル。このスクリプトは、Real Application Securityセッションを明示的に作成し、連結します。
SET ECHO OFF
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
COLUMN EMAIL FORMAT A10
COLUMN FIRST_NAME FORMAT A15
COLUMN LAST_NAME FORMAT A15
COLUMN DEPARTMENT_ID FORMAT 9999
COLUMN MANAGER_ID FORMAT 9999
COLUMN SALARY FORMAT 999999
SET ECHO ON
-- Connect as RAS administrator
connect rasadm/rasadm;
-- Variable used to remember the session ID;
var gsessionid varchar2(32);
-- Create an application session for SMARVIS and attach to it.
declare
sessionid raw(16);
begin
sys.dbms_xs_sessions.create_session('SMAVRIS', sessionid);
:gsessionid := rawtohex(sessionid);
sys.dbms_xs_sessions.attach_session(sessionid, null);
end ;
/
pause;
-- Display the current user, it should be SMAVRIS now.
select xs_sys_context('xs$session','username') from dual;
-- Display the enabled application roles and database roles.
select role_name from v$xs_session_roles union
select role from session_roles order by 1;
pause;
-- SMAVRIS can view all the records including SALARY column.
select email, first_name, last_name, department_id, manager_id, salary
from employees where department_id = 60 or department_id = 40
order by department_id, email;
-- EMPLOYEES table has 107 rows, we expect to see all of them.
select count(*) from employees;
pause;
-- Disable HR_ROLE.
exec sys.dbms_xs_sessions.disable_role('hr_role');
-- SMAVRIS should only be able to see her own record.
select email, first_name, last_name, department_id, manager_id, salary
from employees where department_id = 60 or department_id = 40
order by department_id, email;
pause;
-- Enable HR_ROLE
exec sys.dbms_xs_sessions.enable_role('hr_role');
-- SMAVRIS can view all the records again.
select email, first_name, last_name, department_id, manager_id, salary
from employees where department_id = 60 or department_id = 40
order by department_id, email;
-- EMPLOYEES table has 107 rows, we expect to see all of them.
select count(*) from employees;
pause;
-- Detach and destroy the application session.
declare
sessionid raw(16);
begin
sessionid := hextoraw(:gsessionid);
sys.dbms_xs_sessions.detach_session;
sys.dbms_xs_sessions.destroy_session(sessionid);
end;
/
exit
Javaデモのソース・ファイルはHRDemo.javaです。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleResultSet.AuthorizationIndicator;
import oracle.security.xs.Role;
import oracle.security.xs.Session;
import oracle.security.xs.XSAccessController;
import oracle.security.xs.XSSessionManager;
/**
* HR demo java version, check data security related privilege at mid-tier
*/
public class HRDemo {
static Connection mgrConnection = null;
static Connection appConnection = null;
static XSSessionManager mgr = null;
static String user = null;
public static void main(String[] args) {
try {
DriverManager.registerDriver(new OracleDriver());
if (args.length >=3) {
user = args[0];
} else {
System.out.println("Usage HRDemo user pwd dbURL");
System.exit(1);
}
setupConnection(args);
queryAsUser("DAUSTIN");
queryAsUser("SMAVRIS");
cleanupConnection();
} catch (Exception e1) {
e1.printStackTrace();
}
}
private static void queryAsUser(String user) throws SQLException {
System.out.println("\nQuery HR.EMPLOYEES table as user \"" + user + "\"");
try {
Session lws = mgr.createSession(appConnection, user, null,null);
mgr.attachSession(appConnection, lws, null, null, null, null, null);
queryEmployees(lws);
mgr.detachSession(lws);
mgr.destroySession(appConnection, lws);
} catch (Exception e) {
// do nothing since this is just a cleanup action
e.printStackTrace();
}
}
public static void setupConnection(String[] args) throws Exception {
mgrConnection =
DriverManager.getConnection(args[2], "dispatcher", "welcome1");
mgr = XSSessionManager.getSessionManager(mgrConnection, 30, 2048000);
appConnection = DriverManager.getConnection(args[2], args[0], args[1]);
}
public static void cleanupConnection() throws Exception {
mgrConnection.close();
appConnection.close();
}
public static void queryEmployees(Session lws) throws SQLException {
Connection conn = lws.getConnection();
String query =
" select email, first_name, last_name, department_id, salary, ora_get_aclids(emp) from hr.employees emp where department_id in (40, 60, 100) order by email";
Statement stmt = null;
ResultSet rs = null;
System.out.printf(" EMAIL | FIRST_NAME | LAST_NAME | DEPT | SALARY | UPDATE | VIEW_SALARY\n");
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
String email = rs.getString("EMAIL");
String first_name = rs.getString("FIRST_NAME");
String last_name = rs.getString("LAST_NAME");
String department_id = rs.getString("DEPARTMENT_ID");
String salary;
if (((OracleResultSet)rs).getAuthorizationIndicator("SALARY") == AuthorizationIndicator.NONE) {
salary = rs.getString("SALARY");
}
else {
salary = "*****";
}
byte[] aclRaw = rs.getBytes(6);
String update, viewSalary;
if (XSAccessController.checkAcl(lws, aclRaw, "UPDATE")) {
update = "true";
}
else {
update = "false";
}
if (XSAccessController.checkAcl(lws, aclRaw, "VIEW_SALARY")) {
viewSalary = "true";
}
else {
viewSalary = "false";
}
System.out.printf("%9s|%12s|%12s|%6s|%8s|%8s|%8s\n", email,
first_name, last_name, department_id,
salary, update, viewSalary);
}
} catch (Exception e) {
// do nothing since this is just a cleanup action
e.printStackTrace();
} finally {
try { if (rs != null) rs.close(); } catch (Exception e) {};
try { if (stmt != null) stmt.close(); } catch (Exception e) {};
}
}
}
クリーンアップ・スクリプトのソース・ファイルはhrdemo_clean.sqlです。
SET ECHO OFF
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO ON
-- Connect as RAS administrator
connect rasadm/rasadm;
-- Remove policy from the table.
begin
sys.xs_data_security.remove_object_policy(policy=>'employees_ds',
schema=>'hr', object=>'employees');
end;
/
pause;
-- Delete application users and roles
exec sys.xs_principal.delete_principal('emp_role', xs_admin_util.cascade_option);
exec sys.xs_principal.delete_principal('hr_role', xs_admin_util.cascade_option);
exec sys.xs_principal.delete_principal('it_role', xs_admin_util.cascade_option);
exec sys.xs_principal.delete_principal('smavris', xs_admin_util.cascade_option);
exec sys.xs_principal.delete_principal('daustin', xs_admin_util.cascade_option);
pause;
-- Delete security class and ACLs
exec sys.xs_security_class.delete_security_class('hrprivs', xs_admin_util.cascade_option);
exec sys.xs_acl.delete_acl('emp_acl', xs_admin_util.cascade_option);
exec sys.xs_acl.delete_acl('it_acl', xs_admin_util.cascade_option);
exec sys.xs_acl.delete_acl('hr_acl', xs_admin_util.cascade_option);
-- Delete data security policy
exec sys.xs_data_security.delete_policy('employees_ds', xs_admin_util.cascade_option);
pause;
-- Delete database role
drop role db_emp;
-- Delete RAS admin user
connect sys/password as sysdba
drop user rasadm;
pause;
-- Delete dispatcher user used by mid-tier
exec sys.xs_principal.delete_principal('dispatcher', xs_admin_util.cascade_option);
exit
hrdemo_setup.logファイル。
The hrdemo_setup.log file.
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Sep 11 14:09:23 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
SQL>
SQL> -- Create a database user as RAS administrator
SQL> connect sys/password as sysdba
Connected.
SQL> grant dba, xs_session_admin to rasadm identified by rasadm;
Grant succeeded.
SQL>
SQL> pause;
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- Introduction
SQL> ----------------------------------------------------------------------
SQL> -- The HR Demo shows how to use basic Real Application Security features.
SQL> -- The demo secures HR.EMPLOYEES table by creating a data security
SQL> -- policy that grants the table access to:
SQL> -- 1) DAUSTIN, an application user in IT department. He has role EMP_ROLE
SQL> -- and IT_ROLE. He can view employee records in IT department,
SQL> -- but he cannot view the salary column except for his own.
SQL> -- 2) SMAVRIS, an application user in HR department. She has role EMP_ROLE
SQL> -- and HR_ROLE. She can view and update all the employee records.
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 1. SETUP - User and Roles
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Connect as RAS administrator
SQL> connect rasadm/rasadm;
Connected.
SQL>
SQL> -- Create database role DB_EMP and grant necessary table privileges.
SQL> -- This role will be used to grant the required object privileges to
SQL> -- application users.
SQL> create role db_emp;
Role created.
SQL> grant select, insert, update, delete on hr.employees to db_emp;
Grant succeeded.
SQL> pause;
SQL>
SQL> -- Create an application role EMP_ROLE for common employees.
SQL> exec sys.xs_principal.create_role(name => 'emp_role', enabled => true);
PL/SQL procedure successfully completed.
SQL>
SQL> -- Create an application role IT_ROLE for IT department.
SQL> exec sys.xs_principal.create_role(name => 'it_role', enabled => true);
PL/SQL procedure successfully completed.
SQL>
SQL> -- Create an application role HR_ROLE for HR department.
SQL> exec sys.xs_principal.create_role(name => 'hr_role', enabled => true);
PL/SQL procedure successfully completed.
SQL>
SQL> -- Grant DB_EMP to the three application roles, so they have the required
SQL> -- object privilege to access the table.
SQL> grant db_emp to emp_role;
Grant succeeded.
SQL> grant db_emp to it_role;
Grant succeeded.
SQL> grant db_emp to hr_role;
Grant succeeded.
SQL>
SQL> -- Create two application users:
SQL> -- DAUSTIN (in IT department), granted EMP_ROLE and IT_ROLE.
SQL> exec sys.xs_principal.create_user(name => 'daustin', schema => 'hr');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.set_password('daustin', 'welcome1');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.grant_roles('daustin', 'emp_role');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.grant_roles('daustin', 'it_role');
PL/SQL procedure successfully completed.
SQL>
SQL> -- SMAVRIS (in HR department), granted EMP_ROLE and HR_ROLE.
SQL> exec sys.xs_principal.create_user(name => 'smavris', schema => 'hr');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.set_password('smavris', 'welcome1');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.grant_roles('smavris', 'emp_role');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.grant_roles('smavris', 'hr_role');
PL/SQL procedure successfully completed.
SQL>
SQL> pause;
SQL>
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 2. SETUP - Security class and ACL
SQL> ----------------------------------------------------------------------
SQL>
SQL> -- Create a security class HRPRIVS based on the predefined DML security class.
SQL> -- HRPRIVS has a new privilege VIEW_SALARY, which is used to control the
SQL> -- access to SALARY column.
SQL> declare
2 begin
3 sys.xs_security_class.create_security_class(
4 name => 'hrprivs',
5 parent_list => xs$name_list('sys.dml'),
6 priv_list => xs$privilege_list(xs$privilege('view_salary')));
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> pause;
SQL>
SQL> -- Create three ACLs to grant privileges for the policy defined later.
SQL> declare
2 aces xs$ace_list := xs$ace_list();
3 begin
4 aces.extend(1);
5
6 -- EMP_ACL: This ACL grants EMP_ROLE the privileges to view an employee's
7 -- own record including SALARY column.
8 aces(1) := xs$ace_type(privilege_list => xs$name_list('select','view_salary'),
9 principal_name => 'emp_role');
10
11 sys.xs_acl.create_acl(name => 'emp_acl',
12 ace_list => aces,
13 sec_class => 'hrprivs');
14
15 -- IT_ACL: This ACL grants IT_ROLE the privilege to view the employee
16 -- records in IT department, but it does not grant the VIEW_SALARY
17 -- privilege that is required for access to SALARY column.
18 aces(1) := xs$ace_type(privilege_list => xs$name_list('select'),
19 principal_name => 'it_role');
20
21 sys.xs_acl.create_acl(name => 'it_acl',
22 ace_list => aces,
23 sec_class => 'hrprivs');
24
25 -- HR_ACL: This ACL grants HR_ROLE the privileges to view and update all
26 -- employees' records including SALARY column.
27 aces(1):= xs$ace_type(privilege_list => xs$name_list('all'),
28 principal_name => 'hr_role');
29
30 sys.xs_acl.create_acl(name => 'hr_acl',
31 ace_list => aces,
32 sec_class => 'hrprivs');
33 end;
34 /
PL/SQL procedure successfully completed.
SQL>
SQL> pause;
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 3. SETUP - Data security policy
SQL> ----------------------------------------------------------------------
SQL> -- Create data security policy for EMPLOYEES table. The policy defines three
SQL> -- realm constraints and a column constraint that protects SALARY column.
SQL> declare
2 realms xs$realm_constraint_list := xs$realm_constraint_list();
3 cols xs$column_constraint_list := xs$column_constraint_list();
4 begin
5 realms.extend(3);
6
7 -- Realm #1: Only the employee's own record.
8 -- EMP_ROLE can view the realm including SALARY column.
9 realms(1) := xs$realm_constraint_type(
10 realm => 'email = xs_sys_context(''xs$session'',''username'')',
11 acl_list => xs$name_list('emp_acl'));
12
13 -- Realm #2: The records in the IT department.
14 -- IT_ROLE can view the realm excluding SALARY column.
15 realms(2) := xs$realm_constraint_type(
16 realm => 'department_id = 60',
17 acl_list => xs$name_list('it_acl'));
18
19 -- Realm #3: All the records.
20 -- HR_ROLE can view and update the realm including SALARY column.
21 realms(3) := xs$realm_constraint_type(
22 realm => '1 = 1',
23 acl_list => xs$name_list('hr_acl'));
24
25 -- Column constraint protects SALARY column by requiring VIEW_SALARY
26 -- privilege.
27 cols.extend(1);
28 cols(1) := xs$column_constraint_type(
29 column_list => xs$list('salary'),
30 privilege => 'view_salary');
31
32 sys.xs_data_security.create_policy(
33 name => 'employees_ds',
34 realm_constraint_list => realms,
35 column_constraint_list => cols);
36 end;
37 /
PL/SQL procedure successfully completed.
SQL>
SQL> pause;
SQL>
SQL> -- Apply the data security policy to the table.
SQL> begin
2 sys.xs_data_security.apply_object_policy(
3 policy => 'employees_ds',
4 schema => 'hr',
5 object =>'employees');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> pause;
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 4. SETUP - Validate the objects we have set up.
SQL> ----------------------------------------------------------------------
SQL> set serveroutput on;
SQL> begin
2 if (sys.xs_diag.validate_workspace()) then
3 dbms_output.put_line('All configurations are correct.');
4 else
5 dbms_output.put_line('Some configurations are incorrect.');
6 end if;
7 end;
8 /
All configurations are correct.
PL/SQL procedure successfully completed.
SQL> -- XS$VALIDATION_TABLE contains validation errors if any.
SQL> -- Expect no rows selected.
SQL> select * from xs$validation_table order by 1, 2, 3, 4;
no rows selected
SQL>
SQL> pause;
SQL>
SQL> ----------------------------------------------------------------------
SQL> -- 5. SETUP - Mid-Tier related configuration.
SQL> ----------------------------------------------------------------------
SQL> exec sys.xs_principal.create_user(name=>'dispatcher', schema=>'HR');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.set_password('dispatcher', 'welcome1');
PL/SQL procedure successfully completed.
SQL>
SQL> exec sys.xs_principal.grant_roles('dispatcher', 'xscacheadmin');
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.grant_roles('dispatcher', 'xssessionadmin');
PL/SQL procedure successfully completed.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, Advanced Analytics and Real Application Testing options
hrdemo_run.logファイル。
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Sep 11 14:09:47 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
SQL>
SQL> -- Connect as DAUSTIN, who has only EMP_ROLE and IT_ROLE
SQL> conn daustin/welcome1;
Connected.
SQL>
SQL> SET SECUREDCOL ON UNAUTH *******
SQL>
SQL> -- DAUSTIN can view the records in IT department, but can only view his own
SQL> -- SALARY column.
SQL> select email, first_name, last_name, department_id, manager_id, salary
2 from employees order by email;
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
AHUNOLD Alexander Hunold 60 102 *******
BERNST Bruce Ernst 60 103 *******
DAUSTIN David Austin 60 103 4800
DLORENTZ Diana Lorentz 60 103 *******
VPATABAL Valli Pataballa 60 103 *******
5 rows selected.
SQL>
SQL>
SQL> SET SECUREDCOL OFF
SQL> pause;
SQL>
SQL> -- DAUSTIN cannot update the record.
SQL> update employees set manager_id = 102 where email = 'DAUSTIN';
0 rows updated.
SQL>
SQL> -- Record is not changed.
SQL> select email, first_name, last_name, department_id, manager_id, salary
2 from employees where email = 'DAUSTIN';
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
DAUSTIN David Austin 60 103 4800
1 row selected.
SQL>
SQL> pause;
SQL>
SQL> -- Connect as SMAVRIS, who has both EMP_ROLE and HR_ROLE role.
SQL> conn smavris/welcome1;
Connected.
SQL>
SQL> -- SMAVRIS can view all the records including SALARY column.
SQL> select email, first_name, last_name, department_id, manager_id, salary
2 from employees where department_id = 60 or department_id = 40
3 order by department_id, email;
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
SMAVRIS Susan Mavris 40 101 6500
AHUNOLD Alexander Hunold 60 102 9000
BERNST Bruce Ernst 60 103 6000
DAUSTIN David Austin 60 103 4800
DLORENTZ Diana Lorentz 60 103 4200
VPATABAL Valli Pataballa 60 103 4800
6 rows selected.
SQL>
SQL> -- EMPLOYEES table has 107 rows, we expect to see all of them.
SQL> select count(*) from employees;
COUNT(*)
----------
107
1 row selected.
SQL>
SQL> pause;
SQL>
SQL> -- SMAVRIS can update the record.
SQL> update employees set manager_id = 102 where email = 'DAUSTIN';
1 row updated.
SQL>
SQL> -- Record is changed.
SQL> select email, first_name, last_name, department_id, manager_id, salary
2 from employees where email = 'DAUSTIN';
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
DAUSTIN David Austin 60 102 4800
1 row selected.
SQL>
SQL> -- change the record back to the original.
SQL> update employees set manager_id = 103 where email = 'DAUSTIN';
1 row updated.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, Advanced Analytics and Real Application Testing options
hrdemo_run_sess.logファイル。
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Sep 11 14:09:59 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
SQL>
SQL> -- Connect as RAS administrator
SQL> connect rasadm/rasadm;
Connected.
SQL>
SQL> -- Variable used to remember the session ID;
SQL> var gsessionid varchar2(32);
SQL>
SQL> -- Create an application session for SMARVIS and attach to it.
SQL> declare
2 sessionid raw(16);
3 begin
4 sys.dbms_xs_sessions.create_session('SMAVRIS', sessionid);
5 :gsessionid := rawtohex(sessionid);
6 sys.dbms_xs_sessions.attach_session(sessionid, null);
7 end ;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> pause;
SQL>
SQL> -- Display the current user, it should be SMAVRIS now.
SQL> select xs_sys_context('xs$session','username') from dual;
XS_SYS_CONTEXT('XS$SESSION','USERNAME')
--------------------------------------------------------------------------------
SMAVRIS
1 row selected.
SQL>
SQL> -- Display the enabled application roles and database roles.
SQL> select role_name from v$xs_session_roles union
2 select role from session_roles order by 1;
ROLE_NAME
--------------------------------------------------------------------------------
DB_EMP
EMP_ROLE
HR_ROLE
XSPUBLIC
4 rows selected.
SQL>
SQL> pause;
SQL>
SQL> -- SMAVRIS can view all the records including SALARY column.
SQL> select email, first_name, last_name, department_id, manager_id, salary
2 from employees where department_id = 60 or department_id = 40
3 order by department_id, email;
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
SMAVRIS Susan Mavris 40 101 6500
AHUNOLD Alexander Hunold 60 102 9000
BERNST Bruce Ernst 60 103 6000
DAUSTIN David Austin 60 103 4800
DLORENTZ Diana Lorentz 60 103 4200
VPATABAL Valli Pataballa 60 103 4800
6 rows selected.
SQL>
SQL> -- EMPLOYEES table has 107 rows, we expect to see all of them.
SQL> select count(*) from employees;
COUNT(*)
----------
107
1 row selected.
SQL>
SQL> pause;
SQL>
SQL> -- Disable HR_ROLE.
SQL> exec sys.dbms_xs_sessions.disable_role('hr_role');
PL/SQL procedure successfully completed.
SQL>
SQL> -- SMAVRIS should only be able to see her own record.
SQL> select email, first_name, last_name, department_id, manager_id, salary
2 from employees where department_id = 60 or department_id = 40
3 order by department_id, email;
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
SMAVRIS Susan Mavris 40 101 6500
1 row selected.
SQL>
SQL> pause;
SQL>
SQL> -- Enable HR_ROLE
SQL> exec sys.dbms_xs_sessions.enable_role('hr_role');
PL/SQL procedure successfully completed.
SQL>
SQL> -- SMAVRIS can view all the records again.
SQL> select email, first_name, last_name, department_id, manager_id, salary
2 from employees where department_id = 60 or department_id = 40
3 order by department_id, email;
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
SMAVRIS Susan Mavris 40 101 6500
AHUNOLD Alexander Hunold 60 102 9000
BERNST Bruce Ernst 60 103 6000
DAUSTIN David Austin 60 103 4800
DLORENTZ Diana Lorentz 60 103 4200
VPATABAL Valli Pataballa 60 103 4800
6 rows selected.
SQL>
SQL> -- EMPLOYEES table has 107 rows, we expect to see all of them.
SQL> select count(*) from employees;
COUNT(*)
----------
107
1 row selected.
SQL>
SQL> pause;
SQL>
SQL> -- Detach and destroy the application session.
SQL> declare
2 sessionid raw(16);
3 begin
4 sessionid := hextoraw(:gsessionid);
5 sys.dbms_xs_sessions.detach_session;
6 sys.dbms_xs_sessions.destroy_session(sessionid);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, Advanced Analytics and Real Application Testing options
Java HRDemo.logファイル。
Query HR.EMPLOYEES table as user "DAUSTIN"
EMAIL | FIRST_NAME | LAST_NAME | DEPT | SALARY | UPDATE | VIEW_SALARY
AHUNOLD| Alexander| Hunold| 60| *****| false| false
BERNST| Bruce| Ernst| 60| *****| false| false
DAUSTIN| David| Austin| 60| 4800| false| true
DLORENTZ| Diana| Lorentz| 60| *****| false| false
VPATABAL| Valli| Pataballa| 60| *****| false| false
Query HR.EMPLOYEES table as user "SMAVRIS"
EMAIL | FIRST_NAME | LAST_NAME | DEPT | SALARY | UPDATE | VIEW_SALARY
AHUNOLD| Alexander| Hunold| 60| 9000| true| true
BERNST| Bruce| Ernst| 60| 6000| true| true
DAUSTIN| David| Austin| 60| 4800| true| true
DFAVIET| Daniel| Faviet| 100| 9000| true| true
DLORENTZ| Diana| Lorentz| 60| 4200| true| true
ISCIARRA| Ismael| Sciarra| 100| 7700| true| true
JCHEN| John| Chen| 100| 8200| true| true
JMURMAN| Jose Manuel| Urman| 100| 7800| true| true
LPOPP| Luis| Popp| 100| 6900| true| true
NGREENBE| Nancy| Greenberg| 100| 12008| true| true
SMAVRIS| Susan| Mavris| 40| 6500| true| true
VPATABAL| Valli| Pataballa| 60| 4800| true| true
hrdemo_clean.logファイル。
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Sep 11 14:10:43 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
SQL>
SQL> -- Connect as RAS administrator
SQL> connect rasadm/rasadm;
Connected.
SQL>
SQL> -- Remove policy from the table.
SQL> begin
2 sys.xs_data_security.remove_object_policy(policy=>'employees_ds',
3 schema=>'hr', object=>'employees');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> pause;
SQL>
SQL> -- Delete application users and roles
SQL> exec sys.xs_principal.delete_principal('emp_role', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.delete_principal('hr_role', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.delete_principal('it_role', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.delete_principal('smavris', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> exec sys.xs_principal.delete_principal('daustin', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> pause;
SQL>
SQL> -- Delete security class and ACLs
SQL> exec xs_security_class.delete_security_class('hrprivs', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> exec sys.xs_acl.delete_acl('emp_acl', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> exec sys.xs_acl.delete_acl('it_acl', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> exec sys.xs_acl.delete_acl('hr_acl', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL>
SQL> -- Delete data security policy
SQL> exec sys.xs_data_security.delete_policy('employees_ds', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL> pause;
SQL>
SQL> -- Delete database role
SQL> drop role db_emp;
Role dropped.
SQL>
SQL> -- Delete RAS admin user
SQL> connect sys/password as sysdba
Connected.
SQL> drop user rasadm;
User dropped.
SQL> pause;
SQL>
SQL> -- Delete dispatcher user used by mid-tier
SQL> exec sys.xs_principal.delete_principal('dispatcher', xs_admin_util.cascade_option);
PL/SQL procedure successfully completed.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, Advanced Analytics and Real Application Testing options
