Oracle

![](/media/202509/2025-09-12_205703_3643120.040572041997196884.png)

## 概述

Oracle 数据库是甲骨文公司（Oracle Corporation）开发的**企业级关系型数据库管理系统（RDBMS）**，自 1979 年发布以来，以其高稳定性、强事务支持、完善的高可用方案和海量数据处理能力，成为金融、电信、政务等关键业务系统的核心数据存储方案。

其设计核心是 “**实例与数据库分离**”：

- **数据库（Database）**：磁盘上的永久文件集合（数据文件、日志文件等），存储实际数据。
- **实例（Instance）**：内存结构 + 后台进程的临时集合，负责处理用户请求，数据库启动时创建，关闭后消失。

值得注意的是：

- Oracle 默认对表名、列名、关键字不区分大小写，但存储时自动转为大写；若需小写，需用双引号包裹（如CREATE TABLE "emp" (...)，不推荐）；
- 在Oracle数据库中，默认的端口号是**1521**；
- Oracle数据库的官网地址：https://www.oracle.com/cn/database/。

**主流版本**

Oracle 版本命名以 “版本号 + 特性后缀” 为主，长期支持版（LTS）是企业首选：

- **11g**：2007 年发布，“g” 代表 Grid（网格计算），引入 RAC（实时应用集群）增强。
- **12c**：2013 年发布，“c” 代表 Container（容器），首次引入**多租户架构（CDB/PDB）** 。
- **19c**：2019 年发布，当前最主流的 LTS 版本（支持至 2027 年），基于 12c 优化稳定性，无重大架构变更。
- **21c**：2021 年发布，引入区块链表、原生 JSON 增强等特性，适合创新场景。

## 特性

Oracle 的特性围绕 “企业级需求” 设计，关键优势如下：

**1. 严格的 ACID 事务支持**

完全遵循原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），支持 5 种事务隔离级别（默认READ COMMITTED），通过精细化锁机制（行级锁、表级锁）和多版本并发控制（MVCC）避免并发冲突。

**2. 高可用性（HA）方案**

RAC（实时应用集群）：多节点共享存储，单点故障时自动切换，支持负载均衡。

Data Guard：主备库实时同步，主库故障时备库快速接管，保障零数据丢失。

闪回技术：快速回滚误删表、数据或整个数据库（如FLASHBACK TABLE 表名 TO BEFORE DROP）。

**3. 超强扩展性**

分区表：将大表按时间、哈希等规则拆分（如按季度分区订单表），提升查询效率。

并行处理：查询、备份等操作可拆分给多个 CPU 核心并行执行，支持 PB 级数据。

分布式数据库：跨地域部署多个数据库，通过数据库链路（DB Link）实现数据互通。

**4. 全方位安全性**

细粒度权限控制（系统权限 + 对象权限 + 角色管理）。

透明数据加密（TDE）、网络加密（SSL/TLS）、列级敏感数据加密。

完善的审计功能，记录所有操作（如登录、数据修改）。

**5. 丰富的数据对象**

支持表、视图、索引、存储过程、函数、触发器、序列、包等，通过 PL/SQL 语言实现复杂业务逻辑封装。

## 默认表空间与用户

Oracle 安装后无 “默认数据库”（需手动创建或使用样板数据库），但存在**默认表空间**和**系统用户**，是数据库运行的基础。

**默认表空间（系统表空间）**

表空间是 Oracle 特有的 “逻辑存储单元”，对应磁盘上的数据文件，默认表空间包括：

| 表空间名 | 作用                                                         | 示例数据文件路径（Linux）                  |
| -------- | ------------------------------------------------------------ | ------------------------------------------ |
| SYSTEM   | 存储数据字典（表结构、用户信息等核心元数据），不可删除。     | /u01/app/oracle/oradata/ORCL/system01.dbf  |
| SYSAUX   | 辅助系统表空间，存储 OEM 元数据、PL/SQL 调试信息等，减轻SYSTEM压力。 | /u01/app/oracle/oradata/ORCL/sysaux01.dbf  |
| UNDOTBS1 | 存储事务回滚数据（undo 数据），用于事务回滚和读一致性。      | /u01/app/oracle/oradata/ORCL/undotbs01.dbf |
| TEMP     | 存储临时数据（如排序、哈希连接结果），会话结束后数据自动清除。 | /u01/app/oracle/oradata/ORCL/temp01.dbf    |
| USERS    | 默认用户表空间，普通用户创建的表默认存储于此。               | /u01/app/oracle/oradata/ORCL/users01.dbf   |

