Oracle Database10g ユーザの管理

Oracle Database10g ユーザの管理

Oracle | Oracle Database10g | {{include_html banner_html, "!Database"}}

ユーザの作成

CREATE USER

create user exam                  -- ユーザー名
 identified by abc123             -- 認証方式
 password expire                  -- 最初のログインでパスワード変更を促す 
 default tablespace users         -- デフォルト表領域
 temporary tablespace temp        -- 一時表領域
 quota 5m on users                -- 割当管理
 account lock                     -- アカウントのロック
 profile default                  -- プロファイルの指定
 /

IDENTIFIED { BY パスワード | EXTERNALLY | GLOBALLY AS '外部名' }

パスワード認証

• データベースでパスワード管理
• プロファイルで制御可能

EXTERNALLY(OS認証)

• OSまたはネットワーク認証サービスでパスワード管理
• ユーザー名には OS_AUTHENT_PREFIX 初期化パラメータで指定されたプレフィックスをつける
• 初期化パラメータファイルのREMOTE_LOGIN_PASSWORDFILEをSHAREDにする

SQL> alter system set remote_login_passwordfile = SHARED scope=spfile;

System altered.

• Oracle 再起動

SQL> show parameters OS_AUTHENT_PREFIX;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- --------------------
os_authent_prefix                    string                 ops$

• 作成してみる

SQL> create user ops$oracle
  2   identified externally;

ユーザーが作成されました。
SQL> grant create session to ops$oracle;

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

• sqlplus / でログイン

$ sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 1 10:17:58 2009

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> 

• 成功

GLOBALLY(グローバル認証)

• Oracle Advanced Securityオプションによる認証方式
• バイオメトリック、X.509証明書、トークンデバイス、Oracle Internet Directory(LDAP準拠)を使用してユーザ識別

ACCOUNT {LOCK | UNLOCK}

• アカウントをロックしておくことができる(デフォルトはアンロック)

SQL> conn exam/abc123@oradb1
ERROR:
ORA-28000: the account is locked

• ALTER USERで解除

SQL> alter user exam account unlock;

ユーザーが変更されました。

PASSWORD EXPIRE

• 最初のログイン時にパスワード変更を促される。

システム権限

• ただし、これだけでは、SQL Plusからログインできない。

SQL> conn exam/abc123@oradb1
ERROR:
ORA-01045: user EXAM lacks CREATE SESSION privilege; logon denied

• CREATE SESSIONシステム権限を付与

SQL> conn system/xxxxx@oradb1 
接続されました。
SQL> grant create session to exam;

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

• 接続はできるようになる

SQL> conn exam/abc123@oradb1
接続されました。

DEFAULT TABLESPACE 表領域名

• ユーザーが作成するオブジェクトを格納するデフォルトの表領域を指定。
• 省略した場合、データベースのデフォルトの表領域に格納。
• データベースのデフォルトの表領域が指定されていない場合、SYSTEM表領域に格納。

<blockquote>ローカル管理の一時表領域（UNDO表領域を含む）またはディクショナリ管理の一時表領域は、ユーザーのデフォルトの表領域として指定できない。</blockquote>

• 表領域の管理

データベースの設定値を確認

SQL> select property_name, property_value from database_properties
  2  where property_name like 'DEFAULT%'
  3  /

PROPERTY_NAME                                                PROPERTY_VALUE
------------------------------------------------------------ -------------------------
DEFAULT_TEMP_TABLESPACE                                      TEMP
DEFAULT_PERMANENT_TABLESPACE                                 USERS
DEFAULT_TBS_TYPE                                             SMALLFILE

TEMPORARY TABLESPACE 一時表領域名

• 一時セグメントが確保される表領域または表領域グループを指定。
• 省略した場合、データベースのデフォルトの一時表領域に格納される。
• データベースのデフォルトの一時表領域が指定されていない場合は、SYSTEM表領域に格納される。

<blockquote>一時表領域で、標準ブロック・サイズである必要がある。</blockquote> <blockquote>UNDO表領域または自動セグメント領域管理の表領域にできない。</blockquote>

QUOTA {サイズ | UNLIMITED} ON 表領域名

• 表領域内に割り当てることができる最大サイズを指定できる。
• 複数の表領域に対して複数のQUOTA句を指定できる。
• UNLIMITEDを使用すると、表領域の領域を無制限に割り当て可能。

<blockquote>一時表領域には指定できない。</blockquote>

PROFILE

• ユーザーに割り当てるプロファイルを指定。
• プロファイルによって、ユーザーが使用できるデータベース・リソース容量が制限。
• 省略した場合、DEFAULTプロファイルがユーザーに割り当て。

例

• CREATE PROFILE - プロファイルを作成
• ALTER USER - ユーザにプロファイルを設定
• ALTER SYSTEM - リソース制限を使用可能に

SQL> create profile limited limit
  2  sessions_per_user  1
  3  cpu_per_session 10
  4  cpu_per_call 1
  5  connect_time 5
  6  idle_time 1
  7  logical_reads_per_session 10
  8  logical_reads_per_call 5
  9  private_sga 4k
 10  composite_limit 50000
 11  /

