Users access Oracle Database Express Edition through database user accounts. Some of these accounts are automatically created administrative accounts—accounts with database administration privileges. You log in to these administrative accounts to create and manage other user accounts, maintain database security, and perform other database administration tasks.
This section contains the following topics:
A user account is identified by a user name and defines the user's attributes, including the following:
Password for database authentication
Privileges and roles
Default tablespace for database objects
Default temporary tablespace for query processing work space
When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user. For example, HR.EMPLOYEES
refers to the table named EMPLOYEES
in the HR
schema. (The EMPLOYEES
table is owned by HR
.) The terms database object and schema object are used interchangeably.
When you drop (delete) a user, you must either first drop all the user's schema objects, or use the cascade feature of the drop operation, which simultaneously drops a user and all of that user's schema objects.
This section contains these topics:
See Also:
"Predefined User Accounts"When creating a user, you grant privileges to enable the user to connect to the database, to run queries and make updates, and to create schema objects. There are two main types of user privileges:
System privileges—A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tables and to delete the rows of any table in a database are system privileges.
Object privileges—An object privilege is a right to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privilege to delete rows from the DEPARTMENTS
table is an example of an object privilege.
Managing and controlling privileges is made easier by using roles, which are named groups of related privileges. You create roles, grant system and object privileges to the roles, and then grant roles to users. Unlike schema objects, roles are not contained in any schema.
Oracle Database Express Edition comes with some predefined roles:
The DBA
role enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating and dropping schema objects in other users' schemas; and more. It grants all system privileges, but does not include the privileges to start up or shut down the database. It is by default granted to user SYSTEM. You should be very cautious about assigning the DBA role to any other database users.
Use of the CONNECT
and RESOURCE
roles is discouraged. Instead, grant only those privileges that the specific user will need. For example:
grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, - CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, - CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, - CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE - to chris;
See Also:
Oracle Database Security Guide for more information on privileges and roles
Oracle Database SQL Language Reference for tables of system privileges, object privileges, and predefined roles.
Certain user accounts are created automatically for database administration. Examples are SYS
and SYSTEM
. Other accounts are automatically created just so that individual Oracle Database XE features or products can have their own schemas. An example is the CTXSYS
account, which is used by the Oracle Text product. Oracle Text is used to index the Oracle Database XE online Help. The Help index is stored in the CTXSYS
schema in the database.
These automatically created accounts are called internal user accounts, and their schemas are called internal schemas.
The only internal accounts that you may log in with are the SYS
and SYSTEM
accounts, although it is recommended that you avoid logging in with the SYS
account. Do not attempt to log in with other internal accounts. See "The SYS and SYSTEM Users" for more information.
Administrative accounts and privileges enable you to perform administrative functions like managing users, managing database memory, and starting up and shutting down the database.
This section contains the following topics:
The following administrative user accounts are automatically created when you install Oracle Database Express Edition (Oracle Database XE). They are both created with the password that you supplied upon installation (Windows operating systems) or configuration (Linux operating systems).
SYSTEM
This is the user account that you log in with to perform all administrative functions other than starting up and shutting down the database.
SYS
All base tables and views for the database data dictionary are stored in the SYS
schema. These base tables and views are critical for the operation of Oracle Database XE. To maintain the integrity of the data dictionary, tables in the SYS
schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS
schema.
There is typically no reason to log in as user SYS
. User SYSTEM
is preferred for all administrative tasks except starting up and shutting down. See "Starting Up and Shutting Down" for more information.
SYSDBA
is a system privilege that is assigned only to user SYS
. It enables SYS
to perform high-level administrative tasks such as starting up and shutting down the database.
Although under typical circumstances it is not necessary to log in to the database as user SYS
, if you want to log in as SYS
with the SQL Command Line (SQL*Plus), you must connect to the database "AS
SYSDBA
." Connecting AS
SYSDBA
invokes the SYSDBA
privilege. If you omit the AS
SYSDBA
clause when logging in as user SYS
, the SQL Command Line rejects the login attempt.
The following example illustrates how to connect to the database with the SYSDBA
privilege from the SQL Command Line:
SQL > connect sys/password as sysdba
password
is the password for the SYS
user account.
Operating system authentication (OS authentication) is a way of using operating system login credentials to authenticate database users. One aspect of OS authentication can be used to authenticate database administrators. If you log in to the Oracle Database XE host computer with a user name that is in a special operating system user group, you are then permitted to connect to the database with the SYSDBA
privilege. An administrator who is authenticated through OS authentication does not need to know the SYS
or SYSTEM
account password.
OS authentication is needed because there must be a way to identify administrative users even if the database is shut down. A user authenticated in this way can then start up the database. (See "Starting Up and Shutting Down" for more information.)
Table 7-1 lists the operating system user groups whose member users can connect to the database with the SYSDBA
privilege.
Table 7-1 Operating System User Groups for OS Authentication
Platform | Operating System User Group Name |
---|---|
Linux |
|
Windows |
|
On each platform, if the OS authentication user group does not already exist, it is automatically created when you install Oracle Database XE. In addition, upon installation on the Linux platform, the user account oracle
is automatically created and placed in the dba
group. Upon installation on the Windows platform, the user performing the installation is automatically added to the ORA_DBA
group. On both platforms, you can add other host users to the OS authentication user group to enable them to connect to the database with the SYSDBA
privilege.
There are three ways to log in to Oracle Database Express Edition (Oracle Database XE) to perform administrative tasks:
Log in as user SYSTEM
Log in as a user who has been granted the DBA
role
Log in and connect to the database as SYSDBA
Table 7-2 provides information about each of these login methods.
Table 7-2 Database Administrator Login Methods
Login Method | Permitted In | Notes | See |
---|---|---|---|
Log in to the database as user |
The Oracle Database XE graphical user interface and the SQL Command Line |
For routine administrative tasks like managing memory and managing users. You must supply the password for the |
|
Log in to the database as a user who has been granted the |
The Oracle Database XE graphical user interface and the SQL Command Line |
For routine administrative tasks like managing users. An administrator must first grant the |
|
Log in and connect to the database as |
the SQL Command Line |
For high-level administrative tasks like starting up and shutting down the database, and changing the |
You can log in as user SYSTEM
using either of the following methods:
Using SQL Developer, open a database connection to the SYSTEM user.
Using the SQL Command Line, enter the following statement:
SQL> CONNECT SYSTEM/<password>;
The procedures for logging in as a user who has been granted the DBA
role are the same as those for logging in as user SYSTEM
, with the following exceptions:
When logging in, you must supply the user name and password for this user account.
An administrator must have previously logged in and granted the DBA
role to this user.
See "User Privileges and Roles" for more information.
You can log in and connect as SYSDBA
using either of the following methods:
Using SQL Developer, open a database connection to the SYS
user AS SYSDBA
.
Using the SQL Command Line, enter one the following statements.
To use database authentication:
SQL> CONNECT SYS/<password> AS SYSDBA;
To use operating system (OS) authentication:
SQL> CONNECT / AS SYSDBA;
The slash (/) indicates that the database should authenticate you with operating system (OS) authentication. Remember that when you connect with OS authentication, you are effectively logging in to the database as user SYS
.
To change the password for user SYS
or SYSTEM
:
Using the SQL Command Line, connect to the database as SYSDBA
.
See "Logging In and Connecting to the Database as SYSDBA" for instructions.
Enter one of the following commands:
ALTER USER SYS IDENTIFIED BY newpassword; ALTER USER SYSTEM IDENTIFIED BY newpassword;
where newpassword
is the desired new password.
You can use SQL Developer or the SQL Command Line (SQL*Plus) to manage database users. This section discusses using SQL Developer, and contains the following topics:
To perform these operations, in the SQL Developer Connections navigator, open a connection
In the SQL Developer Connections navigator, open a connection to the SYSTEM
user.
In the nodes under this SYSTEM
connection, expand Other Users.
This displays nodes for all database users, including several Oracle-supplied internal users. The Connections navigator hierarchy may look like this:
Connections
. . .
SYSTEM
Views
Editioning Views
. . .
Other Users
ANONYMOUS
APEX_040000
APEX_PUBLIC_USER
APQQOSSYS
CHRIS
CTXSYS
. . .
HR
MDSYS
To create a new database user, right-click the Other Users node in the Connections navigator and select Create User.
To perform an action on a database user, right-click that user in the hierarchy and select the appropriate command (Edit User or Drop User).
See Also:
Oracle Database SQL Language Reference and Oracle Database Security Guide for information on managing users with the SQL Command Line (SQL*Plus).To create a new database user, right-click the Other Users node in the SQL Developer Connections navigator and select Create User. Before creating a user, determine the following:
Whether or not you want to permit the user to create database objects in that user's own schema.
If so, on the Create Database User page, grant individual session-related and create object system privileges. See the following topics for more information:
"User Privileges and Roles" for details on privileges and roles
"Configuring Privilege and Role Authorization" in Oracle Database Security Guide for more information on system privileges
"Creating and Managing Schema Objects" in Oracle Database Express Edition 2 Day Developer's Guide for more information on database objects
Whether or not you want to grant the user DBA privileges.
If so, on the Create Database User page, grant the DBA
role. See "User Privileges and Roles" for details on the DBA
role.
Because DBA privileges include the ability to create database objects in any schema, if you grant the DBA
role, you do not need to grant individual create object system privileges.
Whether or not to create the user with an expired password.
When you do this, the password that you assign the user is used only for the user's first login. Upon first login, the user is prompted to select a new password.
See Also:
"About User Accounts"Suppose you want to create a user account for a database application developer named Nick. Because Nick is a developer, you want to grant him all CREATE
system privileges so that he can create the schema objects that his applications require. In addition, you want to create his account with the password firesign.
To create the user Nick
:
Right-click the Other Users node in the SQL Developer Connections navigator and select Create User.
In the Create/Edit User dialog box, for the User tab, enter the information shown in the following figure:
User Name: NICK
New Password and Confirm Password: Desired password for the user.
Password expired (user must change): Select or not, as desired. (It is not selected in the figure.)
Account is Locked: Select or not, as desired. (It is not selected in the figure.)
Edition Enabled: Select or not, as desired. (It is not selected in the figure.)
Default Tablespace: USERS
Temporary Tablespace: TEMP
In the Create/Edit User dialog box, click the System Privileges tab, and under Granted select the following privileges because you want to be sure that NICK
will have them):
ALTER SESSION
CREATE SESSION
CREATE DATABASE LINK
CREATE MATERIALIZED VIEW
CREATE PROCEDURE
CREATE PUBLIC SYNONYM
CREATE ROLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
UNLIMITED TABLESPACE
In the Create/Edit User dialog box, click Apply, then click Close.
You can use the Manage Database Users page to alter a user. Altering a user means changing some of that user's attributes. You can change all user attributes except the user name, default tablespace, and temporary tablespace. If you want to change the user name, you must drop the user and re-create that user with a different name. (Before you drop the user, ensure that the user's schema objects are either no longer needed or are backed up (for example, by exporting them). See "Dropping Users" for more information.)
One of the attributes that you can alter is the user password. If you do this, you must either communicate the new password to the user, or request the new password from the user and then enter it. An easier and more secure way to cause a password change is to expire the password. When you expire a password, the user is prompted to change the password at the next login. See "Expiring a User Password" for more information.
See Also:
"Exporting and Importing Metadata and Data" for information on how to export and import a schema.
Suppose Nick is promoted to senior developer, and he has shown an interest in helping with routine database administration tasks. You decide to grant the DBA
role to Nick.
To alter Nick's user account:
In the SQL Developer Connections navigator, expand the SYSTEM
connection and right-click the Other Users node.
Right-click NICK and select Edit User.
In the Create/Edit User dialog box, click the Roles tab.
Under Granted, select DBA.
In the Create/Edit User dialog box, click Apply, then click Close.
To temporarily deny access to the database for a particular user, you can lock the user account. If the user then attempts to connect, the database displays an error message and disallows the connection. You can unlock the user account when you want to allow database access again for that user.
Note:
Many internal user accounts are locked (or both expired and locked). You should not attempt to log in with these locked user accounts. See "Internal User Accounts" for more information.The HR
user account, which contains a sample schema, is initially expired and locked. You must log in as SYSTEM
, unlock the account, and assign a password before you can log in as HR
.
To lock or unlock a user account:
In the SQL Developer Connections navigator, expand the SYSTEM
connection and right-click the Other Users node.
Right-click the desired user and select Edit User.
In the Create/Edit User dialog box, in the User tab, select or deselect Account is Locked: selecting (checking) causes the account to be locked; deselecting (unchecking) causes the account to be unlocked.
In the Create/Edit User dialog box, click Apply, then click Close.
When you expire a user password, the user is prompted to change the password at the next login. Reasons to expire a password include the following:
A user password becomes compromised.
You have a security policy in place that requires users to change their passwords on a regular basis.
A user has forgotten his or her password.
In this case, you alter the user account, assign a new temporary password, and expire the password. The user then logs in with the temporary password and is prompted to choose a new password.
See "Altering Users" for more information.
Suppose Nick's password becomes compromised, and you want to assign him a new one. The easiest way to do this is to expire his current password. The next time that Nick logs in with the compromised password, he is prompted to choose a new password.
To expire Nick's password:
In the SQL Developer Connections navigator, expand the SYSTEM
connection and right-click the Other Users node.
Right-click NICK and select Edit User.
In the Create/Edit User dialog box, in the User tab, select (check) Password expired (user must change next login).
In the Create/Edit User dialog box, click Apply, then click Close.
Dropping a user removes the user from the database. Before you can drop a user, you must first drop all the user's schema objects. Or, you can use the cascade feature of the drop operation, which simultaneously drops a user and also that user's schema objects. The following are two alternatives to dropping a user and losing all the user's schema objects:
To temporarily deny access to the database for a particular user while preserving the user's schema objects, you can lock the user account. See "Locking and Unlocking User Accounts" for more information.
To drop a user but retain the data from the user's tables, export the tables first. See "Exporting and Importing Metadata and Data" for instructions.
Caution:
Under no circumstances should you attempt to drop theSYS
or SYSTEM
users, or any other internal user accounts. Doing so could cause Oracle Database XE to malfunction.Suppose Nick's project is canceled and Nick takes a position in another department. You want to drop the user NICK
and all associated schema objects.
To drop user NICK
and all his owned schema objects:
In the SQL Developer Connections navigator, expand the SYSTEM
connection and right-click the Other Users node.
Right-click NICK and select Drop User.
In the Drop User dialog box, (check) Cascade
This indicates that you want to drop the user's schema objects also. If the user has schema objects and you do not select this option, you receive an error message if you attempt to complete the drop operation.
In the Drop User dialog box, click Apply.
This section provides reference information for managing user accounts. It covers the following topics:
Table 7-3 lists the Oracle Database XE predefined user accounts. Many of these accounts are internal accounts. You must not drop internal accounts, and with the exception of the accounts SYS
and SYSTEM
, you must not attempt to log in with an internal account.
Table 7-3 Oracle Database Express Edition Predefined User Accounts
User Account Name | Purpose |
---|---|
|
Internal. Used for anonymous HTTP access to the database. Required by the Oracle Database XE graphical user interface. This account must remain unlocked. The account password is set upon installation (Windows) or configuration (Linux). For optimal security, avoid changing the password for this account. |
|
Internal. |
|
Internal. |
|
Internal. |
|
Internal. |
|
Internal. |
|
For the |
|
Internal. |
|
Internal. |
|
Owns the data dictionary base tables and views. The account password is set upon installation (Windows) or configuration (Linux). |
|
Log in with this account to perform routine database administration. The account password is set upon installation (Windows) or configuration (Linux). |
|
Internal. |
|
Internal. |