教程案例教案1

1、Administering User SecurityObjectivesAfter completing this lesson, you should be able to:Create and manage database user accounts:Authenticate usersAssign default storage areas (tablespaces)Grant and revoke privilegesCreate and manage rolesCreate and manage profiles:Implement standard password secur

2、ity featuresControl resource usage by usersDatabase User AccountsEach database user account has:A unique usernameAn authentication methodA default tablespace A temporary tablespaceA user profileAn initial consumer groupAn account statusPredefined Accounts: SYS and SYSTEMSYS account:Is granted the DB

3、A roleHas all privileges with ADMIN OPTIONIs required for startup, shutdown, and some maintenance commandsOwns the data dictionaryOwns the Automatic Workload Repository (AWR)SYSTEM account is granted the DBA role. These accounts are not used for routine operations.Creating a UserSelect Server Users,

4、 and then click the Create button.Authenticating UsersPasswordExternalGlobal注：題52Administrator AuthenticationOperating system security:DBAs must have the OS privileges to create and delete files.Typical database users should not have the OS privileges to create or delete database files. Administrato

5、r security:For SYSDBA, SYSOPER, and SYSASM connections: DBA user by name is audited for password file and strong authentication methodsOS account name is audited for OS authenticationOS authentication takes precedence over password file authentication for privileged usersPassword file uses case-sens

6、itive passwordsUnlocking a User Account andResetting the PasswordSelect the user, select Unlock User, and click Go.注：題15PrivilegesThere are two types of user privileges:System: Enables users to perform particular actions in the databaseObject: Enables users to access and manipulate a specific object

7、System privilege: Create sessionHR_DBAObject privilege: Update employees注：題34System PrivilegesObject PrivilegesTo grant object privileges:Choose the object type.Select objects.Select privileges.Search and select objects.123GRANTREVOKERevoking System Privilegeswith ADMIN OPTIONREVOKE CREATE TABLE FRO

8、M jeff;UserPrivilegeObjectDBAJeffEmiJeffEmiDBA注：題32GRANTREVOKERevoking Object Privilegeswith GRANT OPTIONBobJeffEmiEmiJeffBobBenefits of Roles Easier privilege management Dynamic privilege management Selective availability of privilegesAssigning Privileges to Roles andAssigning Roles to UsersUsersPr

9、ivilegesRolesHR_CLERKHR_MGRJennyDavidRachelDeleteemployees.Selectemployees.Updateemployees.Insertemployees.CreateJob.Predefined RolesRolePrivileges IncludedCONNECTCREATE SESSIONRESOURCECREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER,

10、 CREATE TYPESCHEDULER_ ADMINCREATE ANY JOB, CREATE EXTERNAL JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULERDBAMost system privileges; several other roles. Do not grant to nonadministrators.SELECT_CATALOG_ROLENo system privileges; HS_ADMIN_ROLE and over 1,700 object privileg

11、es on the data dictionaryCreating a RoleSelect Server Roles. Click OK when finished.Add privileges and roles from the appropriate tab.Add privileges and roles from the appropriate tab.Add privileges and roles from the appropriate tab.CREATE ROLE secure_application_roleIDENTIFIED USING ;Secure RolesR

12、oles can be nondefault.Roles can be protected through authentication.Roles can also be secured programmatically.SET ROLE vacationdba;注：題77Assigning Roles to Users注：題21、72Profiles and UsersUsers are assigned only one profile at a time.Profiles:Control resource consumptionManage account status and pas

13、sword expirationNote: RESOURCE_LIMIT must be set to TRUE before profiles can impose resource limitations.Implementing Password Security FeaturesPassword historyAccount lockingPassword aging and expiration Password complexity verificationUserSetting up profilesNote: Do not use profiles that cause the

14、 SYS, SYSMAN, and DBSNMP passwords to expire and the accounts to be locked.Creating a Password ProfileSupplied Password Verification Function: VERIFY_FUNCTION_11GThe VERIFY_FUNCTION_11G function insures that the password is:At least eight charactersDifferent from the username, username with a number

15、, or username reversedDifferent from the database name or the database name with a numberA string with at least one alphabetic and one numeric characterDifferent from the previous password by at least three lettersTip: Use this function as a template to create your own customized password verificati

16、on.Assigning Quotas to UsersUsers who do not have the UNLIMITED TABLESPACE system privilege must be givena quota before they can create objects in a tablespace. Quotas can be:A specific value in megabytes or kilobytesUnlimitedSummaryIn this lesson, you should have learned how to:Create and manage database user accounts:Authenticate usersAssign default storage areas (tablespaces)Grant and revoke privilegesCreate and manage rolesCreate and manage profiles:Implement standard password security featuresControl resource usage by us