**默认系统用户**

- SYS：超级管理员，拥有最高权限（SYSDBA），负责管理数据库实例。
- SYSTEM：管理员用户，权限次于SYS，负责管理用户、表空间等。
- SCOTT：示例用户（11g 及以前版本默认存在），包含EMP（员工表）、DEPT（部门表）等示例表，用于学习。

**解锁 SCOTT 用户示例**：

```sql
-- 以SYS身份登录
CONN SYS/[SYS密码] AS SYSDBA;
-- 解锁用户
ALTER USER SCOTT ACCOUNT UNLOCK;
-- 重置密码
ALTER USER SCOTT IDENTIFIED BY tiger; -- 经典密码tiger（可自定义）
```

## 库的状态

查看数据库的状态：

```sql
-- 1. 查看实例状态（OPEN/CLOSED/MOUNTED）
SELECT STATUS FROM V$INSTANCE;

-- 2. 查看数据库打开模式（READ WRITE/READ ONLY）
SELECT OPEN_MODE FROM V$DATABASE;

-- 3. 查看当前PDB（12c及以后）
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM DUAL;

-- 4. 查看监听状态（需sysdba）
SELECT * FROM V$LISTENER_NETWORK;
```

## 表的操作

表是 Oracle 存储数据的核心对象，操作同样包括创建、修改、删除等，需CREATE TABLE/ALTER TABLE权限。

**1. 创建表**

需指定**列名、数据类型、约束**（主键、非空、外键等），Oracle 常用数据类型：

- VARCHAR2(n)：可变长度字符串（n≤4000）；
- NUMBER(p,s)：数值（p 总位数，s 小数位数，如NUMBER(10,2)表示整数 8 位 + 小数 2 位）；
- DATE：日期时间（精确到秒）；
- TIMESTAMP：日期时间（精确到毫秒）。

**示例：创建员工表（EMP）和部门表（DEPT）**：

```sql
-- 1. 创建部门表（DEPT，主表）
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) PRIMARY KEY, -- 部门编号（主键，非空唯一）
    DNAME  VARCHAR2(14) NOT NULL, -- 部门名称（非空）
    LOC    VARCHAR2(13)           -- 部门位置
);

-- 2. 创建员工表（EMP，从表，外键关联DEPT）
CREATE TABLE EMP (
    EMPNO    NUMBER(4) PRIMARY KEY, -- 员工编号（主键）
    ENAME    VARCHAR2(10) NOT NULL, -- 员工姓名（非空）
    JOB      VARCHAR2(9),           -- 职位
    MGR      NUMBER(4),             -- 上级编号（自关联EMP.EMPNO）
    HIREDATE DATE,                  -- 入职日期
    SAL      NUMBER(7,2),           -- 薪资（7位总长度，2位小数）
    COMM     NUMBER(7,2),           -- 奖金
    DEPTNO   NUMBER(2),             -- 部门编号（外键）
    -- 外键约束：EMP.DEPTNO关联DEPT.DEPTNO
    CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPTNO) 
        REFERENCES DEPT (DEPTNO)
        ON DELETE CASCADE -- 级联删除：删除DEPT时自动删除关联的EMP记录
);
```

**2. 查看表结构**

```sql
-- 1. 简洁查看（SQL*Plus专用）
DESC EMP;

-- 2. 详细查看列信息（数据字典视图）
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'EMP'; -- Oracle默认表名大写，需匹配

-- 3. 查看表的约束
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, COLUMN_NAME 
FROM USER_CONS_COLUMNS 
WHERE TABLE_NAME = 'EMP';
```

**3. 修改表（ALTER TABLE）**

支持添加列、修改列、删除列、添加约束等。

```sql
-- 1. 给EMP表添加“邮箱”列
ALTER TABLE EMP 
ADD (EMAIL VARCHAR2(50) UNIQUE); -- UNIQUE约束：邮箱唯一

-- 2. 修改EMP表“薪资”列的数据类型（需无数据或数据兼容）
ALTER TABLE EMP 
MODIFY (SAL NUMBER(10,2)); -- 薪资从7位改为10位

-- 3. 删除EMP表的“奖金”列
ALTER TABLE EMP 
DROP COLUMN COMM;

-- 4. 给EMP表“职位”列添加非空约束
ALTER TABLE EMP 
MODIFY (JOB NOT NULL);

-- 5. 禁用/启用外键约束
ALTER TABLE EMP 
DISABLE CONSTRAINT FK_EMP_DEPT; -- 禁用外键

ALTER TABLE EMP 
ENABLE CONSTRAINT FK_EMP_DEPT;  -- 启用外键
```

