OracleDB23ai マルチテナント：OS認証手順４パターン

## １．目的
マルチテナントになりOS認証も複雑になったので整理。以下４パターン。

![6-1_OS認証全体図.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3491580/f95b3803-835c-486d-ada1-3c745f7c49c8.png)

<table>
  <caption>OS認証　4パターン</caption>
  <thead>
    <tr>
      <th>#</th> <th>接続先</th><th>oracleユーザ</th> <th>接続方法</th>
    </tr>
  </thead>
  <tr>
    <td> 1 </td>
    <td>CDB</td>
    <td>sys</td> 
    <td><ul>
        <li>osユーザがにdbaグループに所属</li>
        <li>ORACLE_SID（環境変数）にCDBのSIDを設定</li>
    </ul></td>
  </tr>
  <tr>
    <td> 2</td>
    <td>PDB</td>
    <td>sys</td> 
    <td><ul>
        <li>osユーザがにdbaグループに所属</li>
        <li>ORACLE_SID（環境変数）にCDBのSIDを設定</li>
        <li>ORACLE_PID_SID（環境変数）にPDBのSIDを設定</li>
    </ul></td>
  </tr>
  <tr>
    <td> 3</td>
    <td>CDB</td>
    <td>一般</td> 
    <td><ul>
        <li>os_authent_prefix（初期パラ）の設定</li>
        <li>common_user_prefix（初期パラ）の設定</li>
    </ul></td>
  </tr>
  <tr>
    <td> 4</td>
    <td>PDB</td>
    <td>一般</td> 
    <td><ul>
        <li>os_authent_prefix（初期パラ）の設定</li>
        <li>BEQ接続（ローカル接続）の設定</li>
    </ul></td>
  </tr>
</table>


## ２. 前提
### ２－１．検証環境
Oracle Database 23ai Free　VirtualBox Appliance (ovaイメージ)

#### 参考

https://qiita.com/nakaie/items/609a85a34301fc9ed79c


## ３．接続手順
### ３－１．CDB sysユーザ（特権ユーザ）
非CDBと同様に環境変数を設定することで接続可能です。
- 接続先のSIDをORACLE_SIDに設定
- 実行osユーザがdbaグループに所属

#### コマンド
``` :環境変数の設定
[oracle@localhost ~]$ export ORACLE_SID=FREE
[oracle@localhost ~]$ export ORACLE_BASE=/opt/oracle
[oracle@localhost ~]$ export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree
[oracle@localhost ~]$ export NLS_LANG=Japanese_Japan.AL32UTF8
[oracle@localhost ~]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@localhost ~]$ id oracle
uid=1000(oracle) gid=1000(oracle) groups=1000(oracle),10(wheel),974(vboxsf),54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)

[oracle@localhost ~]$ sqlplus / as sysdba;
```

#### 補足：TWO_TASK環境変数
:::note warn
TWO_TASKが設定されていると認証に失敗します
:::

``` 
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on 火 9月 10 15:37:12 2024
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid credential or not authorized; logon denied
ヘルプ: https://docs.oracle.com/error-help/db/ora-01017/


ユーザー名を入力してください:
``` 
これは、TWO_TASK環境変数にfreepdb1が設定されているためです。

TWO_TASKはデフォルトの接続識別子を指定する環境変数です。

TWO_TASKが設定されていると、Oracle Netはこれを接続識別子としてネットワーク接続します。
以下コマンドと同等。

```
sqlplus /@FREEPDB1 as sysdba;
```

結果：tnsnames.oraを参照→リスナ経由でネットワーク接続→ユーザ名が空なのでエラー

なお、ORACLE_SID（ローカル接続）とTWO_TASKの両方が設定されていた場合、TWO_TASKによる接続が優先されます。

※Oracle Database 23ai Free　VirtualBox のデフォルト設定

TWO_TASKを空にすることで接続できます。

```
[oracle@localhost ~]$ echo $TWO_TASK
freepdb1
[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ echo $TWO_TASK

[oracle@localhost ~]$ sqlplus / as sysdba;

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on 火 9月 10 16:02:08 2024
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.



Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
に接続されました。
SQL>

```

#### 参考：マニュアル

https://docs.oracle.com/en/database/oracle/oracle-database/23/netag/identifying-and-accessing-database.html#GUID-855C5E77-9F2C-44E4-BD77-32FDF2D9196D



### ３－２．PDB sysユーザ（特権ユーザ）
環境変数 ORACLE_SID に加えて ORACLE_PDB_SID にPDBのSIDを設定することで接続できます。


#### コマンド
``` :PDBに対するOS認証（sys）
[oracle@localhost ~]$ echo $ORACLE_SID
FREE
[oracle@localhost ~]$ echo $ORACLE_PDB_SID

[oracle@localhost ~]$ export ORACLE_PDB_SID=freepdb1
[oracle@localhost ~]$ echo $ORACLE_PDB_SID
freepdb1
[oracle@localhost ~]$ sqlplus / as sysdba;

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on 火 9月 10 16:11:46 2024
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.



Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
に接続されました。
SQL>

```
### ３－３．CDB 一般ユーザ
以下（１）と（２）の条件を満たすため
os_authent_prefixとcommon_user_prefixの値を合わせます。（今回はC##）

