!!!Oracle Database10g ユーザの管理 [Oracle][Oracle Database10g] {{include_html banner_html, "!Database"}} !!ユーザの作成 ![CREATE USER|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_8.html#14522] create user exam -- ユーザー名 identified by abc123 -- 認証方式 password expire -- 最初のログインでパスワード変更を促す default tablespace users -- デフォルト表領域 temporary tablespace temp -- 一時表領域 quota 5m on users -- 割当管理 account lock -- アカウントのロック profile default -- プロファイルの指定 / !IDENTIFIED { BY パスワード | EXTERNALLY | GLOBALLY AS '外部名' } ::パスワード認証 *データベースでパスワード管理 *プロファイルで制御可能 ::EXTERNALLY(OS認証) *OSまたはネットワーク認証サービスでパスワード管理 *ユーザー名には OS_AUTHENT_PREFIX 初期化パラメータで指定されたプレフィックスをつける *初期化パラメータファイルの[REMOTE_LOGIN_PASSWORDFILE|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19228-03/initparams.htm#90315]をSHAREDにする SQL> alter system set remote_login_passwordfile = SHARED scope=spfile; System altered. *Oracle 再起動 SQL> show parameters OS_AUTHENT_PREFIX; NAME TYPE VALUE ------------------------------------ ---------------------- -------------------- os_authent_prefix string ops$ *作成してみる SQL> create user ops$oracle 2 identified externally; ユーザーが作成されました。 SQL> grant create session to ops$oracle; 権限付与が成功しました。 *sqlplus / でログイン $ sqlplus / SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 1 10:17:58 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> *成功 ::GLOBALLY(グローバル認証) *Oracle Advanced Securityオプションによる認証方式 *バイオメトリック、X.509証明書、トークンデバイス、Oracle Internet Directory(LDAP準拠)を使用してユーザ識別 !ACCOUNT {LOCK | UNLOCK} *アカウントをロックしておくことができる(デフォルトはアンロック) SQL> conn exam/abc123@oradb1 ERROR: ORA-28000: the account is locked *[ALTER USER|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_4.html#14589]で解除 SQL> alter user exam account unlock; ユーザーが変更されました。 !PASSWORD EXPIRE *最初のログイン時にパスワード変更を促される。 {{ref_image ora_pass_expire.jpg}} ::システム権限 *ただし、これだけでは、SQL Plusからログインできない。 SQL> conn exam/abc123@oradb1 ERROR: ORA-01045: user EXAM lacks CREATE SESSION privilege; logon denied *CREATE SESSIONシステム権限を付与 SQL> conn system/xxxxx@oradb1 接続されました。 SQL> grant create session to exam; 権限付与が成功しました。 *接続はできるようになる SQL> conn exam/abc123@oradb1 接続されました。 !DEFAULT TABLESPACE 表領域名 *ユーザーが作成するオブジェクトを格納するデフォルトの表領域を指定。 *省略した場合、データベースのデフォルトの表領域に格納。 *データベースのデフォルトの表領域が指定されていない場合、SYSTEM表領域に格納。 ""ローカル管理の一時表領域（UNDO表領域を含む）またはディクショナリ管理の一時表領域は、ユーザーのデフォルトの表領域として指定できない。 *[表領域の管理|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19224-02/tspaces.htm#691776] ::データベースの設定値を確認 SQL> select property_name, property_value from database_properties 2 where property_name like 'DEFAULT%' 3 / PROPERTY_NAME PROPERTY_VALUE ------------------------------------------------------------ ------------------------- DEFAULT_TEMP_TABLESPACE TEMP DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_TBS_TYPE SMALLFILE !TEMPORARY TABLESPACE 一時表領域名 *一時セグメントが確保される表領域または表領域グループを指定。 *省略した場合、データベースのデフォルトの一時表領域に格納される。 *データベースのデフォルトの一時表領域が指定されていない場合は、SYSTEM表領域に格納される。 ""一時表領域で、標準ブロック・サイズである必要がある。 ""UNDO表領域または自動セグメント領域管理の表領域にできない。 !QUOTA {サイズ | UNLIMITED} ON 表領域名 *表領域内に割り当てることができる最大サイズを指定できる。 *複数の表領域に対して複数のQUOTA句を指定できる。 *UNLIMITEDを使用すると、表領域の領域を無制限に割り当て可能。 ""一時表領域には指定できない。 !PROFILE *ユーザーに割り当てるプロファイルを指定。 *プロファイルによって、ユーザーが使用できるデータベース・リソース容量が制限。 *省略した場合、DEFAULTプロファイルがユーザーに割り当て。 ::[例|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_6.html#22969] *[CREATE PROFILE|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_6.html#21753] - プロファイルを作成 *[ALTER USER|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_4.html#14589] - ユーザにプロファイルを設定 *[ALTER SYSTEM|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_2.html#22628] - リソース制限を使用可能に SQL> create profile limited limit 2 sessions_per_user 1 3 cpu_per_session 10 4 cpu_per_call 1 5 connect_time 5 6 idle_time 1 7 logical_reads_per_session 10 8 logical_reads_per_call 5 9 private_sga 4k 10 composite_limit 50000 11 / プロファイルが作成されました。 SQL> alter user exam profile limited 2 ; ユーザーが変更されました。 SQL> alter system set resource_limit = true; システムが変更されました。 ,パラメータ,内容 ,SESSIONS_PER_USER,ユーザーを制限する同時セッション数。 ,CPU_PER_SESSION,1セッション当たりのCPU時間制限。100分の1秒単位。 ,CPU_PER_CALL,1コール（解析、実行またはフェッチ）当たりのCPU時間制限。100分の1秒単位。 ,CONNECT_TIME,1セッション当たりの合計経過時間制限を指定。分単位。 ,IDLE_TIME,セッション中の連続的な非活動時間の長さを制限。分単位。長時間実行の問合せなどの処理は、この制限を受けない。 ,LOGICAL_READS_PER_SESSION,1セッション中に読み込まれるデータ・ブロックの数の制限を指定。 ,LOGICAL_READS_PER_CALL,SQL文（解析、実行またはフェッチ）を処理する1つのコールで読み込まれるデータ・ブロックの数の制限を指定。 ,PRIVATE_SGA,1つのセッションでシステム・グローバル領域（SGA）の共有プール内に割り当てることができるプライベート領域の大きさを指定。 ,COMPOSITE_LIMIT,1セッション当たりのリソースの総コストをサービス単位で指定します。サービス単位の合計は、CPU_PER_SESSION、CONNECT_TIME、LOGICAL_READS_PER_SESSIONおよびPRIVATE_SGAの重み付き合計として計算。 ::制限違反の例 SQL> conn exam/abc123@oradb1 ERROR: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit SQL> select * from all_tables; select * from all_tables * 行1でエラーが発生しました。: ORA-00604: error occurred at recursive SQL level 1 ORA-02393: exceeded call limit on CPU usage SQL> select table_name from all_tables; select table_name from all_tables * 行1でエラーが発生しました。: ORA-02396: exceeded maximum idle time, please connect again !![権限の付与|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_9.html#25446] *Oracleでは権限は、データベースに対して行う操作の権限である、システム権限と、個々のオブジェクト操作の権限であるオブジェクト権限の2つに分類できる。 *システム権限はデータベース管理者が付与し、オブジェクト権限はオブジェクト所有者が付与する。 !システム権限 *ANYがついた権限はすべてのスキーマに対して権限が及ぶ *WITH ADMIN OPTION を指定すると、自分が付与された権限を他ユーザに与えることが可能となる GRANT システム権限名 [,･･･] TO ユーザー名 | PUBLIC [,･･･] [WITH ADMIN OPTION] [システム権限一覧|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_9.html#3445231] ,システム権限,内容 ,CREATE TABLE,権限を付与したスキーマ内での表の作成 ,CREATE ANY TABLE,任意のスキーマ内での表の作成.表を定義するための割当て制限が必要 ,INSERT ANY TABLE,任意のスキーマ内の表またはビューへの行の挿入 ,SELECT ANY TABLE,任意のスキーマ内の表、ビューまたはマテリアライズド・ビューの問合せ ,UPDATE ANY TABLE,任意のスキーマ内の表またはビューの行の更新 ,CREATE TABLESPACE,表領域の作成 ,UNLIMITED TABLESPACE,任意の表領域の無制限な使用。設定されている任意の割当て制限を上書き。このシステム権限をロールに付与することはできません。 ,CREATE USER,ユーザーの作成 ,ALTER USER,任意のユーザーの変更 ,SYSDBA,※1参照 ,SYSOPER,※2参照 ::SYSDBA システム権限(※1) *STARTUPおよびSHUTDOWN操作の実行 *ALTER DATABASE（オープン、マウント、バックアップまたはキャラクタ・セットの変更） *CREATE DATABASE *ARCHIVELOGおよびRECOVERY *CREATE SPFILE *RESTRICTED SESSION権限を含みます。 ::SYSOPER システム権限(※2) *STARTUPおよびSHUTDOWN操作の実行 *ALTER DATABASE（オープン、マウントまたはバックアップ） *ARCHIVELOGおよびRECOVERY *CREATE SPFILE *RESTRICTED SESSION権限を含みます。 ::CREATE TABLE 権限の付与 *テーブル作成ができない SQL> show user ユーザーは"EXAM"です SQL> create table test(id number, name char(10)); create table test(id number, name char(10)) * 行1でエラーが発生しました。: ORA-01031: insufficient privileges *SYSTEMから権限付与 SQL> show user ユーザーは"SYSTEM"です。 SQL> grant create table to exam with admin option; 権限付与が成功しました。 *表を作成 SQL> show user ユーザーは"EXAM"です。 SQL> create table test(id number, name char(10)); 表が作成されました。 *[ユーザーに対してシステム権限を付与する|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_9.html#27717] ::Roleおよびユーザーに付与されているシステム権限の一覧 *DBA_SYS_PRIVSを使用する SQL> select * from dba_sys_privs 2 where grantee = 'EXAM' 3 / GRANTEE PRIVILEGE ------------------------------------------------------------ ------------------ EXAM CREATE SESSION EXAM CREATE TABLE !オブジェクト権限 *スキーマ内のオブジェクトに対してアクセス権を与えることができる。 *オブジェクト権限を付与されない限り、自スキーマのオブジェクトにしかアクセスできない。 GRANT オブジェクト権限名 | ALL [,･･･] ON オブジェクト名 TO ユーザー名 | PUBLIC [,･･･] [WITH GRANT OPTION] *ALLを指定すると、Grant Option付きで付与されているオブジェクト権限に対するすべての権限を付与できます。 *With Grant Optionを指定すると、権限受領者による、他のユーザーまたはロールに対するオブジェクト権限の付与を許可できます。 ::[オブジェクト権限とその権限によって許可される操作 |http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_9.html#12463]から一部抜粋 *表権限 ,オブジェクト権限,許可される操作 ,SELECT,SELECT文での表の問合せ。 ,UPDATE,UPDATE文での表のデータの変更。 ,INSERT,INSERT文での表への新しい行の追加。 ,DELETE,DELETE文での表の行の削除。 ,ALTER,ALTER TABLE文での表定義の変更。 *ビュー権限 ,オブジェクト権限,許可される操作 ,SELECT,SELECT文でのビューの問合せ。 ,UPDATE,UPDATE文でのビューのデータの変更。 ,INSERT,INSERT文でのビューへの新しい行の追加。 ,DELETE,DELETE文でのビューの行の削除。 ,UNDER,ビューのサブビューの作成。 *順序権限 ,オブジェクト権限,許可される操作 ,SELECT,CURRVAL疑似列およびNEXTVAL疑似列を使用した順序の値の検査および増分。 ,ALTER,ALTER SEQUENCE文での順序定義の変更。 ::付与例 *exam2ユーザを作成し、接続し、examユーザのtest表をselect SQL> select * from exam.test; select * from exam.test * 行1でエラーが発生しました。: ORA-00942: table or view does not exist *examユーザから、exam2へtest表へのselect権限を付与 SQL> grant select on test to exam2; 権限付与が成功しました。 *exam2ユーザで、select、および insert。insertは失敗する。 SQL> select * from exam.test; ID NAME ---------- -------------------- 1 abc SQL> insert into exam.test values(2, 'def'); insert into exam.test values(2, 'def') * 行1でエラーが発生しました。: ORA-01031: insufficient privileges *exam2ユーザへtest表へのすべての権限をgrant optionつきで付与 SQL> grant all on test to exam2 with grant option; 権限付与が成功しました。 *exam2でデータを削除してみる。truncateはできないが、deleteはできる SQL> truncate table exam.test; truncate table exam.test * 行1でエラーが発生しました。: ORA-01031: insufficient privileges SQL> delete from exam.test; 1行が削除されました。 *権限の取り消し SQL> revoke all on test from exam2; 取消しが成功しました。 ::ユーザーに付与されているオブジェクト権限のリスト ,データディクショナリ,内容 ,DBA_TAB_PRIVS,オブジェクト権限（列固有の権限を除く） ,DBA_COL_PRIVS,列固有の権限 SQL> select table_name, privilege, grantable from dba_tab_privs 2 where grantee = 'EXAM_MANAGER' 3 / TABLE_NAME PRIVILEGE GRANTA ------------ ---------------------- ------ TEST FLASHBACK NO TEST DEBUG NO TEST QUERY REWRITE NO TEST ON COMMIT REFRESH NO TEST UPDATE NO TEST SELECT NO TEST INSERT NO TEST DELETE NO TEST ALTER NO !ロール *目的ごとに作成した権限のセット *ユーザーに直接権限を与えるのではなく、ロールをユーザーに与える *システム権限、オブジェクト権限を付与できる *ロールにロールを付与できる *有効/無効を切り替えることができる *パスワードを設定できる *WITH ADMIN OPTIONを指定し、他のユーザに付与することを許可できる CREATE ROLE ロール名 [IDENTIFIED { BY パスワード | EXTERNALLY | GLOBALLY AS '外部名' } ::例 *ロールの作成 SQL> conn system/abc123@oradb1 接続されました。 SQL> create role exam_manager 2 identified by abc123 3 / ロールが作成されました。 *ロールに権限を付与し、ロールをexam2に付与 SQL> grant all on exam.test to exam_manager; 権限付与が成功しました。 SQL> grant exam_manager to exam2; 権限付与が成功しました。 *これだけでは、exam2は、exam.test表を操作できない SQL> select * from exam.test; select * from exam.test * 行1でエラーが発生しました。: ORA-00942: table or view does not exist *ロールを有効にする SQL> set role exam_manager identified by abc123; ロールが設定されました。 *表操作可能に SQL> insert into exam.test values(3,'ghi'); 1行が作成されました。 ::現在使用可能なRole一覧 *SESSION_ROLESデータディクショナリビューに問い合わせる SQL> set role exam_manager identified by north123; ロールが設定されました。 SQL> select * from session_roles; ROLE ------------------------------------------------------------ EXAM_MANAGER ---- {{include_html banner_html, "!Oracle"}}