Oracle 数据库同义词与权限详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

同义词简化对象访问，权限控制访问[1]：

详细见：Oracle 同义词详解、Oracle 权限管理详解。

---

2. 同义词

2.1 私有

CREATE SYNONYM emp FOR scott.employees;
-- 仅当前用户

SELECT * FROM emp;

2.2 公共

CREATE PUBLIC SYNONYM emp FOR scott.employees;
-- 所有用户

2.3 DB Link

CREATE DATABASE LINK remote_db CONNECT TO remote_user IDENTIFIED BY ****** USING 'remote_tns';

CREATE SYNONYM remote_emp FOR employees@remote_db;

SELECT * FROM remote_emp;

2.4 查看

SELECT synonym_name, table_owner, table_name, db_link
FROM user_synonyms;

SELECT * FROM all_synonyms WHERE synonym_name = 'EMP';

2.5 删除

DROP SYNONYM emp;
DROP PUBLIC SYNONYM emp;

---

3. 权限

3.1 系统权限

-- 创建
GRANT CREATE SESSION TO user1;
GRANT CREATE TABLE TO user1;
GRANT CREATE PROCEDURE TO user1;
GRANT CREATE VIEW TO user1;
GRANT CREATE SEQUENCE TO user1;

-- 管理
GRANT DBA TO user1;
GRANT SYSDBA TO user1;

3.2 对象权限

-- 表
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO user1;
GRANT SELECT ON employees TO user1 WITH GRANT OPTION;

-- 列
GRANT UPDATE (salary) ON employees TO user1;

-- 过程
GRANT EXECUTE ON my_proc TO user1;

-- 包
GRANT EXECUTE ON my_pkg TO PUBLIC;

3.3 撤销

REVOKE SELECT, INSERT ON employees FROM user1;
REVOKE EXECUTE ON my_proc FROM user1;

---

4. 角色

4.1 创建

CREATE ROLE app_role;
CREATE ROLE app_read_only IDENTIFIED BY ******

4.2 授权

GRANT SELECT ON employees TO app_role;
GRANT EXECUTE ON my_proc TO app_role;

GRANT app_role TO user1;
GRANT app_role TO user2;

4.3 启用/禁用

SET ROLE app_role;
SET ROLE ALL;
SET ROLE NONE;
SET ROLE app_role IDENTIFIED BY ******

4.4 查看

SELECT role FROM user_roles;
SELECT * FROM role_sys_privs WHERE role = 'APP_ROLE';
SELECT * FROM role_tab_privs WHERE role = 'APP_ROLE';

---

5. 用户

5.1 创建

CREATE USER user1 IDENTIFIED BY ******
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users
  PROFILE default;

-- 19c+
CREATE USER user1 IDENTIFIED BY ******
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

5.2 修改

ALTER USER user1 IDENTIFIED BY ******
ALTER USER user1 DEFAULT TABLESPACE users;
ALTER USER user1 QUOTA 500M ON users;
ALTER USER user1 ACCOUNT LOCK;
ALTER USER user1 ACCOUNT UNLOCK;
ALTER USER user1 PASSWORD EXPIRE;

5.3 删除

DROP USER user1;
DROP USER user1 CASCADE;

---

6. Profile

6.1 创建

CREATE PROFILE app_profile LIMIT
  SESSIONS_PER_USER 5
  CPU_PER_SESSION 10000
  CPU_PER_CALL 1000
  LOGICAL_READS_PER_SESSION 100000
  LOGICAL_READS_PER_CALL 10000
  IDLE_TIME 30
  CONNECT_TIME 480
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX 5
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  PASSWORD_VERIFY_FUNCTION verify_function;

6.2 分配

ALTER USER user1 PROFILE app_profile;

6.3 资源限制

ALTER SYSTEM SET resource_limit = TRUE;

---

7. SYS / SYSTEM

7.1 SYS

