ALTER USER|ROLE… PROFILE MANAGEMENT CLAUSES v17
Name
ALTER USER|ROLE
Synopsis
option
can be the following compatible clauses:
option
can be the following non-compatible clauses:
For information about the administrative clauses of the ALTER USER
or ALTER ROLE
command that are supported by EDB Postgres Advanced Server, see the PostgreSQL core documentation.
Only a database superuser can use the ALTER USER|ROLE
clauses that enforce profile management. The clauses enforce the following behaviors:
Include the
PROFILE
clause and aprofile_name
to associate a predefined profile with a role or to change the predefined profile associated with a user.Include the
ACCOUNT
clause and theLOCK
orUNLOCK
keyword to place the user account in a locked or unlocked state.Include the
LOCK TIME 'timestamp'
clause and a date/time value to lock the role at the specified time and unlock the role at the time indicated by thePASSWORD_LOCK_TIME
parameter of the profile assigned to this role. IfLOCK TIME
is used with theACCOUNT LOCK
clause, only a database superuser can unlock the role with theACCOUNT UNLOCK
clause.Include the
PASSWORD EXPIRE
clause with theAT 'timestamp'
keywords to specify a date/time for the password associated with the role to expire. If you omit theAT 'timestamp'
keywords, the password expires immediately.Include the
PASSWORD SET AT 'timestamp'
keywords to set the password modification date to the time specified.Include the
STORE PRIOR PASSWORD {'password' 'timestamp} [, ...]
clause to modify the password history, adding the new password and the time the password was set.
Each login role can have only one profile. To discover the profile that's currently associated with a login role, query the profile
column of the DBA_USERS
view.
Parameters
name
The name of the role to associate with the specified profile.
password
The password associated with the role.
profile_name
The name of the profile to associate with the role.
timestamp
The date and time when the clause is enforced. When specifying a value for timestamp
, enclose the value in single quotes.
Notes
For information about the Postgres-compatible clauses of the ALTER USER
or ALTER ROLE
command, see the PostgreSQL core documentation.
Examples
This example uses the ALTER USER… PROFILE
command to associate a profile named acctg
with a user named john
:
This example uses the ALTER ROLE… PROFILE
command to associate a profile named acctg
with a user named john
: