Autonomous Database上のOracle APEXにて、Real Application SecurityのHRデモを動かしてみました。HRデモのマニュアルの説明はこちらです。実施した作業を書き残しておきます。マニュアルではSQLを実行することで、RASによる保護を確認していますが、この確認をOracle APEXのアプリケーションで行うことが目標です。
Always FreeのAutonomous Transaction Processingと、デモのステージングのためにAlways FreeのComputeインスタンスをひとつ作っています。
以下の手順の説明では、ATPのデータベース名をAPEXRASとします。実際に作業をなぞるときは、適当にデータベース名は置き換えてください。また、Oracle APEXのワークスペースとしてRASDEVが作成済みとします。Always FreeのComputeインスタンスは、Oracle Linux 7.9で作成済みとします。
サンプル・スキーマの入手
RASのHRデモは、Oracle Databaseのサンプル・スキーマに含まれるHRのスキーマの存在を前提にしています。ですので、ADBにサンプル・スキーマをインストールする必要があります。まず、サンプル・スキーマのスクリプトを入手します。
サンプル・スキーマのスクリプトは、こちらにあります。
https://github.com/oracle/db-sample-schemas
インストール手順の詳細はGitHubのページを参照して頂くとして、ここでは実際に行なった作業について記載します。Computeインスタンスは作成直後の状態を想定しています。
Computeインスタンスにユーザーopcで接続し、GitHubからクローンします。最初にgitをインストールします。
[opc@rasstage ~]$ sudo yum install git
gitがインストールされます。サンプル・スキーマのインストール手順(2.1 Clone this repository)にあるように、gitコマンドを実行し、リポジトリをクローンします。
[opc@rasstage ~]$ cd $HOME
[opc@rasstage ~]$ git clone https://github.com/oracle/db-sample-schemas.git
Cloning into 'db-sample-schemas'...
remote: Enumerating objects: 37, done.
remote: Counting objects: 100% (37/37), done.
remote: Compressing objects: 100% (28/28), done.
remote: Total 556 (delta 11), reused 23 (delta 9), pack-reused 519
Receiving objects: 100% (556/556), 35.58 MiB | 4.55 MiB/s, done.
Resolving deltas: 100% (285/285), done.
[opc@rasstage ~]$
サンプル・スキーマを構成するスクリプトに含まれている固定のパスを、環境に合わせて更新します。(2.3 Change all embeded paths to match your working directory)
[opc@rasstage ~]$ cd db-sample-schemas/
[opc@rasstage db-sample-schemas]$ ls
CONTRIBUTING.md bus_intelligence info_exchange mkunplug.sql sales_history
LICENSE.md customer_orders mk_dir.sql mkverify.sql shipping
README.md drop_sch.sql mkplug.sql order_entry
README.txt human_resources mksample.sql product_media
[opc@rasstage db-sample-schemas]$ pwd
/home/opc/db-sample-schemas
[opc@rasstage db-sample-schemas]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
[opc@rasstage db-sample-schemas]$
サンプル・スキーマを作成するスクリプトの入手については完了です。
sqlplusの入手
次にsqlplusが提供されているパッケージを探します。
[opc@rasstage db-sample-schemas]$ cd $HOME
[opc@rasstage ~]$ yum search sqlplus
読み込んだプラグイン:langpacks, ulninfo
============================= N/S matched: sqlplus =============================
oracle-instantclient12.2-sqlplus.x86_64 : SQL*Plus for Instant Client.
oracle-instantclient18.3-sqlplus.x86_64 : SQL*Plus for Instant Client.
Name and summary matches only, use "search all" for everything.
[opc@rasstage ~]$
この記事を書いている時点では、oracle-instantclient18.3-sqlplus.x86_64がインストールできる最新のパッケージのようです。こちらをインストールします。
[opc@rasstage ~]$ sudo yum install oracle-instantclient18.3-sqlplus
[opc@rasstage ~]$ export PATH=/usr/lib/oracle/18.3/client64/bin:$PATH
[opc@rasstage ~]$ export LD_LIBRARY_PATH=/usr/lib/oracle/18.3/client64/lib
[opc@rasstage ~]$ sqlplus /nolog
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Dec 3 02:55:25 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> exit
[opc@rasstage ~]$
ADBへの接続情報とウォレットの配置
[opc@rasstage ~]$ cd $HOME
[opc@rasstage ~]$ ls
db-sample-schemas wallet_APEXRAS.zip
[opc@rasstage ~]$ mkdir -p network/admin
[opc@rasstage ~]$ unzip -d network/admin wallet_APEXRAS.zip
Archive: wallet_APEXRAS.zip
inflating: network/admin/README
inflating: network/admin/cwallet.sso
inflating: network/admin/tnsnames.ora
inflating: network/admin/truststore.jks
inflating: network/admin/ojdbc.properties
inflating: network/admin/sqlnet.ora
inflating: network/admin/ewallet.p12
inflating: network/admin/keystore.jks
[opc@rasstage ~]$
[opc@rasstage ~]$ export ORACLE_HOME=$HOME
[opc@rasstage ~]$ export TWO_TASK=apexras_low
[opc@rasstage ~]$ sqlplus admin/**管理パスワード**
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Dec 3 04:10:50 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Thu Dec 03 2020 04:06:57 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[opc@rasstage ~]$
サンプル・スキーマの作成
SQL> select tablespace_name, contents, status from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------------------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
UNDOTBS1 UNDO ONLINE
TEMP TEMPORARY ONLINE
DATA PERMANENT ONLINE
DBFS_DATA PERMANENT ONLINE
SAMPLESCHEMA PERMANENT READ ONLY
UNDO_2 UNDO ONLINE
8 rows selected.
[opc@rasstage ~]$ cd db-sample-schemas/human_resources/
[opc@rasstage human_resources]$ pwd
/home/opc/db-sample-schemas/human_resources
[opc@rasstage human_resources]$
[opc@rasstage human_resources]$ cp hr_main.sql hr_main.sql.sys
CONNECT sys/&pass_sys@&connect_string AS SYSDBA;
CONNECT admin/&pass_sys@&connect_string
[opc@rasstage human_resources]$ mkdir $HOME/logs
[opc@rasstage human_resources]$ sqlplus admin/**管理パスワード** @hr_main.sql
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Dec 3 04:47:36 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Thu Dec 03 2020 04:43:07 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
specify password for HR as parameter 1:
Enter value for 1: **HRのパスワード**
specify default tablespeace for HR as parameter 2:
Enter value for 2: DATA
specify temporary tablespace for HR as parameter 3:
Enter value for 3: TEMP
specify password for SYS as parameter 4:
Enter value for 4: **管理パスワード**
specify log path as parameter 5:
Enter value for 5: /home/opc/logs/
specify connect string as parameter 6:
Enter value for 6: apexras_low
User dropped.
User created.
User altered.
User altered.
Grant succeeded.
[中略]
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name from all_tables where owner = 'HR';
TABLE_NAME
--------------------------------------------------------------------
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
REGIONS
7 rows selected.
SQL> exit
Real Application Securityの設定
[opc@rasstage ~]$ sqlplus -s /nolog @ hrdemo_setup.sql
specify password for ADMIN as parameter 1:
Enter value for 1: **管理パスワード**
specify password for HR as parameter 2:
Enter value for 2: **HRに設定したパスワード**
specify password for daustin, smavirus and dispatcher as parameter 3:
Enter value for 3: **RASのプリンシパルに設定するパスワード**
specify connect string as parameter 4:
Enter value for 4: apexras_low
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
[中略]
PL/SQL procedure successfully completed.
[opc@rasstage ~]$
- SYSによる接続はすべてADMINに変更。AS SYSDBAは削除。
- HRのパスワードがhrと決め打ちになっていたのを、パラメータに変更。
- 接続文字列の指定がないので、CONNECT文での接続時に必ず接続文字列を指定。
- 元のスクリプトでsmavrisにXSCONNECTのロールを割り当てる部分がdaustinになっているので（元のスクリプトの記載ミス）、これをsmavrisへ変更。
- RASのプリンシパルのパスワードがwelcome1で決め打ちになっていたのを、パラメータで指定するように変更。
- hr_session、hr_commonのパスワードとしてwelcome1が決め打ちになっていたのを、HRのパスワードを使うように変更。
- sys.xs_admin_util.grant_system_privilegeの呼び出しをsys.xs_admin_cloud_util.grant_system_privilegeへ変更(Autonomous Databaseで必須の作業）
[opc@rasstage ~]$ sqlplus -s /nolog @ hrdemo.sql
specify password for daustin as parameter 1:
Enter value for 1: **daustinのパスワード**
specify password for smavris as parameter 2:
Enter value for 2: **smavrisのパスワード**
specify connect string as parameter 3:
Enter value for 3: apexras_low
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.
0 rows updated.
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
DAUSTIN David Austin 60 103 4800
1 row selected.
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.
COUNT(*)
----------
107
1 row selected.
1 row updated.
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
DAUSTIN David Austin 60 102 4800
1 row selected.
1 row updated.
[opc@rasstage ~]$
[opc@rasstage ~]$ sqlplus -s /nolog @ hrdemo_session.sql
specify password for hr_session (HR) as parameter 1:
Enter value for 1: **HRのパスワード**
specify connect string as parameter 2:
Enter value for 2: apexras_low
PL/SQL procedure successfully completed.
XS_SYS_CONTEXT('XS$SESSION','USERNAME')
--------------------------------------------------------------------------------
SMAVRIS
1 row selected.
ROLE_NAME
--------------------------------------------------------------------------------
DB_EMP
EMPLOYEE
HR_REPRESENTATIVE
XSCONNECT
XSPUBLIC
XS_CONNECT
6 rows selected.
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.
COUNT(*)
----------
107
1 row selected.
PL/SQL procedure successfully completed.
EMAIL FIRST_NAME LAST_NAME DEPARTMENT_ID MANAGER_ID SALARY
---------- --------------- --------------- ------------- ---------- -------
SMAVRIS Susan Mavris 40 101 6500
1 row selected.
PL/SQL procedure successfully completed.
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.
COUNT(*)
----------
107
1 row selected.
PL/SQL procedure successfully completed.
[opc@rasstage ~]$
APEXアプリケーションの作成
SQL> grant select on hr.employees to rasdev;
Grant succeeded.
SQL>
select * from hr.employees
APEXアプリケーションによる保護の確認
1つのエラーが発生しました
- ORA-00001: 一意制約(HR.JHIST_EMP_ID_ST_DATE_PK)に反しています
SQL> alter trigger hr.update_job_history disable;
パーシング・スキーマの追加
