Chapter 9: Database Security

In this chapter, you will

• Learn more about users

• See how privileges are used to enable users to perform tasks in the database

• Explore how privileges are divided into two types: system privileges and object privileges

• Learn how system privileges allow you to perform actions such as execute DDL statements

• See how object privileges allow you to perform actions such as execute DML statements

• Explore how to manage privileges using roles

  Note  

  You ll need to type in the SQL statements shown in this chapter.

Users

In this section, you ll learn how to create a user, alter a user s password, and drop a user .

Creating a User

To create a user in the database, you use the CREATE USER statement. The simplified syntax for the CREATE USER statement is as follows :

 CREATE USER  user_name  IDENTIFIED BY  password  [DEFAULT TABLESPACE  def_tabspace  ] [TEMPORARY TABLESPACE  temp_tabspace  ]; 

where

• user_name specifies the name of the database user.

• password specifies the password for the database user.

• def_tabspace specifies the default tablespace where objects are stored. These objects include tables. If you omit a default tablespace, the default SYSTEM tablespace is used, which always exists in a database. Note: Tablespaces are used by the database to separate objects. For more details on tablespaces, speak with your DBA or consult the Oracle reference documentation.

• temp_tabspace specifies the default tablespace where temporary objects are stored. These objects include temporary tables that you ll learn about in the next chapter. If you omit a temporary tablespace, the default SYSTEM tablespace is used.

The following example connects as system and creates a user named jason with a password of price :

 CONNECT system/manager CREATE USER jason IDENTIFIED BY price; 

Note  

If you want to follow along with these examples you ll need to connect to the database as a privileged user. I used the system user in the example, which has a default password of manager in my database. If you re not acting as your own DBA, you ll need to speak with your DBA to get the password for a privileged user who is able to create users.

The next example creates a user named henry and specifies a default and temporary tablespace:

 CREATE USER henry IDENTIFIED BY hooray DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; 

Note  

If your database doesn t have tablespaces named users and temp , you can skip this example. The henry user isn t used elsewhere in this book, and I included the example only so you can see how to specify tablespaces for a user. You can view all the tablespaces in a database by connecting as the system user and running the query SELECT * FROM dba_tablespaces .

If you want a user to be able to do things in the database, that user must be granted the necessary permissions to do those things. For example, to connect to the database a user must be granted the permission to create a session, which is the CREATE SESSION system privilege. Permissions are granted by a privileged user ( system , for example) using the GRANT statement.

The following example grants the CREATE SESSION permission to jason :

 GRANT CREATE SESSION TO jason; 

You are now able to connect as jason .

The following example creates other users used in this chapter and grants the CREATE SESSION privilege to those users:

 CREATE USER steve IDENTIFIED BY button; CREATE USER gail IDENTIFIED BY seymour; GRANT CREATE SESSION TO steve, gail; 

Changing a User s Password

You can change a user s password using the ALTER USER statement. For example, the following ALTER USER statement changes the password for jason to marcus :

 ALTER USER jason IDENTIFIED BY marcus; 

You can also change the password for the user you re currently logged in as using the PASSWORD command. After you enter PASSWORD , SQL*Plus prompts you to enter the old password and the new password twice for confirmation. The following example connects as jason and executes PASSWORD :

  CONNECT jason/marcus   PASSWORD  Changing password for JASON Old password: ****** New password: ****** Retype new password: ****** Password changed 

Deleting a User

You delete a user using the DROP USER statement. The following example connects as system and uses DROP USER to delete jason :

 CONNECT system/manager DROP USER jason; 

Note  

You must add the keyword CASCADE after the user s name in the DROP USER statement if that user s schema contains objects such as tables and so on.