Administering users in Oracle 10g

Learning objective

After completing this topic, you should be able to administer user accounts on an Oracle 10g database.

Exercise overview

In this exercise, you're required to create a profile that will limit the idle time to 15 minutes, create a role called HR_MANAGER and create a user with the name Alice Barnard.

This involves the following tasks:

• creating a profile with resource limitations
• creating a role
• creating a user and assigning a role

A new HR manager, Alice Barnard, has been appointed in your company and you need to create a user account to enable her to access the database, select from the HR.EMPLOYEES table, and update records in the EMPLOYEES table.

She also needs the rights to insert and delete new employee records.

In addition to this, you want to ensure that if employees forget to log out of the database at the end of the day, they will be logged out automatically if the database is idle for more than 15 minutes.

Task 1: Creating a profile with resource limitations

It has been noted that sometimes employees in the HR department forget to log out of the database when they finish work. To correct this you decide to set a limit on the time the database can remain idle.

Create a profile called "HRPROFILE" that limits idle time to 15 minutes. Type in the idle time value but keep all other fields in the new profile at their default settings.

Steps list

Instructions
1. Click Administration in the Oracle Home page
2. Click Profiles in the Security section of the Administration tabbed page
3. Click Create in the Profiles page
4. Type HRPROFILE in the Name text box
5. Type 15 in the Idle Time (Minutes) text box
6. Click OK

Task 2: Creating a role

To allow Alice Barnard rights to insert and delete records in the EMPLOYEES table, you have to set up a role called HR_MANAGER with those permissions.

Create a role named "HR_MANAGER".

Steps list

Instructions
1. Click Roles in the Security section of the Administration tabbed page
2. Click Create in the Roles page
3. Type HR_MANAGER in the Name text box in the General tabbed page of the Create Role page
4. Click OK in the Create Role page

Task 3: Creating a user

When the HR_MANAGER role was created, you granted it the object privileges INSERT and DELETE as well as assigning the HR_CLERK role to it.

You now want to create a user account for Alice Barnard and to assign her the HR_MANAGER role to allow her to exercise its privileges over the HR.EMPLOYEES table.

Create a new user account using the username "Alice Barnard" and the password "HRM1". Then assign the role HR_MANAGER to the new user.

Steps list

Instructions
1. Click Users in the Security section
2. Click Create in the Users page
3. Type Alice Barnard in the Name text box in the Create User page
4. Type HRM1 in the Enter Password text box
5. Type HRM1 in the Confirm Password text box
6. Click Roles in the Create User page
7. Click Modify in the Roles tabbed page
8. Select HR_MANAGER in the Available Roles list box and click Move
9. Click OK in the Modify Roles page
10. Click OK in the Create User page