Alter Profile

Valid in: SQL, ESQL

The Alter Profile statement alters a user profile.

Syntax

The Alter Profile statement has the following format:

[EXEC SQL] ALTER [DEFAULT] PROFILE [profile_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})
                           | NOEXPIRE_DATE | EXPIRE_DATE = 'expire_date'
                           | DEFAULT_PRIVILEGES = ( priv {, priv} ) | all
                           | NODEFAULT_PRIVILEGES

• ALTER DEFAULT PROFILE

  Modifies the settings of a default profile. You cannot specify both DEFAULT and a profile_name in the same statement.

• profile_name

  Specifies the name of an existing profile. Each user can be given a profile, which provides the default attributes for that user. The profile_name can be specified using a delimited identifier.

• ADD PRIVILEGES | DROP PRIVILEGES

  Adds or drops privileges to or from the user profile. You cannot use either ADD PRIVILEGES or DROP PRIVILEGES if with_option is specified in the with_clause.

  If a with_clause item is not specified, its value is left unchanged.

• 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 for users with this profile. Must be an existing 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 using this profile. Any valid date can be used. When the expiration date is reached, the user is no longer able to log on. If NOEXPIRE_DATE is specified, this profile has no expiration limit.

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

More Information:

Create Profile

Embedded Usage

In an embedded Alter Profile statement, the with clause can be specified using a host string variable (with :hostvar).

Permissions

You must have maintain_users privileges and be connected to the iidbdb database. Additional privileges are required to perform certain operations, as summarized in the table below:

Locking

The Alter Profile statement locks iiprofile exclusively.

Related Statements

Alter User

Create Profile

Create User

Drop Profile

Examples: Alter Profile

The following examples alter a user profile:

1. Update a default profile by using the alter default profile variant of the alter profile statement.

   alter default profile
   with expire_date = '30 days';

2. Change the default profile to include createdb privileges.

   alter default profile
   add privileges ( createdb );

   Only one of default profile and profile profile_name can be specified.

3. Alter the trusted profile to add the createdb privilege and make the default group trusted_group:

   alter profile trusted
   add privileges ( createdb )
   with group = trusted_group

   All users currently using this profile have the appropriate changes made to their security privilege and group.

4. Alter the security auditing for profile, clerk.

   alter profile clerk
   with security_audit = (query_text, default_events);