概要
- Oracle Database 19c でスキーマによるテーブル管理をするため、ユーザーとテーブルの作成例を示す
- 動作確認環境: Oracle Database 19c (19.3.0) Enterprise Edition
スキーマによるテーブル管理
Oracle のスキーマは MySQL や PostgreSQL のデータベースという概念に近い。Oracle のスキーマには、テーブルやインデックスなどを含めることができる。
スキーマを使用したデータベース・オブジェクトの保護 - アプリケーション開発者のセキュリティの管理
スキーマとは、データベース・オブジェクトを含めることができるセキュリティ・ドメインです。ユーザーおよびロールに付与された権限によって、これらのデータベース・オブジェクトへのアクセスが制御されます。
一意スキーマでのデータベース・オブジェクトの保護 - アプリケーション開発者のセキュリティの管理
ほとんどのスキーマはユーザー名と考えることができます。つまり、ユーザーがデータベースに接続してそのデータベース・オブジェクトへのアクセスを可能にするアカウントです。
SYS ユーザーと SYSTEM ユーザー
ユーザーアカウントの作成には SYS ユーザーまたは SYSTEM ユーザーを使用する。
- SYS: 最強の権限を持つ管理ユーザー
- SYSTEM: 管理ユーザー
Oracle Database 2日でデータベース管理者 19c - ユーザー・アカウントおよびセキュリティの管理
SYSおよびSYSTEM管理ユーザー・アカウントはOracle Databaseのインストール時に自動的に作成されます。インストールの際に適用したパスワードを使用しても作成できます。どちらも自動的にDBAロールが付与されます。
スキーマ管理用アカウントを作成する
SQL*Plus にて SYSTEM アカウントで、テーブルやインデックスが属するスキーマ管理用アカウント foo を作成する。アカウント名 foo がスキーマ名となる。
CREATE USER foo IDENTIFIED BY "ここに設定したいパスワードを書く"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON users;
スキーマ管理用アカウント foo に接続権限 (CREATE SESSION) やテーブル作成権限 (CREATE TABLE) などを付与する。
GRANT CREATE SESSION TO foo;
GRANT
CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR,
CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE,
CREATE TRIGGER, CREATE TYPE TO foo;
アプリケーション用アカウントを作成する
SQL*Plus にて SYSTEM アカウントで、アプリケーション用アカウント foo_app を作成する。
CREATE USER foo_app IDENTIFIED BY "ここに設定したいパスワードを書く"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON users;
アプリケーション用アカウント foo_app に接続権限 (CREATE SESSION) を付与する。
GRANT CREATE SESSION TO foo_app;
テーブルを作成する
スキーマ管理用アカウント foo でテーブル (スキーマ名=foo, テーブル名=hoge) を作成する。
CREATE TABLE foo.hoge (
hoge_id NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
code CHAR (4 CHAR) DEFAULT 'AAAA' NOT NULL,
type NUMBER (1) NOT NULL,
data VARCHAR2 (8 CHAR),
updated_at TIMESTAMP (6),
CONSTRAINT hoge_id_pk PRIMARY KEY (hoge_id)
);
テーブルに必要な各種インデックスを作成する。
CREATE UNIQUE INDEX foo.code_ui ON foo.hoge (code);
CREATE BITMAP INDEX foo.type_bi ON foo.hoge (type);
CREATE INDEX foo.type_data_ci ON foo.hoge (type, data);
アプリケーションアカウント foo_app に hoge テーブルのレコード操作系権限を与える。
GRANT SELECT, INSERT, UPDATE, DELETE ON foo.hoge TO foo_app;
レコードを追加する
アプリケーションアカウント foo_app でレコードを追加する。
INSERT INTO
foo.hoge
(type, data, updated_at)
VALUES
(9, 'CAFEBABE', LOCALTIMESTAMP(6));
レコードが追加されたか確認する。
SELECT * FROM foo.hoge;