7 Administering User Accounts and Security

SYS and SYSTEM Users

The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.

• SYS

  This account can perform all administrative functions. All base (underlying) 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. 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.

  The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.

• SYSTEM

  This account can perform all administrative functions except the following:

  • Backup and recovery

  • Database upgrade

  While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.

SYSDBA and SYSOPER System Privileges

SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.

You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database using AS SYSDBA.

The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.

When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

Viewing Roles

You view roles on the Roles page of Oracle Enterprise Manager Database Control (Database Control).

To view roles: 

1. Go to the Database Home page, logging in with a user account that has privileges to manage roles. An example of such a user account is SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Security section, click Roles.

   The Roles page appears.

   
   Description of the illustration roles.gif
   
   

4. To view the details of a particular role, in the Select column, select the name of the role you want to view, and then click View.

   If you do not see the role, then it may be on another page. In this case, do one of the following:

   • Just above the list of roles, click Next to view the next page. Continue clicking Next until you see the desired role.

   • Use the Search area of the page to search for the desired role. In the Object Name field, enter the first few letters of the role, and then click Go.

     You can then select the role and click View.

   The View Role page appears. In this page, you can see all the privileges and roles granted to the selected role.

Example: Creating a Role

Suppose you want to create a role called APPDEV for application developers. Because application developers must be able to create, modify, and delete the schema objects that their applications use, you want the APPDEV role to include the system privileges shown in Table 7-2.

To create the APPDEV role: 

1. Go to the Roles page, as described in "Viewing Roles".

2. Click Create.

   The Create Role page appears.

3. In the Name field, enter APPDEV.

4. Click System Privileges to go to the System Privileges subpage.

   
   Description of the illustration create_role.gif
   
   

   The table of system privileges for this role contains no rows yet.

5. Click Edit List.

   The Modify System Privileges page appears.

6. In the Available System Privileges list, double-click privileges to add them to the Selected System Privileges list.

   The privileges to add are listed in Table 7-2.

   
   Description of the illustration modify_system_privs.gif
   
   

   Note:

   Double-clicking a privilege is a shortcut. You can also select a privilege and then click the Move button. To select multiple privileges, hold down the Shift key while selecting a range of privileges, or press the Ctrl key and select individual privileges, then click Move after you have selected the privileges.

7. Click OK.

   The System Privileges subpage returns, showing the system privileges that you selected. At this point, you could click Roles to assign other roles to the APPDEV role, or click Object Privileges to assign object privileges to the APPDEV role.

8. Click OK to return to the Roles page.

   The APPDEV role now appears in the table of database roles.

Example: Modifying a Role

Suppose your applications make use of Oracle Streams Advanced Queuing, and you determine that developers must be granted the roles AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE to develop and test their applications. You must edit the APPDEV role to grant it these two Advanced Queuing roles.

To modify the APPDEV role: 

1. Go to the Roles page, as described in "Viewing Roles".

2. In the Select column, click APPDEV role, and then click Edit.

   The Edit Role page appears.

3. Click Roles to navigate to the Roles subpage.

4. Click Edit List.

   The Modify Roles page appears.

5. In the Available Roles list, double-click the roles AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE to add them to the Selected Roles list.

6. Click OK.

   The Roles subpage returns, showing that the roles that you selected were granted to the APPDEV role.

7. Click Apply to save your changes.

   An update message appears, indicating that the role APPDEV was modified successfully.

Deleting a Role

Use caution when deleting a role, because Database Control deletes a role even if that role is currently granted to one or more users. Before deleting a role, you may want to determine if the role has any grantees. Dropping (deleting) a role automatically removes the privileges associated with that role from all users that had been granted the role.

To determine if a role has any grantees: 

1. Go to the Roles page as described in "Viewing Roles".

2. In the Select column, click the desired role.

   If you do not see the desired role, then it may be on another page. In this case, do one of the following:

   • Just above the list of roles, click Next to view the next page. Continue clicking Next until you see the desired role.

   • Use the Search area of the page to search for the desired role. In the Object Name field, enter the first few letters of the role, and then click Go.

   You can then select the role.

3. In the Actions list, select Show Grantees, and then click Go.

   A report appears, listing the users that are granted the selected role.

4. Click Cancel to return to the Roles page.

To delete a role: 

1. If you are not there, then go to the Roles page, as described in "Viewing Roles".

2. In the Select column, click the desired role, and then click Delete.

   A confirmation page appears.

3. Click Yes.

   A confirmation message indicates that the role has been deleted successfully.

Viewing User Accounts

You view user accounts on the Users page of Oracle Enterprise Manager Database Control (Database Control).

To view users: 

