教你如何成為Oracle 10g OCP - 第八章 使用者、許可權和角色管理

 

8.1  使用者管理

每個使用者有一系列的屬性，包括以下幾個方面：

A. 唯一的使用者名稱，必須字母開頭，最多30位元組，不能有特殊字元。
B. 認證： 通常使用密碼認證
C. 預設表空間： 使用者建立後，如果在使用者下建立了物件，那麼生成與使用者同名的schema,
即schema與使用者名稱的區別在於是否存在使用者物件。10g之前，如果沒有指定default tbs,
那麼system會被作為預設表空間；10g或之後，在建庫的時候就可以指定一個資料庫級的
預設表空間，這樣定義使用者沒有指定預設表空間的話，預設就使用這個資料庫級的TBS.
可以透過下面的語句查詢：
select * from database_properties ;
或者 select name,value$ from props$ where name like 'DEFAULT%'
D. 預設臨時表空間： Oracle9i之前，如果沒有指定，會使用system作為臨時表空間，9i開始，
可以為資料庫指定預設臨時表空間，定義使用者時沒有指定的話，使用資料庫預設的臨時表空間。
E. profile(配置檔案)，用於控制使用者密碼策略以及資源使用。
F. 使用者組
G. 鎖定狀態： 可以將使用者鎖定和解鎖 。

 

資料庫建立後，會預先建立兩個非常重要的使用者：sys及system, sys是超級使用者，許可權太大，
所以登陸的時候需要加入as sysdba 增強安全性，因為以as sysdba登陸後，登陸的時間資訊
會記錄在審計檔案中。system僅僅時候資料庫管理員，沒有sys那麼大許可權。

通常我們不使用sys及system等使用者登入資料庫進行管理，而是建立一個使用者，賦予該使用者DBA
角色許可權，用該使用者來進行日常管理工作。

8.1.1  建立和刪除使用者

CREATE USER sidney
    IDENTIFIED BY out_standing1
    DEFAULT TABLESPACE example
    QUOTA 10M ON example
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON system
    PROFILE app_user
    PASSWORD EXPIRE;

認證方式： 密碼認證，外部認證，全域性認證

外部認證 -
密碼認證大家都熟悉，這裡介紹一下外部認證。使用外部認證需要使用引數os_authent_prefix，
如果作業系統存在一個使用者susan,  而資料庫中存在一個使用者ops$susan, 則只要以susan使用者登
陸OS, 當登陸資料庫時，可以不用提供使用者密碼，即可以以ops$susan登陸到資料庫。若此引數為
空，那麼只要資料庫中存在與作業系統同名的使用者，該使用者就可以透過外部認證登陸資料庫，因
為不完全，oracle不建議採用該認證方式。 

SQL> show parameter os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

全域性認證 -  需要採用oracle的高階安全設定.

 

-------------- 

建立使用者語法：
  CREATE USER username
            IDENTIFIED {BY password|EXTERNALLY}
           [DEFAULT TABLESPACE tablespace_name]
           [TEMPORARY TABLESPACE tablespace_name]
           [QUOTA [n [K|M]] ON tablespace_name]
           [PASSWORD EXPIRE]
           [ACCOUNT LOCK|UNLOCK]
           [PROFILE filename];

引數：

IDENTIFIED BY password:
     用來指定使用者的確認方式為資料庫確認，password是為該使用者指定的密碼

IDENTIFIED   EXTERNALLY:
     指定確認方式為作業系統確認，並且生成一個用作業系統確認的使用者

DEFAULT TABLESPACE:
     使用者預設的表空間，用於在該使用者模式下建立的物件(如表、索引、簇、檢視等)沒有指
定表空間時資料庫預設的儲存表空間

TEMPORARY TABLESPACE:
        臨時表空間，用於儲存排序等事務處理時的儲存空間使用者暫存段表空間

QUOTA [n [K|M]] [UNLIMITD] ON tsname:
        指定表空間的限制配額，就是限制允許使用者使用的表空間的數量(大小)，UNLIMITD
表示無大小限制

PASSWORD EXPIRE:
     表明使用者第一次登入時要修改密碼

ACCOUNT LOCK|UNLOCK:
         鎖定使用者,lock為鎖定，unlock為未鎖定或解鎖，預設為不鎖定(unlocked)

PROFILE filename:
         使用者的配置檔案，用於限制使用者對系統資源的使用，如果沒有指定，則系統使用默
認的使用者配置檔案

 

舉例：

