Chapter 10

ユーザと権限/ロール/プロファイル

Me
Me
2021.12.12に更新

ユーザ

クライアントからデータベースへ接続する際、あらかじめ作成されたユーザ情報でログインを行う必要がある. Oracle データベースの場合、インストール時に管理用ユーザとしてDBAロール(一般的な管理業務を行うのに必要な権限群)が付与されたSYSユーザおよびSYSTEMユーザが自動で作成される. また、SYSユーザには最上位権限であるSYSDBA権限が設定されており、データベースの起動/停止、リカバリやバックアップを含めた全ての管理権限が付与されている.
一般的な用途で使われるユーザは、一般ユーザと呼ばれ、利用には管理ユーザにより前もって作成されてある必要がある.

一般ユーザ

ユーザの追加方法

新規のユーザを追加する場合、CREATE USERシステム権限を持つユーザで下記のコマンドを実行する. また、ユーザ作成時に認証に関する設定も合わせて行う. (参照).

データベース認証が前提の場合

データベース認証とは、認証をデータディクショナリに格納されたユーザ情報を用いて行う手法を指す. この場合、ユーザはログイン時にユーザ名およびパスワードを指定し、認証を行う.

データベース認証の利点は下記の通り.

  • 認証がデータベース内で完結し、外部に依存しない.
  • セキュアなパスワード管理を Oracle に委託することができる.
  • 小規模であれば管理が容易.
CREATE USER <ユーザ名> 
	IDENTIFIED BY <パスワード> 

また、以下のようにオプションを指定できる (参考).

CREATE USER <ユーザ名>
	 IDENTIFIED BY <パスワード>
	 DEFAULT TABLESPACE <デフォルト表領域名>
	 TEMPORARY TABLESPACE <一時表領域名>           //処理がメモリに収まらない場合に一時的に使用される領域
	 QUOTA [<サイズ> | UNLIMITED] ON <デフォルト表領域名>   //使用可能な記憶域の最大サイズ
	 PROFILE [<プロファイル名> | DEFAULT]           //ユーザプロファイル (デフォルトはDEFAULT)
	 ACCOUNT [LOCK | UNLOCK]                 //アカウントの使用不可または使用可
	 PASSWORD EXPIRE                      //パスワード期限切れ (次回ログイン時に再設定)

ログインの際は、以下のようにユーザ名とパスワードを指定する.

 sqlplus <ユーザ名>/<パスワード>

外部認証が前提の場合 (OS認証など)

外部認証とは、認証そのものをデータベース以外に委託し、データベースへログインを行う手法を指す. OS 認証の場合、OSのユーザ名とデータベース内のユーザ名の対応関係を照らし合わせて、正しいデータベース・ユーザにログインを行う.

CREATE USER <ユーザ名> 
	IDENTIFIED EXTERNALLY 

この際、ユーザ名にはOSユーザ名に特定の接頭辞をつけたものを指定する. 接頭辞はデフォルトでops$が指定されており、初期化パラメータOS_AUTHENT_PREFIXで設定される.

SHOW PARAMETERS os_authent_prefix

出力例)  |         NAME         |   TYPE   |   VALUE   |
        |        :----:        |  :----:  |   :----:  | 
        |   os_authent_prefix  |  string  |    ops$   |   

ログインの際は、以下のようにユーザ名やパスワードを省略する.

 sqlplus /

ユーザの編集方法

既存のユーザを編集する場合、ALTER USERシステム権限を持つユーザで下記のコマンドを実行する.

ALRER USER <ユーザ名> //編集対象のユーザ名
	 IDENTIFIED BY <パスワード>
	 DEFAULT TABLESPACE <デフォルト表領域名>
	 TEMPORARY TABLESPACE <一時表領域名> 
	 QUOTA [<サイズ> | UNLIMITED] ON <デフォルト表領域名>
	 PROFILE [<プロファイル名> | DEFAULT]
	 ACCOUNT [LOCK | UNLOCK] 
	 PASSWORD EXPIRE 

ユーザの削除方法

既存のユーザを削除する場合、DROP USERシステム権限を持つユーザで下記のコマンドを実行する.

DROP USER <ユーザ名> [CASCADE]

CASCADEオプションは、削除対象のユーザが何かしらのオブジェクトを所有している場合に指定しなければならず、ユーザ情報とまとめて、そのユーザが所有している全てのオブジェクトの削除を行う.

権限

Oralceの権限には、データベース内での操作を行う権限であるシステム権限と、データベース内に存在する特定のオブジェクトに対する操作権限であるオブジェクト権限の2種類が存在する (参考).

