TECH NOTE

PDBにローカルユーザーの作成を行う手順について解説します。

前提

手順に使用しているデータベース環境およびCDB、PDBの設定、作成するローカルユーザーは以下のとおりです。

手順に沿ってローカルユーザーを作成する際は適宜読み替えてください。

項目	値
バージョン	Oracle Database 19c (19.3.0)
CDB
SID	ORCLCDB
PDB
PDBローカル管理者ユーザー	PDBADMIN
PDBローカル管理者パスワード	oracle
SID	PDB1
ローカルユーザー
ユーザー	user01
パスワード	password

PDBローカル管理者ユーザーにDBA権限を与える

PDBローカル管理者ユーザーを使ってローカルユーザーを作成するため、PDBローカル管理者ユーザーにDBA権限を付与します。

PDBローカル管理者ユーザーの権限を確認する

まずはCDBにOS認証でCDBに接続しますが、ORACLE_SID環境変数が必要となるため、ORACLE_SID環境変数を確認します。

$ echo $ORACLE_SID

この環境はDocker上に構築したOracle Database 19cですが、ログインしてみるとORACLE_SID環境変数は設定されていませんでした。

ORACLE_SID環境変数を設定してSQL*Plusを起動します。

$ ORACLE_SID=ORCLCDB sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 19 14:36:24 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 

念のため、CDBに接続出来ていることを確認しましょう。

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> 

PDBについても確認します。今回はすでに作成済みのPDBであるPDB1にローカルユーザーを作成していきます。

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> 

PDB1に切り替えます。

SQL> alter session set container=pdb1;

Session altered.

SQL> 

PDB1に切り替わったことを確認します。

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>

PDBローカル管理者ユーザーPDBADMINに必要な権限があるかどうか確認します。

ここでは、以下の2つのテーブルを確認していきます。

• SYS.DBA_ROLE_PRIVS – データベース内のすべてのユーザーおよびロールに付与されたロールを示す
• SYS.DBA_SYS_PRIVS – ユーザーおよびロールに付与されたシステム権限を示す

まずはSYS.DBA_ROLE_PRIVSを確認します。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'PDBADMIN'
  4  /

GRANTEE  GRANTED_ROLE ADM
-------- ------------ ---
PDBADMIN PDB_DBA      YES

SQL> 

PDB_DBAというロールを付与されていることがわかります。

次にSYS.DBA_SYS_PRIVSも確認しておきましょう。

SQL> select GRANTEE, PRIVILEGE, ADMIN_OPTION FROM SYS.DBA_SYS_PRIVS where GRANTEE = 'PDBADMIN'
  2  /

no rows selected

SQL> 

特に権限は付与されていません。

では、PDBADMINに与えられていたPDB_DBAロールについても確認していきます。

まずは、SYS.DBA_ROLE_PRIVSを確認します。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'PDB_DBA'
  4  /

GRANTEE GRANTED_ROLE ADM
------- ------------ ---
PDB_DBA CONNECT      NO

SQL> 

CONNECTロールが付与されているだけでした。

次にSYS.DBA_SYS_PRIVSを確認します。

SQL> SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'PDB_DBA'
  4  /

GRANTEE PRIVILEGE                                ADM
------- ---------------------------------------- ---
PDB_DBA CREATE PLUGGABLE DATABASE                NO
PDB_DBA CREATE SESSION                           NO

SQL> 

CREATE PLUGGABLE DATABASE、CREATE SESSIONが付与されているだけで、DBA関連の権限は付与されていないことがわかります。

PDB_DBAロールにDBAロールを付与する

PDBローカル管理者ユーザーでローカルユーザーを作成できるようにするために、PDBローカル管理者ユーザーに付与されていたPDB_DBAロールにDBAロールを付与することにします。

SQL> GRANT DBA TO PDB_DBA;

Grant succeeded.

SQL> 

PDB_DBAロールにDBAロールが付与されているはずなので、確認しておきましょう。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'PDB_DBA'
  4  /

GRANTEE GRANTED_ROLE ADM
------- ------------ ---
PDB_DBA DBA          NO
PDB_DBA CONNECT      NO

もともと付与されていたCONNECTロールに加え、DBAロールが付与されていることを確認できました。

これでPDBローカル管理者ユーザーPDBADMINでローカルユーザーの作成ができるようになったので、一度ログアウトしましょう。

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

ローカルユーザーを作成する

今度はPDBADMINユーザーでPDB1へログインします。

$ sqlplus pdbadmin/oracle@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 19 15:09:27 2022
Version 19.3.0.0.0

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

Last Successful login time: Sat Mar 19 2022 13:56:08 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 

くどいようですが、念のためPDB1に接続していることを確認します。

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> 

テーブルスペースの確認

ローカルユーザーに割り当てるテーブルスペースを作成するべきかどうかを判断するために、テーブルスペースを確認します。

