7/22/2010
Database and Information Retrieval
ICT118 Lecture 7 Creating users and assigning privileges
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 1
Oracle 11g: SQL
Chapter 7 User Creation and Management
Includes slides from Cengages Casteel web site
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 7. 2
Objectives
Explain the concept of data security Create a new user account Identify two types of privileges: system and object Grant privileges to a user Address password expiration requirements Change the password of an existing account
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 3
7/22/2010
Objectives (continued)
Create a role Grant privileges to a role Assign a user to a role View privilege information Revoke privileges from a user and a role Remove a user and roles
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 4
Data Security
User accounts provide a method of authentication They can grant access to specific objects They identify owners of objects
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 5
Creating a User
The CREATE USER command gives each The user a user name and password
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 6
7/22/2010
Assigning User Privileges
There are two types of privileges System privileges
Allow access to the database and execution of DDL operations
Object privileges
Allow a user to perform DML and query operations
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 7
Assigning User Privileges (continued)
Even with a valid user name and password, a user still needs the CREATE SESSION privilege to connect to a database
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 8
System Privileges
Affect a users ability to create, alter, and drop objects Use of ANY keyword with an object privilege (INSERT ANY TABLE) is considered a system privilege List of all available system privileges available through SYSTEM_PRIVILEGE_MAP
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 9
7/22/2010
SYSTEM_PRIVILEGE_MAP
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 10
Granting System Privileges
System privileges are given through the GRANT command
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 11
Granting System Privileges (continued)
GRANT clause identifies system privileges being granted TO clause identifies receiving user or role WITH ADMIN OPTION clause allows a user to grant privilege to other database users
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 12
7/22/2010
Object Privileges
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
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 13
Granting Object Privileges
Grant object privileges through the Grant GRANT command
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 14
Granting Object Privileges (continued)
GRANT clause identifies object privileges ON clause identifies object TO clause identifies user or role receiving privilege WITH GRANT OPTION clause gives a user the ability to assign the same privilege to other users
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 7. 15
7/22/2010
GRANT Command Examples
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 16
Password Management
To change a user password, use the PASSWORD command or the ALTER USER command
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 17
Utilizing Roles
A role is a group, or collection, of privileges
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 18
7/22/2010
Utilizing Roles (continued)
Roles can be assigned to users or other roles
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 19
Utilizing Roles (continued)
A user can be assigned several roles All roles can be enabled at one time Only one role can be designated as the default role for each user Default role can be assigned through the ALTER USER command
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 20
Utilizing Roles (continued)
Roles can be modified with the ALTER ROLE command Roles can be assigned passwords
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 21
7/22/2010
Viewing Privilege Information
ROLE_SYS_PRIVS lists all system privileges assigned to a role SESSION_PRIVS lists a users currently enabled roles
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 22
ROLE_TAB_PRIVS Example
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 23
Removing Privileges and Roles
Revoke system privileges with the Revoke REVOKE command
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 24
7/22/2010
Removing Privileges and Roles (continued)
Revoking an object privilege if the privilege was originally granted using WITH GRANT OPTION, the effect cascades and is revoked from subsequent recipients
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 25
Removing Privileges and Roles (continued)
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 26
Dropping a Role
Users receiving privileges via a role that is dropped will no longer have those privileges available
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 27
7/22/2010
Dropping a User
The DROP USER command is used to remove a user account
22 July, 2010
ICT118 Database and Information Retrieval, Sem 2, 2010
7. 28
Summary
Database account management is only one facet of data security A new user account is created with the CREATE USER command The IDENTIFIED BY clause contains the password for the account System privileges are used to grant access to the database and to create, alter, and drop database objects The CREATE SESSION system privilege is required before a user can access his account on the Oracle server The system privileges available in Oracle 11g can be viewed through the SYSTEM_PRIVILEGE_MAP
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 7. 29
Summary (continued)
Object privileges allow users to manipulate data in database objects Privileges are given through the GRANT command The ALTER USER command, combined with the PASSWORD EXPIRE clause, can be used to force a user to change her password upon the next attempted login to the database The ALTER USER command, combined with the IDENTIFIED BY clause, can be used to change a users password
Privileges can be assigned to roles to make the administration of privileges easier
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 7. 30
10
7/22/2010
Summary (continued)
Roles are collections of privileges The ALTER USER command, combined with the DEFAULT ROLE keywords, can be used to assign a default role(s) to a user Privileges can be revoked from users and roles using the REVOKE command Roles can be revoked from users using the REVOKE command A role can be deleted using the DROP ROLE command A user account can be deleted using the DROP USER command
22 July, 2010 ICT118 Database and Information Retrieval, Sem 2, 2010 7. 31
11
