User Accounts - Oracle

In order to gain access to the database, a user must provide a username to access the resources associated with that account. Each username must have a password and is associated with one and only one schema in the database. Some accounts may have no objects in the schema, but instead would have the privileges granted to that account to access objects in other schemas.

In this section, we explain the syntax and give examples for creating, altering, and dropping users. In addition, we show you how to become another user without explicitly knowing the password for the user.

Creating Users

The CREATE USER command is fairly straightforward. It has a number of parameters, which we present in Table 31-1 along with a brief description of each one. The Oracle Database XE GUI (shown in Figure 31-1) provides most of the functionality provided with the CREATE USER command; you only need to useCREATE USERwhen you need a parameter not available in the GUI, such asQUOTA orPROFILE.

Table 31-1. The Options for the CREATE USER Command 

In the following example, we create a user (KELLYC) to correspond with the user Kelly Chung, employee number 188 in theHR.EMPLOYEEStable from the sample schemas installed with Oracle Database XE:

SQL> create user kellyc identified by kellyc311
  2     account unlock
  3     default tablespace users
  4     temporary tablespace temp;

User created.

SQL>

The userKELLYCis authenticated by the database with an initial password ofKELLYC311. The second line is not required; all accounts are unlocked by default. Both the default permanent tablespace and the default temporary tablespace are defined at the database level, so the last two lines of the command aren’t required unless you want a different default permanent tablespace or a different temporary tablespace for the user.

Even though the userKELLYChas been either explicitly or implicitly assigned a default permanent tablespace, she cannot create any objects in the database until we provide both a quota and the rights to create objects in their own schema.

A quota is simply a space limit, by tablespace, for a given user. Unless a quota is explicitly assigned or the user is granted theUNLIMITED TABLESPACEprivilege (privileges are discussed later in this chapter in the section “Using Object Privileges”), the user cannot create objects in her own schema. In the following example, we give theKELLYCaccount a quota of 250MB in theUSERStablespace:

SQL> alter user kellyc quota 250M on users; User altered.

Note that we could have granted this quota at the time the account was created, along with almost every other option in theCREATE USERcommand. A default role, however, can only be assigned after the account is created. (We discuss role management later in this chapter in the section “Creating, Assigning, and Maintaining Roles.”)

Unless we grant some basic privileges to a new account, the account cannot even log in; therefore, we need to grant at least theCREATE SESSIONprivilege or theCONNECTrole (roles are discussed in detail in the “Creating, Assigning, and Maintaining Roles” section). TheCONNECTrole contains theCREATE SESSIONprivilege, along with other basic privileges, such asCREATE TABLEandALTER SESSION. In the following example, we grantKELLYCtheCONNECTprivilege:

SQL> grant connect to kellyc;
Grant succeeded.

Now the userKELLYChas a quota on theUSERS tablespace as well as the privileges to create objects in that tablespace.

All these options forCREATE USERare available in the Oracle Database XE Web-based interface, as you can see in Figure 31-1.

Figure 31-1.  Creating users with the Oracle Database XE GUI

Please check back next week for the next part of the series.