大型数据库系统分析与设计：Less09-Users

1、Administering User SecurityObjectivesAfter completing this lesson, you should be able to do the following:Create and manage database user accountsCreate and manage rolesGrant and revoke privilegesControl resource usage by usersUser Management: OverviewCreate a user with an assigned storage area (tab

2、lespace).Assign quota to limit storage usage.Limit resource usage with profile.Authenticate a user with a password.Manage password rules with profiles (expire passwords and lock account).Assign privileges to roles and roles to users.UsersPrivilegesRolesHR_MGRJennyInsertemployeesDatabase Users and Sc

3、hemasA schema is the collection ofobjects owned by a user.Username and schema are often used interchangeably.A user can be associated with only one schema, but he or she can use objects from many schemas with the appropriate permissions.Schema ObjectsTablesTriggersIndexesViewsSequencesStored program

4、 unitsSynonymsUser-defined data typesDatabase linksDatabase User AccountsEach database user account has:A unique usernameAn authentication methodA default tablespace A temporary tablespaceA user profileA consumer groupA lock statusUserPrivilegeRoleProfileDatabase User Accounts Full Notes PagePredefi

5、ned Accounts: SYS and SYSTEMThe SYS account:Is granted the DBA roleHas all privileges with ADMIN OPTIONIs required for startup, shutdown, and some maintenance commandsOwns the data dictionaryOwns the Automatic Workload Repository (AWR)The SYSTEM account is granted the DBA role. These accounts are no

6、t used for routine operations.Creating a UserSelect Administration Schema Users & Privileges Users, and then click the Create button.PrivilegesThere are two types of user privileges:System: Enables users to perform particular actions in the databaseObject: Enables users to access and manipulate a sp

7、ecific objectUser PrivilegeRoleProfileSystem privilege: Create sessionHR_DBAObject privilege: Update employeesSystem PrivilegesSystem PrivilegesFull Notes PageObject PrivilegesTo grant object privileges:1.Choose the object type.2.Select objects.3.Select privileges.GRANTREVOKERevoking System Privileg

8、eswith ADMIN OPTIONREVOKE CREATE TABLE FROM jeff;UserPrivilegeObjectDBAJeffEmiJeffEmiDBAGRANTREVOKERevoking Object Privileges with GRANT OPTIONBobJeffEmiEmiJeffBobCreating a RoleSelect Administration Schema Users & Privileges Roles. User PrivilegeRoleProfileBenefits of Roles Easier privilege managem

9、ent Dynamic privilege management Selective availability of privilegesPredefined RolesCONNECTCREATE SESSIONRESOURCECREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPESCHEDULER_ ADMINCREATE ANY JOB, CREATE EXTERNAL JOB, CREATE

10、 JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULERDBAMost system privileges, several other roles. Do not grant to nonadministrators.SELECT_CATALOG_ROLENo system privileges, but HS_ADMIN_ROLE and over 1,700 object privileges on the data dictionaryCREATE ROLE secure_application_roleIDENTIF

11、IED USING ;Secure RolesRoles may also be secured programmatically.Roles may be nondefault.SET ROLE vacationdba;Roles may be protected through authentication.Profiles and UsersUsers are assigned only one profile at any given time.Profiles:Control resource consumptionManage account status and password

12、 expirationUserPrivilegeRole ProfileProfiles and UsersFull Notes PageWhere We AreDone:Creating a userCreating a roleAssigning system and object privileges to a role Creating a profileLimiting resource usage with a profileTo DoModifying user accounts: Assigning storage area (tablespace)Assigning quot

13、a to limit storage usageAuthenticating users with passwordsManaging password rules with profiles (expiring passwords and locking accounts)Assigning privileges to roles and roles to usersModifying Users: Default and Temporary Tablespaces and LockingDefault: Default location of database objectsTempora

14、ry: Used for sortingBest practice: Be specific in your tablespace assignments.Assigning Quota to UsersUsers who do not have the UNLIMITED TABLESPACE system privilege must be given a quota before they can create objects in a tablespace. Quotas can be:UnlimitedA specific value in megabytes or kilobyte

15、sAssigning Quota to UsersFull Notes PageAuthenticating UsersPasswordExternalGlobalAuthenticating UsersFull Notes PageAdministrator 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

16、 delete database files. Administrator Security:SYSBA and SYSOPER connections are authorized via password file or OS.Password file authentication records the DBA user by name.OS authentication does not record the specific user.OS authentication takes precedence over password file authentication for S

17、YSDBA and SYSOPER. Locking and Unlocking AccountsFailed login attemptsManual lockingAccount lockedManual unlockingAccount unlockedUserDBASetting Password ExpirationPassword management includes the following:Specifying the maximum lifetime for a passwordSpecifying a grace period for changing a passwo

18、rdNote: Do not use profiles that cause the SYS, SYSMAN, and DBSNMP passwords to expire and the accounts to get locked.Unlocking a User Account andResetting the PasswordSelect the user, and click Unlock User.Assigning Privileges to Roles and Roles to UsersUsersPrivilegesRolesHR_CLERKHR_MGRJennyDavidRachelDeleteemployeesSelectemployeesUpdateemployeesInsertemployeesAssigning