用户管理与权限分配
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com
正文
一：前言
一：用户与模式的关系
1 SCOTT@orcl> conn system/oracle as sysdba; 2 Connected. 3 SYS@orcl>
二：创建与管理用户
1：身份验证
Oracle 为用户提供了3种身份验证方法
1： 密码验证
2 ：外部验证
3：全局验证
2：创建用户
1：创建一个 mr 用户，密码为 mrsoft 并设置默认表空间为 users 临时表空间为 temp 的用户
1 SYS@orcl> conn sys/oracle as sysdba; 2 Connected. 3 SYS@orcl> create user mr identified by mrsoft default tablespace users temporary tablespace temp; 4 5 User created. 6 7 SYS@orcl>2：创建用户，并配置其在指定表空间上的磁盘限额。
有时为聊避免用户在创建表和索引对象时占用过多的空间，可以配置用户在指定的表空间是昂的磁盘限额。在创建用户时，可以通过 quota xxxxM on tablespace _specify 字句配置指定表空间的最大可用限额。
如：创建一个 east 用户，密码为：mrsoft 默认表空间为 users 临时表空间为 temp 的用户并在指定该 用户上的 tbsp_1表空间上最多可以使用大小为10m
1 SYS@orcl> create tablespace tbsp_1 datafile '/u01/app/oracle/oradata/orcl/tbsp_1.dbf' size 20m autoextend on next 10m ; 2 3 Tablespace created. 4 5 SYS@orcl> 6 SYS@orcl> create user east identified by mrsoft default tablespace users temporary tablespace temp quota 10m on tbsp_1; 7 8 User created. 9 10 SYS@orcl>
3：创建用户，并配置其在指定表空间上不受限制。
如果要设置用户在指定表空间上不受限制，可以使用 quota unlimited on tablespace_specify 子句。
如：创建一个用户名为 df,密码为; mrsoft 临时表空间为 temp 默认表空间为 tbsp_1 并且该用户使用 tbsp_1表空间不受限制。
1 SYS@orcl> create user df identified by mrsoft default tablespace tbsp_1 temporary tablespace temp quota unlimited on tbsp_1; 2 3 User created. 4 5 SYS@orcl>
4：注意要点
2：修改用户
1：修改用户的磁盘限额
2：修改用户的命令(密码)
3：解锁被锁住的用户
1 SYS@orcl> select username,account_status from dba_users; 2 3 USERNAME ACCOUNT_STATUS 4 ------------------------------ -------------------------------- 5 DF OPEN 6 MR OPEN 7 EAST OPEN 8 SCOTT OPEN 9 ORACLE_OCM EXPIRED & LOCKED 10 XS$NULL EXPIRED & LOCKED 11 BI EXPIRED & LOCKED 12 PM EXPIRED & LOCKED 13 MDDATA EXPIRED & LOCKED 14 IX EXPIRED & LOCKED 15 SH EXPIRED & LOCKED 16 17 USERNAME ACCOUNT_STATUS 18 ------------------------------ -------------------------------- 19 DIP EXPIRED & LOCKED 20 OE EXPIRED & LOCKED 21 APEX_PUBLIC_USER EXPIRED & LOCKED 22 SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED 23 SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED 24 DBSNMP OPEN 25 SYSMAN OPEN 26 FLOWS_FILES EXPIRED & LOCKED 27 MDSYS EXPIRED & LOCKED 28 ORDSYS EXPIRED & LOCKED 29 EXFSYS EXPIRED & LOCKED 30 31 USERNAME ACCOUNT_STATUS 32 ------------------------------ -------------------------------- 33 WMSYS EXPIRED & LOCKED 34 APPQOSSYS EXPIRED & LOCKED 35 APEX_030200 EXPIRED & LOCKED 36 OWBSYS_AUDIT EXPIRED & LOCKED 37 ORDDATA EXPIRED & LOCKED 38 CTXSYS EXPIRED & LOCKED 39 ANONYMOUS EXPIRED & LOCKED 40 XDB EXPIRED & LOCKED 41 ORDPLUGINS EXPIRED & LOCKED 42 OWBSYS EXPIRED & LOCKED 43 SI_INFORMTN_SCHEMA EXPIRED & LOCKED 44 45 USERNAME ACCOUNT_STATUS 46 ------------------------------ -------------------------------- 47 OLAPSYS EXPIRED & LOCKED 48 MGMT_VIEW OPEN 49 SYS OPEN 50 SYSTEM OPEN 51 OUTLN EXPIRED & LOCKED 52 HR OPEN 53 54 39 rows selected. 55 56 SYS@orcl> alter user SH account unlock; 57 58 User altered. 59 60 SYS@orcl> select username,account_status from dba_users; 61 62 USERNAME ACCOUNT_STATUS 63 ------------------------------ -------------------------------- 64 DF OPEN 65 MR OPEN 66 EAST OPEN 67 SCOTT OPEN 68 SH EXPIRED 69 ORACLE_OCM EXPIRED & LOCKED 70 XS$NULL EXPIRED & LOCKED 71 BI EXPIRED & LOCKED 72 PM EXPIRED & LOCKED 73 MDDATA EXPIRED & LOCKED 74 IX EXPIRED & LOCKED 75 76 USERNAME ACCOUNT_STATUS 77 ------------------------------ -------------------------------- 78 DIP EXPIRED & LOCKED 79 OE EXPIRED & LOCKED 80 APEX_PUBLIC_USER EXPIRED & LOCKED 81 SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED 82 SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED 83 DBSNMP OPEN 84 SYSMAN OPEN 85 FLOWS_FILES EXPIRED & LOCKED 86 MDSYS EXPIRED & LOCKED 87 ORDSYS EXPIRED & LOCKED 88 EXFSYS EXPIRED & LOCKED 89 90 USERNAME ACCOUNT_STATUS 91 ------------------------------ -------------------------------- 92 WMSYS EXPIRED & LOCKED 93 APPQOSSYS EXPIRED & LOCKED 94 APEX_030200 EXPIRED & LOCKED 95 OWBSYS_AUDIT EXPIRED & LOCKED 96 ORDDATA EXPIRED & LOCKED 97 CTXSYS EXPIRED & LOCKED 98 ANONYMOUS EXPIRED & LOCKED 99 XDB EXPIRED & LOCKED 100 ORDPLUGINS EXPIRED & LOCKED 101 OWBSYS EXPIRED & LOCKED 102 SI_INFORMTN_SCHEMA EXPIRED & LOCKED 103 104 USERNAME ACCOUNT_STATUS 105 ------------------------------ -------------------------------- 106 OLAPSYS EXPIRED & LOCKED 107 MGMT_VIEW OPEN 108 SYS OPEN 109 SYSTEM OPEN 110 OUTLN EXPIRED & LOCKED 111 HR OPEN 112 113 39 rows selected. 114 115 SYS@orcl>
4：删除用户
三：用户权限管理
1：权限简介
2：授权操作
1 SYS@orcl> conn system/oracle 2 Connected. 3 SYSTEM@orcl> grant connect,resource to east; 4 5 Grant succeeded. 6 7 SYSTEM@orcl> conn east/123455; 8 Connected. 9 EAST@orcl>
1 SYS@orcl> conn sys/oracle as sysdba; 2 Connected. 3 SYS@orcl> create user dongfang identified by mrsoft default tablespace users quota 10m on users; 4 5 User created. 6 7 SYS@orcl> create user xifang identified by mrsoft default tablespace users quota 10m on users; 8 9 User created. 10 11 SYS@orcl> grant create session ,create table to dongfang with admin option; 12 13 Grant succeeded. 14 15 SYS@orcl> conn dongfang/mrsoft; 16 Connected. 17 DONGFANG@orcl> grant create session ,create table to xifang; 18 19 Grant succeeded. 20 21 DONGFANG@orcl> conn xifang/mrsoft; 22 Connected. 23 XIFANG@orcl> create table tb_xifang(id number,nam varchar(20)); 24 25 Table created. 26 27 XIFANG@orcl>--------oracle 11g 200种权限
1 XIFANG@orcl> desc system_privilege_map 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 PRIVILEGE NOT NULL NUMBER 5 NAME NOT NULL VARCHAR2(40) 6 PROPERTY NOT NULL NUMBER 7 8 XIFANG@orcl>1 XIFANG@orcl> select * from system_privilege_map; 2 3 PRIVILEGE NAME PROPERTY 4 ---------- ---------------------------------------- ---------- 5 -3 ALTER SYSTEM 0 6 -4 AUDIT SYSTEM 0 7 -5 CREATE SESSION 0 8 -6 ALTER SESSION 0 9 -7 RESTRICTED SESSION 0 10 -10 CREATE TABLESPACE 0 11 -11 ALTER TABLESPACE 0 12 -12 MANAGE TABLESPACE 0 13 -13 DROP TABLESPACE 0 14 -15 UNLIMITED TABLESPACE 0 15 -20 CREATE USER 0 16 17 PRIVILEGE NAME PROPERTY 18 ---------- ---------------------------------------- ---------- 19 -21 BECOME USER 0 20 -22 ALTER USER 0 21 -23 DROP USER 0 22 -30 CREATE ROLLBACK SEGMENT 0 23 -31 ALTER ROLLBACK SEGMENT 0 24 -32 DROP ROLLBACK SEGMENT 0 25 -40 CREATE TABLE 0 26 -41 CREATE ANY TABLE 0 27 -42 ALTER ANY TABLE 0 28 -43 BACKUP ANY TABLE 0 29 -44 DROP ANY TABLE 0 30 31 PRIVILEGE NAME PROPERTY 32 ---------- ---------------------------------------- ---------- 33 -45 LOCK ANY TABLE 0 34 -46 COMMENT ANY TABLE 0 35 -47 SELECT ANY TABLE 0 36 -48 INSERT ANY TABLE 0 37 -49 UPDATE ANY TABLE 0 38 -50 DELETE ANY TABLE 0 39 -60 CREATE CLUSTER 0 40 -61 CREATE ANY CLUSTER 0 41 -62 ALTER ANY CLUSTER 0 42 -63 DROP ANY CLUSTER 0 43 -71 CREATE ANY INDEX 0 44 45 PRIVILEGE NAME PROPERTY 46 ---------- ---------------------------------------- ---------- 47 -72 ALTER ANY INDEX 0 48 -73 DROP ANY INDEX 0 49 -80 CREATE SYNONYM 0 50 -81 CREATE ANY SYNONYM 0 51 -82 DROP ANY SYNONYM 0 52 -83 SYSDBA 0 53 -84 SYSOPER 0 54 -85 CREATE PUBLIC SYNONYM 0 55 -86 DROP PUBLIC SYNONYM 0 56 -90 CREATE VIEW 0 57 -91 CREATE ANY VIEW 0 58 59 PRIVILEGE NAME PROPERTY 60 ---------- ---------------------------------------- ---------- 61 -92 DROP ANY VIEW 0 62 -105 CREATE SEQUENCE 0 63 -106 CREATE ANY SEQUENCE 0 64 -107 ALTER ANY SEQUENCE 0 65 -108 DROP ANY SEQUENCE 0 66 -109 SELECT ANY SEQUENCE 0 67 -115 CREATE DATABASE LINK 0 68 -120 CREATE PUBLIC DATABASE LINK 0 69 -121 DROP PUBLIC DATABASE LINK 0 70 -125 CREATE ROLE 0 71 -126 DROP ANY ROLE 0 72 73 PRIVILEGE NAME PROPERTY 74 ---------- ---------------------------------------- ---------- 75 -127 GRANT ANY ROLE 0 76 -128 ALTER ANY ROLE 0 77 -130 AUDIT ANY 0 78 -135 ALTER DATABASE 0 79 -138 FORCE TRANSACTION 0 80 -139 FORCE ANY TRANSACTION 0 81 -140 CREATE PROCEDURE 0 82 -141 CREATE ANY PROCEDURE 0 83 -142 ALTER ANY PROCEDURE 0 84 -143 DROP ANY PROCEDURE 0 85 -144 EXECUTE ANY PROCEDURE 0 86 87 PRIVILEGE NAME PROPERTY 88 ---------- ---------------------------------------- ---------- 89 -151 CREATE TRIGGER 0 90 -152 CREATE ANY TRIGGER 0 91 -153 ALTER ANY TRIGGER 0 92 -154 DROP ANY TRIGGER 0 93 -160 CREATE PROFILE 0 94 -161 ALTER PROFILE 0 95 -162 DROP PROFILE 0 96 -163 ALTER RESOURCE COST 0 97 -165 ANALYZE ANY 0 98 -167 GRANT ANY PRIVILEGE 0 99 -172 CREATE MATERIALIZED VIEW 0 100 101 PRIVILEGE NAME PROPERTY 102 ---------- ---------------------------------------- ---------- 103 -173 CREATE ANY MATERIALIZED VIEW 0 104 -174 ALTER ANY MATERIALIZED VIEW 0 105 -175 DROP ANY MATERIALIZED VIEW 0 106 -177 CREATE ANY DIRECTORY 0 107 -178 DROP ANY DIRECTORY 0 108 -180 CREATE TYPE 0 109 -181 CREATE ANY TYPE 0 110 -182 ALTER ANY TYPE 0 111 -183 DROP ANY TYPE 0 112 -184 EXECUTE ANY TYPE 0 113 -186 UNDER ANY TYPE 0 114 115 PRIVILEGE NAME PROPERTY 116 ---------- ---------------------------------------- ---------- 117 -188 CREATE LIBRARY 0 118 -189 CREATE ANY LIBRARY 0 119 -190 ALTER ANY LIBRARY 0 120 -191 DROP ANY LIBRARY 0 121 -192 EXECUTE ANY LIBRARY 0 122 -200 CREATE OPERATOR 0 123 -201 CREATE ANY OPERATOR 0 124 -202 ALTER ANY OPERATOR 0 125 -203 DROP ANY OPERATOR 0 126 -204 EXECUTE ANY OPERATOR 0 127 -205 CREATE INDEXTYPE 0 128 129 PRIVILEGE NAME PROPERTY 130 ---------- ---------------------------------------- ---------- 131 -206 CREATE ANY INDEXTYPE 0 132 -207 ALTER ANY INDEXTYPE 0 133 -208 DROP ANY INDEXTYPE 0 134 -209 UNDER ANY VIEW 0 135 -210 QUERY REWRITE 0 136 -211 GLOBAL QUERY REWRITE 0 137 -212 EXECUTE ANY INDEXTYPE 0 138 -213 UNDER ANY TABLE 0 139 -214 CREATE DIMENSION 0 140 -215 CREATE ANY DIMENSION 0 141 -216 ALTER ANY DIMENSION 0 142 143 PRIVILEGE NAME PROPERTY 144 ---------- ---------------------------------------- ---------- 145 -217 DROP ANY DIMENSION 0 146 -218 MANAGE ANY QUEUE 1 147 -219 ENQUEUE ANY QUEUE 1 148 -220 DEQUEUE ANY QUEUE 1 149 -222 CREATE ANY CONTEXT 0 150 -223 DROP ANY CONTEXT 0 151 -224 CREATE ANY OUTLINE 0 152 -225 ALTER ANY OUTLINE 0 153 -226 DROP ANY OUTLINE 0 154 -227 ADMINISTER RESOURCE MANAGER 1 155 -228 ADMINISTER DATABASE TRIGGER 0 156 157 PRIVILEGE NAME PROPERTY 158 ---------- ---------------------------------------- ---------- 159 -233 MERGE ANY VIEW 0 160 -234 ON COMMIT REFRESH 0 161 -235 EXEMPT ACCESS POLICY 0 162 -236 RESUMABLE 0 163 -237 SELECT ANY DICTIONARY 0 164 -238 DEBUG CONNECT SESSION 0 165 -241 DEBUG ANY PROCEDURE 0 166 -243 FLASHBACK ANY TABLE 0 167 -244 GRANT ANY OBJECT PRIVILEGE 0 168 -245 CREATE EVALUATION CONTEXT 1 169 -246 CREATE ANY EVALUATION CONTEXT 1 170 171 PRIVILEGE NAME PROPERTY 172 ---------- ---------------------------------------- ---------- 173 -247 ALTER ANY EVALUATION CONTEXT 1 174 -248 DROP ANY EVALUATION CONTEXT 1 175 -249 EXECUTE ANY EVALUATION CONTEXT 1 176 -250 CREATE RULE SET 1 177 -251 CREATE ANY RULE SET 1 178 -252 ALTER ANY RULE SET 1 179 -253 DROP ANY RULE SET 1 180 -254 EXECUTE ANY RULE SET 1 181 -255 EXPORT FULL DATABASE 0 182 -256 IMPORT FULL DATABASE 0 183 -257 CREATE RULE 1 184 185 PRIVILEGE NAME PROPERTY 186 ---------- ---------------------------------------- ---------- 187 -258 CREATE ANY RULE 1 188 -259 ALTER ANY RULE 1 189 -260 DROP ANY RULE 1 190 -261 EXECUTE ANY RULE 1 191 -262 ANALYZE ANY DICTIONARY 0 192 -263 ADVISOR 0 193 -264 CREATE JOB 0 194 -265 CREATE ANY JOB 0 195 -266 EXECUTE ANY PROGRAM 0 196 -267 EXECUTE ANY CLASS 0 197 -268 MANAGE SCHEDULER 0 198 199 PRIVILEGE NAME PROPERTY 200 ---------- ---------------------------------------- ---------- 201 -269 SELECT ANY TRANSACTION 0 202 -270 DROP ANY SQL PROFILE 0 203 -271 ALTER ANY SQL PROFILE 0 204 -272 ADMINISTER SQL TUNING SET 0 205 -273 ADMINISTER ANY SQL TUNING SET 0 206 -274 CREATE ANY SQL PROFILE 0 207 -275 EXEMPT IDENTITY POLICY 0 208 -276 MANAGE FILE GROUP 1 209 -277 MANAGE ANY FILE GROUP 1 210 -278 READ ANY FILE GROUP 1 211 -279 CHANGE NOTIFICATION 0 212 213 PRIVILEGE NAME PROPERTY 214 ---------- ---------------------------------------- ---------- 215 -280 CREATE EXTERNAL JOB 0 216 -281 CREATE ANY EDITION 0 217 -282 DROP ANY EDITION 0 218 -283 ALTER ANY EDITION 0 219 -284 CREATE ASSEMBLY 0 220 -285 CREATE ANY ASSEMBLY 0 221 -286 ALTER ANY ASSEMBLY 0 222 -287 DROP ANY ASSEMBLY 0 223 -288 EXECUTE ANY ASSEMBLY 0 224 -289 EXECUTE ASSEMBLY 0 225 -290 CREATE MINING MODEL 0 226 227 PRIVILEGE NAME PROPERTY 228 ---------- ---------------------------------------- ---------- 229 -291 CREATE ANY MINING MODEL 0 230 -292 DROP ANY MINING MODEL 0 231 -293 SELECT ANY MINING MODEL 0 232 -294 ALTER ANY MINING MODEL 0 233 -295 COMMENT ANY MINING MODEL 0 234 -301 CREATE CUBE DIMENSION 0 235 -302 ALTER ANY CUBE DIMENSION 0 236 -303 CREATE ANY CUBE DIMENSION 0 237 -304 DELETE ANY CUBE DIMENSION 0 238 -305 DROP ANY CUBE DIMENSION 0 239 -306 INSERT ANY CUBE DIMENSION 0 240 241 PRIVILEGE NAME PROPERTY 242 ---------- ---------------------------------------- ---------- 243 -307 SELECT ANY CUBE DIMENSION 0 244 -308 CREATE CUBE 0 245 -309 ALTER ANY CUBE 0 246 -310 CREATE ANY CUBE 0 247 -311 DROP ANY CUBE 0 248 -312 SELECT ANY CUBE 0 249 -313 UPDATE ANY CUBE 0 250 -314 CREATE MEASURE FOLDER 0 251 -315 CREATE ANY MEASURE FOLDER 0 252 -316 DELETE ANY MEASURE FOLDER 0 253 -317 DROP ANY MEASURE FOLDER 0 254 255 PRIVILEGE NAME PROPERTY 256 ---------- ---------------------------------------- ---------- 257 -318 INSERT ANY MEASURE FOLDER 0 258 -319 CREATE CUBE BUILD PROCESS 0 259 -320 CREATE ANY CUBE BUILD PROCESS 0 260 -321 DROP ANY CUBE BUILD PROCESS 0 261 -322 UPDATE ANY CUBE BUILD PROCESS 0 262 -326 UPDATE ANY CUBE DIMENSION 0 263 -327 ADMINISTER SQL MANAGEMENT OBJECT 0 264 -328 ALTER PUBLIC DATABASE LINK 0 265 -329 ALTER DATABASE LINK 0 266 -350 FLASHBACK ARCHIVE ADMINISTER 0 267 268 208 rows selected. 269
3：回收系统权限
1：撤销 east 用户的 resource 系统权限。
1 2 XIFANG@orcl> conn system/oracle 3 Connected. 4 5 SYSTEM@orcl> revoke resource from east; 6 Revoke succeeded. 7 SYSTEM@orcl>
2：撤销 拥有 with admin option 权限的用户某些权限
1 SYSTEM@orcl> revoke create table from dongfang ; 2 3 Revoke succeeded. 4 5 SYSTEM@orcl> conn xifang/mrsoft; 6 Connected. 7 XIFANG@orcl> create table tb_xifang2(id number,name varchar2(20)); 8 9 Table created. 10 11 XIFANG@orcl> conn dongfang/mrsoft; 12 Connected. 13 DONGFANG@orcl> create table tb_dongfang(id number,name varchar2(20)); 14 create table tb_dongfang(id number,name varchar2(20)) 15 * 16 ERROR at line 1: 17 ORA-01031: insufficient privileges 18 19 20 DONGFANG@orcl>
4：对象授权
1 DONGFANG@orcl> conn system/oracle 2 Connected. 3 SYSTEM@orcl> grant select,insert,delete,update on scott.emp to xifang; 4 5 Grant succeeded. 6 7 Warning: You are no longer connected to ORACLE. 8 @> conn xifang/mrsoft; 9 Connected. 10 XIFANG@orcl> select * from scott.emp; 11 12 EMPNO ENAME JOB MGR HIREDATE SAL COMM 13 ---------- ---------- --------- ---------- --------- ---------- ---------- 14 DEPTNO 15 ---------- 16 9527 EAST SAESMAN 17 18 19 8889 dfadf ger 07-JAN-18 20 10 21 22 7369 SMITH CLERK 7902 17-DEC-80 23 20 24
5：回收对象权限
1 2 XIFANG@orcl> conn system/oracle 3 Connected. 4 SYSTEM@orcl> revoke delete,update ,select on scott.emp from xifang; 5 6 Revoke succeeded. 7 8 SYSTEM@orcl> conn xifang/mrsoft; 9 Connected. 10 XIFANG@orcl> select * from scott.emp; 11 select * from scott.emp 12 * 13 ERROR at line 1: 14 ORA-01031: insufficient privileges 15 16 17 XIFANG@orcl>
6：查询用户与权限
四：角色管理
1：角色概述
2：预定义角色
1 XIFANG@orcl> conn system/oracle 2 Connected. 3 SYSTEM@orcl> set pagesize 500; 4 SYSTEM@orcl> select * from dba_roles; 5 6 ROLE PASSWORD AUTHENTICAT 7 ------------------------------ -------- ----------- 8 CONNECT NO NONE 9 RESOURCE NO NONE 10 DBA NO NONE 11 SELECT_CATALOG_ROLE NO NONE 12 EXECUTE_CATALOG_ROLE NO NONE 13 DELETE_CATALOG_ROLE NO NONE 14 EXP_FULL_DATABASE NO NONE 15 IMP_FULL_DATABASE NO NONE 16 LOGSTDBY_ADMINISTRATOR NO NONE 17 DBFS_ROLE NO NONE 18 AQ_ADMINISTRATOR_ROLE NO NONE 19 AQ_USER_ROLE NO NONE 20 DATAPUMP_EXP_FULL_DATABASE NO NONE 21 DATAPUMP_IMP_FULL_DATABASE NO NONE 22 ADM_PARALLEL_EXECUTE_TASK NO NONE 23 GATHER_SYSTEM_STATISTICS NO NONE 24 JAVA_DEPLOY NO NONE 25 RECOVERY_CATALOG_OWNER NO NONE 26 SCHEDULER_ADMIN NO NONE 27 HS_ADMIN_SELECT_ROLE NO NONE 28 HS_ADMIN_EXECUTE_ROLE NO NONE 29 HS_ADMIN_ROLE NO NONE 30 GLOBAL_AQ_USER_ROLE GLOBAL GLOBAL 31 OEM_ADVISOR NO NONE 32 OEM_MONITOR NO NONE 33 WM_ADMIN_ROLE NO NONE 34 JAVAUSERPRIV NO NONE 35 JAVAIDPRIV NO NONE 36 JAVASYSPRIV NO NONE 37 JAVADEBUGPRIV NO NONE 38 EJBCLIENT NO NONE 39 JMXSERVER NO NONE 40 JAVA_ADMIN NO NONE 41 CTXAPP NO NONE 42 XDBADMIN NO NONE 43 XDB_SET_INVOKER NO NONE 44 AUTHENTICATEDUSER NO NONE 45 XDB_WEBSERVICES NO NONE 46 XDB_WEBSERVICES_WITH_PUBLIC NO NONE 47 XDB_WEBSERVICES_OVER_HTTP NO NONE 48 OLAP_DBA NO NONE 49 ORDADMIN NO NONE 50 OLAP_XS_ADMIN NO NONE 51 CWM_USER NO NONE 52 OLAP_USER NO NONE 53 SPATIAL_WFS_ADMIN NO NONE 54 WFS_USR_ROLE NO NONE 55 SPATIAL_CSW_ADMIN NO NONE 56 CSW_USR_ROLE NO NONE 57 MGMT_USER NO NONE 58 APEX_ADMINISTRATOR_ROLE NO NONE 59 OWB$CLIENT YES PASSWORD 60 OWB_DESIGNCENTER_VIEW NO NONE 61 OWB_USER NO NONE 62 63 54 rows selected. 64 65 SYSTEM@orcl>
3：创建角色与授权
1：创建 一个名为 designer的角色，该角色的口令为123456
1 SYSTEM@orcl> conn system/oracle 2 Connected. 3 SYSTEM@orcl> create role designer identified by 123456; 4 5 Role created. 6 7 SYSTEM@orcl> 8
2：给 designer 角色授权 create view create table 权限。
3：把 designer 角色授权给用户 dongfang
1 SYSTEM@orcl> conn system/oracle 2 Connected. 3 SYSTEM@orcl> create role designer identified by 123456; 4 5 Role created. 6 7 SYSTEM@orcl> grant create view,create table to designer ; 8 9 Grant succeeded. 10 11 SYSTEM@orcl> grant designer to dongfang ; 12 13 Grant succeeded.1 DONGFANG@orcl> set role designer identified by 123456; 2 3 Role set. 4 5 DONGFANG@orcl> create table table_dongfang12(id number,name varchar2(20)); 6 7 Table created. 8 9 DONGFANG@orcl>
4：管理角色
1： 查看角色所包含的权限
1 SYSTEM@orcl> select * from role_sys_privs where role='DESIGNER'; 2 3 ROLE PRIVILEGE ADM 4 ------------------------------ ---------------------------------------- --- 5 DESIGNER CREATE TABLE NO 6 DESIGNER CREATE VIEW NO 7 8 SYSTEM@orcl>2：修改角色密码
1 SYSTEM@orcl> alter role designer not identified; 2 3 Role altered. 4 5 SYSTEM@orcl> alter role designer identified by mrsoft; 6 7 Role altered. 8 9 SYSTEM@orcl>3：设置当前用户要生效的角色
1 SYSTEM@orcl> create role queryer; 2 3 Role created. 4 5 SYSTEM@orcl> set role queryer; 6 7 Role set. 8 9 SYSTEM@orcl> set role designer identified by mrsoft; 10 11 Role set. 12 13 SYSTEM@orcl>
4：删除角色
1 SYSTEM@orcl> drop role queryer; 2 3 Role dropped. 4 5 SYSTEM@orcl>
5：角色与权限的查询
示例1：在sys用户的dba模式下 ：查询hr用户被授予权限
1 SYS@orcl> select granted_role,default_role from dba_role_privs where grantee='HR'; 2 3 GRANTED_ROLE DEF 4 ------------------------------ --- 5 RESOURCE YES 6 7 SYS@orcl>示例2：在某个模式下，如果用户要确定当前会话中的所有有效角色。可以通过session_role 数据字典来查询；
1 SYS@orcl> conn scott/scott 2 Connected. 3 SCOTT@orcl> select * from session_roles; 4 5 ROLE 6 ------------------------------ 7 CONNECT 8 RESOURCE 9 10 SCOTT@orcl>
五：资源配置 profile
1： profile 概述
2：使用 profile 管理密码
1：账户锁定
1 SCOTT@orcl> conn sys/oracle as sysdba; 2 Connected. 3 SYS@orcl> 4 SYS@orcl> create profile lock_account limit failed_login_attempts 5 password_lock_time 7; 5 6 Profile created. 7 8 SYS@orcl> alter user dongfang profile lock_account; 9 10 User altered. 11 12 SYS@orcl>
2： 密码的过期时间
1 2 SYS@orcl> create profile password_lift_time limit password_life_time 30 password_grace_time 3; 3 4 Profile created. 5 6 SYS@orcl> alter user dongfang profile password_lift_time; 7 8 User altered. 9 10 SYS@orcl>
3： 密码历史
4：密码的复杂度
3：使用 profile 管理资源
1 SYS@orcl> show parameter resource_limit; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 resource_limit boolean FALSE 6 SYS@orcl> alter system set resource_limit=true; 7 8 System altered. 9 10 SYS@orcl> show parameter resource_limit; 11 12 NAME TYPE VALUE 13 ------------------------------------ ----------- ------------------------------ 14 resource_limit boolean TRUE 15 SYS@orcl>
4：维护 profile 文件
1：修改profile 文件
1 SYS@orcl> alter profile password_lift_time limit cpu_per_session 20000 sessions_per_user 10 cpu_per_call 500 password_life_time 180 failed_login_attempts 10; 2 3 Profile altered. 4 5 SYS@orcl>
2：删除 profile 文件
5：显示 profiel信息
1：显示用户的资源配置信息
1 SYS@orcl> select profile ,username from dba_users ; 2 3 PROFILE USERNAME 4 ------------------------------ ------------------------------ 5 DEFAULT EAST 6 DEFAULT MR 7 DEFAULT SCOTT 8 DEFAULT SH 9 DEFAULT SPATIAL_WFS_ADMIN_USR 10 DEFAULT SPATIAL_CSW_ADMIN_USR 11 DEFAULT APEX_PUBLIC_USER 12 DEFAULT OE 13 DEFAULT DIP 14 DEFAULT IX 15 DEFAULT MDDATA 16 17 PROFILE USERNAME 18 ------------------------------ ------------------------------ 19 DEFAULT PM 20 DEFAULT BI 21 DEFAULT XS$NULL 22 DEFAULT ORACLE_OCM 23 DEFAULT DONGFANG 24 DEFAULT XIFANG 25 MONITORING_PROFILE DBSNMP 26 DEFAULT SYSMAN 27 DEFAULT OLAPSYS 28 DEFAULT SI_INFORMTN_SCHEMA 29 DEFAULT OWBSYS 30 31 PROFILE USERNAME 32 ------------------------------ ------------------------------ 33 DEFAULT ORDPLUGINS 34 DEFAULT XDB 35 DEFAULT ANONYMOUS 36 DEFAULT CTXSYS 37 DEFAULT ORDDATA 38 DEFAULT OWBSYS_AUDIT 39 DEFAULT APEX_030200 40 DEFAULT APPQOSSYS 41 DEFAULT WMSYS 42 DEFAULT EXFSYS 43 DEFAULT ORDSYS 44 45 PROFILE USERNAME 46 ------------------------------ ------------------------------ 47 DEFAULT MDSYS 48 DEFAULT FLOWS_FILES 49 DEFAULT SYSTEM 50 DEFAULT SYS 51 DEFAULT MGMT_VIEW 52 DEFAULT OUTLN 53 DEFAULT HR 54 55 40 rows selected. 56 57 SYS@orcl>
1 2 SYS@orcl> select profile from dba_users where username='DONGFANG'; 3 4 PROFILE 5 ------------------------------ 6 DEFAULT
2：显示指定 profile 文件的资源配置信息
1 SYS@orcl> select resource_name,resource_type ,limit from dba_profiles order by profile, limit; 2 3 RESOURCE_NAME RESOURCE LIMIT 4 -------------------------------- -------- -------------------- 5 PASSWORD_LOCK_TIME PASSWORD 1 6 FAILED_LOGIN_ATTEMPTS PASSWORD 10 7 PASSWORD_LIFE_TIME PASSWORD 180 8 PASSWORD_GRACE_TIME PASSWORD 7 9 PASSWORD_VERIFY_FUNCTION PASSWORD NULL 10 PRIVATE_SGA KERNEL UNLIMITED 11 PASSWORD_REUSE_TIME PASSWORD UNLIMITED 12 PASSWORD_REUSE_MAX PASSWORD UNLIMITED 13 CONNECT_TIME KERNEL UNLIMITED 14 LOGICAL_READS_PER_CALL KERNEL UNLIMITED 15 CPU_PER_SESSION KERNEL UNLIMITED 16 17 RESOURCE_NAME RESOURCE LIMIT 18 -------------------------------- -------- -------------------- 19 IDLE_TIME KERNEL UNLIMITED 20 SESSIONS_PER_USER KERNEL UNLIMITED 21 COMPOSITE_LIMIT KERNEL UNLIMITED 22 CPU_PER_CALL KERNEL UNLIMITED 23 LOGICAL_READS_PER_SESSION KERNEL UNLIMITED 24 FAILED_LOGIN_ATTEMPTS PASSWORD 5 25 PASSWORD_LOCK_TIME PASSWORD 7 26 PASSWORD_LIFE_TIME PASSWORD DEFAULT 27 PASSWORD_GRACE_TIME PASSWORD DEFAULT 28 PRIVATE_SGA KERNEL DEFAULT 29 CPU_PER_SESSION KERNEL DEFAULT 30 31 RESOURCE_NAME RESOURCE LIMIT 32 -------------------------------- -------- -------------------- 33 CONNECT_TIME KERNEL DEFAULT 34 IDLE_TIME KERNEL DEFAULT 35 COMPOSITE_LIMIT KERNEL DEFAULT 36 LOGICAL_READS_PER_CALL KERNEL DEFAULT 37 SESSIONS_PER_USER KERNEL DEFAULT 38 PASSWORD_REUSE_TIME PASSWORD DEFAULT 39 CPU_PER_CALL KERNEL DEFAULT 40 PASSWORD_REUSE_MAX PASSWORD DEFAULT 41 PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT 42 LOGICAL_READS_PER_SESSION KERNEL DEFAULT 43 PASSWORD_LOCK_TIME PASSWORD DEFAULT 44 45 RESOURCE_NAME RESOURCE LIMIT 46 -------------------------------- -------- -------------------- 47 PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT 48 PASSWORD_REUSE_MAX PASSWORD DEFAULT 49 PASSWORD_REUSE_TIME PASSWORD DEFAULT 50 PASSWORD_LIFE_TIME PASSWORD DEFAULT 51 PRIVATE_SGA KERNEL DEFAULT 52 CONNECT_TIME KERNEL DEFAULT 53 IDLE_TIME KERNEL DEFAULT 54 LOGICAL_READS_PER_CALL KERNEL DEFAULT 55 LOGICAL_READS_PER_SESSION KERNEL DEFAULT 56 CPU_PER_CALL KERNEL DEFAULT 57 CPU_PER_SESSION KERNEL DEFAULT 58 59 RESOURCE_NAME RESOURCE LIMIT 60 -------------------------------- -------- -------------------- 61 SESSIONS_PER_USER KERNEL DEFAULT 62 COMPOSITE_LIMIT KERNEL DEFAULT 63 PASSWORD_GRACE_TIME PASSWORD DEFAULT 64 FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED 65 66 48 rows selected.
——————————————————————————————————————————————————————————————————————
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？