用户是使用者合法访问数据库内容的必需的方式，其包含了使用者对数据库各种操作的权限、各种资源使用的限制，同时，每个数据库对象必然隶属于某一个用户，任何数据库对象隶属于其被创建时连接的用户。
创建用户
create user u_sample identified by passwd;
dba_users视图
SQL> desc dba_users
名称 空值? 类型
--------------------------- -------- --------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
用户密码
--查看加密密码
SQL> select username,password from dba_users
where username like 'U_SAMPLE';
USERNAME PASSWORD
------------------------------ ------------------------------
U_SAMPLE 5CC163D09B897DCD
--修改密码
SQL> alter user u_sample identified by oracle;
User U_SAMPLE已变更。
USERNAME PASSWORD
------------------------------ ------------------------------
U_SAMPLE 7EB47D942C3BED5B
password字段存放明文密码被hash算法加密后的密文
账户状态
SQL> select username,account_status from dba_users
where username like 'U_SAMPLE';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
U_SAMPLE OPEN
- OPEN表示账户为解锁状态；
- EXPIRED表示账户为过期状态(需要设置口令才能解除此状态)；
- LOCKED表示账户为锁定状态。
演示
SQL> alter user u_sample account lock;
User U_SAMPLE已变更。
SQL> select username,account_status from dba_users
where username like 'U_SAMPLE';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
U_SAMPLE LOCKED
SQL> alter user u_sample account unlock;
User U_SAMPLE已变更。
SQL> select username,account_status
from dba_users
where username like 'U_SAMPLE';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
U_SAMPLE OPEN
SQL> alter user u_sample identifid by oracle; --重新设置密码，可解除expired状态，这里不便重现账户过期的场景
User U_SAMPLE已变更。
默认表空间
用户的默认表空间
SQL> select username,default_tablespace
from dba_users
where username like 'U_SAMPLE';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
U_SAMPLE USERS
- 表空间是由一个或多个数据文件组成的用于存放各种数据库对象的命名集合
- 用户的默认表空间是指用户创建表或索引等对象且语句中未指定表空间时，数据库会为对象默认分配的表空间
数据库默认表空间
SQL> select property_name,property_value
from database_properties
where property_name like 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------
DEFAULT_PERMANENT_TABLESPACE USERS
- 数据库默认表空间是指新建用户且语句中未指定默认表空间时，数据库会为用户默认分配的默认表空间
- 数据库默认表空间在数据库创建时已经被指定为USERS表空间，因此新建用户u_sample的默认表空间为USERS
修改默认表空间(以新建的sample_tbs表空间为例)
--创建表空间
SQL> create tablespace sample_tbs datafile '/oracle/ITLTEST/sample_tbs01.dbf' size 50M;
Tablespace SAMPLE_TBS 已创建。
SQL> select tablespace_name,sum(bytes/1024/1024) size_mb from dba_data_files
where tablespace_name like 'SAMPLE_TBS'
group by tablespace_name;
TABLESPACE_NAME SIZE_MB
------------------------------ ----------
SAMPLE_TBS 50
--修改数据库默认表空间
SQL> alter database default tablespace sample_tbs;
Database已变更。
SQL> select property_name,property_value from database_properties
where property_name like 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------
DEFAULT_PERMANENT_TABLESPACE SAMPLE_TBS
--修改用户默认表空间
SQL> alter user u_sample default tablespace sample_tbs;
User U_SAMPLE已变更。
SQL> select username,default_tablespace from dba_users where username like 'U_SAMPLE';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
U_SAMPLE SAMPLE_TBS
用户Profile
限制用户对数据库和实例资源的使用相关的资源限制参数和密码参数的命名集合
--查看用户分配的profile
SQL> select username,profile from dba_users where username like 'U_SAMPLE';
USERNAME PROFILE
------------------------------ ------------------------------
U_SAMPLE DEFAULT
--查看default profile内容
SQL> select * from dba_profiles where profile like 'DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
- 资源类
- COMPOSITE_LIMIT：资源使用综合限制。考虑用四个参数来计算加权的composite_limit，分别
为:cpu_per_session,logical_reads_per_sessions,connect_time,private_sga。可以使用alter resource cost来设置.。- SESSIONS_PER_USER：限制用户可以并发打开的最大会话数。
- CPU_PER_SESSION：限制每个用户会话内使用CPU的最大时间，单位为0.01s。
- CPU_PER_CALL：限制用户会话中每个调用使用CPU的最大时间，单位为0.01s。
- LOGICAL_READS_PER_SESSION：限制每个用户会话的最大数据块逻辑读取次数。
- LOGICAL_READS_PER_CALL：限制每个会话调用的最大数据块逻辑读取次数。
- IDLE_TIME：限制用户会话处于空闲状态下能持续的最大分钟数。
- CONNECT_TIME：限制用户会话能保持连接到数据库的最大分钟数
- PRIVATE_SGA：限制用户请求私有SGA（仅适用于共享服务器模式）。
- 密码类
- FAILED_LOGIN_ATTEMPTS：指用户被锁之前可以尝试的最大登录数。
- PASSWORD_LIFE_TIME：指定使用特定密码的时间限制，如果超出此时间间隔，那么密码将过期。
- PASSWORD_REUSE_TIME：指定重新使用密码要经过多少天。
- PASSWORD_REUSE_MAX：指定重新使用某个特定密码前，要经过多少次修改。
- PASSWORD_VERIFY_FUNCTION：此参数允许指定Oracle提供的密码验证函数（主要是复杂度验证）来建立自动密码验证。
- PASSWORD_LOCK_TIME：指用户被锁之前可以尝试的最大登录次数。
- PASSWORD_GRACE_TIME：设置一个时间段，在此时间段内将发出一个密码过期警告。
- 在名为default的profile中，所有资源相关的限制值均为unlimited，表示无限制，密码相关的限制中，除了FAILED_LOGIN_ATTEMPTS限制密码最大错误尝试为10次和PASSWORD_VERIFY_FUNCTION为null表示无密码复杂度验证以外，其他均无限制。
- 用户会话是指用户访问数据库时建立的连接，必须建立连接才能向数据库请求运行SQL
- 用户会话的调用是运行SQL语句的子处理过程。一条SQL的运行分为多个阶段，而每个阶段都由多个调用组成
创建新的用户profile
SQL> create profile pf_sample limit PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 7 IDLE_TIME 30;
Profile PF_SAMPLE 已创建。
SQL> select * from dba_profiles where profile like 'PF_SAMPLE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
PF_SAMPLE COMPOSITE_LIMIT KERNEL DEFAULT
PF_SAMPLE SESSIONS_PER_USER KERNEL DEFAULT
PF_SAMPLE CPU_PER_SESSION KERNEL DEFAULT
PF_SAMPLE CPU_PER_CALL KERNEL DEFAULT
PF_SAMPLE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PF_SAMPLE LOGICAL_READS_PER_CALL KERNEL DEFAULT
PF_SAMPLE IDLE_TIME KERNEL 30
PF_SAMPLE CONNECT_TIME KERNEL DEFAULT
PF_SAMPLE PRIVATE_SGA KERNEL DEFAULT
PF_SAMPLE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
PF_SAMPLE PASSWORD_LIFE_TIME PASSWORD DEFAULT
PF_SAMPLE PASSWORD_REUSE_TIME PASSWORD DEFAULT
PF_SAMPLE PASSWORD_REUSE_MAX PASSWORD DEFAULT
PF_SAMPLE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PF_SAMPLE PASSWORD_LOCK_TIME PASSWORD 1
PF_SAMPLE PASSWORD_GRACE_TIME PASSWORD 7
新建的profile，除了语句中指定了的限制项以外，其他限制项的值均为DEFAULT，表示继承default 用户profile中的值。 因此可以看出，default profile主要有两个特点
- 新建用户默认分配的profile为default
- 新建profile未指定的限制项的值来自于default
用户的权限、角色
权限
- 权限规定了用户可以对数据库执行哪些操作
- 权限分为系统权限和对象权限，对象权限是指用户对于特定对象的操作权限，而系统权限是指某一类操作的权限
- 权限可以被授予和废除，需要
--给用户授予create table系统权限
SQL> grant create table to u_sample;
Grant 成功。
--给用户授予访问dba_tables的权限
SQL> grant select on dba_tables to u_sample;
Grant 成功。
--查看用户被授予的系统权限
SQL> select * from DBA_SYS_PRIVS where grantee like 'U_SAMPLE';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ---------------
U_SAMPLE CREATE TABLE NO
--查看用户被授予的对象权限
SQL> select * from DBA_TAB_PRIVS where grantee like 'U_SAMPLE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
-------------------- --------------- -------------------- -------------- --------------------- ---------- ----------
U_SAMPLE SYS DBA_TABLES SYS SELECT NO NO
--废除权限
SQL> revoke create table from u_sample;
Revoke 成功。
SQL> revoke select on dba_tables from u_sample;
Revoke 成功。
SQL> select * from DBA_TAB_PRIVS where grantee like 'U_SAMPLE';
未选择任何行
SQL> select * from DBA_SYS_PRIVS where grantee like 'U_SAMPLE';
未选择任何行
- 具有某种权限的用户可以将该权限授予给其他用户，同时也可以废除该权限，但需要注意的是：
- 用户在被授予系统权限时语句中必须带有with admin option，才可以将该系统权限授予给其他用户
- 用户在被授予对象权限时语句中必须带有with grant option，才可以将该系统权限授予个其他用户
角色
角色是权限的命名集合，可以包含系统权限或对象权限，也可以不包含任何权限
- 角色里权限的来源：像用户一样被授予或废除权限或其他角色
- 角色的使用：像系统权限一样，可以授予给用户，且授予用户需要对该角色具有admin_optiion（参考系统权限的授予）
- 角色的创建：创建角色的用户本身需要具有create role系统权限（sys用户是最高权限用户，无需考虑）
- 系统预先建立的角色：有很多系统里已经预先建立好的角色，理论上可以像用户新定义的角色那样添加或废除权限，但存在很高的风险，因为有很多系统用户可能会使用这些角色，修改风险难以预估。
--创建新角色
SQL> create role rl_sample;
Role RL_SAMPLE 已创建。
--向新建角色里添加connect和resource角色
SQL> grant connect,resource to rl_sample;
Grant 成功。
--向角色里添加权限
SQL> grant select any table to rl_sample;
Grant 成功。
SQL> grant select on dba_tables to rl_sample;
Grant 成功。
--查看角色中的系统权限
SQL> select * from ROLE_SYS_PRIVS where role in ('CONNECT','RESOURCE','RL_SAMPLE') order by 1;
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- --------------
CONNECT CREATE SESSION NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE TYPE NO
--查看角色中的对象权限
SQL> select * from ROLE_TAB_PRIVS where role in ('CONNECT','RESOURCE','RL_SAMPLE');
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRANTABLE
--------------- --------------- -------------------- -------------------- ------------------------- ---
RL_SAMPLE SYS DBA_TABLES SELECT NO
--查看角色中的角色
SQL> select * from ROLE_ROLE_PRIVS where role in ('CONNECT','RESOURCE','RL_SAMPLE');
ROLE GRANTED_ROLE ADMIN_OPTION
------------------------------ ------------------------------ -------------
RL_SAMPLE CONNECT NO
RL_SAMPLE RESOURCE NO
--将新建角色授予用户
SQL> grant rl_sample to u_sample;
Grant 成功。
--查看用户被授予的角色
SQL> select * from dba_role_privs where grantee like 'U_SAMPLE';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
U_SAMPLE RL_SAMPLE NO YES
--权限验证
SQL> conn u_sample/oracle
Connected.
SQL> select count(*) from dba_tables;
COUNT(*)
----------
10160
SQL> conn / as sysdba
connected.
SQL> revoke select on dba_tables from rl_sample;
Revoke 成功.
SQL> conn u_sample/oracle
Connected.
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> revoke connect from rl_sample;
Revoke 成功.
sys@ITLTEST>conn u_sample/oracle
ERROR:
ORA-01045: user U_SAMPLE lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
网友评论