52-Oracle 23 ai DB_DEVELOPER_ROLE和BOOLEAN类型-新特性-优快云博客

本文链接：https://blog.youkuaiyun.com/sharpwt/article/details/148793178

小伙伴们有时候还为开权限而烦恼呢，给太大有风险，开发不小心搞坏数据，给小了，

不停申请授权。现在需要配合各种安全检测，还需要进行授权管理的测评。

现在Oracle Database 23ai 的开发者角色与布尔类型新特性的改变。

一、权限管理标准化：DB_DEVELOPER_ROLE

传统方案的办法

权限碎片化：需手动组合 CONNECT（仅基础连接） + RESOURCE（基础对象创建）角色，缺失现代权限（如调试、JSON/图数据处理）
安全风险：开发者常被过度授予 DBA 角色，导致生产环境误操作风险

23ai 解决方案和传统的对比

单角色覆盖全场景：整合 40+ 系统权限，
自动继承关键子角色：包含 RESOURCE（基础对象创建）、CTXAPP（全文索引）、SODA_APP（JSON处理）

--23ai以前
-- 使用 SYSDBA 执行
CREATE USER app_dev IDENTIFIED BY "Pwd123!" DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
--User 已创建。
GRANT CONNECT,RESOURCE TO app_dev;  -- 基础开发权限
-- 单独授予调试权限（RESOURCE 不包含）
GRANT DEBUG CONNECT SESSION TO app_dev;  

-- 授予 JSON 处理角色（需单独添加）
  -- RESOURCE 在 23ai 前不自动包含此角色
GRANT SODA_APP TO app_dev;
--23ai
SYS@FREE> create user dev_user identified by "Pwd123!";

User created.
-- 1. 授予开发者角色（需 SYSDBA 执行）
GRANT DB_DEVELOPER_ROLE TO dev_user;

-- 2. 验证权限覆盖范围（以 dev_user 身份执行）
CONNECT dev_user/Pwd123!
SELECT PRIVILEGE 
FROM USER_SYS_PRIVS 
WHERE USERNAME = USER;

权限对比（传统 vs 23ai）

权限类型	CONNECT	RESOURCE(23ai)	DB_DEVELOPER_ROLE
核心权限	CREATE SESSION	CREATE TABLE/VIEW	继承RESOURCE所有权限
现代开发支持	无	部分（如属性图）	全面（AI/微服务/调度任务）
安全管控	低风险	中风险（权限冗余）	最小权限原则，审计友好

实操脚本

-- 授予开发者角色
GRANT DB_DEVELOPER_ROLE TO dev_user;  

-- 验证权限列表
SELECT PRIVILEGE 
FROM DBA_SYS_PRIVS 
WHERE GRANTEE = 'DB_DEVELOPER_ROLE';  
-- 输出包含 CREATE JOB, DEBUG CONNECT SESSION...
--PRIVILEGE
----------------------------------------
CREATE DOMAIN
CREATE MLE
EXECUTE DYNAMIC MLE
CREATE CUBE BUILD PROCESS
CREATE CUBE
CREATE CUBE DIMENSION
CREATE MINING MODEL
CREATE JOB
DEBUG CONNECT SESSION
ON COMMIT REFRESH
CREATE DIMENSION
FORCE TRANSACTION
CREATE SESSION

13 rows selected.

二、布尔类型 BOOLEAN：杜绝逻辑冗余

传统模拟方案的缺陷

方案	实现	工程痛点
NUMBER(1)	is_active NUMBER(1) CHECK (is_active IN (0,1))	需手动转换：WHERE is_active = 1
CHAR(1)	archived CHAR(1) CHECK (archived IN ('Y','N'))	语言依赖（如法语需 'O'/'N'）
NULL 处理	用 NULL 表示“未知”	索引效率低，三值逻辑复杂

23ai 原生方案

直接存储逻辑状态：

ALTER TABLE HR.EMPLOYEES ADD is_processed BOOLEAN DEFAULT FALSE;
--Table altered.

简化查询与更新：

-- 条件过滤
SELECT * FROM HR.EMPLOYEES WHERE is_processed = TRUE;  
--
SELECT * FROM HR.EMPLOYEES WHERE is_processed = FALSE;
107 rows selected.