**4. 删除表**

```sql
DROP TABLE EMP; -- 普通删除（可通过闪回恢复）
DROP TABLE EMP PURGE; -- 彻底删除（不可恢复，释放空间）
```

## 数据操作（DML 与事务）

DML（数据操纵语言）用于增删改查数据，包括INSERT/SELECT/UPDATE/DELETE，需表的操作权限（如SELECT ANY TABLE）。

**1. 插入数据**

支持单条插入和批量插入。

```sql
-- 1. 单条插入（指定列名）
INSERT INTO DEPT (DEPTNO, DNAME, LOC) 
VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO) 
VALUES (7369, 'SMITH', 'CLERK', TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, 10);

-- 2. 批量插入（多条数据）
INSERT ALL
    INTO DEPT (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'DALLAS')
    INTO DEPT (DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'CHICAGO')
SELECT 1 FROM DUAL; -- 必须加SELECT 1 FROM DUAL（Oracle语法要求）

-- 3. 从其他表插入数据（复制数据）
INSERT INTO EMP_BACKUP (EMPNO, ENAME, SAL)
SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10; -- 复制部门10的员工数据
```

**2. 查询表**

Oracle 的SELECT支持复杂查询（关联、聚合、排序），语法灵活。

基础查询：

```sql
-- 1. 查询所有列
SELECT * FROM EMP;

-- 2. 查询指定列（去重）
SELECT DISTINCT DEPTNO FROM EMP; -- DISTINCT：去重部门编号

-- 3. 条件查询（WHERE）
SELECT ENAME, JOB, SAL 
FROM EMP 
WHERE DEPTNO = 20 AND SAL > 2000; -- 部门20且薪资>2000的员工

-- 4. 排序（ORDER BY）
SELECT ENAME, SAL 
FROM EMP 
ORDER BY SAL DESC; -- 按薪资降序排列（DESC降序，ASC升序默认）

```

**3. 事务控制**

Oracle 默认自动开启事务，需手动提交（COMMIT）或回滚（ROLLBACK）。

```sql
-- 示例：完整事务流程
INSERT INTO EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES (8001, 'TEST', 5000, 30);
SAVEPOINT SP1; -- 创建保存点SP1

UPDATE EMP SET SAL = 6000 WHERE EMPNO = 8001;
ROLLBACK TO SP1; -- 回滚到SP1（更新操作被撤销，插入操作保留）

COMMIT; -- 提交事务（插入操作永久生效）

-- 若执行错误，回滚所有未提交操作
ROLLBACK;
```

## 元数据查询

Oracle 的元数据（如用户、表、约束信息）存储在**数据字典**（静态视图）和**动态性能视图**（动态视图，前缀V$）中，需对应权限查询。

**1. 核心数据字典视图**

数据字典视图按前缀区分权限范围：

- USER_*：当前用户拥有的对象（如USER_TABLES）；
- ALL_*：当前用户有权访问的对象（如ALL_TABLES）；
- DBA_*：所有对象（需DBA权限，如DBA_TABLES）。

用户与权限相关：

```sql
-- 1. 查看当前用户信息
SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE 
FROM USER_USERS;

-- 2. 查看当前用户拥有的角色
SELECT GRANTED_ROLE FROM USER_ROLE_PRIVS;

-- 3. 查看当前用户拥有的系统权限（如CREATE TABLE）
SELECT PRIVILEGE FROM USER_SYS_PRIVS;

-- 4. 查看当前用户拥有的表权限
SELECT TABLE_NAME, PRIVILEGE FROM USER_TAB_PRIVS;
```

