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.0user01ユーザーでログインしてみます。
$ 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 /以上でローカルユーザーの作成が完了しました。
![[SQL]INSERT SELECTでテーブルから直接INSERTする](https://t0k0sh1.com/wp-content/uploads/2024/02/4f66f6552a144fb40d553fbd13ae7128-300x158.png)
![[SQL]AVG関数の集計対象にNULLの値は含まれるのか？](https://t0k0sh1.com/wp-content/uploads/2024/02/695f8de8baef42d4407870747e737362-300x158.png)
![[Oracle]WITH句で階層問い合わせを行う](https://t0k0sh1.com/wp-content/uploads/2023/03/a6ac56027ff203bd46b3aaf610f9309a-300x158.jpg)
![[Oracle]ユーザー（スキーマ）を作成する](https://t0k0sh1.com/wp-content/uploads/2023/03/21350391f98f688d557ab99d8938a90b-300x158.jpg)
![[Oracle]SQL*Plusで実行計画を取得する（SET AUTOTRACE）](https://t0k0sh1.com/wp-content/uploads/2022/03/5fc2b02d1b4bd574e4a16216c6feff64-300x158.jpg)