プロファイルが作成されました。

SQL> alter user exam profile limited
  2  ;

ユーザーが変更されました。

SQL> alter system set resource_limit = true; 

システムが変更されました。

パラメータ	内容
SESSIONS_PER_USER	ユーザーを制限する同時セッション数。
CPU_PER_SESSION	1セッション当たりのCPU時間制限。100分の1秒単位。
CPU_PER_CALL	1コール（解析、実行またはフェッチ）当たりのCPU時間制限。100分の1秒単位。
CONNECT_TIME	1セッション当たりの合計経過時間制限を指定。分単位。
IDLE_TIME	セッション中の連続的な非活動時間の長さを制限。分単位。長時間実行の問合せなどの処理は、この制限を受けない。
LOGICAL_READS_PER_SESSION	1セッション中に読み込まれるデータ・ブロックの数の制限を指定。
LOGICAL_READS_PER_CALL	SQL文（解析、実行またはフェッチ）を処理する1つのコールで読み込まれるデータ・ブロックの数の制限を指定。
PRIVATE_SGA	1つのセッションでシステム・グローバル領域（SGA）の共有プール内に割り当てることができるプライベート領域の大きさを指定。
COMPOSITE_LIMIT	1セッション当たりのリソースの総コストをサービス単位で指定します。サービス単位の合計は、CPU_PER_SESSION、CONNECT_TIME、LOGICAL_READS_PER_SESSIONおよびPRIVATE_SGAの重み付き合計として計算。

制限違反の例

SQL> conn exam/abc123@oradb1
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

SQL> select * from all_tables;
select * from all_tables
               *
行1でエラーが発生しました。:
ORA-00604: error occurred at recursive SQL level 1
ORA-02393: exceeded call limit on CPU usage

SQL> select table_name from all_tables;
select table_name from all_tables
*
行1でエラーが発生しました。:
ORA-02396: exceeded maximum idle time, please connect again

権限の付与

• Oracleでは権限は、データベースに対して行う操作の権限である、システム権限と、個々のオブジェクト操作の権限であるオブジェクト権限の2つに分類できる。
• システム権限はデータベース管理者が付与し、オブジェクト権限はオブジェクト所有者が付与する。

システム権限

• ANYがついた権限はすべてのスキーマに対して権限が及ぶ
• WITH ADMIN OPTION を指定すると、自分が付与された権限を他ユーザに与えることが可能となる

GRANT システム権限名 [ PUBLIC [,･･･] [WITH ADMIN OPTION ,･･･] TO ユーザー名 ]

システム権限一覧

システム権限	内容
CREATE TABLE	権限を付与したスキーマ内での表の作成
CREATE ANY TABLE	任意のスキーマ内での表の作成.表を定義するための割当て制限が必要
INSERT ANY TABLE	任意のスキーマ内の表またはビューへの行の挿入
SELECT ANY TABLE	任意のスキーマ内の表、ビューまたはマテリアライズド・ビューの問合せ
UPDATE ANY TABLE	任意のスキーマ内の表またはビューの行の更新
CREATE TABLESPACE	表領域の作成
UNLIMITED TABLESPACE	任意の表領域の無制限な使用。設定されている任意の割当て制限を上書き。このシステム権限をロールに付与することはできません。
CREATE USER	ユーザーの作成
ALTER USER	任意のユーザーの変更
SYSDBA	※1参照
SYSOPER	※2参照

SYSDBA システム権限(※1)

• STARTUPおよびSHUTDOWN操作の実行
• ALTER DATABASE（オープン、マウント、バックアップまたはキャラクタ・セットの変更）
• CREATE DATABASE
• ARCHIVELOGおよびRECOVERY
• CREATE SPFILE
• RESTRICTED SESSION権限を含みます。

SYSOPER システム権限(※2)

• STARTUPおよびSHUTDOWN操作の実行
• ALTER DATABASE（オープン、マウントまたはバックアップ）
• ARCHIVELOGおよびRECOVERY
• CREATE SPFILE
• RESTRICTED SESSION権限を含みます。

CREATE TABLE 権限の付与

• テーブル作成ができない

SQL> show user
ユーザーは"EXAM"です
SQL> create table test(id number, name char(10));
create table test(id number, name char(10))
* 
行1でエラーが発生しました。:
ORA-01031: insufficient privileges

• SYSTEMから権限付与

SQL> show user
ユーザーは"SYSTEM"です。
SQL> grant create table to exam with admin option;

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

• 表を作成

SQL> show user
ユーザーは"EXAM"です。
SQL> create table test(id number, name char(10));

表が作成されました。

• ユーザーに対してシステム権限を付与する

Roleおよびユーザーに付与されているシステム権限の一覧

• DBA_SYS_PRIVSを使用する

SQL> select * from dba_sys_privs
  2  where grantee = 'EXAM'
  3  /

GRANTEE                                                      PRIVILEGE          
------------------------------------------------------------ ------------------
EXAM                                                         CREATE SESSION     
EXAM                                                         CREATE TABLE       

