Oracle使用者、角色、權限和系統資源管理常用操作

1. 查詢使用者資訊

   SELECT USERNAME,DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE, ACCOUNT_STATUS, CREATED FROM dba_users;

2. 查詢使用者空間使用和上限情況

   SELECT username, tablespace_name, bytes/1024/1024 space_used_in_mb, max_bytes/1024/1024 max_space_in_mb FROM dba_ts_quotas;

3. 建立使用者

   CREATE USER 使用者名 IDENTIFIED BY 密碼 DEFAULT TABLESPACE 表空間名 TEMPORARY TABLESPACE 表空間名 QUOTA {正整數[K|M] | UNLIMITED} ON 表空間名 PASSWORD EXPIRE PROFILE 概要檔案名

4. 改變使用者表空間配額

   ALTER USER 使用者名 QUOTA 38M ON 表空間名;

5. 删除使用者. 慎用,使用者相關對象也将消失.

   DROP USER 使用者名 [CASCADE];

6. 鎖定和解鎖使用者

   ALTER USER 使用者名 ACCOUNT UNLOCK|LOCK;

7. 啟用資源限制.否則概要檔案中的定義不起作用.

   a.)修改參數檔案, RESOURCE_LIMIT = TRUE

   b.)使用指令, ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

8. 查詢概要檔案情況. RESOURCE_TYPE為KERNEL時即是資源限制.PASSWORD為密碼安全限制.

   SELECT * FROM dba_profiles;

9. 建立資源限制的概要檔案. 最大值可為正整數,UNLIMITED或DEFAULT.

   CREATE PROFILE 概要檔案名 LIMIT

      [SESSIONS_PER_USER 最大值]     

      [CPU_PER_SESSION 最大值]          --總共的CPU使用時間.機關是百分之一秒

      [CONNECT_TIME 最大值]               --連接配接時間,機關是分鐘

      [IDLE_TIME 最大值]                         --伺服器端程序的限制時間,機關是分鐘

      [LOGICAL_READS_PER_SESSION 最大值]       --磁盤和記憶體最大讀取資料塊數

      [CPU_PER_CALL 最大值]

      [LOGICAL_READS_PER_CALL 最大值]

10. 建立密碼限制的概要檔案.可以與資源限制為同一概要檔案.其中PASSWORD_REUSE_MAX和PASSWORD_REUSE_TIME互斥,一者設值并且不是DEFAULT或UNLIMITED則另一者必須為UNLIMITED.

    CREATE PROFILE 概要檔案名 LIMIT

       [FAILED_LOGIN_ATTEMPTS 最大值]              --超過此登入錯誤次數則賬戶鎖定

       [PASSWORD_LOCK_TIME 最大值]                  --被鎖定的天數

       [PASSWORD_LIFE_TIME 最大值]                     --密碼的可使用天數

       [PASSWORD_GRACE_TIME 最大值]                --超過天數後的第一次登入之後可用來改變密碼的寬免天數

       [PASSWORD_REUSE_TIME 最大值]                 --密碼可重用之前的天數

       [PASSWORD_REUSE_MAX 最大值]                  --密碼可重用之前的密碼變化次數

       [PASSWORD_VERIFY_FUNCTION 函數名|NULL|DEFAULT]         --驗證密碼複雜性的方式

11. 使用預設密碼驗證函數.

    a.)通過sys使用者運作Oracle自帶腳本utlwdmg.sql腳本.腳本在$ORACLE_HOME/rdbms/admin目錄下

    b.)指定PASSWORD_VERIFY_FUNCTION值為verify_function

    将作如下檢查:

      w.)最小四個字元

       x.)不能與使用者名相同

       y.)至少包含一個數字、一個字元和一個特殊數字

       z.)至少有三個字母與之前的密碼不同

12. 自制密碼驗證函數.

    必須在sys模式下建立.并使用以下函數說明:

    function_name(userid_parameter IN VARCHAR2(30), password_parameter IN VARCHAR2(30), old_password_parameter IN VARCHAR2(30)) RETURN BOOLEAN

    該函數出錯和無效時,相應的語句會被終止.

13. 修改概要檔案

    ALTER PROFILE 概要檔案名 LIMIT

    …

    …;

14. 删除概要檔案

    DROP PROFILE 概要檔案名 [CASCADE]; 