- 数据字典所有者
- SYSDBA
- 内部

7.2 SYSTEM

- 管理
- 工具
- 避免业务

7.3 SYSDBA / SYSOPER

-- SYSDBA
CONNECT sys AS SYSDBA

-- SYSOPER
CONNECT sys AS SYSOPER

---

8. Schema

8.1 概念

- Schema = User
- 对象集合
- 命名空间

8.2 操作

-- 创建对象
CREATE TABLE scott.t (...);

-- 访问
SELECT * FROM scott.t;

-- 同义词
CREATE SYNONYM t FOR scott.t;

---

9. View

9.1 权限

-- 权限
GRANT CREATE VIEW TO user1;

-- 创建
CREATE OR REPLACE VIEW emp_dept AS
SELECT e.id, e.name, d.dept_name
FROM scott.employees e, scott.departments d
WHERE e.dept_id = d.id;

-- 授权
GRANT SELECT ON emp_dept TO user2;

9.2 安全

- 列级
- 行级（WHERE）
- WITH CHECK OPTION
- WITH READ ONLY

详细见：Oracle 视图与物化视图详解。

---

10. VPD / FGAC

10.1 VPD

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema => 'SCOTT',
    object_name => 'employees',
    policy_name => 'emp_dept_policy',
    function_schema => 'SCOTT',
    policy_function => 'emp_security'
  );
END;
/

10.2 函数

CREATE OR REPLACE FUNCTION emp_security(
  schema_var VARCHAR2, table_var VARCHAR2
) RETURN VARCHAR2 IS
  v_dept NUMBER;
BEGIN
  SELECT dept_id INTO v_dept FROM users WHERE username = USER;
  RETURN 'dept_id = ' || v_dept;
END;
/

详细见：Oracle PL/SQL 安全编程详解。

---

11. 审计

11.1 标准

AUDIT SELECT ON employees BY ACCESS;
AUDIT INSERT, UPDATE, DELETE ON employees BY SESSION;
AUDIT EXECUTE ON my_proc BY ACCESS;

11.2 查看

SELECT * FROM dba_audit_trail WHERE obj_name = 'EMPLOYEES';

详细见：Oracle 审计详解。

---

12. 查询权限

12.1 系统权限

SELECT * FROM user_sys_privs;
SELECT * FROM dba_sys_privs WHERE grantee = 'USER1';

12.2 对象权限

SELECT * FROM user_tab_privs;
SELECT * FROM all_tab_privs;
SELECT * FROM dba_tab_privs WHERE grantee = 'USER1';

12.3 列权限

SELECT * FROM user_col_privs;

---

13. 应用场景

13.1 多 Schema

- 应用 Schema：app_owner
- 用户 Schema：user1, user2
- 同义词：简化
- 角色：权限

13.2 应用角色

-- 角色
CREATE ROLE app_read;
CREATE ROLE app_write;

GRANT SELECT ON app_owner.t TO app_read;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_owner.t TO app_write;

-- 用户
GRANT app_read TO report_user;
GRANT app_write TO admin_user;

13.3 跨库

-- DB Link
CREATE DATABASE LINK remote_db CONNECT TO ... USING '...';

-- 同义词
CREATE SYNONYM remote_t FOR t@remote_db;

-- 使用
SELECT * FROM remote_t;

---

14. 常见坑与排错

14.1 ORA-01031

- 权限不足
- GRANT

14.2 ORA-00942

- 表/视图不存在
- 权限
- 同义词

14.3 角色

- DEFERRABLE
- DEFAULT ROLE
- 启用

---

15. 最佳实践

1. 最小权限：安全
2. 角色：管理
3. 同义词：简化
4. 视图：限制
5. VPD：行级
6. 审计：监控
7. Profile：限制
8. 密码策略：强
9. Schema 分离：清晰
10. 文档化：权限

---

16. 参考资料

[1] Oracle Database Security Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/