1. Go to the Database Home page, logging in with a user account that has privileges to manage users, for example, SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Security section, click Users.

   The Users page appears.

   
   Description of the illustration users_page.gif
   
   

4. To view the details of a particular user, in the Select column, click the user, and then click View.

   If you do not see the user, then it may be on another page. In this case, do one of the following:

   • Just above the list of users, click Next to view the next page. Continue clicking Next until you see the desired user.

   • Use the Search area of the page to search for the desired user. In the Object Name field, enter the first few letters of the user name, and then click Go.

   • Click a table column to change the sort order of the data in the table. For example, to list the users in reverse alphabetical order, click the UserName column heading.

   You can then select the user and click View.

   The View User page appears, and displays all user attributes.

Example: Creating a User Account

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 the database privileges and roles that he requires to build and test his applications. You also want to give Nick a 10 MB quota on his default tablespace so that he can create schema objects in that tablespace.

To create the user Nick: 

1. Go to the Users page, as described in "Viewing User Accounts".

2. On the Users page, click Create.

   The Create User page appears, displaying the General subpage.

   
   Description of the illustration create_user.gif
   
   

3. In the Name field, enter NICK.

4. In the Profile list, accept the value DEFAULT.

   This setting assigns the default password policy to user Nick.

   See "Setting the Database Password Policy".

5. Accept the default value Password in the Authentication list.

   For information about other more advanced methods to authenticate users, see Oracle Database Security Guide.

6. In the Enter Password and Confirm Password fields, enter a password that is secure.

   Create a password that is secure. See Oracle Database Security Guide for more information.

7. Do not select Expire password now. If the account status is set to expired, then the user or the database administrator must change the password before the user can log in to the database.

8. (Optional) Next to the Default Tablespace field, click the flashlight icon, select the USERS tablespace, and then click Select.

   All schema objects that Nick creates will then be created in the USERS tablespace unless he specifies otherwise. If you leave the Default Tablespace field blank, then Nick is assigned the default tablespace for the database, which is USERS in a newly installed database. For more information about the USERS tablespace, see "About Tablespaces".

9. (Optional) Next to the Temporary Tablespace field, click the flashlight icon, select the TEMP tablespace, and then click Select.

   If you leave the Temporary Tablespace field blank, then Nick is assigned the default temporary tablespace for the database, which is TEMP in a newly installed database. For more information about the TEMP tablespace, see "About Tablespaces".

10. For the Status option, accept the default selection of Unlocked.

    You can later lock the user account to prevent users from logging in with it. To temporarily deny access to a user account, locking the user account is preferable to deleting it, because deleting it also deletes all schema objects owned by the user.

11. Grant roles, system privileges, and object privileges to the user, as described in "Example: Granting Privileges and Roles to a User Account".

    Note:

    Do not click OK in Step 13 of "Example: Granting Privileges and Roles to a User Account". Instead, skip that step and continue with Step 12 in this procedure.

12. Assign a 10 MB quota on the USERS tablespace, as described in "Example: Assigning a Tablespace Quota to a User Account".

13. If you did not click OK while assigning the tablespace quota (previous step), then click OK now to create the user.

Creating a New User Account by Duplicating an Existing User Account

To create a user account that is similar in attributes to an existing user account, you can duplicate the existing user account.

To create a new user account by duplicating an existing user account: 

1. Go to the Users page, as described in "Viewing User Accounts".

2. In the Select column, click the user to duplicate.

3. In the Actions list, select Create Like, and then click Go.

   The Create User page appears. This page displays a new user with the same attributes as the duplicated user.

4. Enter a user name and password, modify the user attributes or privileges if desired, and then click OK to save the new user.

The Actions list also provides shortcuts for other actions, and provides a way to display the SQL command used to create a user.

Example: Granting Privileges and Roles to a User Account

Suppose you are creating or modifying a user account named Nick. Because Nick is a database application developer, you want to grant him the APPDEV role, which enables him to create database objects in his own schema. (You created the APPDEV role in "Example: Creating a Role".) Because you want Nick to be able to create tables and views in other schemas besides his own, you want to grant him the CREATE ANY TABLE and CREATE ANY VIEW system privileges. In addition, because he is developing a human resources application, you want him to be able to view the tables in the hr sample schema and use them as examples. You therefore want to grant him the SELECT object privilege on those tables. Finally, you want Nick to be able to log in to Database Control so that he can use the graphical user interface to create and manage his database objects. You therefore want to grant him the SELECT_CATALOG_ROLE role. The following table summarizes the privileges and roles to grant to Nick.

