Oracle 数据库、实例、表空间、用户

1 用户（user）

在 Oracle 数据库中，为了便于管理用户所创建的数据库对象（比如数据表、索引、视图等），引入了模式的概念，这样某个用户所创建的数据库对象就都属于该用户模式。

1.1 简介

1.1.1 定义

Oracle 用户就是访问 oracle 数据库的人。通过对用户的各种安全参数进行控制，以维护数据库的安全性，这些概念包括模式（schema）、权限、角色、存储设置、空间限额、存取资源限制、数据库审计等。当用户登录 Oracle 时需要进行身份认证，以便确认该用户能够进行的操作。

认证 是指对需要使用数据、资源或应用程序的用户进行身份确认。通过认证后，可以为用户后面的数据库操作提供一种可靠的连接关系。Oracle 提供了多种身份认证方式：操作系统身份认证，Oracle 数据库身份认证，管理员身份认证。

  • 操作系统身份认证：主要对登录操作系统的用户的合法身份进行认证。如：sqlplus / as sysdba
  • Oracle 数据库身份认证：使用存储在数据库中的信息对试图连接数据库的用户进行身份认证。在用户连接数据库时，必须提供正确的用户名和密码，才能登录到 Oracle 数据库。
  • 数据库管理员（DBA）认证：DBA 拥有最高的管理权限，可以执行一些特殊的操作，比如关闭或启动数据库。

1.1.2 用户相关信息

1.1.2.1 用户默认表空间

表空间 是信息存储的最大逻辑单位、当用户连接到数据库时，若未指出数据的目标存储表空间时，则数据存储在用户的默认表空间中。

用户的默认表空间可以在创建用户时指定，也可以使用 aler user 命令指定。如果创建用户时不指定默认表空间，则普通用户的默认空间为 users，sys 用户的默认表空间为 system，可以通过 dba_users 查看（all_users 的信息要比 dba_users 少）。

1.1.2.2 用户临时表空间

临时表空间用来管理数据库排序操作以及用于存储 临时表、中间排序结果 等临时对象。当 ORACLE 需要用到排序时，并且 PGA 中 sort_area_size 大小不足时，将会把数据放入临时表空间里进行排序。如果创建用户时，不指定默认的临时表空间，则默认临时表空间为 temp。

1.1.2.3 用户资源文件

用户资源文件用来对用户的资源存取进行限制，防止非正常连接数据库。包括：cpu 使用时间限制、内存逻辑读个数限制、每个用户同时可以连接的会话数限制、一个会话的空间和时间限制、一个会话的持续时间限制、每次会话的专用 SGA 空间限制。

创建资源文件命令如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create profile profile_name limit 参数 ...;

资源文件的参数如下：
connect_time：指定一个会话能保持连接到数据库的总时间
cpu_per_call：限制事务内每个调用使用CPU的时间。
cpu_per_sessin：限制每个会话内使用CPU的时间。
sessions_per_time：限制用户可以打开并发的最大会话数。
idle_time：限制用户的最大空闲时间。
logical_reads_per_session：限制数据块读取的总数目。
logical_reads_per_call：限制每个会话调用总的逻辑读取数。
private_sga：指定一个在SGA的共享池组件中分配的空间限额（仅适用于共享服务器）。

composite_limit：对资源设置使用一个总的限制。Oracle考虑用四个参数来计算加权的composite_limit。分别为:cpu_per_session, logical_reads_per_sessions, connect_time, private_sga。可以使用 alter resource cost来设置.。

密码类：
failed_login_attempts：指用户被锁之前可以尝试的最大登录数。
password_life_time：指定使用特定密码的时间限制，如果超出此时间间隔，那么密码将过期。
password_grace_time：设置一个时间段，在此时间段内将发出一个密码过期警告。
password_lock_time：设置用户被锁定的天数，过了此天数，用户将自行解锁。
password_reuse_time：指定重新使用密码要经过多少天。
password_reuse_max：指定重新使用某个特定密码前，要经过多少次修改。
passwrod_verify_function：此参数允许指定Oracle提供的密码验证函数来建立自动密码验证。