表与列相关：
```sql
-- 1. 查看当前用户的所有表
SELECT TABLE_NAME, CREATED, NUM_ROWS 
FROM USER_TABLES;

-- 2. 查看EMP表的列信息（类型、长度、是否非空）
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'EMP';

-- 3. 查看EMP表的约束（主键、外键、唯一）
SELECT 
    CONSTRAINT_NAME, 
    CONSTRAINT_TYPE, -- P=主键，R=外键，U=唯一
    SEARCH_CONDITION -- 约束条件（如NOT NULL）
FROM USER_CONSTRAINTS 
WHERE TABLE_NAME = 'EMP';

-- 4. 查看外键关联的表
SELECT 
    CC.TABLE_NAME AS 从表,
    CC.COLUMN_NAME AS 从表列,
    PC.TABLE_NAME AS 主表,
    PC.COLUMN_NAME AS 主表列
FROM USER_CONS_COLUMNS CC
JOIN USER_CONSTRAINTS C ON CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
JOIN USER_CONSTRAINTS PC ON C.R_CONSTRAINT_NAME = PC.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'R' AND CC.TABLE_NAME = 'EMP';
```

表空间相关：
```sql
-- 1. 查看当前用户的默认表空间和临时表空间
SELECT DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE 
FROM USER_USERS;

-- 2. 查看所有表空间的使用情况（需DBA权限）
SELECT 
    TABLESPACE_NAME,
    ROUND(TOTAL_SIZE/1024/1024,2) AS 总大小MB,
    ROUND(FREE_SIZE/1024/1024,2) AS 空闲大小MB
FROM (
    SELECT 
        TABLESPACE_NAME,
        SUM(BYTES) AS TOTAL_SIZE,
        SUM(BYTES - USED_BYTES) AS FREE_SIZE
    FROM DBA_TEMP_FILES -- 临时表空间用DBA_TEMP_FILES，数据文件用DBA_DATA_FILES
    GROUP BY TABLESPACE_NAME
);

```

**2. 元数据函数**

Oracle 提供少量元数据函数，辅助获取环境信息：

```sql
-- 1. 获取当前用户
SELECT USER FROM DUAL;

-- 2. 获取当前用户环境信息（如语言、容器）
SELECT 
    USERENV('LANGUAGE') AS 语言,
    USERENV('CON_NAME') AS 当前PDB,
    USERENV('SESSIONID') AS 会话ID
FROM DUAL;

-- 3. 获取数据库版本（需DBMS_DB_VERSION包）
SELECT DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE AS 数据库版本
FROM DUAL;
```

## 用户与权限管理核心概念

其用户与权限管理体系以**严谨性、细粒度和安全性**著称，核心围绕 “用户（User）”“模式（Schema）”“权限（Privilege）” 和 “角色（Role）” 四大概念展开。

> 用户（User）与模式（Schema）

- **用户**：登录 Oracle 数据库的身份标识，由用户名和密码定义（如 SCOTT）。
- **模式**：用户拥有的数据库对象（表、视图、存储过程等）的集合，**用户与模式一一对应**（创建用户时自动创建同名模式）。例如，SCOTT 用户的所有表（如 EMP、DEPT）都存储在 SCOTT 模式下。

注意：Oracle 中 “用户” 和 “模式” 常被混为一谈，但本质上用户是登录身份，模式是对象容器。

> 权限分类

Oracle 权限按 “作用对象” 分为两类，覆盖不同操作范围：

| 权限类型     | 作用范围                         | 示例权限                                                     |
| ------------ | -------------------------------- | ------------------------------------------------------------ |
| **系统权限** | 允许用户执行特定的数据库操作     | CREATE TABLE（创建表）、DROP ANY TABLE（删除任何表）、CONNECT（连接数据库） |
| **对象权限** | 允许用户操作其他用户的数据库对象 | SELECT ON SCOTT.EMP（查询 SCOTT.EMP 表）、UPDATE ON HR.EMPLOYEES（更新 HR.EMPLOYEES 表） |

> 角色

角色是 “权限的集合”，用于简化权限管理（类似 MySQL 角色，但 Oracle 预定义角色更丰富）。

- **预定义角色**：Oracle 内置的常用角色（如 CONNECT、RESOURCE、DBA）。
- **自定义角色**：用户根据业务需求创建的角色（如 READ_ONLY_ROLE）。

> 权限存储与查询

Oracle 权限信息存储在数据字典视图中，核心视图包括：

- DBA_USERS：所有用户的基本信息（状态、表空间等）。
- DBA_SYS_PRIVS：用户或角色拥有的系统权限。
- DBA_TAB_PRIVS：用户或角色拥有的对象权限。
- DBA_ROLES：所有角色的信息。
- DBA_ROLE_PRIVS：用户被授予的角色。

