3.9 How do I…Create, modify, and associate user profiles?

Problem

When I create user accounts in the database, they have some high limits on the amount of CPU time they can use and the number of sessions to which they can connect. When a user or developer creates a table, it is created in an undesirable location. How can I create, modify, and associate user profiles?

Technique

The PROFILE clause in the CREATE USER and ALTER USER statements enables a profile to be specified for user accounts. A profile restricts the use of database resources for user accounts assigned to it. The CREATE PROFILE statement creates a new profile. The syntax of the CREATE PROFILE statement is shown in Figure 3.18.

After a profile has been created, it can be assigned to new user accounts with the PROFILE clause of the CREATE USER statement. It can also be assigned to existing user accounts by executing an ALTER USER statement.

The default tablespace specifies where data objects will be created when the location is not specified in a CREATE statement. The temporary tablespace specifies the tablespace used when temporary storage is required by an operation the user account executes. The ALTER USER statement is used to change the default tablespace and the temporary tablespace of a user account. The syntax of the ALTER USER statement is shown in Figure 3.19.

Steps

1. Run SQL*Plus and connect as the WAITE user account. The CREATE PROFILE privilege is required to perform this step. Any user account with the DBA role has this privilege. Load CHP3_17.sql into the SQL buffer with the GET command. The statement contained in CHP3_17.sql creates a profile restricting the number of concurrent sessions a user account can have connected to the database. Because no other parameters are specified, they are assumed to be the default.

SQL> GET CHP3_17.sql

1 CREATE PROFILE WAITE_PROFILE

2* LIMIT SESSIONS_PER_USER 4

Line 1 contains the CREATE PROFILE keywords and specifies the name of the profile to be created. Line 2 limits the number of sessions per user account to four.

2. Run the statement from the SQL buffer.

SQL> /

Profile created.

3. Load CHP3_18.sql into the SQL buffer. The file contains an ALTER USER statement to assign the new profile to the WAITE user account.

SQL> GET CHP3_18.sql

1 ALTER USER WAITE

2* PROFILE WAITE_PROFILE

Line 1 contains the ALTER USER keywords, and line 2 contains the PROFILE clause to specify a new profile.

4. Execute the statement to modify the user account.

SQL> /

User Altered

The WAITE user account is limited to four concurrent sessions by the profile.

How It Works

A profile limits resources allocated to a user account. A profile is like a role in that it groups user accounts into defined categories. A profile can be created by any user account with the CREATE PROFILE privilege and is assigned to a user account with a CREATE USER or ALTER USER statement. Steps 1 and 2 create a profile limiting the number of concurrent connections of a user account to four. Steps 3 and 4 assign the new profile to the WAITE user account.

Comments

A profile should be created for users and developers within the database. When developing applications, a developer might have many concurrent connections, which can drain resources.