为用户指定资源文件：alter user user_name profile rofile_name;

1.1.2.4 用户表空间限额

配额大小指的是用户指定使用表空间的的大小，默认情况下用户没有配额限制。在创建或修改用户时，可以由参数 quota 指定。若用户在向表空间存储数据时，超出了此限额，则会产生错误。错误信息如：ORA-01536:space quota exceeded for tablespace tablespacename…。

通过查询字典 dba_ts_quotas 查看表空间限额信息：

1
SQL> select tablespace_name, username, bytes/1024/1024 size_MB, max_bytes from dba_ts_quotas;

1.1.2.5 用户管理有关的数据字典

Oracle 和用户管理相关的数据字典主要有三个：DBA_USERS、USER_USERS、ALL_USERS：

  • DBA_USERS：该数据字典用于查询 Oracle 的所有用户信息
  • ALL_USERS：该数据字典用来查询所有用户的信息，但是能够查询的信息比 DBA_USERS 要少得多
  • USER_USERS：该数据字典用来查询当前用户的信息

1.1.3 用户、模式、模式对象

用户、模式、模式对象区别：

  • 用户：这里的用户并不是指数据库的操作人员，而是数据库中定义的一个名称，更准确地说它是账户，只是习惯上称其为用户。也可以说用户是用来连接数据库和访问数据库对象的
  • 模式：在 oracle 中，为了便于管理用户所创建的数据库对象，引入了模式概念。模式是一个数据库对象的集合。模式为一个数据库用户所拥有，并且具有与该用户相同的名称
  • 模式对象：由用户创建的逻辑结构，用以存储或引用数据，其实就是指数据库对象

用户与模式区别：

  • 用户是连接数据库对象
  • 模式是管理数据库对象的
  • 用户与模式在 oracle 中是一对一关系

模式与模式对象：

  • 模式 是一个数据库对象的集合。模式为一个数据库用户所有，并且具有与该用户相同的名称，比如 SYSTEM 模式、SCOTT 模式 等。在一个模式内部不可以直接访问其他模式的数据库对象，即使在具有访问权限的情况下，也需要指定模式名称才可以访问其他模式的数据库对象。
  • 模式对象 是由用户创建的逻辑结构，用以存储或引用数据。简单地说，模式与模式对象之间的关系就是拥有与被拥有的关系，即模式拥有模式对象；而模式对象被模式所拥有。

1.1.4 实例模式 SCOTT

Oracle 提供的 SCOTT 模式的目的，就是为了给用户提供一些实例表和数据来展示 Oracle 数据库的一些特性，通过连接到 SCOTT 用户模式，查询数据字典视图 USER_TABLES 可以获得该模式所包含的数据表，共计 4 个。

另外，用户也可以在 SYSTEM 模式下查询 SCOTT 模式所拥有的数据表，但要求使用 dba_tables 数据表。

1.1.5 各个用户区别

(1)sys 和 system 用户：

  • system：普通管理员。用户具有 DBA 权限，但是没有 SYSDBA 权限，用户只能用 normal 身份登陆。system 是数据库内置的一个普通管理员，手工创建的任何用户在被授予 dba 角色后都跟这个用户差不多。
  • sys：超级管理员。用户具有 SYSDBA 或者 SYSOPER 权限，登陆也只能用这两个身份，不能用normal。sys 用户具有 DBA 权限，并具有 SYS 模式。只能通过 SYSDBA 登录数据库，是 Oracle 数据库中权限最高的帐号

normal 、sysdba、 sysoper 区别：

  • normal：是普通用户，登录后仍然是登录名（show user命令）
  • sysdba：拥有最高的系统权限，sysdba 登陆后是 sys （show user命令）
  • sysoper：主要用来启动、关闭数据库，sysoper 登陆后用户是 public（show user命令）

