This course introduces creating and managing users and controlling resources through profiles. You'll learn how to create new users and manage existing users, and how to manage profiles and password security. You'll also learn how to obtain information about existing users and profiles.
In many larger organizations, a security administrator may handle security — the functionality provided by Oracle for security may not be handled by the DBA. But as the resident expert on Oracle software, you should familiarize yourself with this subject.
This course presents information relevant to the following Oracle 1Z1-031: Oracle9i DBA Fundamentals I exam objectives:
Managing Password Security and Resources
- Manage passwords using profiles
- Administer profiles
- Control use of resources using profiles
- Obtain information about profiles, password management, and resources
- Create new database users
- Alter and drop existing database users
- Monitor information about existing users
Lesson 2. Creating New Users
The safest database is one with no users — but take away the users, and there's little reason to have a database. One of your primary tasks early in the creation of a new database is adding new users.After completing this lesson, you should be able to:
- Add new users to an Oracle database
- Identify the components of the create user statement
This lesson focuses on the prudent creation of users in Oracle. You'll learn the basic syntax for user creation.
User creation is an ongoing task. As users enter and leave the organization, so too must you keep track of access to the database granted to those users.
When you use Oracle's database authentication method, new users are created with the create user statement.
This statement highlights several items of information that comprise the syntax and semantics of user creation, and these areas will be covered on the following pages.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
create user defines the user's name in Oracle. If you're using operating system authentication to enable users to access the database, then the usernames should by default be preceded with OPS$. In no other case is it recommended that a username contain a nonalphanumeric character, although both _ and # are permitted characters in usernames.
The name should start with a letter. On single-byte character sets, the name can be from 1 to 30 characters long. On multibyte character sets, the name of a user must be limited to 30 bytes.
In addition, the name should contain one single-byte character according to Oracle recommendations. The username isn't case sensitive and can't be a reserved word.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
identified by defines the user's Oracle database password. This item should contain at least three characters, and preferably six or more. Generally, it's recommended that users change their password once they know their username is created.
Users should change their passwords to something that's not a word or a name, and that preferably contains a numeric character somewhere. As with the username, the password can be a maximum length of 30 bytes and can't be a reserved word. If operating system authentication is being used, use the keywords identified externally.
This is the only aspect of a user's ID in Oracle that the user is allowed to change with the alter user command.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
Tablespace management is a crucial task in Oracle. The default tablespace clause names the location where the user's database objects are created by default. This clause plays an important role in protecting the integrity of the SYSTEM tablespace.
If no default tablespace is named for a user, objects that the user creates may be placed in the SYSTEM tablespace. Recall that SYSTEM contains database objects that are critical to database use, such as the data dictionary and the SYSTEM rollback segment.
Users shouldn't be allowed to create their database objects in the SYSTEM tablespace.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
Oracle9i lets you create a default temporary tablespace when you create a database. You can also alter it using the create temporary tablespace command.
Creating default temporary tablespaces means that you need not specify the temporary tablespace clause for every user.
You can, however, assign users to a different temporary tablespace than the default temporary tablespace for all users, through the temporary tablespace clause in the create user or alter user commands.
If a default temporary tablespace isn't defined and if temporary tablespace isn't explicitly specified by the DBA when a username is created, the location for all temporary segments for that user will be the SYSTEM tablespace.
SYSTEM is a valuable resource that should not be used for user object storage.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
A quota is a limit on the amount of space a user's database objects can occupy within the tablespace. If a user attempts to create a database object that exceeds that user's quota for that tablespace, the object creation script will fail. A quota can be specified in kilobytes (KB) or megabytes (MB).
A quota clause should be issued separately for every tablespace other than the temporary tablespace on which the user will have access to create database objects. If you want a user to have the ability to use all the space in a tablespace, you can specify quota unlimited on tblspcname.
Users need quotas on tablespaces only to create database objects. They don't need a quota on a tablespace to update, insert, or delete data in an existing object in the tablespace, as long as they have the appropriate privilege on the object for data being inserted, updated, or deleted.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
Profiles are a bundled set of resource-usage parameters the DBA can set to limit a user's overall host machine utilization. The idea behind their use is that many end users of the system need only a certain amount of the host machine's capacity during their session.
To reduce the chance that one user could affect the overall database performance with, say, a poorly formulated ad hoc report that drags the database to its knees, you may assign profiles for each user that limit the amount of time they can spend on the system.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
The password expire clause enforces the requirement that a user change his or her password on first logging into Oracle. This extra level of password security guarantees that not even you, the DBA, will know a user's password.
If this clause isn't included, the user won't have to change the password on first logging into Oracle.
CREATE USER spanky
IDENTIFIED BY first01
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
QUOTA 10M ON users_01
PROFILE app_developer
PASSWORD EXPIRE
ACCOUNT UNLOCK;
account unlock means that a user's account is available for use immediately. It's the default for the user accounts you create.
The DBA can prevent users from using their accounts by specifying account lock instead.
Question 1
Question 2
Question 3
Question 4
Topic 2.1 Exercises
* Exercise 1
Try adding a new database user to your Oracle9i database.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Use the create user statement to add a new user to your database. |
| 2 | Name the user kdunlap and give him the password temp123. |
| 3 | Define the user's default and temporary tablespace. |
| 4 | Limit the user's database object space on the tablespace to ten megabytes. |
| 5 | Assign a profile to the user, if you wish. |
| 6 | Make the user's account available immediately and ensure that the user changes the password the first time he logs into Oracle. |
In this lesson, you learned how to add new users to an Oracle database using the create user statement.
You also learned what the different components of the create user statement are, and you learned what roles these components play in creating the user's account.
Lesson 3. User Account Guidelines
An Oracle database user can be authenticated by an operating system. Certain guidelines should be followed when managing any user accounts.After completing this lesson, you should be able to:
- Create an operating system-authenticated user
- Identify guidelines for user-account management
An Oracle database user can be authenticated at various levels — by Oracle itself, by an operating system, or by a remote service. The following command shows how to create a user authenticated by an operating system:
CREATE USER sam
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users;
CREATE USER sam
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users;
This command creates a user called SAM who will be authenticated by the operating system. This means the user SAM must have an operating system account on the machine where Oracle is executing.
Once logged into the machine, the user SAM should give the following command to log into the database:
sqlplus /
Before you create an operating system-authenticated user, you need to define the OS_AUTHENT_PREFIX initialization parameter. This parameter specifies the format of the username of operating system-authenticated users. This value defaults to OPS$ to make it backward compatible, but it can now be defined as a NULL as follows:
OS_AUTHENT_PREFIX = ""
To permit operating system-authenticated users to log into Oracle from a remote machine, set the REMOTE_OS_AUTHENT parameter to TRUE. Doing so specifies that the user can be authenticated by a remote operating system. The default value of this parameter is FALSE.
Be aware that using this parameter opens a security hole in the database, whereby rogue operating system-authenticated users may be able to gain access to your Oracle database.
There are several guidelines you should follow when you manage user accounts. In many cases, these guidelines are new for Oracle8i and higher:
- Employ a standard password for user creation, such as 123abc or first1, and employ password expire to force users to change this password to something else the first time they log into Oracle.
- Avoid operating system authentication unless all your users will access Oracle while connected directly to the machine hosting your database (this second part is also not advised).
- Always create a default temporary tablespace. Then you don't have to worry about assigning a temporary tablespace during user creation. But if you don't have a default temporary tablespace, be sure to always assign temporary tablespace and default tablespace to users with the ability to create database objects, such as developers.
- Give few users quota unlimited. Although it's annoying to have users asking for more space, it's even more annoying to reorganize tablespaces which have been carelessly filled with database objects.
- Become familiar with the user-account management and other host machine limits that can be set via profiles. These features take Oracle user-account management to new levels of security.
Question 5
Question 6
Question 7
Question 8
In this lesson, you learned how to create a user authenticated by an operating system. You also learned how to employ the OS_AUTHENT_PREFIX initialization parameter to specify the format of the username of operating system-authenticated users.
You learned that permitting operating system-authenticated users to log into Oracle via the REMOTE_OS_AUTHENT parameter can compromise your database by providing access to authenticated-users on rogue systems.
Finally, you learned several guidelines to follow that will ease and enhance the management of user accounts.
Lesson 4. Altering and Dropping Existing Users
Once a user is created, there are a number of reasons why a DBA may need to modify that user. The alter user statement lets you make changes to the user's account, while the drop user statement lets you drop a user.After completing this lesson, you should be able to:
- Employ the alter user statement to modify an existing user's account
- Drop a user from an Oracle database
Once a user is created, there are a few reasons why you might need to modify that user.
One is to expire the password if a user forgets it, so the next time the user logs in, she can change her password. The alter user identified by statement is used to change the user's password:
ALTER USER athena
IDENTIFIED BY forgotpassword
PASSWORD EXPIRE;
In certain situations, a user's account may become locked. This may occur if the user forgot his or her password and tried to log in using a bad password too many times.
To unlock a user's account while also making it possible for the user to change the password, issue the following alter user statement:
ALTER USER athena
IDENTIFIED BY forgotpassword
ACCOUNT UNLOCK
PASSWORD EXPIRE;
Other situations abound. In an attempt to prevent misuse, you may want to lock an account that's tried many times unsuccessfully to gain access to Oracle with the following statement:
ALTER USER athena
ACCOUNT LOCK;
You should remember that changes to passwords, account lock status, or password expiration are applied only to subsequent user sessions, not to the current session.
The password management section of another lesson covers more about account locking.
You may want to reorganize tablespaces to distribute the input/output (I/O) load and make more effective use of the hardware running Oracle. Perhaps this effort involves dropping some tablespaces and creating new ones. If you want to change a user's default tablespace, issue the alter user default tablespace statement.
ALTER USER spanky
DEFAULT TABLESPACE overflow_tabspc01;
This change is good for preserving the integrity of the SYSTEM tablespace. This statement will affect only newly created objects. Existing objects created in other tablespaces by that user will continue to reside in those tablespaces until they're dropped.
If the user specifies a tablespace in which to place a database object, that specification will override the default tablespace.
ALTER USER spanky
DEFAULT TABLESPACE overflow_tabspc01;
By the same token, you may want to reorganize the tablespace used for disk sorts as you move from permanent tablespaces to temporary tablespaces. Do this with alter user temporary tablespace.
Only the DBA can make these changes; users cannot change their own temporary or default tablespaces.
ALTER USER spanky
TEMPORARY TABLESPACE temp_overflow_01;
You need to specify the temporary tablespace clause for users only if no default temporary tablespace exists for the database, or if you want to assign users to a temporary tablespace other than the default temporary tablespace for the database.
A tablespace accessible to the user at user creation can have a quota. The DBA can alter the quota with the alter user quota statement.
For example, the DBA may want to reduce the quota on the USERS_01 tablespace from 10MB to 5MB for user SPANKY. If the user has already created over 5MB worth of database objects in the tablespace, no further data can be added to those objects and no new objects can be created. Only the DBA can change a user's tablespace quota; the users can't change their own quotas.
ALTER USER spanky
QUOTA 5M ON users_01;
Specifying quota 0 on SYSTEM for a user will prevent that user from creating any object in the SYSTEM tablespace, even if that user still has the default tablespace set to SYSTEM. However, this restriction doesn't include the creation of packages, stored procedures, and functions.
ALTER USER spanky
QUOTA 0 ON system;
All aspects of the user's account covered in this course can be modified by the DBA. However, the aspects of the account that can be changed by the user are far more limited.
A situation may arise in regular database use where a user wants to change his or her password. This is accomplished with the following:
ALTER USER athena
IDENTIFIED BY mynewpassword;
Except for altering the password, the user can change nothing about his or her own user account, except in certain situations where the alter any user privilege has been granted to that user.
As users come and go, their access should be modified to reflect their departure. To drop a user from the database, execute the drop user statement. If a user has created database objects, the user can't be dropped until the objects are dropped as well.
To drop the user and all related database objects in one fell swoop, Oracle provides the cascade option.
DROP USER spanky CASCADE;
If you want to remove a user but assign his or her table(s) to another user, you should use the EXPORT tool to dump the user's table(s). Then use IMPORT with the FROMUSER and TOUSER parameters to import the tables to the new user.
Question 9
Question 10
Question 11
Question 12
Topic 4.1 Exercises
* Exercise 1
Try altering and dropping existing users on an Oracle9i database.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Change an existing user's password and make it possible for the user to change the password. |
| 2 | Alter the user's default and temporary tablespace. |
| 3 | Change the quota on the tablespace for the user. |
| 4 | Drop the user and all related database objects from the database. |
In this lesson, you learned how to use the alter user statement to change a user's ID configuration. You learned to change a user's password, lock and unlock an account, and change a user's temporary and default tablespace locations.
You also learned that the only aspect of a user's account that the user can change is his or her password.
Finally, you learned how to drop a user from the Oracle database with the drop user statement. You can't drop a user until the objects the user created are dropped. You learned how to use the cascade option to remove the user and all related database objects.
Lesson 5. Monitoring User Information
Data dictionary views are used to obtain information about users.After completing this lesson, you should be able to:
- Monitor information about users
- Identify several data dictionary views
As the DBA, you may periodically want to monitor information about users. Several data dictionary views may be used for this purpose.
Some information you may want to collect includes default and temporary tablespaces, objects created by that user, and the current account status for that user account.
The following pages detail data dictionary views that can be used to determine this information.
DBA_USERS contains the username, Oracle-generated ID number, encrypted password, default and temporary tablespace information, and user profile that was specified in the ID creation statements or any alteration that may have followed.
Also, the view offers ACCOUNT_STATUS, which may be locked, open, or expired; LOCK_DATE, which is the date on which the account was locked (NULL for open accounts); and EXPIRY_DATE, which is the date for account expiration.
DBA_OBJECTS contains specific information about every object in the database, including ID, type, status, and important dates.
The DBA can determine which objects belong to which users by checking the OWNER column of this view.
DBA_SEGMENTS is similar to DBA_OBJECTS. It contains information about various segments (tables, indexes, and so on) created by users, where they reside, and their space allocation information.
DBA_TS_QUOTAS names all users and any tablespace quotas that have been created for them.
A value of -1 in MAX_BYTES or MAX_BLOCKS means that the user has an unlimited space quota for that tablespace.
Question 13
Question 14
Question 15
In this lesson, you learned that data dictionary views can be used to obtain information about users.
You also learned what information the following views contain: DBA_USERS, DBA_OBJECTS, DBA_SEGMENTS, and DBA_TS_QUOTAS.
Lesson 6. Controlling Resource Use with Profiles
User profiles control the implementation of host system resources by the Oracle database with respect to the user.After completing this lesson, you should be able to:
- Enable resource restrictions to be used in conjunction with profiles
- Identify the three levels of host system resource settings
- Assign resource costs to set composite limits
Profiles are objects in the Oracle database that limit a user's ability to utilize the resources of the system hosting the Oracle database.
In other words, if you want to restrict a group of users from abusing the central processing unit (CPU) utilization on the machine hosting the Oracle database, you can use profiles.
Oracle's use of the host machine on behalf of certain users can be managed by creating specific user profiles. These should correspond to the amount of activity anticipated by typical transactions generated by those different types of users.
User profiles aren't meant to force the user off the system every time an artificially low resource-usage threshold is exceeded. Rather, resource-usage thresholds should let users do everything they need to on the Oracle database, while also limiting unwanted or unacceptable use.
If users make a mistake, or try to do something that hurts database performance, profiles can stop them short, helping to reduce problems.
Let's explore how to use profiles in the Oracle database.
Before proceeding into a full-fledged discussion of profiles, you must make a change to your init.ora file so Oracle will enforce host system resource limits set in profiles.
To use resource limits, you must change the init.ora RESOURCE_LIMIT parameter to TRUE on your Oracle database.
To enable resource restrictions to be used in conjunction with profiles on the current database session, you can also issue the following statement:
ALTER SYSTEM
SET RESOURCE_LIMIT = TRUE;
Once resource limits are enabled, there are three different aspects of resource usage and limitation to consider when setting up profiles. This lesson will cover all three.
They are
- Session-level resource limits for individuals
- Call-level resource limits
- Assignment of resource cost to enable composite limits
*Session-Level Resource Limits
Resource-usage areas can have limits assigned at the session level within the profiles you create. If a session-level resource limit is exceeded, the user gets an error and the session is terminated. At the session level, the resource limits are as follows:
| Resource Limits | Explanation |
|---|---|
| sessions_per_user | The number of sessions a user can open concurrently with the Oracle database. |
| cpu_per_session | The maximum allowed CPU time in 1/100 seconds a user can utilize in one session. |
| logical_reads_per_session | The maximum number of disk I/O block reads that can be executed in support of user processing in one session. |
| idle_time | The time in minutes a user can issue no commands before Oracle times out the session. |
| connect_time | The time in minutes a user can be connected to the database. |
| private_sga | The amount of private memory in KB or MB that can be allocated to a user for private storage. Used only when MTS is employed on your Oracle database. |
*Call-Level Resource Limits
Resource-usage areas can have limits assigned at the call level within the profiles you create. If the user exceeds a call-level usage limit, the SQL statement that produced the error is terminated, any transaction changes made by the offending statement are rolled back, previous statements remain intact, and the user remains connected to Oracle. There are two call-level usage limits:
| Resource Limits | Explanation |
|---|---|
| logical_reads_per_call | The maximum number of disk I/O block reads that can be executed in support of the user's processing in one session. |
| cpu_per_call | The maximum allowed CPU time in 1/100 seconds that any individual operation in a user session can use. |
*Composite Limits and Resource Costs
In some cases, you may find individual resource limits inflexible. The alternative is setting composite resource limits on the principle of resource cost.
Resource cost is an arbitrary number that reflects the relative value of that resource based on the host machine's capabilities.
For example, on a host machine with few CPUs and many disk controllers, you might consider cpu_per_session more valuable than logical_reads_per_session.
The statement used for assigning a resource cost is alter resource cost.
Resource costs apply only to the cpu_per_session, logical_reads_per_session, connect_time, and private_sga resources.
The default value for each resource cost is zero.
Resource costs are specified as an abstract unit value, not a monetary resource price.
For example, setting the resource cost of CPU cycles per session equal to 1.5 doesn't mean that each CPU cycle costs a user process $1.50 to run.
Here is an example of setting the resource costs for a number of usage limits:
ALTER RESOURCE COST
CPU_PER_SESSION 10
LOGICAL_READS_PER_SESSION 2
PRIVATE_SGA 6
CONNECT_TIME 1;
In Oracle8i, Oracle introduced a new feature for host system resource management — the use of resource consumer groups. This feature uses built-in PL/SQL procedures and functions to control the use of host system resources by users.
For more information, consult the database resource management section of the Oracle9i Database Administrators Guide that comes as part of your Oracle Generic Documentation.
Question 16
Question 17
Question 18
Question 19
In this lesson, you learned how to change the init.ora file so Oracle enforces host system resource limits. You also learned to enable resource restrictions to be used in conjunctions with profiles by issuing the ALTER SYSTEM SET RESOURCE_LIMIT = TRUE statement.
You learned that the three levels of host system resource settings are session-level, call-level, and composite limits.
Finally, you learned how to use the alter resource cost command to set composite limits.
Lesson 7. Administering Profiles
Profiles prevent misuse of underlying host system resources by setting limits on those resources at the user level.After completing this lesson, you should be able to:
- Create a user profile
- Explain the purpose of the DEFAULT profile
- Alter or drop an existing profile
Profiles are assigned to users with the create user or alter user command. Examples of the create user command are shown in other lessons.
A special user profile exists in Oracle at database creation called DEFAULT. If you don't assign a profile to a user with the profile clause in the create user statement, Oracle assigns the DEFAULT profile to that user.
The DEFAULT profile isn't very restrictive of host system resources. In fact, DEFAULT gives users unlimited use of all resources definable in the database.
You might create a user profile that has some host system usage restrictions on it, such as the one shown.
This is a good example of using profiles to set individual resource limits. You don't need to define limits for all available resources, because any resources not explicitly assigned limits will be assigned the default value for that limit specified in the DEFAULT profile.
CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 1
CPU_PER_SESSION 10000
CPU_PER_CALL 20
CONNECT_TIME 240
IDLE_TIME 20
LOGICAL_READS_PER_SESSION 50000
LOGICAL_READS_PER_CALL 400
PRIVATE_SGA 1024;
Caution: If you change the value for a resource limit in the DEFAULT profile, you may be making changes to other profiles on your system as well.
CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 1
CPU_PER_SESSION 10000
CPU_PER_CALL 20
CONNECT_TIME 240
IDLE_TIME 20
LOGICAL_READS_PER_SESSION 50000
LOGICAL_READS_PER_CALL 400
PRIVATE_SGA 1024;
Once profiles are created, they're assigned to users with the profile clause in either the create user or alter user statement.
The following example shows each statement in use:
CREATE USER spanky
IDENTIFIED BY orange#tabby
TEMPORARY TABLESPACE temp_01
QUOTA 5M ON temp_01
PROFILE developer;
ALTER USER athena
PROFILE developer;
*Altering Profiles
Once created, you can alter the host system resource limit settings in your profile via the alter profile command. Changing a user profile may be required if user profiles in the database rely on default values set in the DEFAULT profile.
For example, if the resource limit cpu_per_session in DEFAULT is changed from unlimited to 20,000, then cpu_per_session in any user profile that doesn't explicitly set one for itself will be affected. You may not want this to happen, but only by explicitly setting its own value for cpu_per_session will the profile not depend on the DEFAULT profile for the cpu_per_session limit.
Issue the following statement to change a resource limit in a profile:
ALTER PROFILE developer LIMIT
CPU_PER_SESSION UNLIMITED;
Any option in any profile can be changed at any time. However, the change will not take effect for users assigned to that profile until the user logs out and logs back in.
*Dropping Profiles
If you want to drop a user profile from the database, do so by executing the drop profile statement. What happens if you try to drop a profile that's already been assigned to users? In that case, you must use the drop profile cascade command.
After you issue drop profile cascade, Oracle switches users assigned to the dropped profile back to the DEFAULT profile. For obvious reasons, the DEFAULT profile can't be dropped.
Let's look at an example of the drop profile command:
DROP PROFILE developer CASCADE;
To set resource limits properly, you need to gather information about how users are utilizing the host machine in database sessions. Employ the audit session command. You learn more about auditing in another discussion.
Resource limits you can gather information for include
- connect_time
- logical_reads_per_session
- logical_reads_per_call
*Creating Profiles with Composite Limits
Once you've set resource costs, you can assign composite limits to your users. Composite limits restrict database use by specifying a limit for how much a host machine resource can be used per session.
Each time the session uses a resource, Oracle tallies the total resource use for that session. When the session hits the composite_limit, the session is terminated.
Profiles are altered to include a composite_limit with the alter profile statement:
ALTER PROFILE developer LIMIT
COMPOSITE_LIMIT 500;
Question 20
Question 21
Question 22
Question 23
Topic 7.1 Exercises
* Exercise 1
Try administering a profile in Oracle9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Create a user profile that has host system usage restrictions. |
| 2 | Assign the new profile to an existing user. |
| 3 | Alter a resource limit setting in the profile. |
| 4 | Drop the profile and switch the user back to the DEFAULT profile. |
In this lesson, you learned how to create a user profile that has host system usage restrictions.
You also learned that the DEFAULT profile will be used to define any resources not explicitly assigned limits when you create a profile. DEFAULT will also be used for any users not assigned a profile. The DEFAULT profile isn't very restrictive of host system resources.
You learned how to alter host system resource settings in a profile with the alter profile command. Finally, you learned how to drop a user profile from the database and assign the DEFAULT profile using the drop profile cascade command.
Lesson 8. Administering Passwords Using Profiles
Oracle includes features that are designed to make it harder than ever to hack the Oracle database as an authorized user without knowing the user's password. This protects the integrity of assigned usernames as well as the overall data integrity of the Oracle database.After completing this lesson, you should be able to:
- Describe the account management features available in Oracle
- Identify password aging and rotation features available in Oracle
- Explain how Oracle manages password complexity verification
Advanced password management features are available in Oracle through the use of profiles. These features include
- Account locking
- Password aging and expiration
- Password history
- Password complexity requirements
Though not required to enable password management, you can run the utlpwdmg.sql script as SYS to support that functionality.
This script can be found in the rdbms/admin subdirectory under the Oracle software home directory. This script makes some additions to the DEFAULT profile for use with password management.
When the password management script is run, all default password management settings defined in the DEFAULT profile are enforced at all times on the Oracle database.
This is unlike other resource limits, which still require that RESOURCE_LIMIT be set to TRUE before the instance starts.
*Account Locking
Account locking lets Oracle lock out an account when a user tries to log into the database unsuccessfully after several attempts. The maximum number of failed attempts is defined per user or by group.
The number of failed attempts is specified by the DBA or security officer, and the failed attempts are tracked by Oracle so if the user fails to log into the database in the specified number of tries, Oracle locks out the user automatically.
A time period for automatic user lockout can be defined so the failed login attempt counter will reset after that time period, and the user may try to log into the database again.
Alternatively, automatic lockout can be permanent, disabled only by the security administrator or DBA. User accounts can also be locked manually by the security administrator or DBA. In this situation, the only way to unlock the account is manually.
The following command shows how to set the parameters for account locking:
ALTER PROFILE default LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME unlimited;
The FAILED_LOGIN_ATTEMPTS setting defines the number of failed login attempts before the account is locked out. In this case, we've set this option to 3, meaning that after three failed attempts at logging, Oracle locks the user's account automatically.
The other option, PASSWORD_LOCK_TIME, indicates the number of days the account is locked after three failed login attempts. In this case, we've specified that the user account stays locked indefinitely, until we, as DBAs, investigate the situation and unlock the account manually.
ALTER PROFILE default LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME unlimited;
*Password Aging
A DBA or security administrator can set a password to have a maximum lifetime in the Oracle database. Once a threshold time period passes, the user must change the password or be unable to access the Oracle database.
A grace period during which the user must change the password can also be defined. If the time of the grace period passes and the user doesn't change the password, the account is locked and only the security administrator can unlock it. A useful technique for creating new users is to create them with expired passwords, so the user enters the grace period on first login and must change the password during that time.
A potential problem arises when users are forced to change their passwords. Sometimes users try to "fool" the system by changing the expired password to something else and then immediately changing the password back.
To prevent this, Oracle9i supports a password history feature that keeps track of recently used passwords and disallows their use for a specified amount of time or number of changes. The interval is defined within the user profile.
The example below implements password aging and rotation.
The PASSWORD_LIFE_TIME parameter defines the lifetime of the password in days after which the password will expire.
The PASSWORD_GRACE_TIME parameter defines the grace period in days for changing the password after the first successful login once the password has expired.
The PASSWORD_REUSE_TIME parameter specifies the maximum number of days before the user can reuse a previous password.
Finally, the PASSWORD_REUSE_MAX parameter specifies the maximum number of times a previous password can be reused.
ALTER PROFILE default LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX 0;
*Password Complexity
Finally, and perhaps most important to the integrity of an Oracle user's account, Oracle provides password complexity verification. There are many commonly accepted practices in creating a password, such as making sure it has a certain character length, that it's not a proper name or word in the dictionary, that it's not all numbers or all characters, and so on.
Too often, however, users don't heed these mandates and create passwords that are easy to decode using any of a number of products available for decoding encrypted password information.
To prevent users from unwittingly subverting the security of the database, Oracle supports the automatic verification of password complexity with the use of a PL/SQL function. This can be applied during user or group profile creation to prevent users from creating passwords of insufficient complexity.
The checks provided by the default function include making sure the minimum password length is four characters and that the password isn't the same as the username. Also, the password must contain at least one letter, number, and punctuation character, and the password must differ from the previously-defined password by at least three characters.
If the level of complexity verification provided by the given PL/SQL function isn't high enough, a PL/SQL function of sufficient complexity may be defined by your organization. It will be subject to certain restrictions. The overall call syntax must conform to the details in the code listing shown on the next page.
The new routine must be assigned as the password verification routine in the user's profile or the DEFAULT profile.
The following must be present in the create profile statement: password_verify_function user_pwcmplx_fname, where user_pwcmplx_fname is the name of the user-defined password complexity function.
Some other constraints on the definition of this function are that an appropriate error must be returned if the routine raises an exception or if the verification routine becomes invalid, and that the verification function will be owned by SYS and used in system context.
The call to the PL/SQL complexity verification function must conform to the following parameter-passing and return-value requirements:
USER_PWCMPLX_FNAME
( user_id_parm IN VARCHAR2,
new_passwd_parm IN VARCHAR2,
old_passwd_parm IN VARCHAR2
) RETURN BOOLEAN;
Here's an example that shows the coding used in a password complexity function. This is a simplified and modified block of code similar to the password verification function provided with Oracle. It will be explained on the following pages.
CREATE OR REPLACE FUNCTION my_pwver (
x_user IN VARCHAR2,
x_new_pw IN VARCHAR2,
x_old_pw IN VARCHAR2
)RETURN BOOLEAN IS
BEGIN
IF LENGTH(x_new_pw) < 6 THEN
RAISE_APPLICATION_ERROR(-20001, 'New password too short.');
ELSIF x_new_pw = x_user THEN
RAISE_APPLICATION_ERROR(-20002, 'New password same as username');
ELSIF x_new_pw = x_old_pw THEN
RAISE_APPLICATION_ERROR(-20003, 'New password same as old');
ELSE
RETURN(TRUE);
END IF;
END;
The function will check three things: that the new password is not the same as the username, that the new password is at least six characters long, and that the new password is not the same as the old password.
When the DBA creates a username, the verification process is called to determine whether the password is appropriate. If the function returns TRUE, the DBA will be able to create the username. If not, the user creation will fail.
CREATE OR REPLACE FUNCTION my_pwver (
x_user IN VARCHAR2,
x_new_pw IN VARCHAR2,
x_old_pw IN VARCHAR2
)RETURN BOOLEAN IS
BEGIN
IF LENGTH(x_new_pw) < 6 THEN
RAISE_APPLICATION_ERROR(-20001, 'New password too short.');
ELSIF x_new_pw = x_user THEN
RAISE_APPLICATION_ERROR(-20002, 'New password same as username');
ELSIF x_new_pw = x_old_pw THEN
RAISE_APPLICATION_ERROR(-20003, 'New password same as old');
ELSE
RETURN(TRUE);
END IF;
END;
The function can be defined in the profile as follows:
ALTER PROFILE default LIMIT
PASSWORD_VERIFY_FUNCTION my_pwver;
*Password Management Resource Limits
After the utlpwdmg.sql script is run, default values will be specified for several password-management resource limits. Each limit is explained below. The default value for each of these settings is unlimited or NULL.
| Resource limits | Explanation |
|---|---|
| failed_login_attempts | The number of unsuccessful attempts at login a user can make before the account locks. |
| password_life_time | The number of days a password will remain active. |
| password_reuse_time | The number of days before the password can be reused. |
| password_reuse_max | The number of times the password must be changed before a password can be reused. |
| password_lock_time | The number of days after which Oracle will unlock a user account that is locked automatically when the user exceeds failed_login_attempts. |
| password_grace_time | The number of days during which an expired password must be changed by the user before Oracle permanently locks the account. |
| password_verify_function | The function used for password complexity verification. |
Here's a sample code that creates a new profile with all the previous password parameters:
CREATE PROFILE tmp_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME unlimited
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 30
PASSWORD_VERIFY_FUNCTION my_pwver
PASSWORD_GRACE_TIME 5;
Question 24
Question 25
Question 26
Question 27
Topic 8.1 Exercises
* Exercise 1
Try administering passwords using profiles in Oracle9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Run the utlpwdmg.sql script to support the functionality of password management. |
| 2 | Alter the DEFAULT profile so if a user has two failed attempts at logging, Oracle locks the account. |
| 3 | Set the lifetime of a password to 30 days and the amount of time before a user can reuse a password to 180 days. |
| 4 | Give users a grace period of 5 days to change their password after the first successful login. |
| 5 | Use a PL/SQL function (either your own or the one Oracle provides) to enforce password complexity requirements. |
In this lesson, you learned how to run the utlpwdmg.sql script to support functionality of password management. You learned that password management features include account locking, password aging and expiration, password history, and password complexity requirements.
You learned how to lock out an account after unsuccessful attempts to log into the database. You also learned how to age passwords and how to ensure that users change their passwords and use different passwords.
You learned how to use a PL/SQL function to prevent users from creating passwords of insufficient complexity. Finally, you learned that you can use a customized PL/SQL function to enforce password complexity requirements.
Lesson 9. Obtaining Profile Information
Data dictionary views can be used to obtain information about resource-usage limits defined for profiles and about the profiles that have been assigned to users.After completing this lesson, you should be able to:
- Find the profile assigned to a user
- Determine resource cost and resource limit settings
- Obtain the resource settings for a particular profile
The following dictionary views offer information about the resource-usage limits defined for profiles and about the profiles that have been assigned to users:
- DBA_PROFILES
- RESOURCE_COST
- USER_RESOURCE_LIMITS
- DBA_USERS
DBA_PROFILES contains specific information about the resource-usage parameters specified in conjunction with each profile.
RESOURCE_COST identifies all resources in the database and their corresponding cost, as defined by the DBA. Cost determines a resource's relative importance of use.
USER_RESOURCE_LIMITS identifies the system resource limits for individual users, as determined by the profile assigned to the users.
DBA_USERS offers information about the profile assigned to a user, current account status, lock date, and password expiry date.
Spend a little time on your Oracle database gaining familiarity with the contents of each of these dictionary views before taking the Oracle9i DBA Fundamentals I exam.
Question 28
Question 29
Question 30
Question 31
Topic 9.1 Exercises
* Exercise 1
Try obtaining profile information from the data dictionary in Oracle9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Find the profile assigned to a user using a data dictionary view. |
| 2 | View resource cost and resource limit settings using two separate data dictionary views. |
| 3 | Find the resource settings in a particular profile using a data dictionary view. |
In this lesson, you learned how to use data dictionary views to obtain information about profiles.
You learned how to find the profile assigned to a user with the DBA_USERS view.
You also learned how to determine resource cost and resource limit settings using the RESOURCE_COST and USER_RESOURCE_LIMITS views.
Finally, you learned how to find the resource settings in a particular profile with the DBA_PROFILES view.
Lesson 10. Course In Review
This lesson includes questions and material covering the entire course. You can use it to review and study for the exam.Topic 10.1 Review Notes
*Review Notes| 1. | Understand how to set up new users in Oracle using the create user command. Know each of the components of that statement as well. In particular, pay attention to the default tablespace and temporary tablespace clauses. |
| 2. | Be aware of the clauses that force users to change their password regularly and after they first login. |
| 3. | Understand how to use the alter user statement to change a user's ID configuration. Also, be aware that the only aspect of a user's ID that can be changed with the alter user statement that cannot also be set with the create user statement is the default role clause. |
| 4. | Remember that users can change only their own passwords. All other aspects of changing a person's user ID must be completed by you, the DBA. |
| 5. | Know the views you might use to find information about users in your Oracle database: DBA_USERS and DBA_TS_QUOTAS. |
| 6. | Understand where to look in the data dictionary for information about where a user's database objects may reside: DBA_OBJECTS and DBA_SEGMENTS. |
| 7. | Know that you must set the RESOURCE_LIMIT initialization parameter before Oracle will enforce resource limits according to your profile settings. |
| 8. | Be sure you can identify the different levels of host system resource settings: session-level, call-level, and composite limits. To set composite limits, you use the alter resource cost command to define cost values for resources. |
| 9. | Be sure you understand the basic purpose behind profiles. They prevent the misuse of underlying host system resources by setting limits on those resources at the user level. |
| 10. | Know the purpose of the DEFAULT profile. It's assigned to users when you don't specify a profile clause in the create user command. It isn't very restrictive, however — all host resource-usage limits are set to unlimited. |
| 11. | Understand how to create profiles with the create profile command. If you don't set an aspect of host system limits in your profile, Oracle defaults that host system limit to whatever setting is present in the DEFAULT profile. |
| 12. | To drop a profile that's been assigned to a user, you must use the cascade option in the drop profile command. Oracle switches the user back to the DEFAULT profile in this situation. |
| 13. | Understand the account management features available in Oracle. You can lock and unlock user accounts. |
| 14. | Be sure you can identify the password aging and rotation features available in Oracle. You can specify that Oracle shouldn't enable the reuse of passwords for a period of time, and that Oracle should force users to change passwords periodically. |
| 15. | Know how Oracle manages password complexity verification in the database, and be sure you can describe the use of PL/SQL functions for this purpose. |
| 16. | Remember that the profile assigned to a user can be found in the DBA_USERS view. |
| 17. | Understand that resource cost and resource limit settings can be found in the RESOURCE_COST and USER_RESOURCE_LIMITS views, respectively. |
| 18. | Be able to explain why the DBA_PROFILES view is handy for finding the resource settings in a particular profile. |
Question 32
Question 33
Question 34
Question 35
Question 36
Question 37
Question 38
Topic 10.2 Exam Preparation
*Exam PreparationThis page contains a bulleted list of fast facts to review, or crib notes for the days leading up to the exam.
- New database users are created with the create user statement.
- User definitions can be altered with the alter user statement and dropped with the drop user statement. Users can issue the alter user statement only to change their password.
- Data dictionary views, such as DBA_USERS and DBA_PROFILES, can be used to find information about a database user.
- Users in operating system-authenticated database environments generally have their usernames preceded by OPS$ at user-creation time.
- User profiles help to limit resource usage on the Oracle database.
- The DBA must set the RESOURCE_LIMIT parameter to TRUE in order to use user profiles.
- A variety of resources, including connection time and CPU time per call, can be limited via profiles.
- Profiles should be created for every type or class of user. Each parameter has a resource limit set for it in a user profile, which can then be assigned to users based on their processing needs.
- Oracle installs a special profile granted to a user if no other profile is defined. This special profile is called DEFAULT, and all values in the profile are set to unlimited.
- Any parameter not explicitly set in another user profile defaults in value to the value specified for that parameter in DEFAULT.
No comments:
Post a Comment