CREATE USER

SQL Language Guide : 7. SQL Statements : CREATE USER

Share this page

CREATE USER

Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET

The CREATE USER statement defines a new user.

This statement has the following format:

[EXEC SQL] CREATE USER user_name

[WITH with_item {, with_item}]

with_item = NOPRIVILEGES| PRIVILEGES = ( priv {,priv} )
            | NOGROUP | GROUP = default_group
            | SECURITY_AUDIT = ( audit_opt {,audit_opt})
            | NOEXPIRE_DATE | EXPIRE_DATE = 'expire_date'
            | DEFAULT_PRIVILEGES = (priv {,priv})| ALL
| NODEFAULT_PRIVILEGES
            | NOPROFILE | PROFILE = profile_name
            | NOPASSWORD | PASSWORD = 'user_password'
               | PASSWORD = X'encrypted_role_password'
            | EXTERNAL_PASSWORD
            | DBMS_AUTHENTICATION = 'REQUIRED' | 'OPTIONAL'

user_name

Specifies the user name to be created. Must be a valid object name.

priv

Specifies one of the following subject privileges, which apply to the user regardless of the database to which the user is connected.

CREATEDB

Allows the user to create databases.

TRACE

Allows the user to use tracing and debugging features.

SECURITY

Allows the user to perform security-related functions, such as creating and dropping users.

OPERATOR

Allows the user to perform database backups and other database maintenance operations.

MAINTAIN_LOCATIONS

Allows the user to create and change the characteristics of locations.

AUDITOR

Allows the user to register or remove audit logs and to query audit logs.

MAINTAIN_AUDIT

Allows the user to change the ALTER USER SECURITY_AUDIT and ALTER PROFILE SECURITY_AUDIT privileges. Also allows the user to enable, disable, or alter security audit.

MAINTAIN_USERS

Allows the user to perform various user-related functions, such as creating or altering users, profiles, group and roles, and to grant or revoke database and installation resource controls.

CHANGE_PASSWORD

Allows the user to change his password.

default_group

Specifies the default group to which the user belongs. It must be an existing group. For details about groups, see CREATE GROUP.

If the GROUP clause is omitted, the default is NOGROUP, which means the user is not assigned to a group.

audit_opt

Defines security audit options:

ALL_EVENTS

Audits all activity by the user.

DEFAULT_EVENTS

Audits the types of events specified with the ENABLE and DISABLE SECURITY_AUDIT statements. This is the default if the SECURITY_AUDIT clause is omitted.

QUERY_TEXT

Audits the query text associated with specific user queries. Security auditing of query text must be enabled as a whole, using the ENABLE and DISABLE SECURITY_AUDIT statements with the QUERY_TEXT option. For example: ENABLE SECURITY_AUDIT QUERY_TEXT

expire_date

Specifies an optional expiration date associated with each user. Any valid date can be used. Once the expiration date is reached, the user is no longer able to log on. If the expire_date clause is omitted, the default is NOEXPIRE_DATE.

DEFAULT_PRIVILEGES =
( priv {, priv} ) | ALL| NODEFAULT_PRIVILEGES

Defines the privileges initially active when connecting to Vector. These must be a subset of those privileges granted to the user.

ALL

All the privileges held by the profile are initially active.

NODEFAULT_PRIVILEGES

No privileges are initially active. Allows default privileges to be removed.

profile_name

Allows a profile to be specified for a particular user. If the profile clause is omitted, the default is NOPROFILE.

user_password

Allows users to change their own password. If the oldpassword clause is missing or invalid the password is unchanged. In addition, users with the maintain_users privilege can change or remove any password.

EXTERNAL_PASSWORD

Allows a user's password to be authenticated externally to Vector. The password is passed to an external authentication server for authentication.

DBMS_AUTHENTICATION =

Indicates whether DBMS authentication is required or optional.

'REQUIRED'

Allows only connection requests that specify the user name and password defined at the DBMS Server level and to a dbms_authentication enabled DBMS Server to succeed; other connections fail. Users with the “security” privilege, including the installation owner, cannot be defined as DBMS_AUTHENTICATION='REQUIRED'.

'OPTIONAL'

Allows connection requests that specify a user name and password defined at the operating system level, an installation password, or a Kerberos principle to succeed. In particular, this allows a local connection to a dbms_authentication server to succeed through implicit OS login authentication, without requiring the DBMS password.

Default: 'OPTIONAL'

CREATE USER Examples

1. Create a user with several privileges, and a smaller set of default privileges.

CREATE USER bspring
WITH PRIVILEGES=(CREATEDB, SECURITY, TRACE,
DEFAULT_PRIVILEGES = (TRACE);

2. Create a user that requires authentication through the DBMS, rather than through operating system authentication, installation passwords, or Kerberos authentication. Allow the user to change his password:

CREATE USER fred
WITH PASSWORD='secret', PRIVILEGES = (CHANGE_PASSWORD),
DBMS_AUTHENTICATION='REQUIRED';