Creating a New Role Associated with a Profile v12
A database superuser can use clauses of the CREATE USER|ROLE
command to assign a named profile to a role when creating the role, or to specify profile management details for a role. The command syntax related to profile management functionality is:
where option
can be the following compatible clauses:
or option
can be the following non-compatible clauses:
For information about the administrative clauses of the CREATE USER
or CREATE ROLE
command that are supported by Advanced Server, see the PostgreSQL core documentation available at:
https://www.postgresql.org/docs/12/static/sql-commands.html
CREATE ROLE|USER... PROFILE
adds a new role with an associated profile to an Advanced Server database cluster.
Roles created with the CREATE USER
command are (by default) login roles. Roles created with the CREATE ROLE
command are (by default) not login roles. To create a login account with the CREATE ROLE
command, you must include the LOGIN
keyword.
Only a database superuser can use the CREATE USER|ROLE
clauses that enforce profile management; these clauses enforce the following behaviors:
Include the
PROFILE
clause and aprofile_name
to associate a pre-defined profile with a role, or to change which pre-defined profile is associated with a user.Include the
ACCOUNT
clause and theLOCK
orUNLOCK
keyword to specify that the user account should be placed 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, the role can only be unlocked by a database superuser with theACCOUNT UNLOCK
clause.Include the
PASSWORD EXPIRE
clause with the optionalAT 'timestamp'
keywords to specify a date/time when the password associated with the role will expire. If you omit theAT 'timestamp'
keywords, the password will expire immediately.
Each login role may only have one profile. To discover the profile that is currently associated with a login role, query the profile
column of the DBA_USERS
view.
Parameters
name
The name of the role.
profile_name
The name of the profile associated with the role.
timestamp
The date and time at which the clause will be enforced. When specifying a value for timestamp
, enclose the value in single-quotes.
Examples
The following example uses CREATE USER
to create a login role named john
who is associated with the acctg_profile
profile:
john
can log in to the server, using the password 1safepwd
.
The following example uses CREATE ROLE
to create a login role named john
who is associated with the acctg_profile
profile:
john
can log in to the server, using the password 1safepwd
.