【Oracle数据库指南】第37篇：Oracle角色与PROFILE管理详解

上一篇【第36篇】Oracle用户与权限管理详解完整版下一篇【第38篇】Oracle数据库备份策略与实现详解摘要角色Role是权限的命名集合通过角色可以将多个权限统一管理简化复杂的权限授予操作PROFILE配置文件是Oracle对用户会话资源和密码安全策略的管控工具。本文详细讲解Oracle角色的创建与管理、系统预定义角色的用途与风险、基于角色的权限体系设计以及PROFILE的口令策略密码复杂度、有效期、锁定策略和资源限制CPU、会话时间、I/O次数的配置与最佳实践。一、角色Role管理1.1 角色概述不使用角色难以维护 用户A ← 直接授予10个权限 用户B ← 直接授予10个权限 用户C ← 直接授予10个权限 50个用户 × 10个权限 500次 GRANT 操作 使用角色易于维护 角色 developer_role ← 包含10个权限 ↓ 授予 用户A、用户B、用户C ... 10次 GRANT 权限到角色 N次 GRANT 角色到用户1.2 Oracle预定义角色角色名权限说明使用建议CONNECT原有14个系统权限10g只剩CREATE SESSION只授予需要连接的用户RESOURCE创建表、索引、序列等对象的权限开发用户但包含UNLIMITED TABLESPACE要谨慎DBA几乎所有系统权限仅限真正的DBA账户EXP_FULL_DATABASE数据泵/导出权限备份账户IMP_FULL_DATABASE数据泵/导入权限恢复账户SELECT_CATALOG_ROLE查询所有数据字典视图只读监控账户EXECUTE_CATALOG_ROLE执行数据字典中的包监控/调优账户SCHEDULER_ADMIN管理 DBMS_SCHEDULER 作业作业调度账户-- 查看角色包含的系统权限SELECTprivilege,admin_optionFROMrole_sys_privsWHEREroleCONNECT;-- 查看角色包含的对象权限SELECTowner,table_name,privilegeFROMrole_tab_privsWHEREroleSELECT_CATALOG_ROLEFETCHFIRST10ROWSONLY;1.3 创建自定义角色-- 创建不加密码的角色普通CREATEROLE developer_role;CREATEROLE app_reader_role;CREATEROLE app_writer_role;-- 创建带密码的角色启用时需要提供密码CREATEROLE secure_role IDENTIFIEDBYRoleSecure2024;-- 创建外部验证角色通过OS认证启用CREATEROLE os_role IDENTIFIED EXTERNALLY;1.4 为角色授予权限-- 为开发者角色授予系统权限GRANTCREATESESSION,CREATETABLE,CREATEVIEW,CREATEINDEX,CREATESEQUENCE,CREATEPROCEDURE,CREATETRIGGER,CREATETYPE,CREATESYNONYMTOdeveloper_role;-- 为只读角色授予对象权限GRANTSELECTONscott.empTOapp_reader_role;GRANTSELECTONscott.deptTOapp_reader_role;GRANTSELECTONscott.ordersTOapp_reader_role;-- 为写入角色授予DML权限GRANTSELECT,INSERT,UPDATE,DELETEONscott.ordersTOapp_writer_role;GRANTSELECT,INSERTONscott.order_itemsTOapp_writer_role;-- 将一个角色授予另一个角色角色嵌套GRANTapp_reader_roleTOapp_writer_role;-- 写入角色自动继承读取权限1.5 将角色授予用户-- 将角色授予用户GRANTdeveloper_roleTOdev_user1,dev_user2;GRANTapp_reader_roleTOreport_user;GRANTapp_writer_role,secure_roleTOpower_user;-- 设置用户的默认角色登录时自动激活ALTERUSERdev_user1DEFAULTROLE developer_role;ALTERUSERpower_userDEFAULTROLE app_writer_role;-- 只激活写入角色不激活secure_role1.6 会话中激活/禁用角色-- 激活带密码的角色在会话中SETROLE secure_role IDENTIFIEDBYRoleSecure2024;-- 激活所有默认角色SETROLEALL;-- 禁用所有角色只保留 PUBLIC 权限SETROLE NONE;-- 激活部分角色SETROLE developer_role,app_reader_role;1.7 删除角色-- 删除角色自动回收所有用户的该角色权限DROPROLE developer_role;-- 查看角色被哪些用户拥有SELECTgrantee,admin_option,default_roleFROMdba_role_privsWHEREgranted_roleDEVELOPER_ROLEORDERBYgrantee;二、PROFILE配置文件管理2.1 PROFILE 概述PROFILE 通过对用户的会话资源和密码策略进行配置实现密码策略防止弱密码、强制定期更改资源限制限制单个用户的CPU、内存、连接时间等-- 查看当前的 DEFAULT Profile 内容SELECTresource_name,limitFROMdba_profilesWHEREprofileDEFAULTORDERBYresource_name;2.2 创建PROFILE——密码策略CREATEPROFILE app_password_profileLIMIT-- 连续登录失败几次后锁定账户防暴力破解FAILED_LOGIN_ATTEMPTS5-- 账户锁定时长天1/24 1小时PASSWORD_LOCK_TIME1/24-- 密码有效期天PASSWORD_LIFE_TIME90-- 密码宽限期过期后的缓冲天数期间可登录但会提示PASSWORD_GRACE_TIME7-- 密码重用等待天数365天内不能用相同密码PASSWORD_REUSE_TIME365-- 密码重用次数限制最近10次不能重用PASSWORD_REUSE_MAX10-- 密码复杂度验证函数Oracle提供ora12c_strong_verify_functionPASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION;Oracle内置密码验证函数VERIFY_FUNCTION11g 默认复杂度8位以上含数字和字母ORA12C_STRONG_VERIFY_FUNCTION12c强密码12位以上可自定义创建PL/SQL函数实现自定义密码规则2.3 创建PROFILE——资源限制CREATEPROFILE app_resource_profileLIMIT-- 允许的并发会话数同一用户可同时开几个连接SESSIONS_PER_USER10-- 单次调用一个SQL语句允许使用的CPU时间1/100秒CPU_PER_CALL6000-- 60秒-- 单个会话允许使用的总CPU时间1/100秒CPU_PER_SESSION UNLIMITED-- 不限制-- 单个会话允许的最大连接时间分钟CONNECT_TIME480-- 8小时-- 允许的最大空闲时间分钟IDLE_TIME30-- 30分钟不活动则断开-- 单次调用允许的逻辑读次数LOGICAL_READS_PER_CALL UNLIMITED-- 单个会话允许的逻辑读总次数LOGICAL_READS_PER_SESSION UNLIMITED-- 私有SGA的限制字节PRIVATE_SGA UNLIMITED;⚠️注意资源限制生效的前提是-- 检查资源限制是否全局开启SHOWPARAMETER resource_limit;-- 开启资源限制默认关闭ALTERSYSTEMSETRESOURCE_LIMITTRUE;2.4 将 PROFILE 分配给用户-- 创建用户时指定CREATEUSERnew_user IDENTIFIEDBYPass1234PROFILE app_password_profile;-- 修改现有用户的 PROFILEALTERUSERapp_user PROFILE app_password_profile;-- 恢复为默认 PROFILEALTERUSERapp_user PROFILEDEFAULT;2.5 修改和删除 PROFILE-- 修改 PROFILE只修改需要更改的参数ALTERPROFILE app_password_profileLIMITFAILED_LOGIN_ATTEMPTS3PASSWORD_LIFE_TIME60;-- 删除 PROFILEDROPPROFILE app_password_profile;-- 删除 PROFILE 并将使用该 PROFILE 的用户改回 DEFAULTDROPPROFILE app_password_profileCASCADE;三、PROFILE 监控与维护3.1 查看用户 PROFILE 配置-- 查看每个用户使用的 PROFILESELECTusername,profile,account_status,expiry_dateFROMdba_usersWHEREaccount_statusOPENORDERBYusername;-- 查看特定 PROFILE 的设置SELECTresource_name,resource_type,limitFROMdba_profilesWHEREprofileAPP_PASSWORD_PROFILEORDERBYresource_type,resource_name;3.2 密码验证函数示例-- 自定义密码验证函数示例CREATEORREPLACEFUNCTIONcheck_password(username VARCHAR2,password VARCHAR2,old_password VARCHAR2)RETURNBOOLEANASBEGIN-- 规则1至少12位IFLENGTH(password)12THENRAISE_APPLICATION_ERROR(-20001,密码长度不能少于12位);ENDIF;-- 规则2必须包含数字IFREGEXP_INSTR(password,[0-9])0THENRAISE_APPLICATION_ERROR(-20002,密码必须包含数字);ENDIF;-- 规则3必须包含大写字母IFREGEXP_INSTR(password,[A-Z])0THENRAISE_APPLICATION_ERROR(-20003,密码必须包含大写字母);ENDIF;-- 规则4必须包含特殊字符IFREGEXP_INSTR(password,[^a-zA-Z0-9])0THENRAISE_APPLICATION_ERROR(-20004,密码必须包含特殊字符);ENDIF;-- 规则5不能包含用户名IFINSTR(UPPER(password),UPPER(username))0THENRAISE_APPLICATION_ERROR(-20005,密码不能包含用户名);ENDIF;RETURNTRUE;END;/-- 在 PROFILE 中使用自定义函数ALTERPROFILE app_password_profileLIMITPASSWORD_VERIFY_FUNCTION check_password;四、综合设计示例4.1 企业级角色与PROFILE体系-- 创建PROFILECREATEPROFILE strict_profileLIMITFAILED_LOGIN_ATTEMPTS5PASSWORD_LOCK_TIME1/24PASSWORD_LIFE_TIME60PASSWORD_GRACE_TIME7PASSWORD_REUSE_TIME365PASSWORD_REUSE_MAX10PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION IDLE_TIME60CONNECT_TIME480SESSIONS_PER_USER5;-- 创建分层角色CREATEROLE read_role;-- 只读CREATEROLE write_role;-- 读写CREATEROLE admin_role;-- 管理GRANTSELECTONscott.emp,scott.deptTOread_role;GRANTread_roleTOwrite_role;GRANTINSERT,UPDATE,DELETEONscott.empTOwrite_role;GRANTwrite_roleTOadmin_role;GRANTCREATETABLE,CREATEINDEXTOadmin_role;-- 创建用户并绑定角色和PROFILECREATEUSERjohn IDENTIFIEDBYJohnSecure2024DEFAULTTABLESPACEusers_dataTEMPORARYTABLESPACEtempQUOTA100MONusers_data PROFILE strict_profile PASSWORD EXPIRE;GRANTCREATESESSIONTOjohn;GRANTwrite_roleTOjohn;五、总结Oracle角色与PROFILE管理的核心要点角色作用权限集合简化授权管理支持嵌套和激活/禁用预定义角色CONNECT/RESOURCE 日常使用DBA 只给DBA账户自定义角色按职责创建只读、读写、管理通过角色授权PROFILE密码策略失败锁定、有效期、重用限制、复杂度函数PROFILE资源限制需开启 RESOURCE_LIMITTRUE 才生效最佳实践通过角色管理权限通过PROFILE强化密码安全上一篇【第36篇】Oracle用户与权限管理详解完整版下一篇【第38篇】Oracle数据库备份策略与实现详解参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档Database Security Guide - Configuring Privilege and Role AuthorizationOracle官方文档Database SQL Reference - CREATE PROFILE