Home arrow Oracle arrow Page 2 - Passwords and User Accounts for Oracle Database XE

User Accounts - Oracle

In this third part of a 10-part article series on securing Oracle Database XE, you'll learn about the role played by passwords and user accounts. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

  1. Passwords and User Accounts for Oracle Database XE
  2. User Accounts
By: Apress Publishing
Rating: starstarstarstarstar / 0
December 23, 2010

print this article



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 




The name of the schema, and therefore the user, to be created. The username can be up to 30 characters long and cannot be a reserved word unless it is quoted (which is not recommended).


How the user will be authenticated: by the database with a password, by the operating system (local or remote), or by a service (such as Oracle Internet Directory).


The tablespace where permanent objects are created, unless a tablespace is explicitly specified during creation.


The tablespace where temporary segments are created during sort operations, index creation, and so forth.

QUOTA { size | UNLIMITED } ON tablespace

The amount of space allowed for objects created on the specified tablespace. Size is in kilobytes or megabytes.

PROFILE profile

The profile assigned to this user. Profiles are discussed later in this chapter. If a profile is not specified, the DEFAULTprofile is used.


The prompt for the user to change his password at first logon.


The state of the account: locked or unlocked. By default, the account is unlocked.

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.


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.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: