Oracle 10G XE User Management 101

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.