Unlocking a Locked Account v11

A database superuser can use clauses of the ALTER USER|ROLE... command to lock or unlock a role. The syntax is:

ALTER USER|ROLE <name>
       ACCOUNT {LOCK|UNLOCK}
       LOCK TIME '<timestamp>'

Include the ACCOUNT LOCK clause to lock a role immediately; when locked, a role’s LOGIN functionality is disabled. When you specify the ACCOUNT LOCK clause without the LOCK TIME clause, the state of the role will not change until a superuser uses the ACCOUNT UNLOCK clause to unlock the role.

Use the ACCOUNT UNLOCK clause to unlock a role.

Use the LOCK TIME 'timestamp' clause to instruct the server to lock the account at the time specified by the given timestamp for the length of time specified by the PASSWORD_LOCK_TIME parameter of the profile associated with this role.

Combine the LOCK TIME 'timestamp' clause and the ACCOUNT LOCK clause to lock an account at a specified time until the account is unlocked by a superuser invoking the ACCOUNT UNLOCK clause.

Parameters

name

The name of the role that is being locked or unlocked.

timestamp

The date and time at which the role will be locked. When specifying a value for timestamp, enclose the value in single-quotes.

Examples

The following example uses the ACCOUNT LOCK clause to lock the role named john. The account will remain locked until the account is unlocked with the ACCOUNT UNLOCK clause:

ALTER ROLE john ACCOUNT LOCK;

The following example uses the ACCOUNT UNLOCK clause to unlock the role named john:

ALTER USER john ACCOUNT UNLOCK;

The following example uses the LOCK TIME 'timestamp' clause to lock the role named john on September 4, 2015:

ALTER ROLE john LOCK TIME ‘September 4 12:00:00 2015’;

The role will remain locked for the length of time specified by the PASSWORD_LOCK_TIME parameter.

The following example combines the LOCK TIME 'timestamp' clause and the ACCOUNT LOCK clause to lock the role named john on September 4, 2015:

ALTER ROLE john LOCK TIME ‘September 4 12:00:00 2015’ ACCOUNT LOCK;

The role will remain locked until a database superuser uses the ACCOUNT UNLOCK command to unlock the role.