Adding users in Oracle databases

Creation of new users in Oracle or adding users to an existing database comprises many steps out of which the most important is specifying values for several parameters in the database. The question is what steps should be taken by the DBA to perform this function and what are the different types of users which exist in Database?

In a database, there are various types of users which have different responsibilities and rights. The main categories are: Two user accounts are automatically created with the database and granted the DBA role. These two user accounts are:

• SYS (initial password: CHANGE_ON_INSTALL)
• SYSTEM (initial password: MANAGER)

When new users in Oracle are added, some rights are assigned to that user so that actions are performed on the database either directly or through roles. There are two types of privileges given to a user:

• System privileges through which the user can manage the performance of database actions.
• Object privileges which allow access to objects, i.e. tables, table columns, indexes, synonyms, procedures, etc.

Various methods to add new users in a database are:

CREATE USER user_name IDENTIFIED BY password;

CREATE USER uwclass IDENTIFIED BY uwclass;

CREATE USER user IDENTIFIED {BY password |
EXTERNALLY}

[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[ { QUOTA {n [K|M] | UNLIMITED} ON
tablespace } [, ... ] ]
[PROFILE profile]
} [ ... ];

• user - user name.
• IDENTIFIED BY password | EXTERNALLY - EXTERNALLY is identified by the operating system outside of the database. The OS_AUTHENT_PREFIX prefix in the parameter file must be set for this option.
• DEFAULT TABLESPACE tablespace_name - all objects created by this user are placed into this tablespace unless user specifically specifies otherwise. The SYSTEM tablespace is the default if not specified.
• TEMPORARY TABLESPACE tablespace_name - storage of intermediate results. The SYSTEM tablespace is the default if not specified.
• QUOTA n [K|M] | UNLIMITED ON tablespace_name - give a user permission to create objects in a tablespace using the QUOTA clause. The QUOTA clause applies a quota of space for a user in a tablespace allowing a user to create objects within that quota of tablespace space. The QUOTE clause effectively gives a use permission to create objects in a tablespace. The role RESOURCE automatically grants unlimited space in a tablespace.

To provide system privileges to the user, the DBA will perform the following:

GRANT {system privilege [, ... ] } TO { { user | role | PUBLIC }
[, ... ] } [WITH ADMIN OPTION];

All users in Oracle are required to have the CREATE SESSION privilege in order to access the database. Each user must be granted the CREATE SESSION privilege either directly or through a role.

System privileges can be granted by one user to other users when the user granting the privilege has the WITH ADMIN OPTION.

Object privileges allow a user to perform a specified action on a specific object. Other users can access user-owned objects by preceding the object name with the user name (username.object). Object privileges extend down to table columns.

GRANT {object privilege [, ... ] | ALL [PRIVILEGES] } ON [schema.] object
TO { { user | role | PUBLIC } [, ... ] }
[WITH GRANT OPTION];

GRANT {object privilege [, ... ] | ALL [PRIVILEGES] } [(column [, ... ])] ON [schema.] object
TO { { user | role | PUBLIC } [, ... ] }
[WITH GRANT OPTION];

Only INSERT, UPDATE and REFERENCES privileges can be granted at the column level.

To create users in Oracle whose authentication is done by the operating system or by password files, the DBA will use:

Method 1:

Step 1. Set the initSID.ora parameters as:

remote_os_authent=TRUE os_authent_prefix = "OPS$"

Step 2. Generate a new spfile

CREATE spfile FROM pfile='initorabase.ora';

3. Add the following to the sqlnet.ora

sqlnet.authentication_services = (NTS)

Method 2:

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

CREATE USER ops$oracle IDENTIFIED EXTERNALLY;

GRANT create session TO ops$oracle;

Step 2: Create a user in the operating system named oracle if one does not already exist.

Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type 'sqlplus' (without the single quotes).

Method 3:

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

CREATE USER "PC100USER" IDENTIFIED EXTERNALLY;

where PC100 is the name of the client computer. Then

GRANT CREATE SESSION TO "PC100USER";

Step 2: Create a user in Windows named USER.

Step 3: Log on Windows as USER and go to the C: command line.

The following methods for authenticating database administrators replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle:

• operating system authentication
• password file

Depending on whether you wish to administer your database locally on the same machine where the database resides or to administer many different databases from a single remote client, the DBA can choose between operating system authentication or password files to authenticate database administrators.

On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group or giving that OS username a special process right.

The database uses password files to keep track of database usernames that have been granted administrator privileges.

When the DBA grants SYSDBA or SYSOPER privileges to users in Oracle then that user's name and privilege information is added to a password file. If the server does not have an EXCLUSIVE password file, that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED then the DBA receives an error message if these privileges are attempted to be granted.

A user's name only remains in the password file while that user has at least one of these two privileges. When the DBA revoke the last of these privileges from a user, that user is removed from the password file. To create a password file and add new users in Oracle to it,

1. Follow the instructions for creating a password file.
2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
3. Connect with SYSDBA privileges as shown in the following example:
4. CONNECT SYS/change_on_install AS SYSDBA
5. Start up the instance and create the database if necessary, or mount and open an existing database.
6. Create users as necessary. Grant SYSOPER or SYSDBA privileges to DBA and other users as appropriate.
7. These users in Oracle are now added to the password file and can connect to the database as SYSOPER or SYSDBA with a username and password (instead of using SYS). The use of a password file does not prevent OS authenticated users in Oracle from connecting if they meet the criteria for OS authentication.

Return to Oracle DBA