概要

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;

参考資料