Transcript Chapter 1 Overview of Database Concepts

Chapter 13
User Creation and Management
Chapter 13
Introduction to Oracle9i: SQL
1
User Accounts
• Provide a method of authentication
• Can grant access to specific objects
• Identify owners of objects
Chapter 13
Introduction to Oracle9i: SQL
2
CREATE USER Command
Gives each user a user name and password
Chapter 13
Introduction to Oracle9i: SQL
3
Database Connection
Even with valid user name and password,
user still needs CREATE SESSION
privilege to connect to a database
Chapter 13
Introduction to Oracle9i: SQL
4
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
Chapter 13
Introduction to Oracle9i: SQL
5
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
Chapter 13
Introduction to Oracle9i: SQL
6
Granting Object Privileges
Granted through GRANT command
Chapter 13
Introduction to Oracle9i: SQL
7
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
Chapter 13
Introduction to Oracle9i: SQL
8
GRANT Command Example –
Object Privileges
Chapter 13
Introduction to Oracle9i: SQL
9
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
Chapter 13
Introduction to Oracle9i: SQL
10
SYSTEM_PRIVILEGE_MAP
Chapter 13
Introduction to Oracle9i: SQL
11
Granting System Privileges
System privileges given through GRANT
command
Chapter 13
Introduction to Oracle9i: SQL
12
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
Chapter 13
Introduction to Oracle9i: SQL
13
GRANT Command Example –
System Privileges
Chapter 13
Introduction to Oracle9i: SQL
14
Changing User Password
Can use PASSWORD command or ALTER
USER command
Chapter 13
Introduction to Oracle9i: SQL
15
Roles
• A group, or collection, of privileges
• Can be assigned to users or other roles
Chapter 13
Introduction to Oracle9i: SQL
16
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
Chapter 13
Introduction to Oracle9i: SQL
17
Modifying a Role
• Roles can be modified with ALTER ROLE
command
• Roles can be assigned passwords
Chapter 13
Introduction to Oracle9i: SQL
18
Viewing Privileges
• ROLE_SYS_PRIVS lists all system
privileges assigned to a role
• SESSION_PRIVS lists user’s currently
enabled roles
Chapter 13
Introduction to Oracle9i: SQL
19
ROLE_SYS_PRIVS Example
Chapter 13
Introduction to Oracle9i: SQL
20
SESSION_PRIVS Example
Chapter 13
Introduction to Oracle9i: SQL
21
Revoking System Privilege
Revoke system privileges with REVOKE
command
Chapter 13
Introduction to Oracle9i: SQL
22
Revoking Object Privilege
If originally granted using WITH GRANT
OPTION, the effect cascades and is revoked
from subsequent recipients
Chapter 13
Introduction to Oracle9i: SQL
23
Dropping a Role
Users receiving privileges via a role that is
dropped will no longer have those
privileges available
Chapter 13
Introduction to Oracle9i: SQL
24
Dropping a User
DROP USER command is used to remove a
user account
Chapter 13
Introduction to Oracle9i: SQL
25