Oracle Database 19c でスキーマによるテーブル管理 (ユーザーとテーブルの作成例)

## 概要

- Oracle Database 19c でスキーマによるテーブル管理をするため、ユーザーとテーブルの作成例を示す
- 動作確認環境: Oracle Database 19c (19.3.0) Enterprise Edition

## スキーマによるテーブル管理

Oracle のスキーマは MySQL や PostgreSQL のデータベースという概念に近い。Oracle のスキーマには、テーブルやインデックスなどを含めることができる。

[スキーマを使用したデータベース・オブジェクトの保護 - アプリケーション開発者のセキュリティの管理](https://docs.oracle.com/cd/F19136_01/dbseg/managing-security-for-application-developers.html#GUID-7B5B46D4-7547-421C-930E-A0C375B60F6D)

> スキーマとは、データベース・オブジェクトを含めることができるセキュリティ・ドメインです。ユーザーおよびロールに付与された権限によって、これらのデータベース・オブジェクトへのアクセスが制御されます。

[一意スキーマでのデータベース・オブジェクトの保護 - アプリケーション開発者のセキュリティの管理](https://docs.oracle.com/cd/F19136_01/dbseg/managing-security-for-application-developers.html#GUID-C3B77A98-D37A-4375-AD73-A3D34A7DB47E)

> ほとんどのスキーマはユーザー名と考えることができます。つまり、ユーザーがデータベースに接続してそのデータベース・オブジェクトへのアクセスを可能にするアカウントです。

## SYS ユーザーと SYSTEM ユーザー

ユーザーアカウントの作成には SYS ユーザーまたは SYSTEM ユーザーを使用する。

- SYS: 最強の権限を持つ管理ユーザー
- SYSTEM: 管理ユーザー

[Oracle Database 2日でデータベース管理者 19c - ユーザー・アカウントおよびセキュリティの管理](https://docs.oracle.com/cd/F19136_01/admqs/administering-user-accounts-and-security.html#GUID-CF1CD853-AF15-41EC-BC80-61918C73FDB5)

> SYSおよびSYSTEM管理ユーザー・アカウントはOracle Databaseのインストール時に自動的に作成されます。インストールの際に適用したパスワードを使用しても作成できます。どちらも自動的にDBAロールが付与されます。

## スキーマ管理用アカウントを作成する

SQL*Plus にて SYSTEM アカウントで、テーブルやインデックスが属するスキーマ管理用アカウント foo を作成する。アカウント名 foo がスキーマ名となる。

```sql
CREATE USER foo IDENTIFIED BY "ここに設定したいパスワードを書く"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON users;
```

スキーマ管理用アカウント foo に接続権限 (CREATE SESSION) やテーブル作成権限 (CREATE TABLE) などを付与する。

```sql
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 を作成する。

```sql
CREATE USER foo_app IDENTIFIED BY "ここに設定したいパスワードを書く"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON users;
```

アプリケーション用アカウント foo_app に接続権限 (CREATE SESSION) を付与する。

```sql
GRANT CREATE SESSION TO foo_app;
```

## テーブルを作成する

スキーマ管理用アカウント foo でテーブル (スキーマ名=foo, テーブル名=hoge) を作成する。

```sql
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)
);
```

テーブルに必要な各種インデックスを作成する。

```sql
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 テーブルのレコード操作系権限を与える。

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON foo.hoge TO foo_app;
```

## レコードを追加する

アプリケーションアカウント foo_app でレコードを追加する。

```sql
INSERT INTO
  foo.hoge
    (type, data, updated_at)
  VALUES
    (9, 'CAFEBABE', LOCALTIMESTAMP(6));
```

レコードが追加されたか確認する。

```sql
SELECT * FROM foo.hoge;
```

## 参考資料

- [Oracle Databaseセキュリティ・ガイド 19c](https://docs.oracle.com/cd/F19136_01/dbseg/)
  - [ユーザー・アカウントの作成](https://docs.oracle.com/cd/F19136_01/dbseg/managing-security-for-oracle-database-users.html#GUID-F78AE647-184C-4790-9E95-493787CACDE1)
  - [Oracle Databaseのインストールで事前に定義されているロール](https://docs.oracle.com/cd/F19136_01/dbseg/configuring-privilege-and-role-authorization.html#GUID-A5B26A03-32CF-4F5D-A6BE-F2452AD8CB8A)
  - [スキーマを使用したデータベース・オブジェクトの保護](https://docs.oracle.com/cd/F19136_01/dbseg/managing-security-for-application-developers.html#GUID-7B5B46D4-7547-421C-930E-A0C375B60F6D)
  - [アプリケーションでのオブジェクト権限](https://docs.oracle.com/cd/F19136_01/dbseg/managing-security-for-application-developers.html#GUID-B6289F9C-B043-410B-A586-50A996F055B4)
- [Oracle Database SQL言語リファレンス, 19c](https://docs.oracle.com/cd/F19136_01/sqlrf/)
  - [GRANT](https://docs.oracle.com/cd/F19136_01/sqlrf/GRANT.html#GUID-20B4E2C0-A7F8-4BC8-A5E8-BE61BDC41AC3)
- [Oracle Database 2日でデータベース管理者 19c](https://docs.oracle.com/cd/F19136_01/admqs/)
  - [SYSおよびSYSTEMユーザー](https://docs.oracle.com/cd/F19136_01/admqs/administering-user-accounts-and-security.html#GUID-CF1CD853-AF15-41EC-BC80-61918C73FDB5)