SYSOPER 权限，即数据库操作员权限，权限包括：

  • 打开数据库服务器；关闭数据库服务器
  • 备份数据库；恢复数据库
  • 日志归档 ；会话限制

SYSDBA 权限，即数据库管理员权限，权限包括：

  • 打开数据库服务器；关闭数据库服务器
  • 备份数据库；恢复数据库
  • 日志归档 ；会话限制
  • 管理功能； 创建数据库

(2)sysman：是 Oracle 数据库中用于 EM 管理的用户，可以删除

(3)scott：Oracle 提供的示例用户，里面有一些给初学者操作的表，例如(emp，dept，salgrade，bonus)

1.2 用户管理

Oracle 数据库中任何对象都属于一个特定用户，用户的创建、删除、授权管理相关操作需要具备 dba(数据库管理员)权限。

1.2.1 创建用户

创建用户的语法如下：

1
2
3
4
5
CREATE USER user_name
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE tablespace_name
PROFILE profile_name QUOTA integer|UNLIMITED ON tablespace;

说明：

  • IDENTIFIED BY password：用户口令
  • DEFAULT TABLESPACE tablespace：默认表空间。如果不指定，默认空间为 users
  • TEMPORARY TABLESPACE tablespace：临时表空间。如果不指定，默认为 temp
  • PROFILE profile|DEFAULT：用户资源文件；
  • QUOTA integer[K|M]|UNLIMITED ON tablespace：用户在表空间上的空间使用限额

查询用户的默认表空间

1
2
3
4
SQL> select username, default_tablespace, temporary_tablespace from dba_users where username = 'WGX';

-- 查询用户 wgx 的资源文件名
select username,profile from dba_users where username='WGX';

1.2.2 修改用户

修改用户的语法如下

1
2
3
4
5
6
7
-- 该命令各部分参数的含义与 create user 完全相同
Alter User user_name
Identified by password
Default Tablespace tablespace_name
Temporary Tablespace tablespace_name
Profile profile_name
Quota integer/unlimited on tablespace;

修改相关 sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- 修改用户 wgx 的密码，注：密码用双引号括起来
SQL> alter user wgx identified by "WGX123456";


-- 修改用户 wgx 的默认表空间
SQL> alter user wgx default tablespace ts003;


-- 修改用户 wgx 的默认临时表空间
SQL> alter user wgx temporary tablespace temp03;


-- 修改用户 wgx 的资源文件
SQL> alter user wgx profile wgx_new_profile;


-- 查看用户 wgx 的信息
SQL> select username,default_tablespace,temporary_tablespace,profile from dba_users where username ='WGX';


-- 为用户 wgx 授予权限
SQL> grant connect,resource to wgx;
Grant succeeded.


-- 强制用户 wgx 修改密码
SQL> Alter user wgx password expire;
User altered.


-- 当用户 wgx 登录时强制要求修改密码
SQL> conn wgx/WGX123456
ERROR:
ORA-28001: the password has expired


-- 为用户加锁
SQL> alter user wgx account lock;
User altered.


-- 以 wgx 用户登录时显示用户被锁定
SQL> conn wgx
Enter password: 
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.


-- 为用户解锁
SQL> alter user wgx account unlock;
User altered.

1.2.3 用户监控

查询用户会话信息

1
SQL> select username, sid, serial#, machine from v$session where username = 'WGX';

删除用户会话信息

1
2
3
4
5
6
7
命令格式：
Alter system kill session 'sid, serial#';

SQL> alter system kill session '34,73';
System altered.

SQL> select username, sid, serial#,status, machine from gv$session where username ='WGX';

1.2.4 删除用户

删除用户的语法

1
drop user user_name [descade];

说明：如果要删除的用户包含对象，需要使用 descade 参数连同包含的对象一并删除

