Passwords and User Accounts for Oracle Database XE

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).

Creating or Re-creating the Password File

Although a default installation of Oracle Database XE will automatically create a password file, there are occasions when you may need to re-create one if it is accidentally deleted or damaged. The orapwd command will create a password file with a single entry for the SYS user and other options, as noted, when you run the orapwd command without any options:

C:> orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>

  where
   
file – name of password file (mand),
   
password – password for SYS (mand),
   
entries – maximum number of distinct DBA and
   
force – whether to overwrite existing file (opt),
   
OPERs (opt),
There are no spaces around the equal-to (=) character.

C:>

Once you re-create the password file, you will have to grant the SYSDBA and SYSOPER privileges to those database users who previously had those privileges. In addition, if the password you provide in the orapwd command is not the same password that the SYS account has in the database, you will have to change the SYS account’s password the next time you are connected to the database so that the password in the database and the password in the password file stay in sync.

The system initialization parameter REMOTE_LOGIN_PASSWORDFILE controls how the password file is used for the database instance. It has three possible values: NONE , SHARED , and EXCLUSIVE .

If the value is NONE , Oracle ignores any password file that exists. Any privileged users must be authenticated by other means, such as by operating system authentication, which is discussed in the next section.

With a value of SHARED , multiple databases can share the same password file, but only the SYS user is authenticated with the password file, and the password for SYS cannot be changed. As a result, this method is not the most secure, but it does allow a DBA to maintain more than one database with a single SYS account.


Tip  If you must use a shared password file, ensure that the password for SYS is at least eight characters long and includes a combination of alphabetic, numeric, and special characters to defend against a brute-force attack.


A value of EXCLUSIVE binds the password file to only one database; other database user accounts can exist in the password file. As soon as you create the password file, use this value to maximize the security of SYSDBA or SYSOPER connections.

The dynamic performance view V$PWFILE_USERS lists all the database users who have either SYSDBA or SYSOPER privileges, as shown here:

SQL> select * from v$pwfile_users;

USERNAME                   SYSDB SYSOP
————————– —– —–
SYS                        TRUE  TRUE
RJB                        TRUE  FALSE SYSTEM                     TRUE  FALSE

SQL>

{mospagebreak title=User Accounts}

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 use CREATE USER when you need a parameter not available in the GUI, such as QUOTA or PROFILE .

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

Parameter

Definition

username

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).

IDENTIFIED { BY password |EXTERNALLY | GLOBALLY AS ‘extname’ }

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).

DEFAULT TABLESPACE tablespace

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

TEMPORARY TABLESPACE tablespace

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.

PASSWORD EXPIRE

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

ACCOUNT {LOCK | UNLOCK}

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 the HR.EMPLOYEES table 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 user KELLYC is authenticated by the database with an initial password of KELLYC311 . 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 user KELLYC has 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 the UNLIMITED TABLESPACE privilege (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 the KELLYC account a quota of 250MB in the USERS tablespace:

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 the CREATE USER command. 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 the CREATE SESSION privilege or the CONNECT role (roles are discussed in detail in the “Creating, Assigning, and Maintaining Roles” section). The CONNECT role contains the CREATE SESSION privilege, along with other basic privileges, such as CREATE TABLE and ALTER SESSION . In the following example, we grant KELLYC the CONNECT privilege:

SQL> grant connect to kellyc;
Grant succeeded.

Now the user KELLYC has a quota on the USERS tablespace as well as the privileges to create objects in that tablespace.

All these options for CREATE USER are 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.

Google+ Comments

Google+ Comments