Previous Topic

Next Topic

Types of Privileges

Privileges are classed according to the type of objects they affect:

Table Privileges

Control access to individual tables or views

Database Privileges

Control access to database resources

Database Procedure Privileges

Control who can execute individual database procedures

Database Event Privileges

Control who can register for and raise specific database events

Database Sequence Privileges

Control who can retrieve values from individual database sequences

Previous Topic

Next Topic

Table Privileges

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. The following table lists valid table privileges:

Privilege

Description

Select

Allows grantee to select rows from the table.

Insert

Allows grantee to add rows to the table.

Update

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:

(column_name {, column_name)

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

excluding (column_name {, column_name})

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

Delete

Allows grantee to delete rows from the table.

References

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:

(column_name {, column_name})

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

excluding (column_name {, column_name})

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.

Copy_into

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

Copy_from

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

All [privileges]

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

Previous Topic

Next Topic

Table Privileges for Views

The following table lists the privileges required to enable the owner of a view to grant privileges on the view:

Privilege

Description

Select

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.

Insert

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.

Update

View owner must own all tables and updateable columns in views used in the view definition, or view owner or public must have grant option for update for the tables and updateable columns in views used in the view definition.

Delete

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.

Previous Topic

Next Topic

Database Privileges

Database privileges control the consumption of computing resources. Valid database privileges are:

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 database procedures. To override the default, grant nocreate_procedure to public, and grant the create_procedure 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.)

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

This table describes database privileges in detail:

Privilege

Description

access

Access allows the specified authorization IDs to connect to the specified database. Noaccess prevents the specified authorization IDs from connecting.

create_procedure

Create_procedure allows the specified authorization IDs to create database procedures in the specified database. Nocreate_procedure prevents the specified users, groups, or roles from creating database procedures. By default, all authorization IDs can create database procedures.

create_sequence

Create_sequence allows the specified authorization IDs to create, alter and drop sequences in the specified database. Nocreate_sequence prevents the specified authorization IDs from creating sequences. By default, all authorization IDs can create, alter and drop sequences.

create_table

Create_table allows the specified authorization IDs to create tables in the specified database. Nocreate_table prevents the specified authorization IDs from creating tables. By default, all authorization IDs can create tables.

db_admin

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

lockmode

Lockmode allows the specified authorization IDs to issue the set lockmode statement. Nolockmode prevents the specified users, groups, or roles from issuing the set lockmode statement. By default, everyone can issue the set lockmode statement.

query_cost_limit

Specifies the maximum cost per query on the database, in terms of disk I/O and CPU usage. By default, authorization identifiers are allowed an unlimited cost per query.

query_cpu_limit

Specifies the maximum CPU usage per query on the database. By default, authorization identifiers are allowed unlimited CPU usage per query.

query_io_limit

Query_io_limit specifies the maximum estimated number of I/O requests allowed for a single query for the specified authorization IDs when connected to the specified database. Integer must be a non-negative integer (or 0 to specify that no I/O is performed). Noquery_io_limit grants an unlimited number of I/O requests per query. Noquery_io_limit is the default.

query_page_limit

Specifies the maximum number of pages per query on the database. By default, authorization identifiers are allowed an unlimited number of pages per query.

query_row_limit

Query_row_limit integer specifies the maximum estimated number of rows returned by a single query for the specified authorization IDs when connected to the specified database. Integer must be a positive number (or 0 to specify that no rows are returned). Noquery_row_limit allows a single query to return an unlimited number of rows. Noquery_row_limit is the default.

update_syscat

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

select_syscat

Select_syscat allows a session to query system catalogs to determine schema information. When connected to the iidbdb database, 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.

connect_time_limit

Connect_time_limit limits the total connect time that a session can consume. The default is no limit, that is, a session can remain connected indefinitely. 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 approximately 130 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.

Idle_time_limit

Idle_time_limit specifies the time that a session can take between issuing statements. The default is no limit, that is, a session can remain idle indefinitely without being disconnected. 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 approximately 130 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.

session_priority

Session_priority determines whether or not a session is allowed to change its priority, and if so what its initial and highest priority can be.

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.

table_statistics

Table_statistics allows users to view (by way of SQL and statdump) and create (by way of optimizedb) database table statistics.

If statistics exist in the database catalogs the DBMS Server automatically uses them when processing queries, even if the user does not possess this privilege.

Note: About query_cost_limit, query_cpu_limit, query_io_limit, query_page_limit, and query_row_limit: restrictions are enforced based on estimates from the DBMS query optimizer. If the optimizer predicts that a query consumes more I/O's than allowed by the session, the query is aborted prior to execution. The accuracy of the optimizer's estimates can be impeded by out-of-date or insufficient statistics about the contents of tables. For details about table statistics, see the description of the optimizedb command in the Command Reference Guide and the information on the query optimizer in the Database Administrator Guide.

Previous Topic

Next Topic

Database Procedure Privileges

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.

Previous Topic

Next Topic

Database Event Privileges

Valid database event privileges are as follows:

Previous Topic

Next Topic

Database Sequence Privileges

The next privilege 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.

Previous Topic

Next Topic

Privilege Defaults

This table lists privilege defaults:

Privilege

Default

Select

Insert

Delete

Update

Only the owner can perform select, insert, delete, or update operations on objects it owns.

References

Only the table owner can create referential constraints that see its tables.

Execute

Only the owner of a database procedure can execute the procedure.

Raise

Only the owner of a database event can raise the event.

Register

Only the owner of a database event can register to receive the event.

Next

Only the owner of a database sequence can execute the next value and current value operators on the sequence.

This table lists database privileges:

Privilege

Description

query_io_limit

Any user can perform unlimited I/O (noquery_io_limit)

query_row_limit

Any user can obtain unlimited rows (noquery_row_limit).

create_table

Any user can create tables (create_table).

create_procedure

Any user can create database procedures (create_procedure).

create_sequence

Any user can create database sequences (create_sequence).

lockmode

Any user can issue the set lockmode statement (lockmode).

db_admin

For a specified database, the DBA of the database and users that have the security privilege have the db_admin privilege. All other users of the database have nodb_admin privilege.


© 2007 Ingres Corporation. All rights reserved.