Oracle数据库用户管理介绍

1、Oracle数据库用户管理介绍Oracle用户管理01Oracle用户管理Account lockingTablespace quotasTemporary tablespaceDefault tablespaceRole privilegesResource limitsSecurity domainDirect privilegesAuthentication mechanism需要了解:创建数据库用户更改，删除已存在数据库用户监控用户信息02Oracle用户管理Schema Objects TablesTriggers Constraints Indexes Views Sequence

2、sStored program units SynonymsUser-defined data types Database links数据库用户A schema is a named collection of objectsA user is created, and a corresponding schema is createdUser can be associated only with one schemaUsername and schema are often used interchangeably03Oracle用户管理创建用户的一基本参数表空间配额默认表空间用户密码权

3、限，角色04Oracle用户管理密码认证创建初始密码：CREATE USER aaron IDENTIFIED BY soccer DEFAULT TABLESPACE dataTEMPORARY TABLESPACE tempQUOTA 15m ON data PASSWORD EXPIRE;05Oracle用户管理ATE USER ops$oracleNTIFIED EXTERNALLY AULT TABLESPACE USERS PORARY TABLESPACE tempTA 15m ON data操作系统认证OS_AUTHENT_PREFIX initialization param

4、eter specifies the format of the usernamesDefaults to OPS$CRE IDE DEF TEM QUOPASSWORD EXPIRE;06Oracle用户管理更改配额ALTER USER abc QUOTA 1m ON USERS;07Oracle用户管理删除用户Use the CASCADE clause to drop all objects in the schema if the schema contains objects.指定CASCADEDROP USER abc;08Oracle用户管理Users currently con

5、nected to the Oracle server cannot be dropped删除用户断开abc用户所有连接DROP USER abc CASCADE;09Oracle用户管理获取用户信息相关数据字典DBA_USERSDBA_TS_QUOTAS10Oracle用户管理权限管理需要了解获取系统对象权限赋予、回收权限获取审计属性两种用户权限:System: 数据库级别权限Object: 对象级别权限11Oracle用户管理系统级权限CategoryExamplesINDEXCREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEXTABLECREAT

6、E TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLESESSIONCREATE SESSION ALTER SESSION RESTRICTED SESSIONTABLESPACECREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE12Oracle用户管理SYSDBA and SYSOPER权限列表CategoryExamplesSYSOPERST

7、ARTUPSHUTDOWNALTER DATABASE OPEN | MOUNTALTER DATABASE BACKUP CONTROLFILE TO RECOVER DATABASEALTER DATABASE ARCHIVELOGSYSDBASYSOPER PRIVILEGES WITH ADMIN OPTIONCREATE DATABASEALTER DATABASE BEGIN/END BACKUP RESTRICTED SESSEIONRECOVER DATABASE UNTIL13Oracle用户管理OBJECT PRIVTABLEVIEWSEQUENCEPROCEDUREALT

8、ERDELETEEXECUTEINDEXINSERTREFERENCESSELECTUPDATE对象权限14Oracle用户管理WITH ADMIN OPTION选项DBAGRANTREVOKEJeffEmiJeffEmiDBA15Oracle用户管理赋予 PrivilegesSQL grant XXX to XXX with admin option;回收 PrivilegesSQL revoke XXX from XXX;16Oracle用户管理获取权限信息相关数据字典DBA_SYS_PRIVSSESSION_PRIVSDBA_TAB_PRIVSDBA_COL_PRIVS17Oracle用

9、户管理PrivilegesRolesUPDATE ON JOBSINSERT ON JOBSSELECT ON JOBSCREATE TABLECREATE SESSIONHR_CLERKHR_MGRABC角色管理Users需要了解创建修改角色控制角色移除角色使用预定义角色获取角色信息18Oracle用户管理CREATE ROLE oe_clerk;CREATE ROLE hr_clerkIDENTIFIED BY bonus;CREATE ROLE hr_manager IDENTIFIED EXTERNALLY;Creating Roles19Oracle用户管理预定义角色Role Nam

10、eDescriptionCONNECT, RESOURCE, DBAThese roles are provided for backward compatibilityEXP_FULL_DATABASEPrivileges to export the databaseIMP_FULL_DATABASEPrivileges to import the databaseDELETE_CATALOG_ROLEDELETE privileges on data dictionary tablesEXECUTE_CATALOG_ROLEEXECUTE privilege on data diction

11、ary packagesSELECT_CATALOG_ROLESELECT privilege on data dictionary tables20Oracle用户管理ALTER ROLE oe_clerkIDENTIFIED BY order;ALTER ROLE hr_clerkIDENTIFIED EXTERNALLY;ALTER ROLE hr_manager NOT IDENTIFIED;修改角色21Oracle用户管理GRANT oe_clerk TO scott;GRANT hr_clerk TO hr_manager;GRANT hr_manager TO scott WIT

