Administering User Security

1. Administering User Security

2. Objectives • After completing this lesson, you should be able to do the following: • Create and manage database user accounts • Create and manage roles • Grant and revoke privileges • Control resource usage by users

3. User Management: Overview • Create a user with an assigned storage area (tablespace). • 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. Users Jenny Roles HR_MGR Insert employees Privileges

4. Database Users and Schemas • A 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 Objects Tables Triggers Indexes Views Sequences Stored program units Synonyms User-defined data types Database links

5. Database User Accounts > User Privilege Role Profile • Each database user account has: • A unique username • An authentication method • A default tablespace • A temporary tablespace • A user profile • A consumer group • A lock status

6. Predefined Accounts: SYS and SYSTEM • The SYS account: • Is granted the DBA role • Has all privileges with ADMINOPTION • Is required for startup, shutdown, and some maintenance commands • Owns the data dictionary • Owns the Automatic Workload Repository (AWR) • The SYSTEM account is granted the DBA role. • These accounts are not used for routine operations.

7. Creating a User • Select Administration > Schema > Users & Privileges > Users, and then click the Create button.

8. Privileges User > Privilege Role Profile • There are two types of user privileges: • System: Enables users to perform particular actions in the database • Object: Enables users to access and manipulate a specific object HR_DBA Object privilege: Update employees System privilege: Create session

9. System Privileges

10. Object Privileges • To grant object privileges: 1. Choose the object type. 2. Select objects. 3. Select privileges.

11. DBA Jeff Emi DBA Jeff Emi Revoking System Privilegeswith ADMINOPTION User GRANT Privilege Object REVOKE REVOKE CREATE TABLE FROM jeff;

12. Bob Jeff Emi Bob Jeff Emi Revoking Object Privileges with GRANTOPTION GRANT REVOKE

13. Creating a Role User Privilege > Role Profile • Select Administration > Schema > Users & Privileges > Roles.

14. Benefits of Roles • Easier privilege management • Dynamic privilege management • Selective availability of privileges

15. Predefined Roles

16. Secure Roles • Roles may be nondefault. SET ROLE vacationdba; • Roles may be protected through authentication. • Roles may also be secured programmatically. CREATE ROLE secure_application_role IDENTIFIED USING <security_procedure_name>;

17. Profiles and Users User Privilege Role > Profile • Users are assigned only one profile at any given time. • Profiles: • Control resource consumption • Manage account status and password expiration

18. Done: Creating a user Creating a role Assigning system and object privileges to a role Creating a profile Limiting resource usage with a profile To Do—Modifying user accounts: Assigning storage area (tablespace) Assigning quota to limit storage usage Authenticating users with passwords Managing password rules with profiles (expiring passwords and locking accounts) Assigning privileges to roles and roles to users Where We Are

19. Modifying Users: Default and Temporary Tablespaces and Locking • Default: Default location of database objects • Temporary: Used for sorting Best practice: Be specific in your tablespace assignments.

20. Assigning Quota to Users • Users who do not have the UNLIMITEDTABLESPACE system privilege must be given a quota before they can create objects in a tablespace. Quotas can be: • Unlimited • A specific value in megabytes or kilobytes

21. Authenticating Users • Password • External • Global

22. Administrator Authentication • Operating 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. Administrator Security: • SYSBAand 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 SYSDBA and SYSOPER.

23. Locking and Unlocking Accounts Failed login attempts User Account locked Manual locking DBA Manual unlocking Account unlocked

24. Setting Password Expiration • Password management includes the following: • Specifying the maximum lifetime for a password • Specifying a grace period for changing a password • Note: Do not use profiles that cause the SYS, SYSMAN, and DBSNMP passwords to expire and the accounts to get locked.

25. Unlocking a User Account andResetting the Password Select the user, and click Unlock User.

26. Assigning Privileges to Roles and Roles to Users Users Jenny David Rachel Roles HR_CLERK HR_MGR Delete employees Select employees Update employees Privileges Insert employees

27. Assigning Roles to Users

28. Checklist for Creating Users • Select a profile. • Select an authentication technique. • Assign a default tablespace and temporary tablespace. • Decide on quotas for each tablespace. • Grant privileges and roles to the user.

29. Summary • In this lesson, you should have learned how to: • Create and manage user accounts • Create and manage roles • Grant and revoke privileges • Control resource usage by users

30. Practice Overview: Administering Users • This practice covers the following topics: • Creating a profile to limit resource consumption • Creating two roles: a. HRCLERK b. HRMANAGER • Creating three new users: One manager and two clerks