Today you will learn how to administer user accounts in the Oracle RDBMS. You will see how user accounts, profiles, and roles are defined and modified. Administering user accounts is probably the most common job of the Oracle DBA because new user accounts are constantly being added or modified. You will see in this lesson how this task is simplified by using roles and profiles.

Oracle security is administered differently depending on what resource is needed. Access to the database is allowed or disallowed based on a user ID. This user ID has permissions associated with it. These permissions can be assigned either individually or via a role or profile.

New Term: Roles and profiles each control a different type of resource. A role is used to assign privileges that allow the user to access different objects and operations; a profile is used to control the amount of system resources that the user is allowed to consume.

A user can be assigned both a role and a profile as necessary. You will learn more about roles and profiles as the lesson continues.

Creating User Accounts

You can create user accounts with Enterprise Manager, Security Manager, or the CREATE USER command. You will essentially be doing the same job no matter which of these tools you use. I present all three of these methods in the following sections.

---

NOTE: You must have the CREATE USER system privilege to create a new user.

---

Creating User Accounts with Enterprise Manager

To create user accounts with Enterprise Manager, go into Enterprise Manager and drill down into the database where you want to add a user or users. Below the Databases entry you will see a number of different symbols representing different objects. By right-clicking the Users entry, you will be presented with a menu. From this menu, click the Create button to invoke the Create User screen, shown in Figure 10.1.

Figure 10.1.

The Create User screen.

