1 / 25

Chapter 13 User Creation and Management

Chapter 13 User Creation and Management. User Accounts. Provide a method of authentication Can grant access to specific objects Identify owners of objects. CREATE USER Command. Gives each user a user name and password. Database Connection.

ban
Download Presentation

Chapter 13 User Creation and Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 13User Creation and Management

  2. User Accounts • Provide a method of authentication • Can grant access to specific objects • Identify owners of objects

  3. CREATE USER Command Gives each user a user name and password

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

  5. 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

  6. 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

  7. Granting Object Privileges Granted through GRANT command

  8. 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

  9. GRANT Command Example – Object Privileges

  10. 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

  11. SYSTEM_PRIVILEGE_MAP

  12. Granting System Privileges System privileges given through GRANT command

  13. 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

  14. GRANT Command Example – System Privileges

  15. Changing User Password Can use PASSWORD command or ALTER USER command

  16. Roles • A group, or collection, of privileges • Can be assigned to users or other roles

  17. 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

  18. Modifying a Role • Roles can be modified with ALTER ROLE command • Roles can be assigned passwords

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

  20. ROLE_SYS_PRIVS Example

  21. SESSION_PRIVS Example

  22. Revoking System Privilege Revoke system privileges with REVOKE command

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

  24. Dropping a Role Users receiving privileges via a role that is dropped will no longer have those privileges available

  25. Dropping a User DROP USER command is used to remove a user account

More Related