Oracle數(shù)據(jù)庫用戶管理介紹

1、Oracle數(shù)據(jù)庫用戶管理介紹Oracle用戶管理01Oracle用戶管理Account lockingTablespace quotasTemporary tablespaceDefault tablespaceRole privilegesResource limitsSecurity domainDirect privilegesAuthentication mechanism需要了解:創(chuàng)建數(shù)據(jù)庫用戶更改，刪除已存在數(shù)據(jù)庫用戶監(jiān)控用戶信息02Oracle用戶管理Schema Objects TablesTriggers Constraints Indexes Views Sequence

2、sStored program units SynonymsUser-defined data types Database links數(shù)據(jù)庫用戶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用戶管理創(chuàng)建用戶的一基本參數(shù)表空間配額默認表空間用戶密碼權

3、限，角色04Oracle用戶管理密碼認證創(chuàng)建初始密碼：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操作系統(tǒng)認證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用戶管理獲取用戶信息相關數(shù)據(jù)字典DBA_USERSDBA_TS_QUOTAS10Oracle用戶管理權限管理需要了解獲取系統(tǒng)對象權限賦予、回收權限獲取審計屬性兩種用戶權限:System: 數(shù)據(jù)庫級別權限Object: 對象級別權限11Oracle用戶管理系統(tǒng)級權限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用戶管理獲取權限信息相關數(shù)據(jù)字典DBA_SYS_PRIVSSESSION_PRIVSDBA_TAB_PRIVSDBA_COL_PRIVS17Oracle用

9、戶管理PrivilegesRolesUPDATE ON JOBSINSERT ON JOBSSELECT ON JOBSCREATE TABLECREATE SESSIONHR_CLERKHR_MGRABC角色管理Users需要了解創(chuàng)建修改角色控制角色移除角色使用預定義角色獲取角色信息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;監(jiān)理默認角色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用戶登錄失敗次數(shù)密碼鎖定時間賬戶密碼鎖定限制Resource_name31Oracle用戶管理Resource_name密碼生命周期用于指定

18、口令寬限期Description密碼過期32Oracle用戶管理PASSWORD_REUSE_TIMEPASSWORD_REUSE_MAX密碼重用天數(shù)最大密碼重用次數(shù)密碼歷史Resource_nameDescription33Oracle用戶管理密碼驗證ParameterDescription密碼驗證函數(shù)34Oracle用戶管理自定義函數(shù)必須有sysfunction_name( username_par password_par old_password_RETURN BOO密碼函數(shù)用戶創(chuàng)建:ameter IN VARCHAR2(30),ameter IN VARCHAR2(30),param

19、eter IN VARCHAR2(30) LEAN35Oracle用戶管理VERIFY_FUNCTION函數(shù)至少4個字符.密碼不等于用戶名.密碼至少含有1個字符，1個數(shù)字，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;創(chuàng)建一個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數(shù)，單位：毫秒每個用戶的最大會話數(shù)會話連接時間，單位:分鐘會話空閑時間，單位：分鐘會話邏輯讀數(shù)共享模式會話SGA占有字節(jié)會話級別資源管理Resource_nameDescription提示：通過對資源的配置來 限制用戶的行為。42Oracle用戶管理CPU_PER_CALLLOGICAL_READS_PER