12、H ADMIN OPTION;赋予角色22Oracle用户管理ALTER UALTER U ALTER U ALTER USER scott DEFAULT ROLE hr_clerk, oe_clerk;SER scott DEFAULT ROLE ALL;SER scott DEFAULT ROLE ALL EXCEPT hr_clerk; SER scott DEFAULT ROLE NONE;监理默认角色23Oracle用户管理SET ROLE hr_clerk;SET ROLE oe_clerk IDENTIFIED BY order; SET ROLE ALL EXCEPT oe_

13、clerk;Enabling and Disabling Roles提示：主要针对session这一层面。SESSION_ROLES24Oracle用户管理REVOKE oe_clerk FROM scott;REVOKE hr_manager FROM PUBLIC;移除RolesDROP ROLE hr_manager;删除Roles25Oracle用户管理BENEFITSPAYROLLHR_MANAGERHR_CLERKPAY_CLERKUser rolesApplication rolesApplication privilegesGuidelines for Creating Rol

14、esUsersPayroll privilegesBenefits privileges26Oracle用户管理Default rolePassword protected (not default)Select privilegesINSERT, UPDATE, DELETE,and SELECT privilegesPAY_CLERKPAY_CLERK_RO密码角色使用27Role ViewDescriptionDBA_ROLESAll roles that exist in the databaseDBA_ROLE_PRIVSRoles granted to users and role

15、sROLE_ROLE_PRIVSRoles that are granted to rolesDBA_SYS_PRIVSSystem privileges granted to users and rolesROLE_SYS_PRIVSSystem privileges granted to rolesROLE_TAB_PRIVSObject privileges granted to rolesSESSION_ROLESRoles that the user currently has enabledOracle用户管理获取角色信息28Oracle用户管理ProfilesA profile

16、is a named set of password and resource limitsProfiles are assigned to users by the CREATE USER or ALTER USER command Can be enabled or disabledCan relate to the DEFAULT profile提示：通过Profile配置来管理用户密码和资源限制29Oracle用户管理UserPassword expiration and agingPassword verificationPassword historyAccount locking

17、Setting up profiles密码管理Set up password management by using profiles and assign them to users.Lock, unlock, and expire accounts using the CREATE USER or ALTER USER command.Password limits are always enforced.30Oracle用户管理Description用户登录失败次数密码锁定时间账户密码锁定限制Resource_name31Oracle用户管理Resource_name密码生命周期用于指定

18、口令宽限期Description密码过期32Oracle用户管理PASSWORD_REUSE_TIMEPASSWORD_REUSE_MAX密码重用天数最大密码重用次数密码历史Resource_nameDescription33Oracle用户管理密码验证ParameterDescription密码验证函数34Oracle用户管理自定义函数必须有sysfunction_name( username_par password_par old_password_RETURN BOO密码函数用户创建:ameter IN VARCHAR2(30),ameter IN VARCHAR2(30),param

19、eter IN VARCHAR2(30) LEAN35Oracle用户管理VERIFY_FUNCTION函数至少4个字符.密码不等于用户名.密码至少含有1个字符，1个数字，1个特殊字符至少与更改前密码有3个字母不同36Oracle用户管理CREATE PROFILE definded_pro LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_LIFE_TIME 30PASSWORD_REUSE_TIME 30PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_G

20、RACE_TIME 5;创建一个PROFILE37Oracle用户管理ALTER PROFILE default FAILED_LOGIN_ATTEMPTS 3PASSWORD_LIFE_TIME 60PASSWORD_GRACE_TIME 10;修改PROFILEDROP PROFILE developer_prof;DROP PROFILE developer_prof CASCADE;删除PROFILE38Oracle用户管理修改用户PROFILE39Oracle用户管理资源管理Resource management limits can be enforced at the sessi

21、on level, the call level, or both.Limits can be defined by profiles using the CREATE PROFILE command.Enable resource limits with the:RESOURCE_LIMIT initialization parameterALTER SYSTEM command40Oracle用户管理生效资源管理Set the initialization parameter RESOURCE_LIMIT to TRUEEnforce the resource limits by enab

22、ling the parameter with the ALTER SYSTEM commandALTER SYSTEM SET RESOURCE_LIMIT=TRUE;41Oracle用户管理SESSIONS_PER_USERCONNECT_TIMEIDLE_TIMELOGICAL_READS_PER_SESSIONPRIVATE_SGACPU_PER_SESSION会话消耗CPU time数，单位：毫秒每个用户的最大会话数会话连接时间，单位:分钟会话空闲时间，单位：分钟会话逻辑读数共享模式会话SGA占有字节会话级别资源管理Resource_nameDescription提示：通过对资源的配置来 限制用户的行为。42Oracle用户管理CPU_PER_CALLLOGICAL_READS_PER