1. 建立一個資料庫驗證的使用者：

  CREATE USER testUser
      IDENTIFIED BY test
      DEFAULT TABLESPACE basedata
      TEMPORARY TABLESPACE TEMP
      QUOTA UNLIMITED ON USERS
      PASSWORD EXPIRE
      ACCOUNT UNLOCK
      PROFILE DEFAULT;
2. 授予使用者連線許可權 (使用者只有擁有CREATE SESSION 許可權才可能與資料庫建立連線)
     GRANT CREATE SESSION TO testUser;
3. 修改使用者資訊：
     ALTER USER testUser IDENTIFIED BY temp;     -- 修改密碼
     ALTER USER testUser ACCOUNT LOCK;       -- 鎖定使用者
4. 刪除使用者：
     DROP USER testUser CASCADE;    -- 級聯刪除，即將此使用者擁有的全部物件一起刪除
5. 查詢使用者資訊：
     SELECT * FROM ALL_USERS;
     SELECT username, password, account_status FROM DBA_USERS;
 

 

8.1.1.2  sysdba的認證方式

對於sys使用者來說，oracle提供了比較特殊的認證方式。 對於sys的認證就是oracle是否允許
我們以sysdba的許可權登陸到資料庫進行管理工作，因為資料庫內建使用者都放在資料字典中，數
據庫沒有開啟之前無法訪問資料字典，普通使用者無法透過資料庫驗證身份，sys如果也放在資料
字典，同樣無法登陸資料庫，資料庫啟動都無法實現。

所以Oracle對sys使用者認證提供兩種方式： 作業系統認證和密碼檔案認證

A. 作業系統認證 -

作業系統使用者A同時也是伺服器資料庫管理員組的成員(unix上是dba組，windows是ora_dba組，這些
使用者組是在安裝oracle軟體的時候就建立了，不是dbca時),表示A已經具有作業系統許可權，不再需要
提供sys的密碼了，而是直接以sysdba許可權登陸。比如：
SQL> connect  /   as sysdba 

ORA_DBA描述是： Members can connect to the Oracle database as a DBA without a password

設定作業系統認證： sqlnet.ora

sqlnet.authentication_services=(NTS)
如果是NTS, 表示要進行作業系統驗證，如果沒有sqlnet.ora檔案，或該檔案沒有記錄該條目，也
表示進行作業系統認證。否則，如果記錄為NONE,

sqlnet.authentication_services=(NONE)
如果是NONE, 表示不進行作業系統認證，即便是dba組成員登陸OS, 如果想以sys的身份登入還是
需要輸入密碼，這個密碼在密碼檔案中。

B. 密碼檔案認證 -

密碼檔案中記錄著 sys使用者的口令以及其它"特權使用者"的使用者名稱／口令，通俗的說就是這個檔案中
記錄了當前具有sysdba/sysoper許可權的使用者名稱及其口令。建立資料庫例項的時侯，Oracle系統會自動建立一個與例項對應的密碼檔案。

對於unix/linux而言，密碼檔案存放在$ORACLE_HOME/dbs目錄下，對於windows而言，%ORACLE_HOME%\
database下，unix下密碼檔案格式為orapw.ora，如果環境變數中ORACLE_SID為大寫，比如TEST,
那麼密碼檔案中的部分也要大寫，小寫也一樣要匹配，否則登入會報錯。windows下面檔案格式為
PWD.ora, SID部分也要遵循大小寫規則。

sys使用者擁有dba，sysdba，sysoper等角色或許可權，是oracle許可權最高的使用者，所有oracle的資料
字典的基表和檢視都存放在sys使用者中，登陸時也只能用sysdba、sysoper這兩個身份，不能用normal
身份system使用者擁有普通角色許可權， 只能用normal身份登陸， 除非你對它授予了sysdba的系統許可權
或者syspoer系統許可權。

 

建立密碼檔案 -
linux/unix下面(在dbs目錄下)：
$orapwd file=orapwora10g.ora password=oracle entries=5 

window下面(在database目錄下)：
$orapwd file=PWDora10g.ora password=oracle entries=5 

其中entries表示同時有幾個使用者擁有sysdba的許可權，同時他們的密碼都放在密碼檔案中。
建立密碼檔案後，必須重新啟動資料庫才能使它生效。

 

