Oracle 10G XE User Management 101

If you’re a DBA, one of your basic tasks involves managing users: dropping, adding, and changing permissions. Even if you aren’t a DBA, if you use a database, you may find it helpful to know the tasks that a DBA performs. This article explains the basics for how to perform these tasks in Oracle 10G XE.

Introduction

In some of the applications we have seen in our recent tutorials an Anonymous User authentication has been used. We have also seen how to access the HR database with the Username/Password pairs. One of the realities of any database management system is the existence of multiple users with varied permissions which may have to be changed, dropped, new ones created or altered. In this tutorial we will see how users are managed in the Oracle 10G XE using its graphical interface. Even if you are not a DBA, it will be useful to know some of these basic tasks that a DBA performs. It’s not comprehensive but shows you some of the basics.

Viewing Users of the Database

User Identification

Oracle 10G XE has just one database with multiple users who can obtain the privilege to create their own objects, which is identified by their schema, the user schema. The user is identified by the following attributes, or properties:

  • Username and password for database authentication.
  • Privileges and roles.
  • Default tablespace for database objects he creates called USERS.
  • Default temporary workspace for his temporary processing called TEMP.

Viewing the Users

The first thing you need to know is how to identify who the users are. Login to the HR database by accessing the home page and insert the credentials as shown in the next picture.

Click on the administration icon to open the administration panel as shown in the next picture.

It has five child nodes, one of which is the Database Users. Now click on the Database Users icon to open the next window shown in the following picture.

Now if you enter HR, HR you will get the message that the user does not have the admin role or that the password is wrong as shown in the following picture.

When you enter SYSTEM for the Username and type in the Password (assuming you remember the password which you must have noted down when you installed Oracle10G XE) you can access the Manage Database Users panel as shown in the next picture. You will see the HR database users when the SHOW drop-down is indicating Database Users.

In order to see all the users, not only the HR database users, pick All Users from the SHOW drop-down as shown in the next picture.

Now click on the Go button, or place your cursor in Search Username and hit the enter key on your keyboard to see the screen shown in the next picture. Now you can see all your colleagues here, including the HR and the Anonymous User. Each of the icons has a little lock and a clock which shows the object is locked with some expiry date set for the password. The All Users also shows that most of the users are in gray. These are the internal user accounts on which there are only a few limited privileges enabled.

The Oracle user interface is more like a .Net Web Wizard control; you can make changes from the control to see the various pages, but the URL address does not change.

Now pick Database Users from the Show drop-down menu and Details from View. Place your cursor in the Search Username textbox and click (or click enter on the keyboard). You will see the window shown in the next picture which shows the details for the chosen user.

Now click on the hyperlinked item HR under Username in this table. You will see the next window which shows the details for the user HR. This has all the elements of his schema as discussed earlier.

Now if you click All System Privileges Granted to HR at the bottom of the screen, you will see all the System privileges this user is entitled. While some of it is due to his RESOURCE role, the others are because he has the CONNECT role as well.

{mospagebreak title=Creating Users}

You can create a new user using the same interface by accessing the create database user. Users have privileges that they carry with their Username and Password. Their password may have to be set to expire in some cases and/or they may have limited roles. These have to be pinned down before you create a user. For Oracle 10G XE the following points need to be defined before you contemplate creating a new user.

  • Are you permitting the user to create database objects in his own
    schema? In this case you grant him the RESOURCE role, or grant
    system privileges to the individual objects he can create.
  • Are you allowing him to take up the DBA role? For this case you
    have to grant him the DBA role. The DBA is much stronger than the
    RESOURCE role which gives permissions to create objects in only
    one schema. This is a serious permission not to be taken lightly
    as he can alter objects by accessing any schema.
  • Are you going to set a password for the user to expire? In this
    case the password assigned to the user can be used for the first
    time login only and he will be reminded to select a new
    password. This is to cover cases when the password is
    compromised or forgotten.

