Object Privileges

The privilege granted depends on the type of object it affects: TABLE, DATABASE, PROCEDURE, DBEVENT, or SEQUENCE.

Table privileges control access to tables and views. By default, only the owner of the table has privileges for the table. To enable others to access the table, the owner must grant privileges to specific authorization IDs or to public. Table privileges must be granted explicitly.

Valid table privileges are:

Allows grantee to change existing rows. To limit the columns that the grantee can change, specify a list of columns to allow or a list of columns to exclude.

To grant the privilege for specific columns, use the following syntax after the UPDATE keyword in the GRANT statement:

To grant the privilege for all columns except those specified, use the following syntax after the UPDATE keyword in the GRANT statement:

If the column list is omitted, update privilege is granted to all columns of the table or, for views, all updatable columns.

Allows grantee to create referential constraints that reference the specified tables and columns. For details about referential constraints, see CREATE TABLE. A list of columns to allow or to exclude can optionally be specified.

To grant the privilege for specific columns except those specified, use the following syntax after the REFERENCES keyword in the GRANT statement:

To grant the privilege for all columns except those specified, use the following syntax after the REFERENCES keyword in the GRANT statement:

If the column list is omitted, references privilege is granted to all columns of the table. The references privilege cannot be granted on a view.

Allows the grantee to issue the COPY...INTO statement on a table. This privilege can be granted on tables only.

Allows the grantee to issue the COPY...FROM statement on a table. This privilege can be granted on tables only.

Grants the subset of select, insert, update, delete, and references privileges for which the grantor has GRANT OPTION. For details, see GRANT ALL PRIVILEGES Option.

When privileges are granted against a table, the date and timestamp of the specified table is updated, and the DBMS Server recreates query plans for repeat queries and database procedures that see the specified table.

The privileges required to enable the owner of a view to grant privileges on the view are as follows:

View owner must own all tables and views used in the view definition, or view owner or public must have GRANT OPTION for SELECT for the tables and views used in the view definition.

View owner must own all tables and views used in the view definition, or view owner or public must have GRANT OPTION for INSERT for the tables and views used in the view definition.

View owner must own all tables and updatable columns in views used in the view definition, or view owner or public must have GRANT OPTION for UPDATE for the tables and updatable columns in views used in the view definition.

View owner must own all tables and views used in the view definition, or view owner or public must have GRANT OPTION for DELETE for the tables and views used in the view definition.

To grant privileges for views the grantor does not own, the grantor must have been granted the specified privilege WITH GRANT OPTION.

Database privileges control the consumption of computing resources.

To override the default for a database privilege, grant a specific value to PUBLIC. For example, by default, everyone (PUBLIC) has the privilege to create tables. To override the default, grant NOCREATE_TABLE to PUBLIC, and grant the CREATE_TABLE privilege to any user, group, or role that you want to have this privilege. (Users, groups, and roles are referred to collectively as authorization IDs.)

Database privileges do not apply to DBAs in their own databases, nor to security administrators.

The database privileges in effect for a session are determined by the values that were granted to the authorization IDs in effect for the session, according to the following hierarchy:

For example, if different values for CREATE_TABLE are granted to PUBLIC, and to the user, group, and role that are in effect for a session, the value for the role of the session prevails.

Valid database privileges are as follows:

Note: The access bitmask of the iidatabase_info catalog is set only if database utilities are used to change the access rights to the database. Using a GRANT [NO]ACCESS ON DATABASE dbname TO PUBLIC statement does not change the access from global to private or vice versa in iidatabase_info.

Confers unlimited database privileges for the specified database and the ability to specify effective user (using the -u flag). A session that has the DB_ADMIN privilege does not have all the rights that a DBA has; some utilities can be run only by a DBA. The DBA of a database and users with the SECURITY privilege have the DB_ADMIN privilege by default. For all other users, the default is NODB_ADMIN.

Allows the specified authorization IDs to update system catalogs when working in a session connected to the iidbdb.

Allows a session to query system catalogs to determine schema information. When connected to the master database (iidbdb), this includes the master database catalogs such as iiuser and iidatabase. SELECT_SYSCAT can be granted to user, group, role or public, and can only be issued when connected to the iidbdb database.

This privilege restricts user queries against the core DBMS catalogs containing schema information, such as iirelation and iiattribute. Standard system catalogs such as iitables can still be queried.

Limits the total connect time that a session can consume. The connect time is checked periodically by the DBMS Server and if the limit has been exceeded for a session, it is disconnected, rolling back any open database transactions.

The units are seconds. The maximum connection time limit is 2147483647 seconds, approximately 68 years. The minimum connection time limit is 1 second.

As with other database privileges this can be granted to user, group, role or public, and can only be issued when connected to the iidbdb database.

Specifies the time that a session can take between issuing statements. The idle time for each session is checked periodically by the DBMS Server, and if a session exceeds its idle time limit it is disconnected, rolling back any open database transactions.

The units are seconds. The maximum idle time limit is 2147483647 seconds, approximately 68 years. The minimum idle time limit is 1 second. Idle_time_limit can be granted to user, group, role or public, and can only be issued when connected to the iidbdb database.

Default: No limit, that is, a session can remain idle indefinitely without being disconnected.

Determines whether a session is allowed to change its priority, and if so, its initial and highest priority.

If NOSESSION_PRIORITY (the default) is specified, users can not alter their session priority.

If SESSION_PRIORITY is specified, users can alter their session priority, up to the limit determined by the privilege.

Allows the specified authorization IDs to issue the SET JOINOP TIMEOUTABORT statement.

NOTIMEOUT_ABORT prevents the specified users, groups, or roles from issuing the SET JOINOP TIMEOUTABORT statement.

The EXECUTE privilege allows the grantee to execute the specified database procedures. To grant the EXECUTE privilege on database procedures, the owner of the procedure must have GRANT OPTION for all the privileges required to execute the procedure. To grant the EXECUTE privilege on database procedures that the grantor does not own, the grantor must have EXECUTE privilege WITH GRANT OPTION for the database procedure.

Database event privileges are as follows:

Allows the specified authorization IDs to raise the database event (using the RAISE DBEVENT statement)

Allows the specified authorization IDs to register to receive a specified database event (using the REGISTER DBEVENT statement)

The sequence privilege is as follows:

Allows the grantee to execute the NEXT VALUE and CURRENT VALUE functions on the specified sequences. To grant the NEXT privilege on sequences, the grantor must either own the sequence or have NEXT privilege WITH GRANT OPTION for the sequence.