15. 資料字典系統權限

    當系統參數O7_DICTIONARY_ACCESSIBILITY = TRUE時,擁有SELECT ANY TABLE權限的使用者可以查詢資料字典,即sys所屬對象.FALSE時則不可. DBA使用者不受限制. 

16. 查詢權限資訊

    SELECT * FROM dba_sys_privs;

    和

    SELECT * FROM session_privs;

17. 使用者必須至少擁有CREATE SESSION權限才可登入.

18. 授予系統權限

    GRANT CREATE SESSION, SELECT ANY TABLE TO 使用者|角色|PUBLIC [WITH ADMIN OPTION];

19. 收回權限. 被有WITH ADMIN OPTION權限使用者二次授予的權限不會級聯收回.

    REVOKE SELECT ANY TABLE FROM 使用者|角色|PUBLIC;

20. 常用系統權限

    CREATE USER   (需要DBA權限)

    ALTER USER

    DROP USER

    SELECT ANY TABLE

    UPDATE ANY TABLE

    DELETE ANY TABLE

    CREATE ANY TABLE

    DROP ANY TABLE

    ALTER ANY TABLE

    CREATE TABLE

    CREATE TABLESPACE

    DROP TABLESPACE

    ALTER TABLESPACE

    UNLIMITED TABLESPACE (所有表空間權限)

    CREATE ANY INDEX

    DROP ANY INDEX

    ALTER ANY INDEX

    CREATE SESSION

    ALTER SESSION

    CREATE VIEW

    CREATE SEQUENCE

    CREATE PROCEDURE

    SYSOPER

    SYSDBA

    ALTER ANY ROLE

    GRANT ANY ROLE

21. 查詢對象權限

    SELECT * FROM dba_tab_privs;

    或

    SELECT * FROM dba_col_privs;

    或

    SELECT * FROM user_tab_privs_made;

    或

    SELECT * FROM user_col_privs_made;

22. 對象權限清單

    EXECUTE

    SELECT

    ALTER

    INSERT

    UPDATE

    DELETE

    INDEX

    REFERENCES

23. 對象權限授予

    GRANT 權限|ALL[(列名[,列名…])] ON 對象名 TO 使用者|角色|PUBLIC [WITH GRANT OPTION]     

24. 對象權限收回. 通過有WITH GRANT OPTION權限使用者二次授予的權限會被級聯收回.

    REVOKE 權限|ALL[(列名[,列名…])] ON 對象名 FROM 使用者|角色|PUBLIC       

25. 角色.使用者的預設角色即使有密碼也會自動生效而不驗證密碼. 可将權限或角色授予角色.可将角色授予使用者.
26. 建立角色時使用NOT IDENTIFIED或IDENTIFIED EXTERNALLY時,該角色自動被授予WITH ADMIN OPTION選項

27. 查詢角色資訊

    SELECT * FROM dba_roles;

    或

    SELECT * FROM dba_role_privs;

    或

    SELECT * FROM role_sys_privs;

    或

    SELECT * FROM user_role_privs;

28. 建立角色

    CREATE ROLE 角色名;

    CREATE ROLE 角色名 IDENTIFIED BY 密碼;

    CREATE ROLE 角色名 IDENTIFIED EXTERNALLY;

29. 更改角色

    ALTER ROLE 角色名 IDENTIFIED BY 密碼;

30. 變更預設角色 (隻可作用于直接授予使用者且不是外部管理的角色)

    ALTER USER 使用者名 DEFAULT ROLE 角色|NONE|ALL,… [EXCEPT 角色清單];

31. 激活和禁止角色 (此一動作可在程式中通過調用DBMS_SESSION.SET_ROLE過程來實作)

    SET ROLE NONE;

    SET ROLE ALL EXCEPT xxx;

    SET ROLE abc IDENTIFIED BY 密碼;      ---用于需要密碼的角色

32. 角色回收 (需要WITH ADMIN OPTION或GRANT ANY ROLE權限)

    REVOKE 角色名,… FROM 使用者|角色|PUBLIC;

33. 角色删除

    DROP ROLE 角色名;

34. 預定義角色

    EXP_FULL_DATABASE

    IMP_FULL_DATABASE

    SELECT_CATALOG_ROLE

    EXECUTE_CATALOG_ROLE

    DELETE_CATALOG_ROLE

    DBA

    CONNECT

    RESOURCE

35. 省事情況下可将CONNECT角色授予一般使用者. RESOURCE和CONNECT角色授予開發人員. 但是可能存在安全漏洞.