Oracleの表領域と権限を徹底解説

テーブルスペース

表領域はデータベースの論理的な区分であり、1つの表領域は1つのデータベースのみに所属することができます。すべてのデータベースオブジェクトは、指定された表領域に格納されます。しかし、主に格納されるのはテーブルなので、テーブルスペースと呼ばれる。

Oracle の多くの最適化は表領域設計の概念に基づいており、データベースは複数の表領域を含むことができ、表領域は 1 つのデータベースのみに所属することができます。表領域は複数のデータファイルを含み、データファイルは1つの表領域にのみ属することができます。

Oracle データベースは、SYSTEM 表領域という 1 つの表領域を保持しています。

テーブルスペースの代表的な用途

1. ユーザーが占有するテーブルスペースのクォータを制御する
   1. 別の表領域を作成し、その最大記憶容量を設定し、その表領域にユーザーを割り当てる。この方法では、ユーザーの記憶容量はその表領域のサイズによって制限されます。
2. データベースが占有するディスクスペースの制御
3. テーブルスペースを柔軟に配置し、データベースの入出力性能を向上させることができます。
   1. 湖南省に関連するデータファイルは湖南省のこの表領域に配置するなど、異なる種類のデータを異なる表領域に配置することが可能です。例えば、湖南省に関連するデータファイルは湖南省のこのテーブルスペースに配置され、北京に関連するデータファイルは北京のこのテーブルスペースに配置されます。
4. 大きなテーブルのソート操作
   1. このように容量の大きなテーブルオブジェクトについては、データベースのパフォーマンスを向上させるために、別のテーブルスペースに配置されることが多い。
      

デフォルトのテーブルスペース

システムでデフォルトで作成されるいくつかのテーブルスペース

1. システム
   1. すべてのディクショナリ・オブジェクトはSYSTEM表空間の内部に存在し、そこにはSYSユーザーの表、ビュー、およびストアド・プロシージャ・オブジェクトが存在します。
2. SYSAUX
   1. SYSTEM表空間のセカンダリ表空間として動作し、SYSTEM表空間の負荷を軽減します。
3. ユーザー
   1. ユーザーが作成したデータベースオブジェクトを格納する
4. UNDOTBS
   1. アンドゥ情報が格納されるアンドゥ表領域。
5. 例
   1. データベースの例で使用するテーブルスペース
6. テンポ
   1. テンポラリーテーブルスペースは、データベース内のソート、インデックス管理、ビューへのアクセスなどの操作を行うための一時的なスペースです。
      

テーブルスペースクエリーコマンド

すべてのテーブルスペースを問い合わせる

select * from V$tablespace

テーブルスペースのデータファイルの表示

select file_name,tablespace_name from dba_data_files

ユーザーのデフォルト・テーブルスペースの表示

select default_tablespace,temporary_tablespace from dba_users where username='username';

指定されたテーブルスペースにどのようなテーブルがあるのかを確認する

select table_name,tablespace_name from dba_tables where tablespace_name='tablespace_name'

テーブルスペースの作成

create tablespace test_data datafile 'test_data.dbf' size 32M autoextend on next 32M maxsize 2048M extent management local

一時的なテーブルスペースの作成

create temporary tablespcae test_temp tempfile 'test_temp.dbf' size 32M autoextend on next 32M maxsize 2048M extent management local

テーブルスペースの削除

drop tablespace tablespace name including contents and datafiles

ユーザにテーブルスペースを与える

create user username identified by password default tablespace default tablespace temporary tablespace temporary tablespace

ユーザーとテーブルスペースの関係

1人のユーザーが1つ以上の表領域を使用することができ、1つの表領域を複数のユーザーが使用することができます。

表領域は物理的なデータファイルとの関係のみであり、表領域は複数のデータファイルを含むことができ、データファイルは1つの表領域にのみ属することができます。

ユーザーのデフォルトの表領域を表示する

select username,deafult_tablespace from dba_users where username='username'

現在のユーザーが所有するテーブルを表示する

select table_name from user_tables

パーミッションカテゴリ

1. システムパーミッション

ユーザーがデータベースを利用するための権限をシステムで指定します。(システムの権限はユーザーに対するものです)。システム権限は、DBAユーザーによってのみ付与されます

この2つのユーザー（sys、system）から始まります。

2. オブジェクトのパーミッション

テーブルまたはビューのアクセス権の他のユーザーへの何らかの許可ユーザー。(これはテーブルまたはビューのためのものです)

システムパーミッション

テーブルの作成、インデックスの作成、インスタンスへの接続など、ユーザーが特定のデータベース操作を実行できるようにします。

1. dba ロール：すべての権限を持ち、システム上で最も高い権限を持ちます。
2. リソースロール。リソース権限を持つユーザーは、データベース・オブジェクトにのみアクセスすることができます。
3. 接続する役割。接続権限を持つユーザーはOracleへのログインのみ可能で、データベースオブジェクトにはアクセスできない

注：上記はすべてロールであり、個別の権限ではなく、権限の集合体です。

すべてのシステムパーミッションを表示する

SELECT * FROM SYSTEM_PRIVILEGE_MAP

システムパーミッション

ビューの読み込み、特定のカラムの更新、ストアドプロシージャの実行など、特定のオブジェクトを操作することを許可する。

select, update, insert, alter, index, delete, all ...... //all includes all permissions

execute //execute procedure privilege

ユーザーを認証する

システムパーミッション

grant system privileges to username [WITH ADMIN OPTION]
grant connect, resource to username;

オブジェクトのアクセス権

grant object privileges on object to user [WITH GRANT OPTION]
grant select, update, insert on product to user;
grant all on product to user name; // all means all object permissions
grant all on product to user name; // public means all users

ユーザー権限の取り消し

システム権限の取り消し

revoke system privileges from user;
revoke resource from username;

オブジェクトのパーミッションの取り消し

revoke object permissions on object from user
revoke select, update on product from user

役割

ロールとは、パーミッションの管理を容易にするための権限のセットで、各ロールは、ある特定の管理タスクを実行するために使用されます。

• ロールの作成／ロールの削除

create role role name / drop role role name

• ロールに権限を与える

システム権限：ロールにシステム権限を付与する

オブジェクトの権限：オブジェクトの権限をロールに付与する

• ロールパーミッションの取り消し

ロールからシステムパーミッションを剥奪する

ロールからオブジェクトのパーミッションを取り消す

表示権限

現在のユーザーに対するすべてのシステムパーミッションを表示する

select * from user_sys_privs

現在のユーザが所属するロールを表示します。

select * from user_role_privs

ロールはどのようなシステム権限を持っていますか

select * from role_sys_privs

ロールが持っているオブジェクトの権限（sysユーザーの下）

select * from role_tab_privs

ユーザーが持っているオブジェクトのパーミッションについて

select * from user_tab_privs

概要

Oracleの表領域と権限に関する記事はこちらです。Oracleの表領域と権限に関する詳しい情報は、スクリプトハウスの過去の記事を検索するか、以下の関連記事を引き続きご覧ください。