Oracle12C--用户管理(20)

知识点的梳理：

Oracle中的每一个用户都是作为数据库对象存在的；
在Oracle中，管理员可以通过3个数据字典查看用户，角色，权限的信息：
- dba_sys_privs：查看用户所拥有的权限；
- dba_role_privs：查看用户所拥有的角色；
- role_sys_privs：查看角色所用的权限；
概要文件定义了用户登录后的相关操作配置，通过概要文件，可以实现对用户的控制；
创建用户
- 语法：想完成以下操作，必须使用管理员权限

create user 用户名 identified by 密码
[DEFAULT tablespace 表空间名称]
[temporary tablespace 表空间名称]
[QUOTA数字 [K|M] | UNLIMITED ON 表空间名称

QUOTA数字 [K|M] | UNLIMITED ON 表空间名称 ...]

[PROFILE 概要文件名称 |DEFAULT]

[PASSWORD EXPIRE]

[ACCOUNT LOCK | UNLOCK]

该语法各部分解析：
create user 用户名 identified by 密码：创建用户同时设置密码，但是用户名和密码不能是Oracle的关键字，不能以数字开头；
default tablespace 表空间名称：用户存储默认使用的表空间，当用户创建对象没有设置表空间时，就将保存在此处指定的表空间下，这样可以和系统表空间进行区分；
temporary tablespace 表空间名称：用户所使用的临时表空间；
quota 数字 [K|M]|UNLIMITED ON 表空间名称：用户在表空间上的使用限额，可以指定多个表空间的限额，如果设置为unlimited,则表示不设置限额；
PROFILE 概要文件名称 |DEFAULT：用户操作的资源文件，如果不指定则使用默认配置资源文件；
password expire：用户密码失效，则在第一次使用时必须修改密码；
ACCOUNT LOCK | UNLOCK：用户是否为锁定状态，默认为UNLOCK；

示例：创建一个新的用户：c##mldnuser，密码为：java_android

CREATE USER c##mldnuser

IDENTIFIED BY java_android

DEFAULT TABLESPACE mldn_data

TEMPORARY TABLESPACE mldn_temp

QUOTA 30M ON mldn_data

QUOTA 20M ON users

ACCOUNT UNLOCK

PASSWORD EXPIRE ;

创建一个c##mldnuser用户，密码为java_android，配置说明如下：
DEFAULT TABLESPACE mldn_data：该用户默认使用的表空间。如果用户在创建数据库对象时没有指定表空间，则默认会使用mldn_data表空间存储；
TEMPORARY TABLESPACEmldn_temp：该用户使用order by或group by 子句，数据量过大时保存的临时表空间；

QUOTA 30M ON mldn_data ：该用户在mldn_data表空间上最多使用30MB大小；
QUOTA 20M ON users：该用户在users表空间上最多使用20MB大小；
ACCOUNT UNLOCK：该用户默认活动账户；
PASSWORD EXPIRE：用户登录后需要强制用户修改密码；

示例：用户创建完后，通过dba_users查看用户信息

SELECT username,user_id,default_tablespace,temporary_tablespace,created,lock_date,profile

FROM dba_users

WHERE username='C##MLDNUSER';

c##mldnuser用户的lock_date列信息为null,这是因为默认创建的用户并非锁定用户，而当一个用户锁定之后，lock_data列就会变为锁定日期

每个用户都存在着多个可以操作的表空间，可以通过dba_ts_quotas数据字典来看出
- 示例：通过dba_ts_quotas查看用户可用表空间配额

SELECT * FROM dba_ts_quotas WHERE username='C##MLDNUSER' ;

概要文件（profiles）
- 概要文件是一组已经命名的口令和资源限制文件，管理员利用它可以直接限制用户的资源访问量或用户管理等操作
- 语法：
  - create profile 概要文件名称 limit 命令(s)
- 在概要文件中可以通过如下两组命令格式来进行控制：
  - 第一组：资源限制命令
    - session_per_user 数字 | UNLIMITED | DEFAULT：允许一个用户同时创建session的最大数量；
    - cpu_per_session 数字 | UNLIMITED | DEFAULT：每一个SESSION允许使用CPU的时间数，单位为毫秒；
    - cpu_per_call 数字 | UNLIMITED | DEFAULT：限制每次调用sql语句期间，CPU的时间总量；
    - connect_time 数字 | UNLIMITED | DEFAULT：每个session的连接时间数，单位为分；
    - IDLE_TIME 数字 | UNLIMITED | DEFAULT：每个session的超时时间，单位为分；
    - LOGICAL_READS_PER_SESSION 数字 | UNLIMITED | DEFAULT：为了防止笛卡尔积的产生，可以限定每一个用户最多允许读取的数据块数；
    - LOGICAL_READS_PER_CALL 数字 | UNLIMITED | DEFAULT：每次调用SQL语句期间，最多允许用户读取的数据库块数；
  - 第二组：口令限制命令
    - FAILED_LOGIN_ATTEMPTS 数字 | UNLIMITED | DEFAULT：当连续登录失败次数达到该参数指定值时，用户被加锁；
    - PASSWORD_LIFE_TIME 数字 | UNLIMITED | DEFAULT：口令的有效期（天），默认为UNLIMITED；
    - PASSWORD_REUSE_TIME 数字 | UNLIMITED | DEFAULT：口令被修改后原有口令隔多少天后可以被重新使用，默认为UNLIMITED；
    - PASSWORD_REUSE_MAX 数字 | UNLIMITED | DEFAULT：口令被修改后原有口令被修改多少次才允许被重新使用；
    - PASSWORD_VERIFY_FUNCTION 数字 | UNLIMITED | DEFAULT：口令效验函数；
    - PASSWORD_LOCK_TIME 数字 | UNLIMITED | DEFAULT：账户因FAILED_LOGIN_ATTEMPTS锁定时，加锁天数；
    - PASSWORD_GRACE_TIME 数字 | UNLIMITED | DEFAULT：口令过期后，继续使用原口令的宽限期（天）；
