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角色授予开发人员. 但是可能存在安全漏洞.