1.3 用户管理

1.3.1 限制 dba 用户远程登录

限制具备超级管理（sysdba）权限的用户远程登录。

设置初始化参数 remote_login_passwordfile=none，禁止 sysdba 用户远程登录

1
2
3
4
5
6
7
8
查看参数
SQL> show parameter remote_login_passwordfile

SQL> alter system set remote_login_passwordfile=none scope=spfile sid='*';
System altered.

--重启数据库，查看初始化参数 remote_login_passwordfile 的取值
SQL> show parameter remote_login_passwordfile

在 sqlnet.ora 中设置 SQLNET.AUTHENTICATION_SERVICES=NONE 禁止 sysdba 角色的自动登录

1
2
3
4
5
6
7
8
[grid@rac2 ~]$ cd $ORACLE_HOME/network/admin

[grid@rac2 admin]$ vi sqlnet.ora
# sqlnet.ora.rac2 Network Configuration File: /u01/app/11.2.0/grid/network/admin/sqlnet.ora.rac2
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/grid
SQLNET.AUTHENTICATION_SERVICES=NONE

1.3.2 用户最小权限原则

根据业务需要，为用户配置所需的最小权限。

1
2
3
4
5
--为用户赋予最小权限
grant 权限 to user_name;

--收回用户的多余权限
revoke 权限 from user_name;

1.3.3 数据字典保护

启用数据字典保护，只有 sysdba 用户才能访问数据字典表。设置初始化参数 O7_DICTIONARY_ACCESSIBILITY 为 false。

1
SQL> show parameter o7

2 权限

2.1 简介

权限 是用户对一项功能的执行权力。每个 Oracle 用户都拥有一些由其创建的表、视图和其他资源。Oracle 系统提供三种权限：对象级（Object），系统级（System），角色级（Role）。

这些权限可以授予给用户、特殊用户 public 或 角色，

  • 用户 public 是 Oracle 预定义的，每个用户享有这个用户享有的权限。如果授予一个权限给特殊用户 Public，则意味作将该权限授予了该数据库的所有用户。
  • Oracle 角色（role）是一组权限（privilege）。可以给角色授予指定的权限，然后将角色赋给相应的用户。对管理权限而言，角色是一个工具，权限能够被授予给一个角色，角色也能被授予给另一个角色或用户。用户可以通过角色继承权限，除了管理权限外角色服务没有其它目的。

2.2 权限分类

在 Oracle 中，根据系统管理方式不同，将权限分为系统权限与实体权限两类。

  • 系统权限：是指被授权用户是否可以连接到数据库上，在数据库中可以进行哪些系统操作：例如启动/停止数据库，修改数据库参数。
    系统权限是针对于用户设置的，用户必须被授予相应的系统权限，才能连接到数据库中进行想要的操作。

  • 实体权限：是指用户对具体的模式实体（schema）所拥有的权限。

2.2.1 系统权限管理

相关操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 系统权限授权：系统权限授权，系统权限只能由 DBA 用户授出，命令如下：
grant connect, resource, dba to 用户名1 [,用户名2]...;


-- 查看系统权限：使用DBA_SYS_PRIVS 查看系统权限
select * from dba_sys_privs where grantee='CONNECT';


-- 系统权限传递：授予权限时使用 WITH ADMIN OPTION选项，则得到的权限可以传递，即用户可以把自己的系统再授予其他用户。
grant connect, resource, dba to white with admin option;


-- 系统权限回收：系统权限无级联，即 A 授予 B 权限，B 授予 C 权限，如果 A 收回 B 的权限，C 的权限不受影响
Revoke connect, resource from 用户名1;

常见的系统权限

1
2
3
4
5
6
7
CREATE SESSION      连接数据库
CREATE TABLESPACE   创建表空间
ALTER TABLESPACE    修改表空间
DROP TABLESPACE     删除表空间
CREATE USER         创建用户
DROP USER           修改用户
CREATE TABLE        创建表