- 示例：定义一个概要文件

CREATE PROFILE c##mldn_profile LIMIT

CPU_PER_SESSION 10000

LOGICAL_READS_PER_SESSION 20000

CONNECT_TIME 60

IDLE_TIME 30

SESSIONS_PER_USER 10

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME UNLIMITED

PASSWORD_LIFE_TIME 60

PASSWORD_REUSE_TIME 30

PASSWORD_GRACE_TIME 6 ;

概要文件说明如下：
CPU_PER_SESSION 10000：每个session所允许占用CPU的最长时间为100秒；
LOGICAL_READS_PER_SESSION 20000：每个session最多允许读取20000个数据块；
CONNECT_TIME 60：每个session最多允许连接60分钟；
IDLE_TIME 30：一个session最大空闲时间为30分钟；
SESSIONS_PER_USER 10：一个用户最多可以创建10个session连接；
FAILED_LOGIN_ATTEMPTS 3：每个用户登录错误为3次；
PASSWORD_LOCK_TIME UNLIMITED：超过3次登录错误，则密码被长期锁定；
PASSWORD_LIFE_TIME 60：每60天修改一次密码；
PASSWORD_REUSE_TIME 30：为防止新旧口令一致，所以旧口令在30天之后才可以继续使用；

PASSWORD_GRACE_TIME 6 ：口令失效后，给用户6天可以继续使用旧口令的完整信息；

概要文件也可以使用dba_profiles数据字典查看概要文件的完整信息：
查询dba_profiles数据字典

SELECT * FROM dba_profiles WHERE profile='C##MLDN_PROFILE' ;

示例：创建用户时指定概要文件

CREATE USER c##mldnjava IDENTIFIED BY hello

PROFILE c##mldn_profile ;

此时新的c##mldnjava用户创建时的相关资源配置全部采用默认方式，而概要文件使用了mldn_profile。

示例：为已经存在的用户设置概要文件

ALTER USER c##mldnuser PROFILE c##mldn_profile ;

查看dba_users数据字典，观察c##mldnjava和c##mldnuser两个用户的定义

SELECT username,user_id,default_tablespace,temporary_tablespace,created,lock_date,profile

FROM dba_users

WHERE username IN ('C##MLDNJAVA','C##MLDNUSER') ;

两个用户都使用了概要文件为c##mldn_profile;

示例：修改已经存在的概要文件

ALTER PROFILE c##mldn_profile LIMIT

CPU_PER_SESSION 1000

PASSWORD_LIFE_TIME 10 ;

此sql修改了CPU的占用时间和密码过期时间

示例：删除概要文件。如果已经有用户使用了此概要文件，则必须使用cascade;

DROP PROFILE c##mldn_profile CASCADE ;

概要文件被删除后，所有拥有此概要文件的用户将自动将使用的概要文件设定为default

维护用户
- 修改用户密码
  - 语法：alter user 用户名 identified by 新密码；
  - 示例：修改c##mldnuser的密码为：hellojava

ALTER USER c##mldnuser IDENTIFIED BY hellojava ;

控制用户锁定
- 语法：alter user 用户名 account lock | unlock;
- 示例：将c##mldnuser用户设置为锁定状态

ALTER USER c##mldnuser ACCOUNT LOCK ;

示例：通过dba_users查看mldnuser用户的锁定信息

SELECT username,user_id,default_tablespace,temporary_tablespace,created,lock_date,profile

FROM dba_users

WHERE username='C##MLDNUSER';

解锁示例：将c##mldnuser用户解锁

ALTER USER c##mldnuser ACCOUNT UNLOCK ;

让密码失效
- 语法：alter user 用户名 password expire;
- 示例：让c##mldnuser密码失效
  - 注意：密码失效之后，用户必须在进行登录时强制性修改密码；

ALTER USER c##mldnuser PASSWORD EXPIRE ;

修改用户表空间配额
- 可以利用alter语句，增加用户在表空间上的配额，来增加保存的数据量空间；
- 语法：alter user 用户名 quota 数字 [k|m] unlimited on 表空间名称 ...
- 示例：修改c##mldnuser用户的表空间配额

ALTER USER c##mldnuser

QUOTA 20M ON system

QUOTA 35M ON users ;

通过dba_ts_quotas数据字典查看mldnuser用户新的表空间配额

SELECT * FROM dba_ts_quotas WHERE username='C##MLDNUSER' ;

删除用户
- 语法：drop user 用户名 [cascade];
  - 注意：如果用户在存在期间进行了数据库对象的创建，就可以利用cascade子句删除模式中的所有对象；
  - 示例：删除c##mldnuser用户

DROP USER c##mldnuser ;

说明：用户被删除后，此用户下的所有对象（表，索引，子程序）都会一起被删除，删除前请备份数据；