Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Purpose
Use the ALTER
PROFILE
statement to add, modify, or remove a resource limit or password management parameter in a profile.
Changes made to a profile with an ALTER
PROFILE
statement affect users only in their subsequent sessions, not in their current sessions.
See Also:
CREATE PROFILE for information on creating a profilePrerequisites
You must have ALTER
PROFILE
system privilege to change profile resource limits. To modify password limits and protection, you must have ALTER
PROFILE
and ALTER
USER
system privileges.
Syntax
alter_profile::=
(resource_parameters::=, password_parameters::=)
Semantics
The keywords, parameters, and clauses in the ALTER
PROFILE
statement all have the same meaning as in the CREATE
PROFILE
statement.
You cannot remove a limit from the DEFAULT
profile.
Please refer to CREATE PROFILE and to the examples in the next section for more information.
Examples
Making a Password Unavailable: Example The following statement makes the password of the new_profile
profile (created in "Creating a Profile: Example") unavailable for reuse for 90 days:
ALTER PROFILE new_profile LIMIT PASSWORD_REUSE_TIME 90 PASSWORD_REUSE_MAX UNLIMITED;
Setting Default Password Values: Example The following statement defaults the PASSWORD_REUSE_TIME
value of the app_user
profile (created in "Setting Profile Resource Limits: Example") to its defined value in the DEFAULT
profile:
ALTER PROFILE app_user LIMIT PASSWORD_REUSE_TIME DEFAULT PASSWORD_REUSE_MAX UNLIMITED;
Limiting Login Attempts and Password Lock Time: Example The following statement alters profile app_user
with FAILED_LOGIN_ATTEMPTS
set to 5 and PASSWORD_LOCK_TIME
set to 1:
ALTER PROFILE app_user LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
This statement causes the app_user
account to become locked for one day after five unsuccessful login attempts.
Changing Password Lifetime and Grace Period: Example The following statement modifies the profile app_user2
PASSWORD_LIFE_TIME
to 90 days and PASSWORD_GRACE_TIME
to 5 days:
ALTER PROFILE app_user2 LIMIT PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 5;
Limiting Concurrent Sessions: Example This statement defines a new limit of 5 concurrent sessions for the app_user
profile:
ALTER PROFILE app_user LIMIT SESSIONS_PER_USER 5;
If the app_user
profile does not currently define a limit for SESSIONS_PER_USER
, the preceding statement adds the limit of 5 to the profile. If the profile already defines a limit, the preceding statement redefines it to 5. Any user assigned the app_user
profile is subsequently limited to 5 concurrent sessions.
Removing Profile Limits: Example This statement removes the IDLE_TIME
limit from the app_user
profile:
ALTER PROFILE app_user LIMIT IDLE_TIME DEFAULT;
Any user assigned the app_user
profile is subject in their subsequent sessions to the IDLE_TIME
limit defined in the DEFAULT
profile.
Limiting Profile Idle Time: Example This statement defines a limit of 2 minutes of idle time for the DEFAULT
profile:
ALTER PROFILE default LIMIT IDLE_TIME 2;
This IDLE_TIME
limit applies to these users:
Users who are not explicitly assigned any profile
Users who are explicitly assigned a profile that does not define an IDLE_TIME
limit
This statement defines unlimited idle time for the app_user2
profile:
ALTER PROFILE app_user2 LIMIT IDLE_TIME UNLIMITED;
Any user assigned the app_user2
profile is subsequently permitted unlimited idle time.