Create User

Valid in: SQL, ESQL

The Create User statement defines a new user.

Syntax

The Create User 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})
                            | NOEXPIREDATE | 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

• 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. If the privileges clause is omitted, the default is NOPRIVILEGES.

  • CREATEDB

    Allows users 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 database and file 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.

• default_group

  Specifies the default group to which the user belongs. Must be an existing group. For details about groups, see Create Group.

  Note: To specify that the user is not assigned to a group, use the NOGROUP option. If the group clause is omitted, the default is NOGROUP.

• audit_opt

  Defines security audit options:

  • ALL_EVENTS

    All activity by the user is audited.

  • DEFAULT_EVENTS

    Only default security auditing is performed, as specified with the ENABLE and DISABLE SECURITY_AUDIT statements. This is the default if the SECURITY_AUDIT clause is omitted.

  • QUERY_TEXT

    Auditing of the query text associated with specific user queries is performed. Security auditing of query text must be enabled as a whole, using the ENALBE and DISABLE SECURITY_AUDIT statements with the QUERY_TEXT option (for example: ENABLE SECURITY_AUDIT QUERY_TEXT).

    Default: DEFAULT_EVENTS if the security_audit clause is omitted.

• 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 Ingres. 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 Ingres. The password is passed to an external authentication server for authentication.

Embedded Usage

In an embedded Create User statement, specify the with clause using a host string variable (with :hostvar).

Permissions

You must have the maintain_users privilege and be connected to the iidbdb database.

Additional privileges are required to perform certain operations, as summarized here:

Locking

The Create User statement locks pages in the iiuser system catalog.

Related Statements

Alter Profile

Alter User

Create Profile

Drop Profile

Drop User

Examples: Create User

The following are Create User statement examples:

1. Create a new user, specifying group and privileges.

   create user bspring with
   group = publishing,
   privileges = (createdb, security);

2. Create a new user, group and no privileges.

   create user barney with
   group = sales,
   noprivileges;

3. Define user expiration date.

   create user bspring
   with expire_date = '6-jun-1995'

4. Define an expiration date relative to the date the statement is executed.

   create user bspring
   with expire_date = '1 month'

5. Specify no expiration date for a user.

   create user bspring
   with noexpire_date

6. Create a user with a password.

   create user bspring
   with password='mypassword';

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

   create user bspring
   with privileges=(write_down, write_fixed, trace,
   default_privileges = (trace);

8. Specify a profile for a particular user.

   create user bspring with profile = dbop

   where dbop is an existing profile.

9. Specify a user with an externally verified password.

   create user bspring
   with external_password;