In this screen you need to fill out the username, the profile (if you aren't using DEFAULT), authentication information such as where the password is taken from and what that password is, and the default and temporary tablespaces that this user will use. You also need to specify whether the user account will be locked or unlocked when created. As always, I have clicked the Show SQL button to display the SQL statement that will create this user. A filled-in Create User screen is shown in Figure 10.2. As you can see, I've identified this user's name, profile (if not DEFAULT), default tablespace, and temporary space. I've also given this user an initial password for security.

Figure 10.2.

The Create User screen.

If you want to create a user account that is identical to another user account already defined in the system, you can right-click the user account you will be utilizing as a template and select the Create Like button.

This will again invoke the Create User screen, this time with the values used by the selected user SYS2 already in place, as shown in Figure 10.3.

Figure 10.3.

The Create User screen with the values of user SYS2 already in place.

With this function, you can essentially clone user accounts to a new username. If you are adding a large number of user accounts that all require the same profile and tablespaces, this can be a real timesaver. It is also possible to add user accounts with Security Manager.

Creating User Accounts with Security Manager

As with Enterprise Manager, you can create user accounts with Security Manager. When you invoke Security Manager and click the Users icon, you will see the already-created user accounts on the left side of the screen and a wealth of information about the user accounts on the right side, as shown in Figure 10.4.

Figure 10.4.

The main screen of Security Manager.

This information includes the following for each user:

• Username.

• Account Status--Whether the account is online.

• Expire Date--When the password will expire.

• Authentication--Whether it's done by OS, by password file, and so on.

• Default Tablespace--Where the user's schema objects are, by default.

• Temporary Tablespace--Where the user's sorts will take place (if needed).

• Profile--The user's profile.

By right-clicking on the Users entry, you can invoke the Create User screen as shown in Figure 10.1. As you can see, this is the same Create User screen that you have seen with Enterprise Manager. The function is the same: Just fill in the boxes and click the OK button, and the new user will be created. As with Enterprise Manager, by right-clicking on a user's icon you will get the option to Create Like that user, as shown in Figure 10.5.

Figure 10.5.

You can choose Create Like from the right-click menu to base a new user on an existing user.

By filling out this screen, you will be able to create a user identical to an existing user but with a different username.

As you will see in the next section, there are many more options available with the command function CREATE USER than with Enterprise Manager or Security Manager.

Creating User Accounts with the CREATE USER Command

The new user account can also be created with the CREATE USER command. This command supports more options than Enterprise Manager or Security Manager.

The Syntax for the CREATE USER Command

The CREATE USER command is run with the following syntax:

SYNTAX:

CREATE USER user_name
IDENTIFIED BY password 
     or IDENTIFIED EXTERNALLY 
     or IDENTIFIED GLOBALLY 
AS `CN=user'
[ DEFAULT TABLESPACE ts_name ]
[ TEMPORARY TABLESPACE ts_name ]
[ QUOTA [ number K or M or UNLIMITED ] ON ts_name ]
[, QUOTA [ number K or M or UNLIMITED ] ON ts_name ]
[ PROFILE profile_name ]
[ PASSWORD EXPIRE ]
[ ACCOUNT LOCK or ACCOUNT UNLOCK ]

The parameters for this command are

• CREATE USER user_name--The CREATE USER command requires a username for the user you want to create.

• IDENTIFIED BYpassword--Oracle internally maintains a password for this user. This is the original password that is created.

• IDENTIFIED EXTERNALL --The username is authenticated in the OS. The username must be identical to the username defined in the OS.

• IDENTIFIED GLOBALLY AS `CNuser'--The username is authenticated by the Oracle security domain central server. The CN name identifies the external name of the user.

---

NOTE: One of the previous three qualifiers must be used in the CREATE USER command or the command will fail.

---

• DEFAULT TABLESPACE ts_name--The default tablespace to which the user is connected. If not specified, the SYSTEM tablespace will be used.

• TEMPORARY TABLESPACEts_name--The temporary tablespace that the user will use by default. If not specified, the SYSTEM tablespace will be used.

• QUOTAnumber K or M ON ts_name--Specifies that the user has a quota of number K (kilobytes) or M (megabytes) on the identified tablespace.

• QUOTA UNLIMITED ONts_name--Specifies that the user has an unlimited quota on the identified tablespace.

• PROFILEprofile_name--The name of the profile that is used for this user.

• PASSWORD EXPIR--Immediately sets the password to the expired state. The user must change his password before he can log on to the database.

• ACCOUNT LOC--If this is set, the account is locked after creation.

• ACCOUNT UNLOC--If this is set, the account is not locked after creation.

There is little you can do with the CREATE USER command that you cannot do with the graphical tools. The QUOTA option is the only additional feature you get with the command-line option.

Modifying User Accounts

Modifying a user is very similar to creating one. You can modify a user with either of the two graphical utilities or with the ALTER USER command. As with the process of user creation, there is only a slight difference between the functionality of the graphical utilities and the command-line utility. Let's look at these operations.

---

NOTE: You must have the ALTER USER system privilege to modify a user account.

---

Modifying User Accounts with Enterprise Manager

You can modify a user in Enterprise Manager by drilling down to the level of the users. When you see the user you want to modify, right-click the user's icon and choose the Quick Edit option from the pop-up menu. This will bring up the Quick Edit User screen, as shown in Figure 10.6.

Figure 10.6.

You modify a user with the Quick Edit User screen.

From here you can change the profile, the password identification method, the tablespaces, the password, and whether the user is locked or unlocked. This is essentially the Create User screen without the option of specifying the username.

---

NOTE: You can also remove the user from the same menu that invoked the Quick Edit option. This will permanently remove the user from the Oracle system.

---

Modifying User Accounts with Security Manager

You can also modify a user in the Security Manager utility. Specify the user you want to modify by clicking on that user's icon on the left side of Security Manager. On the right side you will see information about that user that you can modify.

You can alter all the data presented on the right side of the screen, except for the username. You can change the profile, the password identification method, the tablespaces, the password, and whether the user is locked or unlocked. Again, this is essentially the Create User screen without the option to specify the username.

Security Manager will also allow you to change permissions and remove users, and lock, unlock, and terminate the user's password, as shown in Figure 10.7.

Figure 10.7.

Use Security Manager options to modify the user's attributes.

By choosing the Add Privileges to Users option, you can add user privileges. Selecting this option brings up the Add Privileges to Users screen, as shown in Figure 10.8.

Figure 10.8.

Use the Add Privileges to Users screen to modify the user's privileges.

From here you can modify the user's privileges by selecting roles, system privileges, or object privileges with or without the ADMIN option. Remember, the ADMIN option allows you to pass that privilege to other user accounts.

Modifying User Accounts with the ALTER USER Command

You can also modify user accounts via the Oracle command ALTER USER. ALTER USER has similar functionality to Enterprise Manager and Security Manager, plus a few options not available with the graphical utilities. The ALTER USER command is used to alter various attributes of the user account.

The Syntax for the ALTER USER Command

The ALTER USER command is run with the following syntax:

SYNTAX:

ALTER USER user_name
IDENTIFIED BY password 
     or IDENTIFIED EXTERNALLY or IDENTIFIED GLOBALLY AS `CN=user'
[ DEFAULT TABLESPACE ts_name ]
[ TEMPORARY TABLESPACE ts_name ]
[ QUOTA [ number K or M or UNLIMITED ] ON ts_name ]
[, QUOTA [ number K or M or UNLIMITED ] ON ts_name ]
[ PROFILE profile_name ]
[ PASSWORD EXPIRE ]
[ ACCOUNT LOCK or ACCOUNT UNLOCK ]
[ DEFAULT ROLE role [, role ] 
     or [ DEFAULT ROLE ALL [ EXCEPT role [, role ] ] ] or [ DEFAULT ROLE NONE ]

---

NOTE: With the exception of the DEFAULT ROLE option, the syntax of the ALTER USER command is identical to that of the CREATE USER command.

---

The parameters for this command are

• ALTER USER user_name--The ALTER USER command requires a username that is to be modified.

• IDENTIFIED BYpassword--Oracle internally maintains a password for this user. The password is changed to the one specified here.

• IDENTIFIED EXTERNALL--The username is authenticated in the OS. The username must be identical in the username defined in the OS.

• IDENTIFIED GLOBALLY AS `CNuser'--The username is authenticated by the Oracle security domain central server. The CN name identifies the external name of the user.

---

NOTE: One of the previous three qualifiers must be used. If you do not specify an IDENTIFIED qualifier, the command will fail.

---

• DEFAULT TABLESPACE ts_name--The default tablespace to which the user is connected. If not specified, the SYSTEM tablespace is used as the default.

• TEMPORARY TABLESPACEts_name--The temporary tablespace that the user will use by default. If not specified, the SYSTEM tablespace is used as the default.

• QUOTAnumber K or M ON ts_name--Specifies that the user has a quota of number K (kilobytes) or M (megabytes) on the identified tablespace.

• QUOTA UNLIMITED ONts_name--Specifies that the user has an unlimited quota on the identified tablespace.

• PROFILEprofile_name--The name of the profile that is used for this user.

• PASSWORD EXPIR--Changes the password to the expired state. The password must be set before the user can log on to the database.

• ACCOUNT LOC--If set, the account is locked after creation.

• ACCOUNT UNLOC--If set, the account is not locked after creation.

• DEFAULT ROLErole [, role]--Specifies the default role or roles for this user.

• DEFAULT ROLE AL--Specifies that all roles are enabled for this user. In conjunction with the EXCEPT role [,role] qualifier, all roles are enabled except for those specified.

• DEFAULT ROLE NON--Specifies that no default roles are given.

As you can see, the privileges are mostly assigned to user accounts via the use of a role. Where privileges are given via the role, limitations on the user are typically assigned via the profile. In the next section of this lesson, you will look at the profiles and the roles, how to create them, and what they mean.

Managing Profiles

Profiles are created as an easier way of setting limitations on users. Instead of setting individual limits on users, you can set up a profile for each type of user. For example, you can set up the following types of users:

• Administrators--These users are given unlimited resources.

• Developers--These users have an unlimited number of sessions but a limited number of CPU cycles/sessions.

• Other--Perhaps you have other types of users with special needs. By defining your own set of special profiles, you can easily assign users with like attributes the same privileges.

By creating profiles for each type of user, you can simplify the security-management task. You can have as many profiles as you want, but a user can only be assigned one profile, unlike roles.

Profiles can be created, removed, and modified in several ways. You can manage profiles graphically with both Enterprise Manager and Security Manager and via scripts on the command line by using the CREATE PROFILE and ALTER PROFILE commands. I present all three ways in the following sections.

Managing Profiles with Enterprise Manager

Profiles can be created graphically with Enterprise Manager. After you have invoked Enterprise Manager and have drilled down into the database that you want to modify, right-click the Profiles entry and choose Create, as shown in Figure 10.9.

Another way to create a profile is by right-clicking a profile that already exists and choosing Create Like.

Figure 10.9.

Choosing Create to create a profile.

Depending on whether you're creating an all-new profile or basing a new one on an existing profile, you will be presented with either the Create Profile screen or the Create Like screen. These screens are identical, except that with Create Like, the values are already set to the values of the selected profile. The Create Like screen is shown in Figure 10.10.

Figure 10.10.

The Create Like screen.

Through this screen you can enter or change a number of values for the profile in both the General and Password tabs. To switch to the Password screen, simply click the Password tab. The Password screen is shown in Figure 10.11.

Figure 10.11.

The Profile Password screen allows you to change password attributes.

The General screen has quite a few options that can be set or changed. These options are split into Details and Database Services items. The parameters for the Details section are

• CPU/Session (Sec/100)--Specifies the amount of time, in hundredths of seconds, that a session can use.

• CPU/Call (Sec/100)--Specifies the amount of time, in hundredths of seconds, that a session can use per call.

• Connect Time (Min)--The total connect time allowed for a session.

• Idle Time (Min)--The total idle time allowed before the session is disconnected.

For the Database Services portion, the parameters are

• Concurrent Sessions (Per User)--The number of concurrent sessions allotted for this user ID.

• Reads/Session (Blocks)--The number of blocks that this session can read. This includes blocks both from disk and from the buffer cache.

• Reads/Call (Blocks)--The maximum number of blocks that can be read per call.

• Private SGA (KBytes)--The maximum size of the session's SGA.

• Composite Limit (Service Units)--A composite limit composed of the preceding limits.

On the Password tab, the parameters that can be set or changed are

• Expire Password--Specifies how long a password is valid before it must be changed.

• Keep Password History--Keeps a number of older passwords and does not let you reuse the past passwords.

• Enforce Password Complexity--Requires passwords to be complex.

• Lock Account on Failed Logon--Limits the number of failed logon attempts before the account is locked.

As you can see, you can change a lot of items in the profile. This can be quite useful for setting limits if necessary. The DEFAULT profile sets all these values to UNLIMITED as the default.

To modify a profile with Enterprise Manager, right-click the profile that you want to alter and select Quick Edit. This will bring up the Quick Edit Profile screen, as shown in Figure 10.12.

Figure 10.12.

The Quick Edit Profile screen allows you to change quota limits.

From this screen, you can edit the properties of that profile. To remove a profile, right-click the profile and select Remove from the pop-up menu. This will permanently remove the profile from the system.

Managing Profiles with Security Manager

With Security Manager you can essentially perform the same operations as with Enterprise Manager, but with a few more options. When you drill down into the profile using Security Manager, you will see not only the profiles but the users assigned to each of those profiles.

Right-click the Profile entry and you will be presented with a list of options, as shown in Figure 10.13.

Figure 10.13.

Use the Security Manager option to create a new profile.

If you choose the Create option, you will be presented with the same Create Profile screen that you saw with Enterprise Manager, with the same options. Clicking one of the profiles causes the profile properties to appear on the right side of Security Manager screen, as shown in Figure 10.14.

You modify profiles under Security Manager by changing the parameters on the right side of the screen. As you can see, there is also a Password tab here. You can click this Password tab and see the same password parameters as with Enterprise Manager.

As will all the facilities available through Enterprise Manager and utilities such as Security Manager, you can also create and modify a profile via the Server Manager or scripts.

Figure 10.14.

The Edit Profile screen offers a detailed view of the default profile.

Managing Profiles with the CREATE PROFILE and ALTER PROFILE Commands

As with all the commands that you have seen so far, you can create and modify profiles via a command-line utility. For the profile, these utilities are the CREATE PROFILE, ALTER PROFILE, and DROP PROFILE commands. The syntax of the CREATE PROFILE and ALTER PROFILE commands is identical except that the first creates a profile and the other changes an already-existing profile.

The syntax of these commands is given in the Oracle8 documentation and really does not need to be repeated here. Look in the Oracle8 Server SQL Reference Manual for the exact syntax and how to use it. Another good way to learn some of the syntax used in these commands is to take advantage of the Show SQL option available in many Enterprise Manager utilities.

If you are setting up many different profiles and you find that the number you have is quite large, it is a good idea to start putting them in a SQL script. If you have it in a script, you can use it to rebuild the database if necessary, and if you ever need to create an additional database for the same user community, it will be much easier to set up the profiles.

Managing Roles

Roles are very important because they are the primary way of allocating system privileges to the user community. Privileges can be allocated to a user either via a role or directly, but directly can be quite time-consuming and tedious for the DBA. By creating a role for a certain type of user and allocating system privileges to that role, you can cut down on the amount of work that must be done and have an easier way of tracking privileges.

A complete list of Oracle default roles and system privileges is given in Appendix C, "Oracle Roles and Privileges." Please see that appendix for answers to questions on specific privileges.

You can create, modify, and assign roles to users via Enterprise Manager, Security Manager, or the CREATE ROLE, ALTER ROLE, DROP ROLE, and ALTER USER commands. In general, if you are just changing a few roles or users, the graphical tools are very nice. If you are modifying a large number of different roles or users, it might be better to use a script, where the SQL commands can be saved and used again if necessary.

Managing Roles with Enterprise Manager

You can create and modify roles graphically via the Enterprise Manager utility. Drill down into the database that you want to administer and right-click the Roles entry. This will bring up the Create Role screen, which is shown in Figure 10.15.

Figure 10.15.

The Create Role screen allows you to create a new role.

Here you can create a new role that, when created, will have no other roles or privileges associated with it. To populate the role with other roles, simply drag and drop a role onto the Granted Roles entry under the role that you have created (see Figure 10.16). This will add that selected role as one for which your newly created role has privileges. It is also possible to do this from system privileges.

Figure 10.16.

Drag and drop roles to populate a new role with them.

After you have created the role and populated it with other roles or system privileges, you might want to assign it to a user. With Enterprise Manager, it is possible simply to drag this role onto the Granted Roles entry under the user to whom you want to give it. This will automatically grant the role to the designated user. This drag-and-drop operation is shown in Figure 10.17.

Figure 10.17.

Dropping roles onto a user to assign that role to the user.

If you like the drag-and-drop features provided in Enterprise Manager, this may be the best method for you. Security Manager provides this functionality plus a little bit more, as you'll learn in the next section.

Managing Roles with Security Manager

Security Manager can also be used to create roles, grant them to users, and manage them. If you prefer using a graphical tool but need a bit more functionality than what is provided with Enterprise Manager, you will find Security Manager very useful.

To create a new role, use the same technique as with Enterprise Manager: Right-click the Roles icon and select Create. This will bring up the same Create Role screen that you saw earlier. It is shown here in Figure 10.18.

Figure 10.18.

The Create Role screen in Security Manager is the same as the one used in Enterprise Manager.

After you have created the role, right-click its name in the Security Manager screen and you will see the option to Add Privileges to Roles.

From this screen, you can modify the new role by adding new roles (as shown in Figure 10.19) or by adding system privileges (as shown in Figure 10.20). Both of these are accomplished by selecting the roles or privileges you want and adding them to the selected role or roles with the mouse.

Figure 10.19.

Adding roles to a role.

Figure 10.20.

Adding system privileges to a role.

Once nice benefit of Security Manager is the capability to expand the roles and privileges to see what is available. Simply drill down into the different parameters to see what is available or allocated. An example of this is shown in Figure 10.21.

Figure 10.21.

This Security Manager screen allows you to view the roles and privileges associated with the DOGS2 role.

Managing Roles from the Command Line

Managing roles is a little different from working with some of the other commands you have seen throughout the book because managing a role takes more than just one command.

To create and maintain a role, you can use the CREATE ROLE, ALTER ROLE, and DROP ROLE commands. The CREATE ROLE and ALTER ROLE commands are used only to create the role and to manage the security on the role; privileges and other roles are not assigned to roles via the ALTER ROLE command. The CREATE ROLE and ALTER ROLE commands take the following options:

CREATE ROLE role
NOT IDENTIFIED
Or IDENTIFIED BY password
Or IDENTIFIED EXTERNALLY
Or IDENTIFIED GLOBALLY

These options simply have to do with the security of the role itself. To modify a role by adding other roles or system privileges to it, you must use the GRANT command.

Using GRANT and REVOKE

You use GRANT to assign roles or system privileges to roles or users. The same command works whether you are assigning these roles or system privileges to an individual user or to a role that in turn can be assigned to many users.

The Syntax for the GRANT Command

The GRANT command takes the following syntax:

SYNTAX:

GRANT role or system privilege [, role or system privilege ]
TO user or role or PUBLIC [, user or role ]
[ WITH ADMIN OPTION ]

The GRANT command can take any number of system privileges and roles and assign them to any number of users or roles. By specifying that you want to grant a role or system privilege to PUBLIC, you are specifying that you want that role or privilege to be granted to all users in the system.

The REVOKE command is just the opposite of the GRANT command; it will take a role or system privilege away from a user or role:

REVOKE role or system privilege [, role or system privilege ]
FROM user or role or PUBLIC [, user or role ]

The DROP Command

Finally, you can use the DROP command to completely take away a role from the system. You should not have to be dropping roles very often if the roles that you create are planned out in advance.

The ADMIN Option

The ADMIN option specifies that the grantee has the right to pass this role or system privilege to any other user or role in the system. If the user is granted the role with the ADMIN option, that user may also alter or drop the role.

Summary

Today you learned how to manage users in your Oracle8 database. At the beginning of the lesson you saw how to create user accounts using both graphical and command-line utilities. This is the most basic building block of user administration.

After the user account has been created comes the task of administering that user account. This might consist of adding and/or removing roles and system privileges, or modifying some of the resource limitations placed on the user in the form of a profile.

You also learned how to administer profiles, which are used to limit certain system resources that the user session might be consuming. By using a profile, you can assign these limits to a profile and then assign the profile to a class of users.

Finally, you learned how and why to use roles. Roles are very powerful devices. By using roles, administrative tasks can be simplified by assigning a set of system privileges to a class of user, and then handling exceptions as they arise.

Roles are very important and should be well documented. By documenting roles, you can easily decide what role a new user should be assigned. As always, any changes to the system, such as adding or modifying roles, should be logged in the system journal.

What's Next?

In tomorrow's lesson, "Managing Processes," you will start to see more of how the Oracle processes work. You will learn how to find out what processes are active in the system, and how to kill runaway processes. You will also see how the Oracle multithreaded server works.

Q&A

Q What's the difference between a privilege and a role?

A A privilege is the authority to perform a certain act or command. A role is a collection of privileges and roles that can be assigned to a user.

Q What is the DEFAULT profile?

A The DEFAULT profile was created by the system when the database was created. By default, all users have unlimited access to the system.

Q Why do you use roles instead of just assigning system privileges?

A Assigning system privileges can be very time-consuming and complicated. By creating a role for each user type, such as accounting, finance, payroll, and so on, you can assign the privileges based on duties.

Q Can a user have more than one role?

A Certainly. A user can have multiple roles, depending on that user's job and duties.

Workshop

The workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. Find the answers to the quiz questions in Appendix A, "Answers."

Quiz

1. Should more than one user share a user account?

2. How many accounts should each user have?

3. Where would you set the maximum amount of CPU per session?

4. Where would you set permissions to export data?

5. What is the ADMIN option?

6. How do you remove a role?

7. How do you remove a system privilege from a role?

8. Are quotas assigned via roles?

9. How are temporary tablespaces assigned?

10. What does the Create Like feature in Enterprise Manager or Security Manager do?

Exercises

1. Create a user using Enterprise Manager.

2. Modify that user using Security Manager.

3. Create a new profile.

4. Create a new role.

5. Add several roles and privileges to that new role.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.