## 用户管理

用户是权限的载体，创建用户时需指定表空间、密码策略等关键属性（Oracle 对用户资源限制更严格）。

**创建用户**

```sql
CREATE USER 用户名
IDENTIFIED BY 密码  -- 设置密码
[DEFAULT TABLESPACE 表空间名]  -- 默认表空间（存储用户对象）
[TEMPORARY TABLESPACE 临时表空间名]  -- 临时表空间（排序等临时操作）
[QUOTA 大小 ON 表空间名]  -- 表空间配额（限制用户使用的存储空间）
[PASSWORD EXPIRE]  -- 强制用户首次登录修改密码
[ACCOUNT LOCK | UNLOCK]  -- 锁定/解锁用户（默认 UNLOCK）;

```

关键说明

- 表空间：Oracle 数据存储的逻辑单位，用户必须指定默认表空间（否则使用系统默认 SYSTEM，不推荐）。
- 配额（QUOTA）：限制用户在表空间中可使用的存储空间（如 QUOTA 100M ON users 表示最多使用 100MB）。
- 密码策略：可通过 profile 配置（如密码过期时间、失败登录次数锁定），默认使用 DEFAULT profile。

```sql
-- 示例1：创建应用用户（指定表空间、配额、强制首登改密码）
CREATE USER app_user
IDENTIFIED BY App_2024!  -- 强密码（含大小写、数字、特殊字符）
DEFAULT TABLESPACE users  -- 默认表空间为 users（业务数据）
TEMPORARY TABLESPACE temp  -- 临时表空间为 temp
QUOTA 500M ON users  -- 限制在 users 表空间使用 500MB
PASSWORD EXPIRE  -- 首次登录必须修改密码
ACCOUNT UNLOCK;  -- 解锁状态（允许登录）

-- 示例2：创建只读用户（限制表空间配额）
CREATE USER read_user
IDENTIFIED BY Read_2024!
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON users  -- 不允许创建对象（只读）
ACCOUNT UNLOCK;

```

**修改用户**

用于修改用户的密码、表空间、状态等属性。

```sql
ALTER USER 用户名
[IDENTIFIED BY 新密码]  -- 修改密码
[DEFAULT TABLESPACE 新表空间]  -- 修改默认表空间
[QUOTA 新大小 ON 表空间名]  -- 修改表空间配额
[ACCOUNT LOCK | UNLOCK]  -- 锁定/解锁用户
[PASSWORD EXPIRE];  -- 强制密码过期（用户下次登录需修改）
```

```sql
-- 示例1：修改用户密码
ALTER USER app_user IDENTIFIED BY New_App_2024!;

-- 示例2：锁定离职员工用户（禁止登录）
ALTER USER app_user ACCOUNT LOCK;

-- 示例3：增加用户表空间配额
ALTER USER app_user QUOTA 1000M ON users;  -- 从 500M 增至 1000M

-- 示例4：强制用户修改密码（如怀疑密码泄露）
ALTER USER read_user PASSWORD EXPIRE;

```

**删除用户**

删除用户及其模式下的所有对象（需谨慎操作）。

```sql
DROP USER 用户名 [CASCADE];  -- CASCADE：级联删除用户所有对象（表、视图等）
```

```sql
-- 删除无对象的用户
DROP USER read_user;

-- 级联删除有对象的用户（如 app_user 创建了表）
DROP USER app_user CASCADE;  -- 必须加 CASCADE，否则报错

```

**查询用户信息**

通过数据字典视图查看用户配置：
```sql
-- 1. 查看所有用户的基本信息（状态、表空间、创建时间）
SELECT username, account_status, default_tablespace, created
FROM dba_users;

-- 2. 查看指定用户的详细配置（如 app_user）
SELECT username, profile, default_tablespace, temporary_tablespace, quota_on_users
FROM dba_users
WHERE username = 'APP_USER';  -- Oracle 用户名默认大写

-- 3. 查看用户的锁定状态
SELECT username, account_status 
FROM dba_users 
WHERE account_status LIKE '%LOCKED%';

```

## 系统权限管理

系统权限控制用户能否执行特定的数据库操作（如创建表、连接数据库），Oracle 提供超过 100 种系统权限，常用的约 20 种。

**常见系统权限说明**