#### （１）os_authent_prefix（初期パラ）の設定
oracleユーザ名 = os_authent_prefixの設定文字列 + OSユーザ名　にする必要があります。

| 項目 | 値（例） |
|:-:|:-:|
| os_authent_prefix  | ops\\$ (デフォルト値)   |
| OSユーザ名  |  shun | 
| oracleユーザ名  |  ops\\$shun（外部認証） | 


なお、セキュリティガイドにはnull値に設定することが推奨されてます。
null値にした場合、 OSユーザ名 = oracleユーザ名　になります。

> OS_AUTHENT_PREFIXパラメータをnull値に設定することを強くお薦めします。

https://docs.oracle.com/cd/F19136_01/dbseg/customizing-the-use-of-strong-authentication.html#GUID-F078796B-7C77-422F-B4F0-ED7A158B706F


理由についてマニュアルに記載は無いですが、以下３点が考えられます。

1. セキュリティ
    攻撃者がOracleユーザ名から認証方式を予測可能
1. 管理容易性
    OSとDBのアカウント管理が一貫性を持ち、アクセス管理が統合される
1. 互換性の問題回避
    デフォルトの接頭辞（ops$）を利用すると、アプリケーションやスクリプトに互換性の問題が発生することがある

#### （２）common_user_prefix（初期パラ）の設定
CDBのユーザ名にはcommon_user_prefixの値をつける必要があります。

マニュアルでは、common_user_prefixをnullに設定することに対する注意が記載されています。
共有ユーザとローカルユーザで同一の名前に設定することが可能
= PDBとCDBで競合する可能性ある


https://docs.oracle.com/cd/F19136_01/refrn/COMMON_USER_PREFIX.html


#### コマンド

``` :OSユーザ作成（ucdb）
[root@localhost ~]# useradd ucdb
[root@localhost ~]# passwd ucdb
```

``` :oracleユーザ作成(C#UCDB)
SQL> create user C##UCDB identified externally;

ユーザーが作成されました。

SQL> grant dba to C##UCDB container=all;

権限付与が成功しました。
```





``` :os_authent_prefixの変更（C##）
SQL> show parameter prefix

NAME				                 TYPE   	 VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix		              string	 C##
os_authent_prefix		              string	 ops$
private_temp_table_prefix	          string	 ORA$PTT_

SQL> alter system set os_authent_prefix='C##' scope=spfile;
SQL> shutdown
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SQL> startup
ORACLEインスタンスが起動しました。

SQL> show parameter prefix

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix		     string	 C##
os_authent_prefix		     string	 C##
private_temp_table_prefix	     string	 ORA$PTT_
```

``` :ucdbユーザで接続
[ucdb@localhost oracle]$ echo $ORACLE_SID
FREE
[ucdb@localhost oracle]$ echo $ORACLE_PDB_SID


[ucdb@localhost oracle]$ sqlplus /

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on 日 9月 1 15:40:09 2024
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.



Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
に接続されました。
SQL>
```


### ３－４．PDB 一般ユーザ
OS認証にはローカル接続（BEQ接続）が必要です。
PDBにローカル接続するための定義を tnsnames.ora に記載します。

:::note warn
ORACLE_PDB_SIDを設定しても接続することはできません。
これはORACLE_SIDで設定したCDB\\$ROOTに接続した後、ORACLE_PDB_SIDで設定したPDBに接続するためです。
（CDB\\$ROOTに対する接続権限なし）

:::

#### コマンド
``` :OSユーザ作成（updb）
[root@localhost ~]# useradd updb
[root@localhost ~]# passwd updb
```

``` :oracleユーザ作成(ops$updb)
SQL> show parameter prefix

NAME				                 TYPE   	 VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix		              string	 C##
os_authent_prefix		              string	 ops$
private_temp_table_prefix	          string	 ORA$PTT_


SQL> create user ops$updb identified externally;
SQL> grant dba to ops$updb

権限付与が成功しました。
```




``` :tnsnames.ora
FREEPDB1BEQ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = BEQ)
               (PROGRAM = /opt/oracle/product/23ai/dbhomeFree/bin/oracle)
               (ARGS = '(DESCRIPTION = (ADDRESS = (PROTOCOL = BEQ)))'))
    (CONNECT_DATA =
      (SERVICE_NAME = FREEPDB1)
    )
  )
```
``` :OS認証による接続
[updb@localhost ~]$ sqlplus /@freepdb1beq

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on 木 9月 26 15:31:44 2024
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

最終正常ログイン時間: 水 9月  04 2024 23:29:50 +00:00


Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
に接続されました。
```

参考

https://blogs.oracle.com/sec/post/052_osauthentication

- My Oracle Support
[マルチテナント・コンテナ・データベースでの接続方法(KROWN:164595) (ドキュメントID 1766741.1)](https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=256018427326892&id=1766741.1&_adf.ctrl-state=1g8lqegn9_52)