This chapter presents methods for managing users and resources in your system. The objectives important to this chapter are:
The chapter begins with a few more details about two of the default users in Oracle9i. We are told that the SYS user owns many tables needed to run a database, many of the views, and many packages and procedures. The SYS user has the rights needed to start and stop an instance, and to back up and recover a database. This user ID also has enough rights to drop critical tables in the database, so the text recommends using the SYSTEM user ID for routine maintenance tasks.
The SYSTEM user ID is able to use the DBA role (explained in Chapter 12) which allows it to create new users, monitor the database, and manage resources.
The command to create a user is
The CREATE USER command can also set several other options for a user ID:
After creating a user, you should grant the right to log in to the user.
The command is
You can run a query to see the settings for a user:
Once the settings for a user are known, you can use the ALTER USER command to change the settings. The syntax is similar to the syntax used when setting the initial values.
Users can be deleted with the DROP USER username command. It should be followed by the keyword CASCADE if the user being dropped owns any database resources.
The text continues with a discussion of profiles. As noted above, each user must be assigned to one and only one profile. You can assign as many users as you wish to any profile. You can restrict what a user can do with a profile, but profiles are not active unless RESOURCE LIMIT is set to TRUE in the init.ora file for the database. Profiles are created with the CREATE PROFILE command, and changed with the ALTER PROFILE command. They are deleted with the DROP PROFILE command.
A profile can be used to set seven settings for passwords. These are standard features including limit on FAILED_LOGIN_ATTEMPTS before the account is locked; number of days, or fraction of a day for PASSWORD_LOCK_TIME; number of days in a PASSWORD_LIFE_TIME, and number of days before an expired password is no longer honored (PASSWORD_GRACE_TIME).
A profile can also be used to set nine settings about system resources. Some useful ones are:
The text continues with discussion about managing passwords. It looks like the kind of discussion that is meant to be a clue to certification questions. We are told that only two settings can be made to a password with the ALTER USER command: setting a password, and setting it to expire immediately. Other settings that affect a password are set through profiles. The last variation is that password complexity (requiring that a password be a certain length, composed of certain characters) in done with a profile, an SQL script, and a function (PASSWORD_VERIFY_FUNCTION).
The text lists four views that can be used to view data about profiles, passwords, and resources.