Oracle 10g user accounts and profiles

Learning objective

After completing this topic, you should be able to recognize how to create user accounts and profiles.

1. Database user accounts

A user's access to a database is often controlled by the values set in a database user account. This provides the user with authentication details such as a username and password.

Depending on the system, a user may have an individual user account or share a common database account.

The user account should include

username

authentication

default tablespace

temporary tablespace

user profile

username: The username should be unique, start with a letter, and have no more than 30 characters. Also, it can't contain any special characters.
authentication: Authentication is usually provided by a password. However, Oracle Database 10g provides several methods including token authentication, digital certificates, and biometrics.
default tablespace: If the user doesn't specify a tablespace when creating objects, they can create them in the default tablespace. However, the rights to create objects in the default tablespace and the allocation of space in it are not granted by default.
temporary tablespace: The temporary tablespace is used to create temporary objects, such as temporary tables, views, and sorts.
user profile: The user profile lists the password and resource restrictions placed on a user.

Question

Which of these are components of a database user account?

Options:

Authentication method

Default location

Temporary tablespace

Unique username

Answer

Authentication method, temporary tablespace, and unique username are all components of a database user account.

Option 1 is correct. The password is the most common method of authentication. However, Oracle Database 10g also provides methods such as digital certificates and biometrics.

Option 2 is incorrect. A default tablespace is a where users can create objects if they have not specified a particular tablespace when creating the object.

Option 3 is correct. Users can create temporary objects in the temporary tablespace, such as temporary tables and sorts.

Option 4 is correct. Each username should be unique, start with a letter, and shouldn't exceed 30 characters. It also can't use any special characters.

When creating a database user, you first click Administration in the Enterprise Manager home page.

Users, roles, and profiles are all listed in the Security section of the Administration tabbed page.

You want to view database user accounts.

You click Users.

In the Users page there is a search function and a results table that lists all the existing users.

You can edit, view or delete the current users by selecting the user account in the Results table and clicking Edit, View or Delete at the top of the table.

In this example, you want to create a new user account.

You click Create.

The Create User page contains several sections that can be used to change the roles, privileges, and quotas attributed to the user.

You set the initial properties of the user account in the General tabbed page.

When creating a user account the administrator needs to

provide a username

assign a profile

choose an authentication method

assign tablespaces

First, you must enter a username in the Name field. It's tchavis in this case.

The user must enter the correct username, as formatted in the Name field, when accessing their database account.

Next you must provide a profile.

A user is assigned to a named profile to control their database usage and access to instance resources. It also places parameters such as length and expiration time on passwords.

The named profiles are generated separately.

To assign a profile to a user you click the Profile drop-down list box and select the appropriate profile.

However, you can leave the new user with the DEFAULT profile.

A user account must be provided with a method of authentication. This is to verify the identify of the user, device or component accessing the database user account.

Authentication also allows the administrator to trace any changes back to particular identities and to control levels of access.

Although you must provide a method of authentication when creating a user account, the method itself can be changed later.

The Create User page provides three methods of authentication:

Password

External

Global

Password: A password - a specific combination of numbers and letters - is associated with each username and must be entered by the user when accessing the database. Expiring the password after it is used for the first time forces the user to create their own password, presuming their application has the necessary functionality.
External: With external authentication, user access to the database is restricted at the operating system or the network authentication system level. This is more convenient for users if they are already using the operating system or on the network, as no additional username and password is required. No password is required in the Create User page.
Global: Global authentication is a series of strong methods provided in the Oracle Advanced Security option. The methods include X.509 digital certificates, token devices, biometrics and Oracle Internet Directory.

You select Password in this case, as it's the most common method of authentication.

You then enter the password in the Enter Password field.

To ensure that you didn't make an error, you are required to enter the same password again in the Confirm Password field.

In the default tablespace field, you can provide the name of the tablespace where a user can create objects if they do not specify one themselves.

The system will automatically provide a default tablespace if you don't specify one here.

When the user initiates a sorting action - to create an index, for example - the temporary tablespace is used.

Again, if you don't specify a temporary tablespace the system will do it for you.

Note