The following example assumes that you are in the process of creating the user account for Nick or editing the account. Either you have accessed the Create User page and have entered all required fields on the General subpage (see "Example: Creating a User Account"), or you have accessed the Edit User page for Nick (see "Example: Modifying a User Account"). The example also assumes that you have not yet granted any privileges or roles to Nick.

To grant privileges and roles to the user Nick: 

1. Toward the top of the Create User or Edit User page, click Roles to display the Roles subpage.

   The Roles subpage shows that the CONNECT role is assigned to Nick. Database Control automatically assigns this role to all users that you create. (The selected Default check box indicates that the CONNECT role is a default role for Nick, which means that it is automatically enabled whenever Nick logs in.)

2. Click Edit List.

   The Modify Roles page appears.

   
   Description of the illustration modify_roles.gif
   
   

3. In the Available Roles list, locate the APPDEV role, and double-click it to add it to the Selected Roles list. Do the same with the SELECT_CATALOG_ROLE role and then click OK.

   The Create User or Edit User page returns, showing that the CONNECT, APPDEV, and SELECT_CATALOG_ROLE roles are granted to Nick.

   Note:

   Double-clicking a role is a shortcut. You can also select the role and then click the Move button. To select multiple privileges, hold down the Shift key while selecting a range of privileges, or press the Ctrl key and select individual privileges.

4. Toward the top of the page, click System Privileges to select the System Privileges subpage.

5. Click Edit List.

   The Modify System Privileges page appears.

6. In the Available System Privileges list, scroll to locate the CREATE ANY TABLE and CREATE ANY VIEW privileges, double-click each to add them to the Selected System Privileges list, and then click OK.

   The Create User or Edit User page returns, showing the newly added system privileges.

   Note:

   To revoke a role, double-click it in the Selected Roles list on the Modify Roles page. To revoke a system privilege, double-click it in the Selected System Privileges list on the Modify System Privileges page.

7. Toward the top of the page, click Object Privileges to select the Object Privileges subpage.

8. In the Select Object Type list, select Table and then click Add.

   The Add Table Object Privileges page appears.

   
   Description of the illustration add_object_privileges.gif
   
   

9. Click the flashlight icon next to the Select Table Objects list.

   The Select Table Objects dialog box appears.

10. In the Schema list, select HR, and then click Go.

    All tables in the hr schema are displayed.

    
    Description of the illustration select_table_objects.gif
    
    

11. Click Select All, and then click the Select button.

    The Select Table Objects dialog box closes, and the names of all tables in the hr schema appear in the Select Table Objects field on the Add Table Object Privileges page.

12. In the Available Privileges list, double-click the SELECT privilege to move it to the Selected Privileges list, and then click OK.

    The Create User or Edit User page returns, showing that the SELECT object privilege for all hr tables is granted to user Nick.

    Note:

    To revoke an object privilege, select it on the Create User or Edit User page (Object Privileges subpage), and then click Delete.

13. Do one of the following to save the role and privilege grants:

    • If you are creating a user account, then click OK to save the new user account.

    • If you are modifying a user account, then click Apply to save the changes for the user account.

Example: Assigning a Tablespace Quota to a User Account

Suppose you are creating or modifying a user account named Nick. You want to assign Nick a space usage quota of 10 MB on his default tablespace.

You must assign Nick a tablespace quota on his default tablespace before he can create objects in that tablespace. (This is also true for any other tablespace in which Nick wants to create objects.) After a quota is assigned to Nick for a particular tablespace, the total space used by all of his objects in that tablespace cannot exceed the quota. You can also assign a quota of UNLIMITED.

The following example assumes that you are in the process of creating the user account for Nick or editing the account. Either you have accessed the Create User page and have entered all required fields on the General subpage (see "Example: Creating a User Account"), or you have accessed the Edit User page for Nick (see "Example: Modifying a User Account"). The example also assumes that Nick has not yet been assigned a quota on any tablespaces.

To assign a tablespace quota to user Nick: 

1. Toward the top of the Create User or Edit User page, select the Quotas subpage.

   The Quotas subpage appears, showing that user Nick does not have a quota assigned on any tablespace.

   
   Description of the illustration quotas.gif
   
   

2. In the Quota column for tablespace USERS, select Value from the list.

3. In the Value column for tablespace USERS, enter 10.

4. Do one of the following to save the new quota assignment:

   • If you are creating a user account, then click OK to save the new user account.

   • If you are modifying a user account, then click Apply to save changes for the user account.

Example: Modifying a User Account

Suppose you want to remove the quota limitations for the user Nick on his default tablespace, USERS. To do so, you must modify his user account.

To modify the user Nick: 

1. Go to the Users page, as described in "Viewing User Accounts".