システム権限の種類

システム権限の一例を以下に記載する.

システム権限 説明
ADMIN チェックポイント、バックアップ、移行、ユーザーの作成および削除などを実行できるシステム管理者権限
GRANT ANY PRIVILEGE 任意のシステム権限を付与/取り消しできる
GRANT ANY OBJECT PRIVILEGE 任意のオブジェクト権限を付与/取り消しできる
CREATE SESSION 新規のセッションの作成 (ログイン) を実行できる
CREATE TABLE 新規のテーブルの作成を実行できる
CREATE VIEW 新規のビューの作成を実行できる
CREATE SEQUENCE 新規のシーケンスの作成を実行できる
CREATE SYNONYM 新規のシノニムの作成を実行できる
CREATE ANY TABLE 任意のユーザが所有する新規のテーブルの作成を実行できる
CREATE ANY VIEW 任意のユーザが所有する新規のビューの作成を実行できる
CREATE ANY SEQUENCE 任意のユーザが所有する新規のシーケンスの作成を実行できる
CREATE ANY SYNONYM 任意のユーザが所有する新規のシノニムの作成を実行できる
ALTER ANY TABLE 任意のテーブルの更新を実行できる
ALTER ANY VIEW 任意のビューの更新を実行できる
ALTER ANY SEQUENCE 任意のシーケンスを更新実行できる
ALTER ANY INDEX 任意の索引を更新できる
DROP ANY TABLE 任意のテーブルを削除できる
DROP ANY VIEW 任意のビューを削除できる
DROP ANY SEQUENCE 任意のシーケンスを削除できる
DROP ANY SYNONYM 任意のシノニムを削除できる
DROP ANY INDEX 任意の索引を削除できる
SELECT ANY SEQUENCE 任意のシーケンスまたはシーケンス上のシノニムを選択できる
SELECT ANY TABLE 任意の表、ビュー、シノニム等を選択できる
INSERT ANY TABLE 任意の表にレコードを挿入できる
LOCK ANY TABLE 任意の表をロックできる

尚、システム権限の一覧はSTSTEM_PRIVILEGE_MAPから確認することができる.

  SELECT * FROM system_privilege_map;
 
  出力例) | PRIVILEGE |          NAME          | PROPERTY | 
         |   :----:  |         :----:         |  :----:  | 
         |     -3    |      ALTER SYSTEM      |     0    | 
         |     -4    |      AUDIT SYSTEM      |     0    | 
                                 ⋮
         |   -410    | DROP ANY ANALYTIC VIEW |     0    | 

管理権限

SYSユーザに付与されているSYSDBA権限などの強力な管理者権限も、システム権限の一部として管理されている. 一般ユーザのログイン情報は、データディクショナリーに保存されているため、インスタンスがOPEN状態でないとログイン処理が行えない. 対して、SYSユーザなどのデータベース管理者アカウントは、インスタンスの起動や停止などを行う際にも使用されるため、OPEN状態でなくともログインできる必要性がある. よって、管理権限が付与されているアカウントの情報は、データベース外のファイルに保存されており、一般ユーザとは異なる認証方法 (OS認証またはパスワードファイル認証) でのログインが必要となる. パスワードファイル認証に用いられるファイルはデフォルトでORACLE_HOME/dbs/orapw<ORACLE_SID>に保存される.

  //SYSDBA権限でログインを行う場合.
  sqlplus / AS sysdba

ログイン時、パスワードファイル認証をオラクルに確認させるには初期化パラメータREMOTE_LOGIN_PASSWORDFILEの値をexclusive(デフォルト)もしくはsharedに設定しておく必要がある (参考).

ユーザへのシステム権限の付与や取り消し

任意のユーザへのシステム権限の付与は以下のコマンドを用いて行う.

 GRANT <システム権限名> TO <ユーザ名>

ユーザに、付与されたシステム権限を他のユーザに付与したり、取り消したりできる権限も与える場合はさらにADMINオプションを追加する.

 GRANT <システム権限名> TO <ユーザ名> WITH ADMIN OPTION 

また、付与されているシステム権限の取り消しは以下のコマンドで行う.

 REVOKE <システム権限名> FROM <ユーザ名>

オブジェクト権限の種類

オブジェクト権限の一例を以下に記載する.

オブジェクト権限 説明
INDEX 表に索引を作成できる
INSERT INSERT文で表やシノニムを使用した表にレコードを挿入できる
UPDATE UPDATE文で表のレコードを更新できる
DELETE DELETE文で表のレコードを削除できる
SELECT SELECT文およびSELECT FOR UPDATE文で表、順序、ビューから選択できる