全部的系统权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
表空间 　
CREATE TABLESPACE   创建表空间；不管用户有何操作系统特权，经由Oracle把文件添加到操作系统中
ALTER TABLESPACE    改变表空间；不管用户有何操作系统特权，经由Oracle把文件添加到操作系统中
MANAGE TABLESPACE   使任何表空间脱机，使任何表空间联机，开始和结束对任何表空间的备份
DROP TABLESPACE     删除表空间
UNLIMITED TABLESPACE  使用任何没有数量限制的表空间。此特权忽略了所分配的任何具体定额。假如被取消的话，被授权者的模式对象仍然保留，但是进一步的表空间分配被拒绝，除非这一分配是具体的表空间定额允许的。此系统特权仅可以授予用户，而不授予角色。一般而言，应分配具体的表空间定额，而不授予此系统特权

用户  　
CREATE ANY USER 创建用户；分配任意表空间上的定额，设置缺省和临时表空间，指定一个环境资源文件（在CREATE USER 语句中）
BECOME ANY USER 成为另一个用户（这是任何一个执行完全数据库导入的用户所需要的）
ALTER USER      改变其他用户：修改任意用户的口令或验证方法，分配表空间定额，设置缺省或临时表空间，在ALTER USER 语句中指定环境资源文件与缺省角色（不必改变自有口令）
DROP USER       删除另一个用户

角色  　
CREATE ROLE     创建角色
ALTER ANY ROLE  改变数据库中的任何一个角色
DROP ANY ROLE   删除数据库中的任何一个角色
GRANT ANY ROLE  授权数据库中的任何一个角色

表
CREATE TABLE      在自有模式中创建表。还使被授权者能在自有模式下的表中创建索引，包括那些用于完整性约束的索引（被授权者必须有表空间的定额或UNLIMITED TABLESPACE 特权）
CREATE ANY TABLE  在任何模式中创建表（假如被授权者有CREATE ANY TABLE 特权并在另一个用户模式中创建了一张表，那么拥有者必须在那个表空间上有空间定额。表的拥有者不必具有CREAT [ANY] TABLE 特权）
ALTER ANY TABLE   改变任何模式中的任何表并编译任何模式中的任何视图
BACKUP ANY TABLE  在任何模式中使用表的导出工具执行一个增量导出操作
DROP ANY TABLE    删除或截断任何模式中的任何表
LOCK ANY TABLE    锁定任何模式中的任何表或视图特权  所能实现的操作
COMMENT ANY TABLE 对任何模式中的任何表、视图或列进行注释
SELECT ANY TABLE  对任何模式中的任何表、视图或快照进行查询
INSERT ANY TABLE  把行插入到任何模式中的任何表或视图中
UPDATE ANY TABLE  修改任何模式中的任何表或视图中的行
DELETE ANY TABLE  删除任何模式中的任何表或视图中的行

索引  　
CREATE ANY INDEX  在任何表的任何模式中创建一条索引
ALTER ANY INDEX   改变数据库中的任何索引
DROP ANY INDEX    删除数据库中的任何索引

视图  　
CREATE VIEW       在自有模式中创建视图
CREATE ANY VIEW   在任意模式中创建视图。要在另一个用户模式中创建视图，你必须具有CREATE ANY VIEW 特权，拥用者必须在该视图引用的对象上具有所需的特权

过程  　
CREATE PROCEDURE      在自有模式中创建存储的过程、函数和包
CREATE ANY PROCEDURE  在任何模式中创建存储的过程、函数和包（这要求用户还要有ALTER ANY TABLE 、BACKUP ANY TA B LE 、DROP ANY TABLE 、SELECT ANY TABLE 、INSERT ANY TABLE 、UPDATE ANY TABLE 、DELETE ANY TABLE 或GRANT ANY TABLE 特权 
ALTER ANY PROCEDURE   编译任何模式中的任何存储的过程、函数或包
DROP ANY PROCEDURE    删除任何模式中的任何存储的过程、函数或包
EXECUTE ANY PROCEDURE 执行任何过程或函数（独立的或成组的），或在任何模式中引用任何包变量
DEBUG ANY PROCEDURE   查看定义任何存储程序

