Chapter 13 User Creation and Management

1
Chapter 13User Creation and Management
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

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

4
User Accounts

• Provide a method of authentication
• Can grant access to specific objects
• Identify owners of objects

5
CREATE USER Command

• Gives each user a user name and password

6
Database Connection

• Even with valid user name and password, user
  still needs CREATE SESSION privilege to connect
  to a database

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

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

9
Granting Object Privileges

• Granted through GRANT command

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

11
GRANT Command Example Object Privileges
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

13
SYSTEM_PRIVILEGE_MAP
14
Granting System Privileges

• System privileges given through GRANT command

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

16
GRANT Command Example System Privileges
17
Changing User Password

• Can use PASSWORD command or ALTER USER command

18
Roles

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

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

20
Modifying a Role

• Roles can be modified with ALTER ROLE command
• Roles can be assigned passwords

21
Viewing Privileges

• ROLE_SYS_PRIVS lists all system privileges
  assigned to a role
• SESSION_PRIVS lists users currently enabled
  roles

22
ROLE_SYS_PRIVS Example
23
SESSION_PRIVS Example
24
Revoking System Privilege

• Revoke system privileges with REVOKE command

25
Revoking Object Privilege

• If originally granted using WITH GRANT OPTION,
  the effect cascades and is revoked from
  subsequent recipients

26
Dropping a Role

• Users receiving privileges via a role that is
  dropped will no longer have those privileges
  available

27
Dropping a User

• DROP USER command is used to remove a user
  account