2. In the Select column, select the user account Nick, and then click Edit.

   If you do not see user Nick, then he may be on another page. In this case, do one of the following:

   • Just above the list of user accounts, click Next to view the next page. Continue clicking Next until you see the user account for Nick.

   • Use the Search area of the page to search for his account. In the Object Name field, enter the letters NI, and then click Go.

   You can then select the user account for Nick and click Edit.

   The Edit User page appears, and displays the general attributes for Nick.

3. Toward the top of the page, select the Quotas subpage.

4. In the Quota column for tablespace USERS, select Unlimited from the list, and then click Apply.

   A message appears, indicating that user Nick was modified successfully.

Locking and Unlocking User Accounts

To temporarily deny access to the database for a particular user account, you can lock the user account. If the user then attempts to connect, then the database displays an error message and does not allow the connection. You can unlock the user account when you want to permit database access again for that user.

To lock or unlock a user account: 

1. Go to the Users page, as described in "Viewing User Accounts".

2. In the Select column, click the desired user account.

   If you do not see the desired user account, then it may be on another page. In this case, use the Next button to view additional pages or use the Search area of the page to search for the desired user account.

3. Do one of the following:

   • To lock the account, select Lock User from the Actions list, and then click Go.

   • To unlock the account, select Unlock User from the Actions list, and then click Go.

   A confirmation message appears.

4. Click Yes.

Expiring a User Password

When you expire a user password, the user is prompted to change his or her password the next time that user 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.

  Note:

  You can automate the automatic expiring of user passwords after a certain interval. See "Setting the Database Password Policy".

• A user has forgotten his or her password.

  In this third case, you modify 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.

To expire a user password: 

1. Go to the Users page, as described in "Viewing User Accounts".

2. In the Select column, click the desired user account.

   If you do not see the desired user account, then it may be on another page. In this case, do one of the following:

   • Just above the list of user accounts, click Next to view the next page. Continue clicking Next until you see the desired user account.

   • Use the Search area of the page to search for the desired user account. In the Object Name field, enter the first few letters of the user account name, and then click Go.

   You can then select the user account.

3. To expire the passwords for all users, select the Multiple option, then click Select All.

4. Select Expire Password from the Actions list, and then click Go.

   A confirmation message appears.

5. Click Yes to complete the task.

Example: Deleting a User Account

Suppose user Nick has moved to another department. Because it is no longer necessary for him to have access to the database, you want to delete his user account.

You must use caution when deciding to deleting a user account, because this action also deletes all schema objects owned by the user. To prevent a user from logging in to the database while keeping the schema objects intact, lock the user account instead. See "Locking and Unlocking User Accounts".

To delete user Nick: 

1. Go to the Users page, as described in "Viewing User Accounts".

2. In the Select column, select the user account Nick, and then click Delete.

   If you do not see the user account Nick, then it may be on another page. In this case, do one of the following:

   • Just above the list of user accounts, click Next to view the next page. Continue clicking Next until you see the user account for Nick.

   • Use the Search area of the page to search for the user account. In the Object Name field, enter the letters NI, and then click Go.

   You can then select the user account for Nick and click Delete.

   A confirmation page appears.

3. Click Yes to confirm the deletion of the user account.

About Password Policies

When you create a user account, a default password policy is assigned to that user account. The default password policy for a newly installed database includes these directives:

• The password for the user account expires automatically in 180 days.

• The user account is locked 7 days after password expiration.

• The user account is locked for 1 day after 10 failed login attempts.

The default password policy is assigned to user accounts through a database object called a profile. Each user account is assigned a profile, and the profile has several attributes that describe a password policy. The database comes with a default profile (named DEFAULT), and unless you specify otherwise when you create a user account, the default profile is assigned to the user account.

For better database security, you may want to impose a more strict password policy. For example, you may want passwords to expire every 70 days, and you may want to lock user accounts after three failed login attempts. (A failed login attempt for a user account occurs when a user enters an incorrect password for the account.) You may also want to require that passwords be complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords. For example, you might specify that passwords must contain at least one number and one punctuation mark.

You change the password policy for every user account in the database by modifying the password-related attributes of the DEFAULT profile.

Modifying the Default Password Policy

You modify the default password policy for every database user account by modifying the password-related attributes of the profile named DEFAULT.

To modify the default password policy: 

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Security section, click Profiles.

   The Profiles page appears.

4. In the Select column, select the profile named DEFAULT, and then click Edit.

   The Edit Profile page appears.

5. Toward the top of the page, select the Password subpage.

   
   Description of the illustration edit_profile.gif
   
   

6. Change field values as required. Click the flashlight icon next to each field to view a list of choices. (Click Help on this page for a description of the fields.)

7. Click Apply to save your changes.