ユーザへのオブジェクト権限の付与や取り消し

任意のユーザへのオブジェクト権限の付与は以下のコマンドを用いて行う.

 GRANT <オブジェクト権限> ON <オブジェクト> TO <ユーザ名>

ユーザに、付与されたオブジェクト権限を他のユーザに付与できる権限も与える場合はさらにGRANTオプションを追加する.

 GRANT <システム権限名> TO <ユーザ名> WITH GRANT OPTION 

また、付与されているオブジェクト権限の取り消しは以下のコマンドで行う.

 REVOKE <オブジェクト権限> ON <オブジェクト> FROM <ユーザ名>

ロール

複数のシステム権限やオブジェクト権限をまとめてグループ化したものをロールと呼ぶ.
データベース作成時に自動で作成されるロールの一例は以下の通り.

オブジェクト権限 説明
CONNECT CREATE SESSIONシステム権限のみが含まれるロール.
DBA 全てのシステム権限を含まれるロール. SYSおよびSYSADMINに付与されており、インスタンスの起動/停止、リカバリなど一部を除いてほぼ全ての管理業務が行える.
SELECT_CATALOG_ROLE データディクショナリビューからレコードを選択できる権限を含んだロール.

ロールの作成

ロールの作成は以下のコマンドで行う.

 CRETAE ROLE <ロール名>
 // ロール使用にパスワード保護を追加する際
 // CRETAE ROLE <ロール名> IDENTIFIED BY <パスワード>

ロールの削除

ロールの削除は以下のコマンドで行う.

 DROP ROLE <ロール名>

ロールへの権限追加や削除

ロールに任意の権限を追加するには以下のコマンドを用いる.

 GRANT <システム権限|オブジェクト権限> TO <ロール名>

また、ロールにロールを付与し、構造をネストすることも可能.

 GRANT <ロール名> TO <ロール名>

ロールから任意の権限を削除するには以下のコマンドを用いる.

 REVOKE <システム権限|オブジェクト権限> FROM <ロール名>

ユーザへのロールの付与や取り消し

任意のユーザへのロールの付与は以下のコマンドを用いて行う.

 GRANT <ロール名> TO <ユーザ名>

ユーザがログインした時点では、上記のように明示的に付与されたロール(パスワード保護されているものは除く)やデフォルト・ロールに設定されているロールは有効化された状態となる. デフォルト・ロールの設定は以下のコマンドで行える (参考).

 ALTER USER <ユーザ名> DEFAULT ROLE <ロール名>

また、付与されているロールをセッション内でのみ有効化することも可能.

 SET ROLE <ロール名>
 // パスワード認証が必要な場合は
 // SET ROLE <ロール名> IDENTIFIED BY <パスワード>

付与されているロールの取り消しは以下のコマンドで行う.

 REVOKE <ロール名> FROM <ユーザ名>

プロファイル

プロファイル毎にデータベース・リソース制限を設定し、任意のユーザに設定することができる(参考1, 参考2)。

新規に作成されたユーザのプロファイルはデフォルトでDEFAULTプロファイルが指定されている. また、同じくデフォルトで作成されているORA_STIG_PROFILEプロファイルは、セキュリティ技術導入ガイド(STIG要件)に準拠した設定がされている.

既存のプロファイルの確認

作成済みのプロファイルとその制限設定内容はデータディクショナリビューDBA_PROFILESから確認できる.

//プロファイルとその設定の一覧を取得 
SELECT * FROM DBA_PROFILES;

出力例)  |      PROFILE      |     RESOURCEC_NAME    | RESOURCEC_TYPE |   LIMIT    |  …
        |       :----:      |          :----:       |     :----:     |   :----:   |  …
        |       DEFAULT     |    COMPOSITE_LIMIT    |     KERNEL     |  UNLIMITED |  …
	|       DEFAULT     |    SESSION_PER_USER   |     KERNEL     |  UNLIMITED |  …
	                             ⋮
        |  ORA_STIG_PROFILE | INACTIVE_ACCOUNT_TIME |    PASSWORD    |     35     |  …	                              

現行ユーザにかけられている制限は下記のように確認できる.

  select * from USER_RESOURCE_LIMITS;
  
  出力例) |   RESOURCE_NAME  |   LIMIT   |
         |      :----:      |   :----:  | 
	 | COMPOSITE_LIMIT  | UNLIMITED |
	 | SESSION_PER_USER | UNLIMITED |
	                 ⋮
	 |   PRIVATE_SGA    | UNLIMITED |	 
  

プロファイルの作成