SQL> SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, MAX_EXTENTS, MAX_SIZE, STATUS, CONTENTS
  2  FROM SYS.DBA_TABLESPACES
  3  /

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT MAX_EXTENTS   MAX_SIZE STATUS CONTENTS
--------------- ---------- -------------- ----------- ---------- ------ ---------------------
SYSTEM                8192          65536  2147483645 2147483645 ONLINE PERMANENT
SYSAUX                8192          65536  2147483645 2147483645 ONLINE PERMANENT
UNDOTBS1              8192          65536  2147483645 2147483645 ONLINE UNDO
TEMP                  8192        1048576             2147483645 ONLINE TEMPORARY
USERS                 8192          65536  2147483645 2147483645 ONLINE PERMANENT


SQL> SELECT FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, STATUS
  2  FROM SYS.DBA_DATA_FILES 
  3  /

FILE_NAME                                      TABLESPACE_NAME      BYTES     BLOCKS STATUS
---------------------------------------------- --------------- ---------- ---------- ---------
/opt/oracle/oradata/ORCLCDB/PDB1/undotbs01.dbf UNDOTBS1         104857600      12800 AVAILABLE
/opt/oracle/oradata/ORCLCDB/PDB1/sysaux01.dbf  SYSAUX           367001600      44800 AVAILABLE
/opt/oracle/oradata/ORCLCDB/PDB1/system01.dbf  SYSTEM           283115520      34560 AVAILABLE
/opt/oracle/oradata/ORCLCDB/PDB1/users01.dbf   USERS              5242880        640 AVAILABLE


SQL> 

デフォルトに使用したいUSERSテーブルスペース、テンポラリに使用したいTEMPテーブルスペースがすでに存在しているため、テーブルスペースは作成せず進めます。

ローカルユーザーを作成する

では、ローカルユーザーuser01を作成します。テーブルスペースは先ほど確認したUSERSテーブルスペースとTEMPテーブルスペースを使用します。

SQL> CREATE USER user01
  2  IDENTIFIED BY password
  3  DEFAULT TABLESPACE USERS
  4  QUOTA UNLIMITED ON USERS
  5  TEMPORARY TABLESPACE TEMP
  6  /

User created.

SQL> 

ローカルユーザーに権限を付与する

ユーザーを作成しただけでは何もできないユーザーになっているため、必要な権限を付与します。ここでは以下の3つを付与します。

• RESOURCEロール – テーブル等のオブジェクト作成を可能にするロール
• CREATE SESSION権限 – データベースに接続するために必要な権限
• UNLIMITED TABLESPACE権限 – テーブルスペースを制限なしで使用できる権限

SQL> GRANT RESOURCE TO user01
  2  /

Grant succeeded.

SQL> GRANT CREATE SESSION TO user01
  2  /

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO user01
  2  /

Grant succeeded.

SQL>

ロール、権限が付与されているか確認します。

SQL> SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'USER01' 
  4  /

GRANTEE GRANTED_ROLE ADM
------- ------------ ---
USER01  RESOURCE     NO


SQL> SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'USER01'
  4  /

GRANTE PRIVILEGE                                ADM
------ ---------------------------------------- ---
USER01 UNLIMITED TABLESPACE                     NO
USER01 CREATE SESSION                           NO


SQL> 

意図したとおりに設定できていることが確認できました。

動作確認を行う

user01ユーザーが使用可能か確認するために、一度切断します。

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

user01ユーザーでログインしてみます。

$ sqlplus user01/password@PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 19 15:28:15 2022
Version 19.3.0.0.0

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

Last Successful login time: Sat Mar 19 2022 15:27:20 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 

問題なく接続できていることを確認できました。

次にテーブルを作成し、データを追加、検索をしてみましょう。

SQL> CREATE TABLE emp
  2  (
  3    empno   VARCHAR2(10) NOT NULL,
  4    empname VARCHAR2(50),
  5    gender  NUMBER(1, 0)
  6  )
  7  /

Table created.

SQL> INSERT INTO emp VALUES ('ABC', 'DEF', '1')
  2  /

1 row created.

SQL> SELECT * FROM emp 
  2  /

EMPNO      EMPNAME                                                GENDER
---------- -------------------------------------------------- ----------
ABC        DEF                                                         1

SQL>

動作確認に使用したテーブルは不要なため、削除しておきます。

SQL> DROP TABLE emp 
  2  /

以上でローカルユーザーの作成が完了しました。

参考文献

• Oracleプラガブル・データベースPDB【表領域・ユーザーの作成手順】 – そういうのがいいブログ (hatenablog.com)
• Oracle 表領域について | コピペで使う (oha-yo.com)
• Oracle Database 18c にテーブルスペース（表領域）及びスキーマ（ユーザー）を作成 – Qiita
• DBA_ROLE_PRIVS (oracle.com)
• DBA_SYS_PRIVS (oracle.com)