迁移脚本

-- 步骤1：添加原生布尔列（允许 NULL 作为过渡）
ALTER TABLE HR.employees 
  ADD is_processed_new BOOLEAN DEFAULT NULL;  -- 初始设为 NULL 便于区分未迁移数据

-- 步骤2：数据迁移（兼容数字型/字符型旧字段）
UPDATE HR.employees
  2  SET is_processed_new =
  3      CASE
  4          WHEN is_processed = 1 OR UPPER(is_processed) = 'Y' THEN TRUE
  5          WHEN is_processed = 0 OR UPPER(is_processed) = 'N' THEN FALSE
  6          ELSE NULL  -- 处理非法值或未定义状态
  7      END;
--
107 rows updated

-- 步骤3：验证迁移完整性
SELECT 
    COUNT(*) AS total_rows,
    COUNT(CASE WHEN is_processed_new IS NOT NULL THEN 1 END) AS migrated_count,
    COUNT(CASE WHEN is_processed_new IS NULL THEN 1 END) AS null_count
FROM HR.employees;
/* 
TOTAL_ROWS MIGRATED_COUNT NULL_COUNT
---------- -------------- ----------
       107            107          0
*/

-- 步骤4：清理非法值（可选）
UPDATE HR.employees
SET is_processed_new = FALSE  -- 将 NULL 设为默认值
WHERE is_processed_new IS NULL;
--0 rows updated.
-- 步骤5：添加非空约束 & 设置默认值
ALTER TABLE HR.employees 
  MODIFY is_processed_new BOOLEAN NOT NULL 
  ADD CONSTRAINT chk_valid_bool CHECK (is_processed_new IN (TRUE, FALSE));  -- 确保仅合法布尔值
--Table altered.
-- 步骤6：删除旧列 & 重命名新列
ALTER TABLE HR.employees DROP COLUMN is_processed;
ALTER TABLE HR.employees RENAME COLUMN is_processed_new TO is_processed;
--Table altered.
-- 步骤7：验证最终结构
SYS@FREE> DESC HR.employees;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMPLOYEE_ID                                                                NUMBER(6)
 FIRST_NAME                                                                 VARCHAR2(20)
 LAST_NAME                                                         NOT NULL VARCHAR2(25)
 EMAIL                                                             NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                                               VARCHAR2(20)
 HIRE_DATE                                                         NOT NULL DATE
 JOB_ID                                                            NOT NULL VARCHAR2(10)
 SALARY                                                                     NUMBER(8,2)
 COMMISSION_PCT                                                             NUMBER(2,2)
 MANAGER_ID                                                                 NUMBER(6)
 DEPARTMENT_ID                                                              NUMBER(4)
 BONUS                                                                      NUMBER(8,2)
 GENDER                                                                     NUMBER(38)
 IS_PROCESSED                                                      NOT NULL BOOLEAN

三、23ai 的开发者角色、boolean类型

新特性收益

开发者角色的权限效率提升：

单角色替代 10+ 手动授权操作，配置时间减少 70%
内置权限审计能力（通过 DBA_SYS_PRIVS 可追溯）

代码简洁性提升：

布尔类型减少 30% 的类型转换代码
逻辑判断直读性增强（WHERE active vs WHERE active = 'Y'）

注意：DB_DEVELOPER_ROLE 为预定义角色，不可修改，支持 CDB/PDB 环境

适应新特性修改步骤

权限迁移：

REVOKE CONNECT, RESOURCE FROM 老用户_user;  
GRANT DB_DEVELOPER_ROLE TO 老用户_user;

布尔列升级：优先在新表中使用 BOOLEAN，旧表分批迁移。
生产验证：

-- 检查布尔列约束
HR@localhost:1521/FREEPDB1>
SELECT column_name, data_type 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'EMPLOYEES';  -- 确认 data_type = 'BOOLEAN'
--
SELECT column_name, data_type
  2  FROM USER_TAB_COLUMNS
  3  WHERE TABLE_NAME = 'EMPLOYEES';

COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
DATA_TYPE
------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID
NUMBER

FIRST_NAME
VARCHAR2

LAST_NAME
VARCHAR2
………………………………
GENDER
NUMBER

IS_PROCESSED
BOOLEAN
14 rows selected.