You can define a default and temporary tablespace at tablespace level. Then, if you do not assign a specific tablespace for the user, they will be assigned the database default and temporary tablespace.

The locked or unlocked status of the new user account means that the account is either accessible or inaccessible.

When the status is locked, no one can log in as the user. When unlocked, the user with the right username and password can access the account.

In this case, you leave the user account unlocked.

Finally, when all the properties have been entered for the new user, you click OK.

A message appears at the top of the Users page, telling you that you have successfully created the object.

Question

You have been asked by human resources to provide the new hire Alex Chung with access to the orcl2 database.

This task requires you to begin creating a new user account with the username achung. Which of these options allows you to complete this task?

Options:

You first click Users in the Security section of the Administration tabbed page. You then click View in the Users page. In the View User page you type achung in the Name field and click OK.

You first click Users in the Security section of the Administration tabbed page. You then click Create in the Users page. In the Create User page you type achung in the Name field and click OK.

Answer

2. Database schemas

A database user, as well as having a database user account, also owns a schema. A schema is the collection of database objects, such as tables, views, and stored Java and PL/SQL code and indexes, that the user has created.

There is no exclusive relationship between a schema and a tablespace, and the same schema can appear in different tablespaces. Similarly, a single tablespace can contain objects from several schemas.

A schema is created at the same time as a user and is provided with the same name.

User and schema are terms that are often used interchangeably, as a user can only be associated with a schema with the same name.

Schema objects - such as tables, triggers, indexes, sequences and views - are created in the user's default tablespace, unless another location is specified.

A user, as schema owner, has full control over their own schema objects and over who can access them.

A user account may be created just to provide a schema, with no expectation that the user account will be used to access the database.

In such a case, the user account should be locked to prevent any one logging into the account.

You can unlock a new or existing account by editing the user's details.

Question

Which of these are characteristics of a schema?

Options:

It has the same name as the user associated with it

It requires a profile to be assigned to it

It's a collection of database objects owned by a user

It's created when the user account is created

Answer

A schema has the same name as the user that owns it. It's made up of database objects owned by the user and it's created when the user account is created.

Option 1 is correct. A schema and user are terms that are often used interchangeably. A user can only be associated with a schema of the same name.

Option 2 is incorrect. A profile must be assigned to a user but not to a schema, although a schema is associated with one specific user.

Option 3 is correct. A schema is made up of the objects created by the user uniquely associated with it. Unless stated otherwise, objects are created in the default tablespace specified in the user account.

Option 4 is correct. When a user account is created, a schema with the same name is also created. Sometimes user accounts are created just to obtain a schema.

3. System resources and profiles

One way of controlling instance resources and database usage is by creating specific profiles that list the limits that need to be imposed.

Each profile lists different limits, and every user is assigned to a particular profile. A user can only be assigned to one profile at a time.

To create a new profile, you first select Profiles in the Security section of the Administration tabbed page in Enterprise Manager.

The Profiles page has a search function and a results table that lists the current profiles in the database.

You can select a profile and view, edit or delete it using Edit, View or Delete at the top of the table.

Here, you select Create to create a new profile.

The Create Profile page contains two tabs: General and Password.

The General tabbed page contains a Name field and two sections called Details and Database Services.

You first enter a profile name in the Name field - Profile 1, in this case.

Oracle Database 10g provides a default profile which forms the basis of all other profiles.

You now want to change the default value for CPU/Session in the Create Profile page.

You click the flashlight icon next to the CPU/Session field, select 1000, and click Select.

The value for CPU/Session has changed to 1000.

In a profile, limitations can be set on individual sessions using CPU/Session, on CPU usage, in the case of CPU/Call, or by changing the individual default settings, such as Connect Time.

Note

The RESOURCE_LIMIT must be set to TRUE in the initialization parameter file if profiles are to impose resource limitations on users. It is set to FALSE by default.

An administrator can use profiles to limit user access to the following system resources:

CPU

Network/Memory

Disk I/O

You can limit CPU resources on a per-session or a per-call basis.

For example, placing a CPU/Session limit of 1000 means that a session initiated by a user with this profile will get logged off with an error message as soon as the session exceeds 10 seconds. CPU time is measured in 100ths of a second.