プロファイルの作成には、下記の通りにCREATE PROFILE文を使用する. プロファイルの新規作成にはCREATE PROFILEシステム権限が付与されている必要がある.

  CREATE PROFILE <プロファイル名> LIMIT <制限パラメータ名> <> 

指定可能な制限パラメータはパスワード・パラメータリソース・パラメータの2種類が存在する (参考).

パスワード・パラメータ

指定可能なパスワード・パラメータは下記にまとめる.

制限パラメータ名 説明
FAILED_LOGIN_ATTEMPTS 連続ログイン失敗の許容回数. 指定値を超えて失敗した場合はアカウントがロックされる
PASSWORD_LIFE_TIME パスワードの有効期限
PASSWORD_REUSE_TIME 過去に使用したことのあるパスワードを再利用するまでに最低限空けなければいけない日数
PASSWORD_REUSE_MAX 過去に使用したことのあるパスワードを再利用するまでの最低限必要とさせるパスワードの変更回数
PASSWORD_LOCK_TIME アカウントがロックされた際に、ロックが継続される日数
PASSWORD_GRACE_TIME パスワードの有効期限が切れた後、初めてのログインから数えてパスワード変更を行える猶予の設定日数
INACTIVE_ACCOUNT_TIME 使われていないアカウントを自動ロックするまでの日数
PASSWORD_VERIFY_FUNCTION ユーザが設定したパスワードの強度を検証するためのスクリプト

リソース・パラメータ

指定可能なリソース・パラメータは下記にまとめる.

制限パラメータ名 説明
SESSION_PER_USER 同一アカウントで同時に作成できるセッションの最大数.
CPU_PER_SESSION セッション毎で使用できるCPU時間合計. 超過した場合はセッションから切断される.
CPU_PER_CALL 各SQL実行で使用できるCPU時間. 超過した場合は実行が中断されエラーが返却される.
CONNECT_TIME セッションの維持時間. 超過した場合はセッションから切断される.
IDLE_TIME セッションの非活動許容時間. 超過した場合はセッションから切断される.
LOGICAL_READS_PER_SESSION 同一セッションで読み込めるデータブロックの合計数 (プライマリおよびセカンダリ・メモリ). 超過した場合はセッションから切断される.
LOGICAL_READS_PER_CALL 各SQL実行で読み込めるデータブロックの合計数. 超過した場合は実行が中断されエラーが返却される.
COMPOSITE_LIMIT セッション毎のリソースの総コスト. コストはCPU_PER_SESSION、CONNECT_TIME、LOGICAL_READS_PER_SESSIONおよびPRIVATE_SGAの重み付き合計として計算される.
PRIVATE_SGA セッションがSGAの共有・プール内で確保できる領域の最大サイズ. 共有サーバー構成の場合のみ適応され、超過した場合は実行が中断されエラーが返却される.

プロファイルの削除

既存のプロファイルを削除する際は、DROPコマンドを下記の通りに使用する.

 DROP PROFILE <プロファイル名> [CASCADE];

CASCADEオプションは、既に削除対象のプロファイルが1人以上のユーザに設定されている場合に指定しなければならず、プロファイルの削除後はDEFAULTプロファイルがそれらのユーザに自動で再設定される.

実践例

SQL DEVELOPERとsysユーザを使用した実際の実行例を以下に記載する. 作成されたユーザ情報はデータディクショナリに登録される.

 // ユーザ名TESTの新規ユーザをパスワードを指定して作成する
 CREATE USER TEST IDENTIFIED BY password;
 // 出力例) User TESTは作成されました。
  
 // データディクショナリービューで作成されたユーザを確認する
 SELECT * FROM DBA_USERS;
 // 出力例) | USERNAME | USERID | PASSWORD | ACCOUNT_STATUS | …
           |  :----:  | :----: |  :----:  |     :----:     | …
           |   SYS    |    0   |  (null)  |      OPEN      | …
	   |  SYSTEM  |    9   |  (null)  |      OPEN      | …
	                         ⋮                           
           |   TEST   |   112  |  (null)  |      OPEN      | …

 // 付与されているロールを確認する (空)
 SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='TEST';
 // 出力例)  | GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DELEGATE_OPTION | …
 
 // セッション作成の権限が入った CONNECT ロールを ADMIN オプション付きで付与する
 GRANT CONNECT TO TEST WITH ADMIN OPTION;
 // 出力例) Grantが正常に実行されました。
 
 // 再度、付与されているロールを確認する
  SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='TEST';
 // 出力例)  | GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DELEGATE_OPTION | …
            |  TEST   |    CONNECT   |     YES      |        NO       | …