引數remote_login_passwordfile -
該引數用來控制能否使用密碼檔案驗證，引數有三個值：
A. None, 遠端使用者無法透過網路方式以sysdba或sysoper的許可權登陸到資料庫。且授權或回收SYSDBA、SYSOPER許可權會失敗。
B. Exclusive, 預設值，表示該密碼檔案只能被一個例項使用，可以向密碼檔案新增新的使用者，若密碼丟失，使用者同樣無法透過密碼認證的方式登陸到資料庫。
C. Shared,  該密碼檔案可以被多個資料庫共享 , 但是密碼檔案只能存放sys使用者的密碼，不能新增其他使用者。 任何使用這個密碼檔案的資料庫都不能進行授權回收SYSDBA、SYSOPER許可權的操作，但是這些資料庫可以使用這個密碼檔案來進行驗證。這種方式一般用於管理RAC資料庫，或者同一臺伺服器上的多個不同資料庫例項。

8.1.1.3  刪除使用者

SQL> drop user susan ; 如果有其他session以該身份登入，刪除使用者會失敗，需要中斷以該使用者
登入實行的session , 然後等待PMON回收資源(查詢v$session), 最後刪除使用者。

如果使用者下面有物件存在，也會報錯，需要加入cascade ;
SQL> drop user susan cascade ;  刪除該使用者及其下面的物件全部刪除。

 

 

8.1.2   Profile與使用者管理

可以使用Profile對使用者所能使用的資源進行管理，profile存放在資料字典中，預設就有一個名為
default的profile . 可以透過dba_profiles 顯示或者透過工具比如toad檢視。

CREATE PROFILE DEFAULT LIMIT
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  CONNECT_TIME UNLIMITED
  IDLE_TIME UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  COMPOSITE_LIMIT UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10  
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL;

從欄位RESOURCE_TYPE中可以看出，profile管理的資源包括兩類： password和kernel .

密碼管理：
A.  FAILED_LOGIN_ATTEMPTS  10 , 連續登陸10次不成功鎖定賬號
B.  PASSWORD_LIFE_TIME UNLIMITED , 密碼在多少天后失效，失效後必須修改密碼
C.  PASSWORD_REUSE_TIME UNLIMITED, 指定的天數內，設定的密碼不能重複
D.  PASSWORD_REUSE_MAX UNLIMITED,  指定的密碼修改次數之內，密碼不能重複
E.  PASSWORD_LOCK_TIME UNLIMITED,  一旦登陸次數超過指定的值，則鎖定賬號多少天
F.  PASSWORD_GRACE_TIME UNLIMITED,  密碼失效緩衝期，期間可以登陸，但是每次都提示更改密碼，
                                    指定的天數內還不更改密碼，超過天數後賬號被鎖定。
G.  PASSWORD_VERIFY_FUNCTION NULL,  指定函式控制密碼複雜度，函式樣例在utlpwdmg.sql指令碼中。

內部資源管理：
A. SESSIONS_PER_USER , 同一個使用者最多能產生多少session
B. CPU_PER_SESSION , 每個session一次最多能持續佔用CPU多長時間，百分之一秒為單位。
C. CPU_PER_CALL,  一次呼叫最多能持續佔用cpu多長時間，比如執行SQL時，會有解析呼叫，執行呼叫等。
D. LOGICAL_READS_PER_SESSION, 每個session能夠進行的邏輯讀的最大個數
E. LOGICAL_READS_PER_CALL, 一次呼叫能夠進行的邏輯讀的最大個數
F. IDLE_TIME,  session持續idle時間超過該引數指定的時間，則中斷該session的連線。idel指
session既沒有消耗CPU, 也沒有等待I/O.
G. CONNECT_TIME, 持續connect時間超過該引數指定的時間，則中斷該session的連線。
H. PRIVATE_SGA,  每個session 能夠使用的pga的最大尺寸。
I. COMPOSITE_LIMIT,  該引數是基於以上的引數的權重而進行的控制。

在刪除profile時，如果被刪除的profile已經分配給使用者了，那麼該profile不能被刪除，可以新增
cascade, 表示刪除profile, 並將名為default的預設pfofile應用到那些受影響的使用者上。
SQL> drop profile my_profile cascade ;

 

 

8.2  許可權管理

系統許可權和物件許可權

1. 系統許可權 - 是指使用者能夠做什麼事情的許可權，比如建立表等，10g中有100多個系統許可權，其中很多
含有any的字樣，比如 create table許可權表示可以在自己的schema中建表，相應的，create any table
表示可以在其他schema中建表，所有系統許可權存放在system_privilege_map的資料字典表中。

對於管理而言，有一些常用的系統許可權：

A. sysdba, sysoper , 最重要也是最大的許可權，能在庫中做任何事情。
B. restricted session , 以restricted模式開啟，通常在進行維護性工作時，不希望其他使用者登入，
會採用這種方式。 預設只有sys具有該許可權。
C. create 或 create any 開頭的許可權
D. drop 或 drop any 許可權
E. create session , 使用者要登陸資料庫，至少要有該許可權。

