Create Profile

Valid in: SQL, ESQL

The Create Profile statement creates a new user profile.

Syntax

The Create Profile statement has the following format:

[EXEC SQL] CREATE PROFILE profile_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

• profile_name

  Defines the name of the profile that is being created. Must be a valid object name that is unique in the installation.

• priv

  Specifies a subject privilege. Subject privileges apply to the user regardless of the database to which the user is connected. If the privileges clause is omitted, the default is NOPRIVILEGES.

  The priv must be one of the following:

  CREATEDB

  Allows users to create databases.

  TRACE

  Allows users 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 users to change the alter user security audit and alter profile security audit privileges. Also, the user can 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. For details about groups, see Create Group. 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.

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

Description

User profiles are a set of subject privileges and other attributes that can be applied to a user or set of users. Each user can be given a profile, which provides the default attributes for that user.

A profile includes:

• Subject privileges
• Default subject privileges
• Default user groups
• Security auditing attributes
• Expire date

A default profile, changeable by the system administrator, is created during installation that determines the default user attributes when no profile is explicitly specified. The initial default profile is:

• NOPRIVILEGES
• NODEFAULT_PRIVILEGES
• NOEXPIRE_DATE
• NOGROUP
• NOSECURITY_AUDIT

Embedded Usage

The with clause in the embedded Create Profile statement 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 this table:

Locking

The Create Profile statement locks the iiprofile system catalog exclusively.

Related Statements

Alter Profile

Alter User

Create User

Drop Profile

Drop User

Examples: Create Profile

The following are Create Profile statement examples:

1. Specifiy a profile for a particular user.

   create profile dbop;
       create user bspring with profile = dbop;

2. Create a dbop profile with the appropriate privileges to maintain a database.

   create profile dbop with
   privileges = ( operator, maintain_locations, trace ),
   group = dbopgroup;