Alter User

Valid in: SQL, ESQL

The Alter User statement changes the characteristics of an existing user.

Use ADD PRIVILEGES to give the user additional privileges. Use DROP PRIVILEGES to remove privileges from the user.

Note: You cannot use either ADD PRIVILEGES or DROP PRIVILEGES if with_option is specified in the with_clause.

Syntax

The Alter User statement has the following format:

[EXEC SQL] ALTER USER user_name

[ADD PRIVILEGES (priv {, priv}) |DROP PRIVILEGES (priv {, priv})]

[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

                            | OLDPASSWORD = 'oldpassword'

• user_name

  Specifies the user name. The user must be an existing Ingres user.

• 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. To specify that the user is not assigned to a group, use the NOGROUP option.

  Default: NOGROUP if the group clause is omitted.

• 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).

• 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.

  Default: NOEXPIRE_DATE if the EXPIRE_DATE clause is omitted.

• DEFAULT_PRIVILEGES

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

• NODEFAULT_PRIVILEGES

  Specifies that the session is started with no privileges active. Allows default privileges to be removed.

• profile_name

  Allows a profile to be specified for a particular user.

  Default: NOPROFILE if the profile clause is omitted.

• 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.

• oldpassword

  Specifies the user's old password.

Embedded Usage

In an embedded Alter User statement, specify the with clause using a host string variable (with :hostvar). The privilege type can be specified using a host string variable.

Permissions

You must be connected to the iidbdb database. The maintain_users privilege is required, except for users who simply want to change their own password. You must have maintain_audit privileges to change security audit attributes.

Locking

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

Related Statements

Create User

Create Profile

Alter Profile

Drop Profile

Examples: Alter User

The following examples change the characteristics of an existing user:

1. Change an existing user, specifying privileges and group.

   alter user bspring with
   group = engineering,
   noprivileges;

2. Change an existing user, specifying privileges and group.

   alter user barney with
   group = marketing,
   privileges = (createdb,trace,security);

3. Specify no expiration date for a predefined user.

   alter user bspring
   with noexpiration_date

4. Allow a user to change their existing password.

   alter user with
   oldpassword='myoldpassword',
   password='mypassword';

5. Allow a user with maintain_users privilege to change or remove any password.

   alter user username
   with password='theirpassword'
   | nopassword

6. Grant createdb privilege to user bspring.

   alter user bspring add privileges ( createdb )

   Specify a profile for a particular user.

   alter user bspring with profile = dbop

   where "dbop" is an existing profile.

7. Specify that a user has an externally verified password.

   alter user bspring
   with external_password;