Creating a User Account

Example 1

One of your users, Jay, accesses the database and creates some tables, queries, and so forth in his own schema. This means he needs access to the resources and he has a password that you have assigned, “xfiles,” set to expire. The next screen shown how this can be set. At first, you get to one of the screens we have seen earlier shown next.

Now click on the Create button to open the window shown in the next picture.

This window shows two main areas, Create Database User and User Privileges. The user has RESOURCE and CONNECT roles. Enter “jay” for the Username, ”xfiles” for the Password, and confirm it (both are case sensitive). You can also check the Expire Password checkbox and grant privileges to all objects by clicking the hyperlink Check All at the bottom right. You leave the Account Status Unlocked so that Jay can get in by logging in. Now you click on the create button on this page which opens the next window as shown.

Now you see that Jay is added to the Database Users with an already expired password. It also says the user was created about a second ago. Now log out of the database by clicking on the Logout hyperlink at the top right of this window. This will bring you to a screen that shows you logged out. This screen also has a hyper link Login to login with some credentials. Click on the hyperlink to open the login screen again.

Now let’s pretend you are Jay. Remember that you were assigned the pair “jay” and “xfiles” for Username/Password. Enter these and click on the login button. You will see the following screen where you need to choose a new password.

Use “xfiles” for your Old Password and choose another for your new password. Click on Apply Changes. You will see that you succeeded in changing the password.

Now “jay” can login with the new password he chose and gets to see his schema objects as shown (which happens to be empty for now).

{mospagebreak title=Altering the User Account}

As an administrator you can manage users by clicking on Database Users and picking the Manage Users drop-down menu item as shown.

Clicking on Manage Users opens the Manage Database Users pane as shown where all users are listed.

Example 2

Here we have a user named Charles. He is locked out of his account and he has asked the DBA to change some of his privileges. The DBA wants to access his account and change these items. The DBA logs on to the database, gets to the above screen, and clicks on the hyperlink of the user Charles. He gets the following page where he can make changes.

The DBA unlocks his account by using the drop-down. He can see that Charles can only create tables, and as requested by Charles he adds the other privileges by placing check marks in the required checkboxes; Create View, Create Procedure and Create Triggers. The DBA then clicks on the Alter User button. Next time Charles logs on he can gain admittance to the database objects he created as shown in the next picture. He can now create the other objects he wanted to create besides tables. On clicking the Alter User button the DBA gets a response that the USER was altered from the database (window not shown).

If the organization for which Charles works had asked the DBA to remove Charles from the database, the DBA could drop him using the DROP button, but before that he has to back up his data, if they are needed. The DBA can also make changes to the password. One safe way to do this is to expire the user’s password, so that the next time Charles can create his own new password, but he has to use his old password to complete this process. The DBA can also give a new password to the user after expiring the password, but he will have to communicate the new password to the user.

{mospagebreak title=What if you are a first time user?}

In the very first tutorial the method for unlocking the HR user account was described. However this article is a more appropriate place. Since to start with there is only one login, that of the system, login with system as the user and the password created while installing Oracle 10G XE. After gaining entry to the database click on Administration and click on the Manage Users menu item which opens up the window shown next. You can see the HR user whose password has expired and note that the account is locked.

Click on the HR icon to open the user’s details as shown in the next window.

Enter the password, hr. Confirm it by entering it again, use the drop-down to change HR’s Account Status from Locked to Unlocked and click on the Alter User button. This brings up the next screen shown. Now the user hr can enter with the password hr and gain access to the database objects.

Summary

This tutorial is a 101 level administration “how-to” for managing users in Oracle 10G XE. It’s a very easy to use interface for administering users. It is however very important to note down your password while installing. In addition to the roles that are discussed in the article, the machine also has entries for the Ora DBA group to which the users can belong – as it happens, on Hodentek the user “jay” belongs to the ORA DBA group.

Google+ Comments

Google+ Comments