centos中sqlplus权限设置详解

CentOS 上 SQL*Plus 权限设置详解

一 权限模型与前置检查

• 权限分为两类：
  • 系统权限（如 CREATE SESSION、CREATE TABLE），作用于整个数据库。
  • 对象权限（如表/视图上的 SELECT、INSERT、UPDATE、DELETE），作用于具体对象。
• 常用角色：
  • CONNECT（包含 CREATE SESSION 等最小会话权限）。
  • RESOURCE（常见开发所需对象创建权限集合）。
• 登录要点：
  • 以 SYS 登录必须使用 AS SYSDBA 或 AS SYSOPER。
  • 本地操作系统认证可免口令：执行 sqlplus / as sysdba；普通用户登录为数据字典口令验证。
  • 多实例环境需设置 ORACLE_SID 指定实例：例如 export ORACLE_SID=orcl。
• 常用连接语法：
  • 本地：sqlplus username/password
  • 指定服务：sqlplus username/password@net_service_name
  • 轻松连接：sqlplus username/password@//host:port/service_name
  • 远程：sqlplus username/password@//host:port/sid
    以上要点涉及登录语法、SYS 必须以 SYSDBA 登录、ORACLE_SID 作用及本地/远程连接方式。

二 创建用户与基础授权

• 建议流程：创建用户 → 指定默认表空间与临时表空间 → 配额 → 解锁 → 授予登录与资源角色 → 按需授予对象权限。
• 示例 SQL（可按需调整表空间与配额）：

-- 1) 创建用户
CREATE USER appuser IDENTIFIED BY "App#2025";

-- 2) 指定默认与临时表空间（请使用你库里存在的表空间名）
ALTER USER appuser DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

-- 3) 配额（示例：USERS 表空间 100MB）
ALTER USER appuser QUOTA 100M ON users;

-- 4) 解锁（如创建时锁定）
ALTER USER appuser ACCOUNT UNLOCK;

-- 5) 授予登录与基础开发角色
GRANT CONNECT, RESOURCE TO appuser;

-- 6) 授予对象权限示例（将 SCOTT.EMP 的查询与更新授予 appuser）
GRANT SELECT, UPDATE ON scott.emp TO appuser;

-- 7) 按需授予建表等系统权限（谨慎）
-- GRANT CREATE TABLE TO appuser;

说明：CREATE USER、ALTER USER、GRANT 等为标准 DDL；CONNECT/RESOURCE 为常用预置角色；对象权限可按表粒度授予。

三 角色管理与可转授权限

• 创建自定义角色并授予系统/对象权限，再把角色授予用户，便于批量与分层授权。
• 示例：

-- 1) 创建角色（可按需设置口令）
CREATE ROLE app_role IDENTIFIED BY app_role_pwd;

-- 2) 给角色授予权限
GRANT CREATE TABLE TO app_role;
GRANT INSERT, DELETE ON scott.emp TO app_role;

-- 3) 将角色授予用户
GRANT app_role TO appuser;

-- 4) 设置默认角色（用户首次登录自动启用）
ALTER USER appuser DEFAULT ROLE app_role;

-- 5) 可转授与回收（WITH ADMIN/GRANT OPTION）
GRANT CREATE SESSION TO appuser WITH ADMIN OPTION;   -- 允许 appuser 再授予 CREATE SESSION
GRANT SELECT, UPDATE ON scott.emp TO appuser WITH GRANT OPTION;  -- 允许再授予对象权限

-- 回收示例
REVOKE CREATE SESSION FROM appuser;
REVOKE SELECT, UPDATE ON scott.emp FROM appuser;
-- 再按最小权限重新授予（不含 WITH ADMIN/GRANT OPTION）
GRANT CREATE SESSION TO appuser;
GRANT SELECT, UPDATE ON scott.emp TO appuser;

要点：WITH ADMIN OPTION 用于系统权限的可转授，WITH GRANT OPTION 用于对象权限的可转授；回收后需按最小权限原则重新授予。

四 常见场景与命令清单

• 仅允许连接数据库（只读应用或入口账号）：

GRANT CREATE SESSION TO app_r;

• 只读某张表：

GRANT SELECT ON schema.table TO app_r;

• 读写某张表：

GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table TO app_rw;

• 允许创建表（开发/测试环境常见）：

GRANT CREATE TABLE TO app_dev;
-- 并给表空间配额
ALTER USER app_dev QUOTA 100M ON users;

• 解锁并重置密码（运维常用）：

ALTER USER scott ACCOUNT UNLOCK;
ALTER USER scott IDENTIFIED BY new_password;

• 撤销权限（最小权限原则）：

REVOKE SELECT, INSERT, UPDATE, DELETE ON schema.table FROM app_rw;
REVOKE CREATE SESSION FROM app_r;

• 连接串示例（远程）：

sqlplus app_r/app_r@//192.168.10.20:1521/orcl

以上命令覆盖常见的最小权限授予、对象级授权与回收、解锁与改密、以及远程连接写法。

五 安全与排错要点

• 最小权限原则：生产环境避免授予 DBA 或 ALL PRIVILEGES，优先使用 CONNECT/RESOURCE 与精确的对象权限组合。
• 角色与可转授：谨慎使用 WITH ADMIN OPTION / WITH GRANT OPTION，避免权限扩散；必要时及时回收。
• 登录认证：
  • SYS 必须以 AS SYSDBA 登录；
  • 本地 sqlplus / as sysdba 走操作系统认证；
  • 多实例请设置 ORACLE_SID 明确目标实例。
• 连接失败快速排查：
  • 检查监听与实例状态（lsnrctl status、sqlplus / as sysdba 后查看实例状态）；
  • 核对连接串（服务名/SID、端口、主机可达性）；
  • 确认账户未锁定、口令正确、已授予 CREATE SESSION。
• 表空间与配额：创建对象前确保用户有相应表空间配额，否则会出现空间不足类报错。
  以上要点涉及 SYS 登录方式、ORACLE_SID 作用、操作系统认证与常见排错路径。