序列  　
CREATE SEQUENCE     在自有模式中创建序列
CREATE ANY SEQUENCE 在任何模式中创建任何序列
ALTER ANY SEQUENCE  在任何模式中改变任何序列
DROP ANY SEQUENCE   在任何模式中删除任何序列
SELECT ANY SEQUENCE 在任何模式中引用任何序列

触发器 　
CREATE TRIGGER      在自有模式中创建触发器
CREATE ANY TRIGGER  在任何模式中创建与任何模式的任何表相关的任何触发器
ALTER ANY TRIGGER   启用、停用或编译任何模式中的任何触发器
DROP ANY TRIGGER    删除任何模式中的任何触发器

同义词 　
CREATE SYNONYM      在自有模式中创建同义词
CREATE ANY SYNONYM  在任何模式中创建任何同义词
DROP ANY SYNONYM    在任何模式中删除任何同义词

分析  　
ANALYZE ANY   分析数据库中的任何表、簇或索引

审计  　
AUDIT ANY     审计数据库中的任何模式对象
AUDIT SYSTEM  启用与停用语句和特权的审计选项

簇 　
CREATE CLUSTER      在自有的模式中创建一个簇
CREATE ANY CLUSTER  在任何一个模式中创建一个簇；操作类似于CREATE ANY TABLE
ALTER ANY CLUSTER   改变数据库中的任何一个簇
DROP ANY CLUSTER    删除数据库中的任何一个簇

数据库 　
ALTER DATABASE          改变数据库；不管操作系统的特权，经由Oracle把文件添加到操作系统中数据库链接 　
CREATE DATABASE LINK    在自有模式中创建专用数据库链接库 　
CREATE LIBRARY          在自有模式中创建调出库
CREATE ANY LIBRARY      在任何模式中创建调出库
DROP LIBRARY            删除自有模式中的调出库
DROP ANY LIBRARY        删除任何模式中的调出库

特权  　
GRANT ANY PRIVILEGE    授予任何系统特权（不包括对象特权）环境资源文件  　
CREATE PROFILE         创建环境资源文件
ALTER PROFILE          改变数据库中的任何环境资源文件
DROP PROFILE           删除数据库中的任何环境资源文件
ALTER RESOURCE COST    设置所有的用户会话中使用的资源开销特权  所能实现的操作

公共数据库链接 　
CREATE PUBLIC DATABASE LINK 创建公共数据库链接
DROP PUBLIC DATABASE LINK   删除公共数据库链接

公共同义词 　
CREATE PUBLIC SYNONYM 创建公共同义词
DROP PUBLIC SYNONYM   删除公共同义词

回滚段 　
CREATE ROLLBACK SEGMENT 创建回滚段
ALTER ROLLBACK SEGMENT  改变回滚段
DROP ROLLBACK SEGMENT   删除回滚段

会话  　
CREATE SESSION  连接到数据库。用户被创建后，没有任何权限，包括登录。用户如果想登录，至少需要有 CREATE SESSION 的权限
ALTER SESSION   发出A LTER SESSION 语句
RESTRICTED SESSION  当数据库利用STARTUP RESTRICT 启动时进行连接（OSOPER与OSDBA角色包含此特权）

快照  　
CREATE SNAPSHOT       在自有模式中创建快照（用户还必须具有C R E ATE TA B L E 特权）
CREATE  ANY SNAPSHOT  在任何模式中创建快照（用户还必须具有CREATE ANY TABLE特权）
ALTER SNAPSHOT        改变任何模式中的任何快照
DROP ANY SNAPSHOT     删除任何模式中的任何快照