| 系统权限         | 作用描述                             | 适用场景           |
| ---------------- | ------------------------------------ | ------------------ |
| CREATE SESSION   | 允许用户连接数据库（最基础权限）     | 所有需要登录的用户 |
| CREATE TABLE     | 允许用户在自己的模式下创建表         | 应用开发用户       |
| CREATE VIEW      | 允许用户在自己的模式下创建视图       | 报表开发用户       |
| CREATE PROCEDURE | 允许用户创建存储过程、函数           | 后端开发用户       |
| DROP ANY TABLE   | 允许删除任何用户的表（高危权限）     | DBA 管理员         |
| ALTER ANY TABLE  | 允许修改任何用户的表结构（高危权限） | DBA 管理员         |
| SELECT ANY TABLE | 允许查询任何用户的表（需谨慎授予）   | 数据分析用户       |

**授予系统权限**

```sql
GRANT 系统权限1, 系统权限2, ... 
TO 用户/角色
[WITH ADMIN OPTION];  -- 允许被授权者将权限转授给其他用户（系统权限专用）
```

```sql
-- 示例1：授予基础连接权限（所有用户必需）
GRANT CREATE SESSION TO app_user, read_user;

-- 示例2：授予应用用户创建表、视图、存储过程的权限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO app_user;

-- 示例3：授予管理员用户高危权限（带转授权）
GRANT DROP ANY TABLE, ALTER ANY TABLE TO dba_admin WITH ADMIN OPTION;

-- 示例4：授予只读用户查询任何表的权限（限制范围）
GRANT SELECT ANY TABLE TO read_user;

```

**回收系统权限**

```sql
REVOKE 系统权限1, 系统权限2, ... FROM 用户/角色;
```

关键特性

- 系统权限回收后，被授权者通过 WITH ADMIN OPTION 转授的权限不会被级联回收（与对象权限不同）。
- 需手动回收所有转授的权限，因此生产环境慎用 WITH ADMIN OPTION。

```sql
-- 回收 app_user 的创建存储过程权限
REVOKE CREATE PROCEDURE FROM app_user;

-- 回收 read_user 的查询任何表权限
REVOKE SELECT ANY TABLE FROM read_user;

```

## 对象权限管理

对象权限控制用户对其他模式中对象（表、视图、存储过程等）的操作权限，需明确指定 “对象所有者。对象名”。

**常用对象权限**

| 对象权限   | 适用对象       | 作用描述                         |
| ---------- | -------------- | -------------------------------- |
| SELECT     | 表、视图、序列 | 允许查询对象数据                 |
| INSERT     | 表、视图       | 允许插入数据                     |
| UPDATE     | 表、视图       | 允许更新数据（可指定列）         |
| DELETE     | 表、视图       | 允许删除数据                     |
| ALTER      | 表、序列       | 允许修改对象结构                 |
| EXECUTE    | 存储过程、函数 | 允许执行程序                     |
| REFERENCES | 表             | 允许在其他表中创建外键引用当前表 |

**授予对象权限**

```sql
GRANT 对象权限1 [(列名1, 列名2)]  -- 可选：限制仅对特定列授权
ON 所有者.对象名
TO 用户/角色
[WITH GRANT OPTION];  -- 允许被授权者转授此对象权限（对象权限专用）

```

```sql
-- 示例1：授予 app_user 查询 SCOTT.EMP 表的权限
GRANT SELECT ON scott.emp TO app_user;

-- 示例2：授予 read_user 更新 HR.EMPLOYEES 表的 name 和 salary 列的权限
GRANT UPDATE (name, salary) ON hr.employees TO read_user;

-- 示例3：授予 app_user 执行 SCOTT.GET_EMP 存储过程的权限
GRANT EXECUTE ON scott.get_emp TO app_user;

-- 示例4：授予管理员转授 HR.DEPARTMENTS 表查询权限的能力
GRANT SELECT ON hr.departments TO dba_admin WITH GRANT OPTION;

```

**回收对象权限**

```sql
REVOKE 对象权限1 [(列名1, 列名2)] 
ON 所有者.对象名
FROM 用户/角色 [CASCADE CONSTRAINTS];  -- CASCADE CONSTRAINTS：级联删除依赖的约束

```

关键特性

- 对象权限回收后，被授权者通过 WITH GRANT OPTION 转授的权限会被级联回收（与系统权限不同）。