許可權賦予 -
SQL> grant create session, create table to susan ; 
加入admin option 表示該使用者也可以把以該admin option方式獲得的許可權賦給其他使用者。
SQL> grant create session, create table to susan with admin option ;

SQL> connect susan/passwd
SQL> grant create session  to susan2 ;

許可權回收 - 
SQL> revoke create session, create table from susan ; 
許可權從susan回收後，不影響已經分配給susan2的許可權。

 

空間配額quota -

SQL> alter user susan quota 100M on base_data ;
可以查詢dba_ts_quotas 檢視；

 

帶有any的系統許可權，能在其他schema下處理物件，不過不能操作sys所有的物件，這個
特性是由o7_dictionary_accessibility 所決定的，該引數預設為false，如果是true,
只要使用者具有any許可權，就能處理其他schema包括sys下的物件了。為了安全，一般建議，
我們應該確保此引數為false。

 

 

物件許可權 -

alter , delete, update, select, insert, execute, index, references 

將某個物件的許可權賦予某個使用者時，必須以物件所有者身份登入，然後將當前使用者所具有的
物件的某個物件許可權賦給另外的使用者。

SQL> connect hr/passwd
SQL> grant select on employees to susan  ;
SQL> grant index on employees to susan  ;

SQL> connect sys/passwd
SQL> grant execute on dbms_output to susan  ;

grant option -

SQL> connect  /   as sysdba
SQL> grant update on hr.employees to susan ;

SQL> grant select on hr.employees to susan with grant option ;
以上表示使用者susan 可以將查詢使用者hr下的employees表的許可權賦給其他使用者。比如：

SQL> connect susan/passwd
SQL> grant select on hr.employees to susan2 ;
然而當我們從susan使用者回收這個許可權的時候，susan2上的此許可權同樣消失了。也就是說物件
許可權的回收具有連帶效應。

 

 

8.3   角色管理

角色可以理解為許可權的集合，我們可以將某個角色賦給某個使用者，那麼這個使用者就擁有了角色
所包含的所有許可權； 也可以將角色賦給另外的角色。 為角色賦予角色時，不能形成閉環，比如
A賦予B,B賦予C, C又賦予A, 則報錯。

可以從dba_roles中看到很多定義好的角色如 dba, resource  。

dba :  擁有大部分的管理許可權，一般不建議透過sys或system管理資料庫，而是建立一個新使用者，
賦予dba許可權就可以使用該使用者對資料庫進行日常管理了。

resource :  該角色擁有很多create開頭的許可權，比如 create table, create procedure等，在
oracle9i 中resource擁有unlimited tablespace的系統許可權，但是到了10g不再具有。

 

建立角色 - 
SQL>  create role my_role ;
SQL>  grant create session to my_role;
將角色賦給其他使用者
SQL>  grant my_role to susan ;
將角色從其他使用者收回
SQL>  revoke my_role from susan ;

我們也可以對角色使用不同的認證方式，表示在啟用角色時，是否對啟用的許可權進行認證，使用下面
的命令啟用角色許可權：

alter role role_name not identified  ;

alter role role_name identified { by |  using | externally | globally} ;

not identified :  不認證，預設選擇。
identified by ： 啟動角色時採用密碼認證。
identified externally ： 啟動角色時採用作業系統認證 。
identified globally ：  啟動角色時採用Oracle的高階安全選項。
identified using ： 只能透過執行特定的包來啟用角色。

 

某個使用者可以有多個角色，這些角色中可分為兩類：

1. 預設角色：  這些角色在使用者登陸時自動啟用，如果我們為預設角色指定認證方式，比如密碼
認證，那麼啟用該角色時不用輸入密碼，也即認證方式對預設角色來說無意義。

2. 非預設角色： 這些角色在使用者登陸時禁用，需要發出set role來進行啟用，啟用時需要認證。

在設定用的角色時，我們指定哪些角色為預設角色，那麼該使用者剩下的其他角色就是非預設角色。如：
SQL> create role app_role identified by oracle ;
SQL> grant create session , resource to app_role ;
SQL> create role conn_role ; 
SQL> grant create session to conn_role ;
SQL> grant app_role, conn_role to susan ;

將conn_role角色設定為susan的預設角色
SQL> alter user susan default role conn_role ;
也可以類似：
SQL> alter user susan default role all except app_role ;
表示除了app_role之外都是預設角色，如果想取消使用者上的所有角色：
SQL> alter user susan default role none ;

可以啟用susan上的另外一個角色：
SQL> set role app_role identified by oracle ;