系统  　
ALTER SYSTEM  发出ALTER SYSTEM 语句

事务  　
FORCE TRANSACTION       强迫提交或回滚本地数据库中悬而未决的自有的分布式事务
FORCE ANY TRANSACTION   强迫提交或回滚本地数据库中悬而未决的任何分布式事务

2.2.2 实体权限管理

实体权限分类：select、update、insert、alter、index、delete、all、execute

DBA 用户可以操作全体用户的任意基表，无需授权。

使用 sys 账户为 white 用户和 black 用户创建表，并输入数据：

1
2
3
4
5
6
7
8
9
SQL> show user
USER is "SYS"
SQL> create table white.t1(x int,name varchar2(20));
                  *
SQL> insert into white.t1 values(1,'white:t1');

SQL> create table black.t22(x int,name varchar2(20));

SQL> insert into black.t22 values(1,'black:t22');

授权相关

1
2
为 white 用户授予实体权限
SQL> grant select on scott.emp to white;

实体权限传递授权：授权时使用 with grant option 参数可实现实体权限的传递授权。

1
SQL> grant select on scott.dept to white with grant option;

实体权限的回收：收回white 用户针对 scott.emp 对象的查询权限
如果收回某个用户的对象权限，那么对于这个用户使用 WITH GRANT OPTION 授予权限的用户来说，同样还会收回这些用户的相同权限，也就是说回收授权是级联的

1
SQL> revoke select on scott.emp from white;

将对象权限授予全体用户

1
2
3
将对象 white.t1 的查询权限授予所有用户
SQL> grant select on white.t1 to public;
Grant succeeded.

2.3 查询用户权限

ORACLE 的数据字典分为：USER，ALL 和 DBA

  • USER_*：有关用户所拥有的对象信息，即用户自己创建的对象信息。
  • ALL_*：有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上其他用户创建的对象但该用户有权访问的信息。
  • DBA_*：整个数据库中对象的信息。

与用户权限相关的数据字典表有：

  • 系统权限
    所有用户和角色所拥有的系统权限，使用 where grantee=‘*’
    查询指定用户或角色所拥有的系统权限：DBA_SYS_PRIVS
    当前用户所拥有的系统权限：USER_SYS_PRIVS
    某个角色所拥有的系统权限：ROLE_SYS_PRIVS

  • 对象权限
    所有用户的对象权限：DBA_TAB_PRIVS
    所有用户的对象权限：ALL_TAB_PRIVS
    当前用户的对象权限：USER_TAB_PRIVS
    角色所拥有的对象权限：ROLE_TAB_PRIVS

  • 当前会话的权限
    当前用户所拥有的系统权限和对象权限：SESSION_PRIVS
    当前用户被激活的角色：SESSION_ROLES

  • 角色信息
    所有角色：DBA_ROLES
    授予用户的角色：DBA_ROLE_PRIVS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--系统级权限
select * from dba_sys_privs;--查询所有用户的系统权限
select * from user_sys_privs;--查询当前用户的系统权限
select * from session_privs;--查自己拥有哪些系统权限

--对象级权限
select * from dba_tab_privs;--所有用户的对象级权限
select * from all_tab_privs;
select * from user_tab_privs;--当前用户的对象级权限

--查询用户拥有的角色
select * from dba_role_privs;--所有用户的角色
select * from user_role_privs;--当前用户的角色

--角色级权限
select * from role_sys_privs;--查看角色所包含的权限(只能查看登陆用户拥有的角色)
select * from role_sys_privs a where a.ROLE = 'CONNECT';
select * from role_sys_privs a where a.ROLE = 'RESOURCE';

3 角色

角色（role）是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。

3.1 系统预定义角色

预定义角色是在数据库安装后，系统自动创建的一些常用的角色。查询角色所包含的权限可以使用以下语句：

