Oracle Autonomous Databaseに慣れる（１）

[SQL*Plus](https://qiita.com/plusultra/items/62a6802b88648f63b6dd)や[SQL Developer](https://qiita.com/plusultra/items/2c4152c3f11355ea0aa4)からOracle Autonomous Databasesに接続できるようになったので、オンプレミス環境のOracle Databaseとマネージド・サービスの違いについて検証しました。本記事は主にユーザー作成とプロファイルについての検証です。下記の例は Always Free環境で作成できるAUTONOMOUS TRANSACTION PROSESSION (ATP) インスタンスで実行しています。

# 管理者ユーザー
Autonomous Databaseインスタンス作成時に同時に作成されるユーザーが管理者となります。管理者名はADMINで固定されています。インスタンスの作成が完了すると、ADMINユーザーを指定してインスタンスに接続できます。クライアントはOracle Database 19cに付属のSQL*Plusを使っています。

```
$ sqlplus ADMIN/{password}@db202003021003_high

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 7 09:42:51 2020
Version 19.3.0.0.0

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

Last Successful login time: Sat Mar 07 2020 20:02:09 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
{service}_DB202003021003

SQL> SHOW CON_ID;

CON_ID
------------------------------
60

SQL> SELECT * FROM V$VERSION;

BANNER                                                                           BANNER_FULL
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------
BANNER_LEGACY                                                                        CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production           Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
                                                                                 Version 18.4.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production                    0
```

ADMINユーザーはデータベース管理に必要な多くのシステム権限とロールを付与されています。

```
SQL> SELECT COUNT(*) FROM SESSION_PRIVS;

  COUNT(*)
----------
       218

SQL> SELECT COUNT(*) FROM SESSION_ROLES;

  COUNT(*)
----------
        45

```

その他の標準ユーザーは以下の通りです。オンプレミス環境には作成されないC##から始まる共通ユーザーや、Golden Gate用のユーザーなどが追加されています。

```
SQL> SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS ORDER BY 1;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
ADMIN                          OPEN
APEX_190100                    LOCKED
APEX_190200                    LOCKED
APEX_INSTANCE_ADMIN_USER       LOCKED
APPQOSSYS                      EXPIRED & LOCKED
AUDSYS                         LOCKED
C##API                         OPEN
C##CLOUD$SERVICE               OPEN
C##CLOUD_OPS                   OPEN
C##DV_ACCT_ADMIN               LOCKED
C##DV_OWNER                    LOCKED
C##OMLIDM                      OPEN
CTXSYS                         LOCKED
DBSFWUSER                      EXPIRED & LOCKED
DBSNMP                         OPEN
DIP                            EXPIRED & LOCKED
DVF                            LOCKED
DVSYS                          LOCKED
FLOWS_FILES                    LOCKED
GGADMIN                        LOCKED
GGSYS                          EXPIRED & LOCKED
GSMADMIN_INTERNAL              EXPIRED & LOCKED
GSMCATUSER                     EXPIRED & LOCKED
GSMUSER                        EXPIRED & LOCKED
LBACSYS                        LOCKED
MDDATA                         LOCKED
MDSYS                          LOCKED
OML$PROXY                      OPEN
ORACLE_OCM                     EXPIRED & LOCKED
ORDS_METADATA                  EXPIRED & LOCKED
ORDS_PUBLIC_USER               OPEN
OUTLN                          EXPIRED & LOCKED
REMOTE_SCHEDULER_AGENT         EXPIRED & LOCKED
SH                             LOCKED
SSB                            LOCKED
SYS                            OPEN
SYS$UMF                        EXPIRED & LOCKED
SYSBACKUP                      EXPIRED & LOCKED
SYSDG                          EXPIRED & LOCKED
SYSKM                          EXPIRED & LOCKED
SYSRAC                         EXPIRED & LOCKED
SYSTEM                         OPEN
XDB                            LOCKED
XS$NULL                        EXPIRED & LOCKED

46 rows selected.
```

# ユーザーの作成
 ユーザーの作成はオンプレミス環境と同じようにCREATE USER文で実行します。面白いのはDEFAULT TABLESPACEや[LOCAL] TEMPORARY TABLESPACEを指定しても無視されることです。
下記の例では存在しない表領域をDEFAULT TABLESPACE属性とTEMPORARY TABLESPACE属性に指定していますが、エラーになりません。DBA_USERSビューを検索すると、それぞれの属性にはDATAとTEMPが指定されていることがわかります。

```
SQL> CREATE USER scott IDENTIFIED BY {password} DEFAULT TABLESPACE notExists1 TEMPORARY TABLESPACE notExists2;

User created.

SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='SCOTT';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT                          DATA                           TEMP
```

# プロファイル
以下のプロファイルが用意されています。

```
SQL> SELECT DISTINCT PROFILE FROM DBA_PROFILES;

PROFILE
------------------------------
DEFAULT
ORA_STIG_PROFILE
```

DEFAULTプロファイルにはパスワードに関する制限が指定されています。

```
SQL> SELECT RESOURCE_NAME, LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_TYPE='PASSWORD' ORDER BY 1;

RESOURCE_NAME                    LIMIT
-------------------------------- ---------------------------
FAILED_LOGIN_ATTEMPTS            UNLIMITED
INACTIVE_ACCOUNT_TIME            UNLIMITED
PASSWORD_GRACE_TIME              7
PASSWORD_LIFE_TIME               360
PASSWORD_LOCK_TIME               1
PASSWORD_REUSE_MAX               4
PASSWORD_REUSE_TIME              1
PASSWORD_VERIFY_FUNCTION         CLOUD_VERIFY_FUNCTION

8 rows selected.
```

ADMINユーザーはCREATE PROFILEシステム権限やALTER PROFILEシステム権限を持っているにもかかわらずPROFILEの作成や、変更はできません。このためATPを使うユーザーには既存のプロファイルの使用が強制されます。パスワード期限やパスワードの複雑性についてはATP標準のプロファイルが強制されることになります。

```
SQL> CREATE PROFILE profile1 LIMIT PASSWORD_LIFE_TIME UNLIMITED;
CREATE PROFILE profile1 LIMIT PASSWORD_LIFE_TIME UNLIMITED
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED
*
ERROR at line 1:
ORA-01031: insufficient privileges
```

ユーザー作成時のPROFILE指定は無視されて、DEFAULTプロファイルが使用されます。存在しないプロファイル名を指定してもエラーになりません。

```

SQL> CREATE USER scott IDENTIFIED BY <password> PROFILE notExists;

User created.

SQL> SELECT username, profile FROM DBA_USERS WHERE username='SCOTT';

USERNAME                       PROFILE
------------------------------ ------------------------------------
SCOTT                          DEFAULT

```


# パスワードの複雑性
DEFAULTプロファイルのPASSWORD_VERIFY_FUNCTION属性にはCLOUD_VERIFY_FUNCTION関数が指定されています。この関数は内部でORA_COMPLEXITY_CHECKをコールしており、以下の仕様で入力されたパスワードをチェックしています。

|チェック項目|エラー・コード|エラー・メッセージ|
|:---|:---|:---|
|パスワードがユーザー名を含まない|20002|Password contains the username|
|小文字を含む|20000|password must contain 1 or more lowercase characters|
|大文字を含む|20000|password must contain 1 or more uppercase characters|
|数字を含む|20000|password must contain 1 or more digits|
|12バイト以上|20000|password length less than 12 bytes|
|30バイト以下|972|identifier is too long|

```
SQL> CREATE USER scott IDENTIFIED BY ABCDEscott12345;
CREATE USER scott IDENTIFIED BY ABCDEscott12345
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password contains the username
```