| Team Fly | |
The ''TESTROLE" is then dropped since it is no longer required.
DROP ROLE "TESTROLE";
Now that we've created users and roles, we can fine-tune our management of these by implementing some user policies through profiles, which we will explore next.
A profile can be used to implement a password management policy, as well as limit resources for a user. When you created the user NEWUSER earlier, a password was supplied along with the DEFAULT profile. Using this DEFAULT profile, the user never needs to change their password and there are no limits placed on any system resources. You can create new profiles to implement your corporate password policies in Oracle. For example, you can specify the number of days after which a user must change their password. You can also establish a policy where a password cannot be reused within a certain period of time and must contain a certain number of changes. A function can be used to ensure that a complex password is created by the user. For example, you may require that a password be more than eight characters long, use alpha, numeric, and special characters, and that it does not repeat a character more than twice. This can all be implemented in a function. An account can be locked after a specified number of login attempts and can remain locked for the number of days defined in the profile.
System limits for a user can also be implemented by a profile. These include limiting system resources such as those for CPU, connect, and idle time as well as the number of sessions employed by the user, limits on reads, and the SGA used. You should note, however, that the Database Resource Manager is the preferred way to limit resources and that you should use profiles to manage passwords.
The following is an example of the creation of a new policy that will lock an account after three failed login attempts and will keep the account locked indefinitely. The password needs to be changed every 60 days and the new password will be verified by your custom function COMPLEX_PASSWORD. The old password cannot be reused for 120 days.
CREATE PROFILE "NEWPOLICY" FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 120 PASSWORD_VERIFY_FUNCTION COMPLEX_PASSWORD
Now, let's grant this policy to user NEWUSER:
ALTER USER NEWUSER PROFILE NEWPOLICY;
| Team Fly | |