```sql
-- 回收 app_user 对 SCOTT.EMP 表的查询权限
REVOKE SELECT ON scott.emp FROM app_user;

-- 回收 dba_admin 对 HR.DEPARTMENTS 表的查询权限（级联回收转授的权限）
REVOKE SELECT ON hr.departments FROM dba_admin;

```

## 角色管理

角色是权限的 “容器”，通过角色批量管理权限可大幅简化操作，尤其适合多用户场景（如 “开发组”“分析组”）。

**预定义角色**

Oracle 提供多个预定义角色，覆盖常见权限需求，无需手动创建：

| 预定义角色          | 包含的核心权限                                               | 适用场景           |
| ------------------- | ------------------------------------------------------------ | ------------------ |
| CONNECT             | CREATE SESSION（连接数据库）、CREATE TABLE（部分版本）等基础权限 | 临时用户、测试用户 |
| RESOURCE            | CREATE TABLE、CREATE VIEW、CREATE PROCEDURE 等创建对象的权限 | 应用开发用户       |
| DBA                 | 几乎所有系统权限（GRANT ANY PRIVILEGE、DROP ANY TABLE 等），超级权限 | 数据库管理员       |
| SELECT_CATALOG_ROLE | 查询数据字典视图的权限（如 DBA_USERS、DBA_TABLES）           | 审计用户、监控用户 |

注意：DBA 角色权限极高，仅授予核心管理员，生产环境需严格控制。

**自定义角色**

当预定义角色无法满足需求时，可创建自定义角色。

```sql
-- 创建角色
CREATE ROLE 角色名 [IDENTIFIED BY 密码];  -- 可选：设置角色密码（激活需输入）

-- 给角色授予权限（系统权限或对象权限）
GRANT 权限1, 权限2 TO 角色名;

-- 将角色授予用户
GRANT 角色名 TO 用户名 [WITH ADMIN OPTION];  -- 允许用户转授角色

```

```sql
-- 步骤1：创建自定义角色 read_only_role
CREATE ROLE read_only_role;

-- 步骤2：给角色授予基础连接和查询权限
GRANT CREATE SESSION TO read_only_role;  -- 允许连接
GRANT SELECT ON scott.emp TO read_only_role;  -- 允许查询 SCOTT.EMP
GRANT SELECT ON hr.employees TO read_only_role;  -- 允许查询 HR.EMPLOYEES

-- 步骤3：将角色授予 read_user 和 report_user
GRANT read_only_role TO read_user, report_user;

```

**角色激活与管理**

用户被授予角色后，需 “激活” 角色才能使用权限（默认激活所有非密码保护的角色）。

```sql
-- 1. 查看用户被授予的角色
SELECT grantee, granted_role, admin_option 
FROM dba_role_privs 
WHERE grantee = 'APP_USER';

-- 2. 查看角色包含的权限
SELECT role, privilege 
FROM dba_sys_privs 
WHERE role = 'READ_ONLY_ROLE';  -- 系统权限
SELECT role, owner, table_name, privilege 
FROM dba_tab_privs 
WHERE role = 'READ_ONLY_ROLE';  -- 对象权限

-- 3. 激活角色（当前会话生效）
SET ROLE read_only_role;  -- 激活指定角色
SET ROLE ALL;  -- 激活所有角色
SET ROLE NONE;  -- 禁用所有角色

-- 4. 删除角色（同时回收所有用户的该角色权限）
DROP ROLE read_only_role;

```

## 权限查询

定期查询权限是保障数据库安全的关键。

**查看用户的系统权限**

```sql
-- 格式：查询用户拥有的系统权限（含通过角色继承的权限）
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'APP_USER';  -- 用户名大写

-- 查看用户通过角色获得的系统权限（需展开角色）
SELECT DISTINCT p.privilege
FROM dba_role_privs r
JOIN dba_sys_privs p ON r.granted_role = p.role
WHERE r.grantee = 'APP_USER';

```

**查看用户的对象权限**

```sql
-- 格式：查询用户拥有的对象权限
SELECT grantee, owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'READ_USER';

```

**查看角色的权限**

```sql
-- 查看角色的系统权限
SELECT role, privilege, admin_option
FROM dba_sys_privs
WHERE role = 'RESOURCE';

-- 查看角色的对象权限
SELECT role, owner, table_name, privilege
FROM dba_tab_privs
WHERE role = 'READ_ONLY_ROLE';

```