Oracle スキーマ（ユーザ）作成

ユーザ（スキーマ）を作成するには「CREATE USER」システム権限を保持していないと作成できません。（DBAロールはCREATE USERシステム権限を持っているのでDBAロールを保持しているユーザでも作成できます。）

前準備

「 CREATE USER 」システム権限保有ユーザとロール一覧を出力するSQL

select t.GRANTEE,decode(r.ROLE ,null,'USER','ROLE') type
,decode(r.ROLE,null,u.COMMON,r.COMMON) common,t.PRIVILEGE
from
(
   select t.GRANTEE,t.PRIVILEGE from dba_sys_privs t where t.PRIVILEGE ='CREATE USER'
) t left outer join dba_roles r on t.GRANTEE = r.ROLE
left outer join dba_users u on t.GRANTEE = u.USERNAME
order by r.ROLE 
;

CREATE USERが実行できるユーザの一覧出力例

SQL> SET LIN 200
SQL> COLUMN GRANTEE FORMAT A20
SQL> /

GRANTEE              TYPE     COMMON PRIVILEGE
-------------------- -------- ------ ----------------------
DBA                  ROLE     YES    CREATE USER
DV_ACCTMGR           ROLE     YES    CREATE USER
EM_EXPRESS_ALL       ROLE     YES    CREATE USER
IMP_FULL_DATABASE    ROLE     YES    CREATE USER
WMSYS                USER     YES    CREATE USER
SYS                  USER     YES    CREATE USER
SOONI                USER     NO     CREATE USER

7行が選択されました。

SQL>

ユーザの作成

以下でユーザの作成が行えます。default tablespace は省略すると ’USERS’ 表領域が割り当てられます。

ユーザ作成SQL

create user scott
identified by tiger       -- 初期パスワード
default tablespace TBS01  -- デフォルト表領域
temporary tablespace TEMP
;

でもこのままでは作成ユーザでのログインはできず、こんなエラーがでます。

ERROR:
ORA-01045: ユーザーSCOTTにはCREATE
SESSION権限がありません。ログオンが拒否されました。

ORA-01045エラーが発生した場合は権限付与が必要

ユーザ（スキーマ）の作成だけでは、ログインもできません。なのでロール（権限）の付与を行います。今回はconnectとresourceの２つのロールを付与します。
CONNECT　・・・一般ユーザ用
RESOURCE ・・・開発者用（このロールだけでは接続できないのでconnectロールも追加してます）
DBA　　　・・・　管理者用（このロールがあればconnectロール無くても接続できます）

grant connect,resource to scott
;

SQL> create table ex02(c1 number);

表が作成されました。

SQL> insert into ex02(c1) values (10);
insert into ex02(c1) values (10)
            *
行1でエラーが発生しました。:
ORA-01950: 表領域'TBS01'に対する権限がありません


SQL>

そうなんです。デフォルト表領域として指定しても、対象の表領域に対してクオータの割り当てを行わないとinsertできません。

ORA-01950が発生した場合対象の表領域に対し使用量（クオータ）の割り当てが必要

以下SQLにてscottユーザに対し表領域(tbs01)への使用制限を無制限（UNLIMITED）にします。

alter user scott quota UNLIMITED on tbs01
;

クオータの確認

SELECT * FROM dba_ts_quotas t where t.USERNAME ='SCOTT'
;

SQL> SET LIN 200
SQL> COLUMN TABLESPACE_NAME FORMAT A20
SQL> COLUMN USERNAME FORMAT A10

SQL> SELECT * FROM dba_ts_quotas t where t.USERNAME ='SCOTT'
  2  ;

TABLESPACE_NAME      USERNAME        BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DROPPE
-------------------- ---------- ---------- ---------- ---------- ---------- ------
TBS01                SCOTT               0         -1          0         -1 NO

SQL>

ここまできてようやく、DBへのログインができ、自身でテーブルの作成やインデックスの作成ができますが、上記ロールに加えSELECT_CATALOG_ROLEも追加する事お勧めします。Oracleを使った開発をやっていると、何かとディクショナリビューとか、動的パフォーマンスビューを見たくなります。このロールを保持する事でこれら可能となります。

実は「表領域への使用量の割り当て」以外にもう一つ方法があります。こちらも参考にどうぞ。