オブジェクト権限

• スキーマ内のオブジェクトに対してアクセス権を与えることができる。
• オブジェクト権限を付与されない限り、自スキーマのオブジェクトにしかアクセスできない。

GRANT オブジェクト権限名 | ALL [,･･･] ON オブジェクト名
  TO ユーザー名 | PUBLIC [,･･･] [WITH GRANT OPTION]

• ALLを指定すると、Grant Option付きで付与されているオブジェクト権限に対するすべての権限を付与できます。
• With Grant Optionを指定すると、権限受領者による、他のユーザーまたはロールに対するオブジェクト権限の付与を許可できます。

オブジェクト権限とその権限によって許可される操作 から一部抜粋

• 表権限

オブジェクト権限	許可される操作
SELECT	SELECT文での表の問合せ。
UPDATE	UPDATE文での表のデータの変更。
INSERT	INSERT文での表への新しい行の追加。
DELETE	DELETE文での表の行の削除。
ALTER	ALTER TABLE文での表定義の変更。

• ビュー権限

オブジェクト権限	許可される操作
SELECT	SELECT文でのビューの問合せ。
UPDATE	UPDATE文でのビューのデータの変更。
INSERT	INSERT文でのビューへの新しい行の追加。
DELETE	DELETE文でのビューの行の削除。
UNDER	ビューのサブビューの作成。

• 順序権限

オブジェクト権限	許可される操作
SELECT	CURRVAL疑似列およびNEXTVAL疑似列を使用した順序の値の検査および増分。
ALTER	ALTER SEQUENCE文での順序定義の変更。

付与例

• exam2ユーザを作成し、接続し、examユーザのtest表をselect

SQL> select * from exam.test;
select * from exam.test
                   *
行1でエラーが発生しました。:
ORA-00942: table or view does not exist

• examユーザから、exam2へtest表へのselect権限を付与

SQL> grant select on test to exam2;

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

• exam2ユーザで、select、および insert。insertは失敗する。

SQL> select * from exam.test;

        ID NAME
---------- --------------------
         1 abc

SQL> insert into exam.test values(2, 'def');
insert into exam.test values(2, 'def')
                 *
行1でエラーが発生しました。:
ORA-01031: insufficient privileges

• exam2ユーザへtest表へのすべての権限をgrant optionつきで付与

SQL> grant all on test to exam2 with grant option;

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

• exam2でデータを削除してみる。truncateはできないが、deleteはできる

SQL> truncate table exam.test;
truncate table exam.test
                    *
行1でエラーが発生しました。:
ORA-01031: insufficient privileges


SQL> delete from exam.test;

1行が削除されました。

• 権限の取り消し

SQL> revoke all on test from exam2;

取消しが成功しました。

ユーザーに付与されているオブジェクト権限のリスト

データディクショナリ	内容
DBA_TAB_PRIVS	オブジェクト権限（列固有の権限を除く）
DBA_COL_PRIVS	列固有の権限

SQL> select table_name, privilege, grantable from dba_tab_privs
  2  where grantee = 'EXAM_MANAGER'
  3  / 

TABLE_NAME   PRIVILEGE              GRANTA
------------ ---------------------- ------
TEST         FLASHBACK              NO
TEST         DEBUG                  NO
TEST         QUERY REWRITE          NO
TEST         ON COMMIT REFRESH      NO
TEST         UPDATE                 NO
TEST         SELECT                 NO
TEST         INSERT                 NO
TEST         DELETE                 NO
TEST         ALTER                  NO

ロール

• 目的ごとに作成した権限のセット
• ユーザーに直接権限を与えるのではなく、ロールをユーザーに与える
• システム権限、オブジェクト権限を付与できる
• ロールにロールを付与できる
• 有効/無効を切り替えることができる
• パスワードを設定できる
• WITH ADMIN OPTIONを指定し、他のユーザに付与することを許可できる

CREATE ROLE ロール名
  [IDENTIFIED { BY パスワード | EXTERNALLY | GLOBALLY AS '外部名' }

例

• ロールの作成

SQL> conn system/abc123@oradb1
接続されました。

SQL> create role exam_manager
  2  identified by abc123
  3  /

ロールが作成されました。

• ロールに権限を付与し、ロールをexam2に付与

SQL> grant all on exam.test to exam_manager;

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

SQL> grant exam_manager to exam2;

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

• これだけでは、exam2は、exam.test表を操作できない

SQL> select * from exam.test;
select * from exam.test
                    *
行1でエラーが発生しました。:
ORA-00942: table or view does not exist

• ロールを有効にする

SQL> set role exam_manager identified by abc123;

ロールが設定されました。

• 表操作可能に

SQL> insert into exam.test values(3,'ghi');

1行が作成されました。

現在使用可能なRole一覧

• SESSION_ROLESデータディクショナリビューに問い合わせる

SQL> set role exam_manager identified by north123;

ロールが設定されました。

SQL> select * from session_roles;

ROLE
------------------------------------------------------------
EXAM_MANAGER

---

{{include_html banner_html, "!Oracle"}}