The error message is:

ORA-02392: exceeded session limit on CPU usage, you are being logged off

The per-call limitation works in a similar way to per-session. However, it logs off the user when a single command takes up too much CPU.

The error message in this case is: ORA-02393: exceeded call limit on CPU usage

Limitations are placed on Network/Memory to keep
the network traffic and system memory overheads
of database sessions under control.

The limits that you can place on Network/Memory resources are

Connect Time

Idle Time

Concurrent Sessions

Private SGA

Connect Time: The value placed in Connect Time sets the number of minutes that a user can connect to the session before being logged off.
Idle Time: The Idle Time setting determines how long, in minutes, a user's session can remain idle before it logs off. The amount of time a session is idle is based on server processes rather than application activity. Also, long-running queries are not affected by the IDLE_TIME limit.
Concurrent Sessions: The Concurrent Sessions setting determines how many sessions can be run concurrently using an individual database user's account.
Private SGA: Private SGA sets the limit on the space allocated for merging bitmaps, SGA sorting, and other operations. This limitation only applies when the sessions take place on a shared server.

Setting limitations on the Disk I/O can restrict the user being able to read at the per-call or per-session level.

The values in the Reads/Call and Reads/Session fields set the total number of reads, either to memory or disk, a user is allowed to make.

You do this to prevent I/O-intensive statements from consuming memory and occupying the disk.

In a profile you can also set composite limits. These are a combination of Connect Time, Private SGA, CPU/Session, and Reads/Session.

Besides limiting user access to system resources, you can impose limitations on a user's password.

You select the Password tabbed page to do this.

In the Password section of the Password tabbed page, you can set the length of time before the password expires, and whether to lock the account when it expires.

In the History section you can set the number of passwords and how long each should be kept.

In the Complexity section, you can set the complexity function.

And in the Failed Log in section, you can determine how many times a user can log in incorrectly before the account is locked, and how many days it will lock for.

You click OK when the profile is complete.

A message appears at the top of the Profiles page telling you the object has been created successfully.

Question

Which system resources can be controlled by profiles?

Options:

CPU

Disk I/O

Network Memory

Schemas

Answer

CPU, Disk I/O, and Network/Memory are the system resources that can be controlled by profiles.

Option 1 is correct. In a profile, limitations can be set on individual sessions using CPU/Session and on CPU usage, in the case of CPU/Call.

Option 2 is correct. Setting Disk I/O limitations in the profile can restrict the user being able to read the disk at the per-call or per-session level.

Option 3 is correct. Limitations are placed on the Network/Memory to prevent a database session from taking up too much of the system or network resources.

Option 4 is incorrect. A schema is not a system resource. Instead it is the collection of database objects, such as tables, views, and stored Java and PL/SQL code and indexes, that the user has created.

Question

As the number of employees in your company increases, you find that restrictions are needed to avoid problems with the database. To control the amount of time each user stays using the database, you decide to create a new profile.

This task requires you to create a new profile called Profile 2 using the default settings. Which of these options allows you to complete this task?

Options:

You select Profiles in the Security section of the Administration tabbed page. You then click Create in the Profiles page. In the Create Profiles page, you type Profile 2 in the Name field and click OK.

You select Users in the Security section of the Administration tabbed page. You then click Create in the Profiles page. In the Create Profiles page, you type Profile 2 in the Name field and click OK.

Answer

Summary

You can create user accounts with a unique username and an authentication method to verify the identity of the user. You can also assign a profile to the user to impose limits on how they use the database. The default and temporary tablespaces in a user's account provide them with a place where they can create and sort objects.

When a user account is created, a schema with the same username is also created. The schema is a collection of database objects that are owned by the user. Sometimes user accounts are created only to obtain the resultant schema. When this is done, the user account should be locked to prevent anyone accessing the database using the user account details.

By creating new profiles, you can impose restrictions on instance resources and database usage. You can limit how long a session can last or how long it takes to execute a command. You can also place restrictions on system or network resources, and prevent I/O-intensive statements from consuming memory and occupying the disk.

Oracle 10g

Wednesday, September 5, 2007