Chapter 13 User Creation and Management

1. Chapter 13User Creation and Management Oracle9i: SQL

2. Chapter Objectives • Explain the concept of authentication • Create a new user account • Grant a user the CREATE SESSION privilege • Make a password expire • Change the password of an existing account Oracle9i: SQL

3. Chapter Objectives • Create a role • Grant privileges to a role • Assign a user to a role • Revoke privileges from a user and a role • Drop a user Oracle9i: SQL

4. User Accounts • Provide a method of authentication • Can grant access to specific objects • Identify owners of objects Oracle9i: SQL

5. CREATE USER Command Gives each user a user name and password Oracle9i: SQL

6. Database Connection Even with valid user name and password, user still needs CREATE SESSION privilege to connect to a database Oracle9i: SQL

7. Privileges • System privileges • Allow access to database and execution of DDL operations • Approximately 140 system privileges in Oracle9i • Object privileges • Allow user to perform DML operations • Total of 13 object privileges in Oracle9i Oracle9i: SQL

8. Object Privileges - Examples • SELECT – display data from table, view, or sequence • INSERT – insert data into table or view • UPDATE – change data in a table or view • DELETE – remove data from a table or view • ALTER – change definition of table or view Oracle9i: SQL

9. Granting Object Privileges Granted through GRANT command Oracle9i: SQL

10. Grant Clauses for Object Privileges • GRANT clause – identifies object privileges • ON clause – identifies object • TO clause – identifies user or role receiving privilege • WITH GRANT OPTION clause – gives user ability to assign same privilege to other users Oracle9i: SQL

11. GRANT Command Example – Object Privileges Oracle9i: SQL

12. System Privileges • Affect ability to create, alter, and drop objects • Use of ANY keyword with object privilege (INSERT ANY TABLE) is considered a system privilege • List of all available system privileges available through SYSTEM_PRIVILEGE_MAP Oracle9i: SQL

13. SYSTEM_PRIVILEGE_MAP Oracle9i: SQL

14. Granting System Privileges System privileges given through GRANT command Oracle9i: SQL

15. Grant Clauses for System Privileges • GRANT clause – identifies system privileges being granted • TO clause – identifies receiving user or role • WITH ADMIN OPTION clause – allows user to grant privilege to other database users Oracle9i: SQL

16. GRANT Command Example – System Privileges Oracle9i: SQL

17. Changing User Password Can use PASSWORD command or ALTER USER command Oracle9i: SQL

18. Roles • A group, or collection, of privileges • Can be assigned to users or other roles Oracle9i: SQL

19. Multiple Roles • User can be assigned several roles • All roles can be enabled at one time • Only one role can be designated as default role for each user • Default role can be assigned through ALTER USER command Oracle9i: SQL

20. Modifying a Role • Roles can be modified with ALTER ROLE command • Roles can be assigned passwords Oracle9i: SQL

21. Viewing Privileges • ROLE_SYS_PRIVS lists all system privileges assigned to a role • SESSION_PRIVS lists user’s currently enabled roles Oracle9i: SQL

22. ROLE_SYS_PRIVS Example Oracle9i: SQL

23. SESSION_PRIVS Example Oracle9i: SQL

24. Revoking System Privilege Revoke system privileges with REVOKE command Oracle9i: SQL

25. Revoking Object Privilege If originally granted using WITH GRANT OPTION, the effect cascades and is revoked from subsequent recipients Oracle9i: SQL

26. Dropping a Role Users receiving privileges via a role that is dropped will no longer have those privileges available Oracle9i: SQL

27. Dropping a User DROP USER command is used to remove a user account Oracle9i: SQL