1
SQL> select * from role_sys_privs where role='RESOURCE';

  • CONNECT，RESOURCE，DBA
    这些预定义角色主要用于数据库管理。Oracle 建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。

    • DBA：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构。
    • RESOURCE：RESOURCE 可以创建实体，不可以创建数据库结构。
    • CONNECT：CONNECT 只可以登录 Oracle，不可以创建实体，不可以创建数据库结构。

    对于普通用户：可以授予 RESOURCE、CONNECT 权限。
    对于 DBA 用户：授予 RESOURCE、CONNECT、DBA 权限。

  • DELETE_CATALOG_ROLE，EXECUTE_CATALOG_ROLE，SELECT_CATALOG_ROLE
    这些角色主要用于访问数据字典视图和包。

  • EXP_FULL_DATABASE，IMP_FULL_DATABASE
    这两个角色用于数据导入导出工具的使用。

  • AQ_USER_ROLE，AQ_ADMINISTRATOR_ROLE
    这两个角色用于 Oracle 高级查询功能。

  • SNMPAGENT
    用于Oracle enterprise manager 和 Intelligent Agent

  • RECOVERY_CATALOG_OWNER
    用于创建拥有恢复库的用户。

3.2 管理角色

3.2.1 一般操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 创建角色
create role role_name;


-- 创建带有口令的角色
create role class_manager identified by Wgx123456;

SQL> create role teacher;
SQL> create role student;


-- 为角色授权
SQL> grant create any table, create procedure to teacher;
SQL> grant SELECT ANY TABLE to student;


-- 授予角色给用户
SQL> grant teacher to white;
SQL> grant student to black;


-- 查看用户 while 和 black 包含的角色：
SQL> select * from dba_role_privs where grantee='WHITE';
SQL> select * from dba_role_privs where grantee='BLACK';


-- 查看角色所包含的权限
SQL> select * from role_sys_privs where ROLE='TEACHER';
SQL> select * from role_sys_privs where ROLE='STUDENT';

3.2.2 为角色添加或删除口令

1
2
3
4
5
6
-- 为角色添加口令
SQL> alter role teacher identified by Tea123456;


-- 删除角色的口令
SQL> alter role teacher not identified;

3.2.3 设置当前用户要生效的角色

假设 user1 用户有 b1、b2、b3 三个角色，那么如果 b1 未生效，则 b1 所包含的权限对于用户 user1 来讲是不拥有的，只有角色生效了，角色内的权限才作用于用户。最大可生效角色数由参数 MAX_ENABLED_ROLES 设定：

1
SQL> show parameter  MAX_ENABLED_ROLES

用户登录后，oracle 将所有直接赋给用户的权限和用户默认角色中的权限赋给用户。

1
2
3
4
5
6
7
SQL> select * from SESSION_ROLES;

SQL> create user boss identified by Boss123456;
SQL> create role manager;

SQL> grant connect,resource,dba to manager;
SQL> grant manager,teacher,student to boss;

使 boss 用户的角色生效：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--设置所有角色失效
SQL> set role none;

SQL> select * from session_roles;
no rows selected

使 teacher 生效
SQL> set role teacher;

--使 connect，student 生效
SQL> set role student,connect;

--使除了 student 外的该用户的所有其它角色生效
SQL> set role all except student;

3.2.4 默认角色/删除角色

修改用户，设置其默认角色

1
SQL> alter user boss default role teacher;

删除角色
角色删除后，原来拥用该角色的用户就不再拥有该角色了，相应的权限也就没有了。

1
2
SQL> drop role student;
Role dropped.

Reference

Oracle
#Oracle
Oracle 数据库、实例、表空间、用户
https://flepeng.github.io/042-Oracle-31-核心概念-Oracle-用户、权限、角色/
作者
Lepeng
发布于
2025年4月1日
许可协议