Oracle® Database Express Edition 2 Day DBA 10g Release 2 (10.2) Part Number B25107-01 |
|
|
View PDF |
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 his 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.
Table 7-1 lists three roles that are predefined in Oracle Database XE. You can grant these roles when you create a user with the Oracle Database XE graphical user interface.
Table 7-1 Oracle Database Express Edition Predefined Roles
Role Name | Description |
---|---|
Enables a user to connect to the database. Grant this role to any user or application that needs database access. |
|
Enables a user to create certain types of schema objects in his own schema. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges. For example, it grants the |
|
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 |
See Also:
"Administering User Privileges, Roles, and Profiles" in Oracle Database Security Guide for more information on privileges and roles
Oracle Database SQL 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 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
, SQL Command Line rejects the login attempt.
The following example illustrates how to connect to the database with the SYSDBA
privilege from 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-2 lists the operating system user groups whose member users can connect to the database with the SYSDBA
privilege.
Table 7-2 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-3 provides information about each of these login methods.
Table 7-3 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 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 SQL Command Line |
For routine administrative tasks like managing users. An administrator must first grant the |
"Logging In as a User with the DBA Role" |
Log in and connect to the database as |
SQL Command Line |
For high-level administrative tasks like starting up and shutting down the database, and changing the |
"Logging In and Connecting to the Database as SYSDBA" |
You can log in as user SYSTEM
with the Oracle Database XE graphical user interface or with SQL Command Line.
Logging In as User SYSTEM with the Oracle Database XE Graphical User Interface
To log in to the database as user SYSTEM
with the Oracle Database XE graphical user interface:
Access the Database Home Page, providing the user name SYSTEM
and the password for the SYSTEM
account.
See "Accessing the Database Home Page" for instructions.
Note:
You set theSYSTEM
account password upon installation (Windows) or configuration (Linux).Logging In as User SYSTEM with SQL Command Line
To log in to the database as user SYSTEM
with SQL Command Line:
Log in to the Oracle Database XE host computer with any user account.
Do one of the following:
On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
On Linux with Gnome: In the Applications menu, point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
A SQL Command Line command window opens.
Note:
You can also start SQL Command Line from a terminal session (Linux) or command window (Windows). See "Connecting Locally with SQL Command Line" for instructions.At the SQL Command Line prompt, enter the following command:
CONNECT SYSTEM/password
where password
is the SYSTEM
account password that you set during installation (Windows) or configuration (Linux)
Note:
These instructions establish a local connection to the database. See "Connecting Remotely with SQL Command Line" for information on connecting to the database remotely.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
only with SQL Command Line (SQL*Plus). You can do so either by supplying the SYS
user name and password, or by using operating system (OS) authentication.
Note:
The following instructions establish a local connection to the database. Do not attempt to connectAS
SYSDBA
remotely. See "About Local and Remote Connections" for more information.Connecting as SYSDBA with the SYS User Name and Password
To connect as SYSDBA
supplying the SYS
user name and password:
Log in to the Oracle Database XE host computer with any user account.
Do one of the following:
On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
On Linux with Gnome: In the Applications menu, point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
A SQL Command Line command window opens.
Note:
You can also start SQL Command Line from a terminal session (Linux) or command window (Windows). See "Connecting Locally with SQL Command Line" for instructions.At the SQL Command Line prompt, enter the following command:
CONNECT SYS/password AS SYSDBA
where password
is the password for the SYS
user account. You set the SYS
account password upon installation (Windows) or configuration (Linux).
Connecting as SYSDBA with OS Authentication
To connect as SYSDBA
using OS authentication:
Do one of the following:
On Windows: Log in to the Oracle Database XE host computer as a user who is a member of the ORA_DBA
user group. This is typically the user that installed Oracle Database XE.
On Linux: Log in to the Oracle Database XE host computer as a user who is a member of the dba
user group. This is typically the oracle
user.
Do one of the following:
On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
On Linux with Gnome: In the Applications menu, point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 10g Express Edition, and then select Run SQL Command Line.
A SQL Command Line command window opens.
Note:
You can also start SQL Command Line from a terminal session (Linux) or command window (Windows). See "Connecting Locally with SQL Command Line" for instructions.At the SQL Command Line prompt, enter the following command:
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 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 the Oracle Database XE graphical user interface or SQL Command Line (SQL*Plus) to manage database users. This section discusses using the Oracle Database XE graphical user interface, and contains the following topics:
See Also:
Oracle Database SQL Reference and Oracle Database Security Guide for information on managing users with SQL Command Line (SQL*Plus).You can view database users with the Oracle Database XE graphical user interface. After viewing a list of users, you can then select an individual user to alter or drop (delete).
To view database users:
Access the Database Home Page.
See "Accessing the Database Home Page" for instructions.
Click the Administration icon, and then click the Database Users icon.
If prompted for administrator credentials, enter the SYSTEM
user name and password or another administrator user name and password, and then click Login. See "About Administrative Accounts and Privileges" for more information.
The Manage Database Users page appears, showing database users.
The icon that represents each user account indicates account status. For example, the icon for user HR
indicates that the account is locked and its password is expired. See Table 7-5 for examples and descriptions of these icons. See also "Locking and Unlocking User Accounts" and "Expiring a User Password" for more information.
(Optional) In the Show list, select Internal Users, and then click Go.
The page redisplays, showing internal user accounts only.
The icons for internal accounts appear in gray (are dimmed), indicating that you can make only limited changes to these accounts. (You can modify only the account password for an internal account). See "Internal User Accounts" for more information. Internal user account icons also indicate account status, and the icon descriptions in Table 7-5 also apply to these icons.
Select Database Users in the Show list and then click Go to return to viewing database users (that are not internal users). You can also select All Users in the Show list to view both database and internal users.
(Optional) In the Search Username field, enter a search string, and then click Go.
The page displays only users with a user name that contains the search string. For example, if you use DB
as the search string, only the users DBSNMP
and XDB
are displayed.
Clear the search string and click Go to return to viewing all users in the category (database users, internal users, or all users).
(Optional) In the View list, select Details, and then click Go to view users as a list instead of as a collection of icons. The list displays user attributes.
To view details on a particular user, click the user's icon if the Icons view is displayed, or click the user's link (under the Username column) if the Details view is displayed.
The User page appears, with the user's information displayed.
You create users with the Create Database User page in the Oracle Database XE graphical user interface. Before creating a user, determine the following:
Whether or not you want to permit the user to create database objects in his own schema.
If so, on the Create Database User page, grant the RESOURCE
role or grant individual create object system privileges. See the following topics for more information:
"User Privileges and Roles" for details on the RESOURCE
role
"Administering User Privileges, Roles, and Profiles" in Oracle Database Security Guide for more information on system privileges
"Managing Database Objects" in Oracle Database Express Edition 2 Day Developer 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 the RESOURCE
role or 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:
Access the Database Home Page.
See "Accessing the Database Home Page" for instructions.
Click the Administration icon, and then click the Database Users icon.
If prompted for administrator credentials, enter the SYSTEM
user name and password or another administrator user name and password, and then click Login.
See "About Administrative Accounts and Privileges" for more information.
On the Manage Database Users page, click Create.
The Create Database User page appears.
Enter user information into text fields as follows (all fields are case-insensitive):
In the Username field, enter nick.
In the Password and Confirm Password fields, enter firesign.
Note that database passwords may not contain spaces, may not contain SQL keywords, and must use only characters in the database character set.
Grant all create object system privileges by clicking Check All at the lower right-hand corner of the User Privileges box.
Before finishing, note the following:
The CONNECT
and RESOURCE
roles are selected by default.
See "User Privileges and Roles" for information about these roles.
The DBA
role is by default not selected. This is correct for Nick, because you do not want to give him DBA privileges, which include the ability to create schema objects in other users' schemas, and to create other users.
The Account Status list defaults to Unlocked. This means that the user can log in with this account. Because you want Nick to be able to log in, you accept this default.
See "Locking and Unlocking User Accounts" for more information.
Click Create.
The Manage Database Users page reappears and displays a confirmation that the user was created.
You can use the Manage Database Users page to alter a user. Altering a user means changing some of his user 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 him 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 his password the next time that he logs in. See "Expiring a User Password" for more information.
See Also:
"Importing, Exporting, Loading, and Unloading Data" for information on how to export and import a schema.
Suppose user 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:
View the Manage Database Users page that contains the icon or link for user NICK
.
See "Viewing Users" for instructions.
Click the NICK icon or link.
The User page appears, with account information for user NICK
displayed.
Select the DBA check box to grant the DBA
role to Nick.
Click Alter User to save your changes.
The Manage Database Users page reappears and displays a confirmation message that the user was altered.
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:
View the Manage Database Users page that contains the icon or link for the user.
See "Viewing Users" for instructions.
Click the icon or link for the user.
The User page appears, with the user account information displayed.
Do one of the following:
To lock the account, select Locked from the Account Status list.
To unlock the account, select Unlocked from the Account Status list.
Click Alter User.
The Manage Database Users page reappears and displays a confirmation message. The large icon for the user now indicates whether the account is locked or unlocked by the presence or absence of a small lock. See "User Account Icons" for examples of these large icons.
When you expire a user password, the user is prompted to change his password the next time that he logs in. 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 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 user 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:
View the Manage Database Users page that contains the icon or link for user NICK
.
See "Viewing Users" for instructions.
Click the NICK icon or link.
The User page appears, with account information for user NICK
displayed.
Select the Expire Password check box, and then click Alter User.
The Manage Database Users page reappears and displays a confirmation message. The large icon for user NICK
now contains a small clock. See "User Account Icons" for examples of these large icons.
Note:
When you view the User page for a user whose password is expired, the Expire Password check box is replaced by the messagePassword Expired
.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 all his 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 "Importing, Exporting, Loading, and Unloading 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:
View the Manage Database Users page that contains the icon or link for NICK
.
See "Viewing Users" for instructions.
Click the NICK icon or link.
The User page appears, with account information for user NICK
displayed.
Click Drop.
The Confirm Drop User page appears.
Select the Cascade check box.
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.
Click Drop User.
A confirmation message is displayed.
This section provides reference information for managing user accounts. It covers the following topics:
Table 7-4 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-4 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. |
On the Manage Database Users page, the icons that represent the various user accounts change depending on account status. Table 7-5 describes these icons.
Table 7-5 User Account Icons
Icon | Meaning |
---|---|
The user account is open. Users can log in to this account and can use the current account password. |
|
The account is locked. Users cannot log in to this account until you unlock it. |
|
The account password is expired. The next time that a user logs in to this account, he must choose a new account password. |
|
The account is locked and the account password is expired. |