oracle

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の2つのロールを付与します。
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を使った開発をやっていると、何かとディクショナリビューとか、動的パフォーマンスビューを見たくなります。このロールを保持する事でこれら可能となります。

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

スポンサーリンク
コピペで使う