3.1 How do I…Create a user account?Problem
After developing my application, I need to create user accounts for the people who will use it. I also want to create user accounts within the development environment to test my application with different sets of privileges. How do I create a new user account in Oracle?
Technique
The CREATE USER statement is used to create a new user account in an Oracle database. User accounts can be created by using SQL*Plus. This How-To focuses on using SQL*Plus to create user accounts because it is portable across all database platforms. In this How-To, you will create user accounts that appear throughout the examples here and later in this chapter. The syntax of the CREATE USER statement is shown in Figure 3.1.
The CREATE USER statement is a SQL statement and ends with a semicolon. The DEFAULT TABLESPACE clause identifies where objects created by the user account will reside if a tablespace is not specified in the CREATE statement. The TEMPORARY TABLESPACE clause identifies where temporary data will reside when required by operations performed by the user account. The QUOTA clause, which can occur many times within the statement, specifies the amount of disk space the user account’s objects can occupy in a given tablespace. The PROFILE clause identifies the profile the user account uses to control resources available, such as the number of concurrent sessions and the maximum CPU time per call.
Steps 1. Run SQL*Plus and connect as the WAITE user account created by the installation script. Load CHP3_1.sql into the SQL buffer. The file contains a CREATE USER statement to create a new user account.
SQL> GET CHP3_1.sql
1 CREATE USER FRED
2* IDENTIFIED BY NEWUSER
Line 1 contains the CREATE USER keywords and specifies the name of the new user account. The IDENTIFIED BY clause contained in line 2 specifies the password required when the user account connects to the database. Because no modifying clauses are specified, the user account is created with default properties. Although the user account is created, it has no permission to do anything with the database. How-To 3.7 covers how to determine privileges of user accounts.
2. Execute the statement to create the user account.
SQL>
User Created.
3. Load CHP3_2.sql into the SQL buffer. The file contains a DROP USER statement to remove the user account FRED from the system.
SQL> GET CHP3_2.sql
1* DROP USER FRED
Line 1 contains the DROP USER keywords and specifies the user account to be removed. If the user account owns database objects, the CASCADE clause must be specified to remove all the user account’s objects from the database. An example of this would be DROP USER FRED CASCADE.
4. Run CHP3_2.SQL to remove the user account.
SQL> /
User dropped.
How It Works
The CREATE USER statement is used to create new user accounts in the database. Executing the CREATE USER statement does not create an account that can use an application or even connect to the database. The user account must be granted system privileges to use the system and object privileges to access database objects. The DROP USER statement is used to remove a user account from the system. If the user account owns objects, the CASCADE clause must be included to remove the objects owned by the user account. A user account cannot be removed if objects remain. Steps 1 and 2 create a new user account, and Steps 3 and 4 remove the account.
Comments
The SQL*Plus method for creating user accounts is important because it is portable across all platforms. If you create a large number of user accounts, you might find it easier to create the statements in Windows Notepad or another text editor and run all the statements as a single script. Roles define a group of related privileges. When creating a new user account, keep in mind that it has no privileges until they are granted. Privileges can be granted directly to a user account or indirectly through roles. The file CHP3_1.sql contains all the statements required to build the user accounts used in this chapter. The examples later in this chapter assume these user accounts exist. How-To’s 3.2, 3.7, 3.8, and 3.9 describe how to grant privileges to user accounts.