Object Permissions

The granting of permissions—also called grants, permits, or object privileges—is usually a DBA responsibility. The permissions system allows for data access to be restricted in several ways. Grants on objects can range from general to specific.

Permissions are classified according to the type of objects they affect, as follows:

• Database
• Table
• View
• Procedure
• Database event
• Role
• Current installation

Ways to Work with Grants

In VDBA, you can access grants in a number of ways using the Database Object Manager. For example, if you expand the branch for a group, role, or user object, there is a Grants sub-branch where you can access all permissions that have been granted to that particular group, role, or user. You can also expand the branch for other object types, such as a database or a table, and use the associated Grantees… sub-branch to access all groups, roles, and users that have been granted each permission allowed for that type of object.

In the Database Object Manager window, you can:

• Grant any permission allowed for a particular object type to any group, role, or user (including public, which encompasses all current and future users)
• View all types of object permissions granted to a particular group, role, or user, or view the permissions granted for a particular object type
• Revoke a previously granted permission

For the detailed steps for performing these procedures, see online help.

Object Ownership and Granting Object Permissions

When you create an object, you become the owner of that object. As the owner of an object, you are automatically entitled to grant and revoke permissions for it (with views, you must also own the base tables). When you grant permissions for an object (other than a database) to another user, you can also grant permission for that user to grant permissions for the object to other users, and you can likewise revoke that permission if necessary. For more information, see online help.

You can also accomplish these tasks using the grant and revoke SQL statements. For more information, see the SQL Reference Guide.

Database Grants

Database permissions are defined on the database as a whole. They set a number of limits that affect the authorization identifiers (that is, groups, roles, users, or public) specified when the grant is defined.

The valid database permissions are summarized below:

• Access

  Enables grantees to connect to the database. By default, all authorization identifiers can connect to all public databases. Private databases, on the other hand, can only be accessed by users who are explicitly granted permission to access them. Access permission to a private database can be granted in any of the following ways:

  • Using a database grant.
  • Enabling the database under Access to Non-Granted Databases in the appropriate dialog (for example, the Create User dialog).
• Connect Time Limit (also called connect_time_limit)

  Specifies the maximum time (in seconds) that a session can consume. By default, there is no connect time limit.

• Create Procedure (also called create_procedure)

  Enables grantees to create database procedures in the database. By default, all authorization identifiers can create database procedures.

• Create Table (also called create_table)

  Enables grantees to create tables in the database. By default, all authorization identifiers can create tables.

• Database Admin (also called db_admin)

  Gives grantees unlimited database privileges for the database and the ability to impersonate another user (using the -u flag). By default, this permission is granted to the DBA (owner) of the database and to any user with the security privilege, such as the system administrator. For all other users, the default is not to allow unlimited database privileges.

• Idle Time Limit (also called idle_time_limit)

  Specifies the maximum time that a session can take between issuing statements. By default, there is no idle time limit.

• Lockmode

  Enables grantees to issue the set lockmode statement. By default, all authorization identifiers can issue the set lockmode statement.

• Query Cost Limit (also called 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 (also called 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 (also called query_io_limit)

  Specifies the maximum number of I/O requests per query on the database. By default, authorization identifiers are allowed an unlimited number of I/O requests.

• Query Page Limit (also called query_page_limit)

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

• Query Row Limit (also called query_row_limit)

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

• Select Syscat (also called select_syscat)

  Allows a session to query system catalogs to determine schema information. By default, sessions are allowed to query the system catalogs.

• Session Priority (also called session_priority)

  Determines whether a session is allowed to change its priority, and if so what its initial and highest priority can be. By default, a session cannot change its priority.

• Table Statistics (also called table_statistics)

  Allows grantees to view and create database table statistics. By default, authorization identifiers can view and create table statistics.

• Update Syscat (also called update_syscat)

  Allows grantees to update system catalogs. By default, authorization identifiers are not allowed to update system catalogs.

Note: Each permission has a corresponding preventing permission to specifically disallow the permission. For example, to prevent access to the database, specify the No Access (also called noaccess) permission.

Most of the database permissions are prohibiting permissions—if not specified, the default is no restrictions. Prohibiting permissions, even if defined, are not enforced for the DBA of the database or any user with the security privilege, such as the system administrator.

Note: To override the default for database permission, create a grant for the permission that specifies the grantee as public.

The database privileges query_io_limit and query_row_limit are enforced based on estimates from the Ingres query optimizer. If the optimizer predicts that a query can require more I/O operations or return more rows than are allowed for the session, the query is aborted prior to execution. This prevents resource consumption by queries that are not likely to succeed.

A session's database permissions are calculated when the session connects to the database and remain in effect for the duration of the session. If, after a session connects to a database, the database permissions for one of that session's authorization identifiers are changed, the active session is not affected. Any new sessions that are established with the same authorization identifiers can be subject to the revised database permissions.

Example: Grant to Prohibit a Group From Creating Tables

This example of a database grant prohibits a particular group from being able to create tables and database procedures in a particular database.

1. In VDBA, select the appropriate database, and then select the Grantees on Database branch.
2. Open the Grant Database dialog. For details, see online help.
3. Enable Groups in the Grantees group box. Then, in the box below, identify the group to which you want to grant the permissions.
4. Ensure that the appropriate database is enabled under Databases. (By default, the database whose Grantees on Database branch was selected when you opened the dialog is enabled.)
5. Enable No Create Procedure and No Create Table in the Privileges group box and click OK.

   Two grants are created for the specified group. You can see the grants by expanding the Grantees for No Create Proc and Grantees for No Create Table sub-branches.

Example: Grant to Set Query_row-limit Privilege for a User

This example sets the query_row_limit privilege for a particular user to 1000,

1. In VDBA, open the Grant Database dialog. For details, see online help.
2. Enable Users in the Grantees group box. Then, in the box below, identify the user to which you want to grant the permission.
3. Under Databases, ensure that the appropriate database is enabled. (By default, the database whose Grantees on Database branch was selected when you opened the dialog is enabled.)
4. Enable Query Row Limit in the Privileges group box, type 1000 in the corresponding edit control, and click OK.

   A grant is created for the specified user. You can see it by expanding the Grantees for Query Row Limit sub-branch.

You can supersede an existing grant by creating another grant for the same authorization identifier and specifying a different value for the privilege. For example, assume that you want to change the query row limit privilege set in the previous example to 250. Follow the procedure enumerated in the previous example and type 250 in Step 3. The new grant replaces the old 1000-row grant.

Similarly, you can revoke a privilege that was previously granted by creating a grant and selecting the corresponding prevented privilege. For example, to return the user's query row limit to an undefined value, follow the procedure enumerated in the previous example and enable No Query Row Limit in Step 3.

The user's query row limit privilege for the database becomes undefined as a result of creating this new grant (the old limit of 1000 is not re-established). If no value for query_row_limit has been defined for any of the other authorization identifiers associated with this user's session, then the number of rows that the session's queries can return is unrestricted.

Note: This method of revoking a privilege is given to illustrate a point, but it is not recommended because it has a negative impact on performance. To revoke the grant in the most efficient way, use one of the standard techniques described in online help.

Table and View Grants

Ingres allows data sharing and updating if users have been issued grant permissions on the tables or views mentioned in the query.

The following query permissions can be granted on both tables and views:

• Select

  Enables grantees to select rows from the table or view, for example using a select statement or a where clause.

• Insert

  Enables grantees to add rows to the table or view, for example using an insert statement.

• Delete

  Enables grantees to delete rows from the table or view, for example using a delete statement.

• Update

  Enables grantees to change existing rows in the table or view, for example using an update statement. An update grant can apply to all columns in the table or view, or only to specific columns.

The following query permissions can be granted on tables only—they are not applicable to views:

• Copy Into (also called copy_into)

  Enables grantees to copy the contents of the table to a data file, for example using the into clause of the copy statement.

• Copy From (also called copy_from)

  Enables grantees to copy the contents of a file to the table, for example using the from clause of the copy statement.

• References

  Enables grantees to create tables that reference the table. A references grant can apply to all columns in the table, or only to specific columns.

  If a user is not the owner and does not have the references permission on a table, that user cannot create a referential constraint that references the table. For more information on referential constraints, see the chapter "Managing Tables and Views."

Table and view permissions are enabling permissions—if no permission is granted, the default is to prohibit access. Table and view permissions are not enforced for the owner of the table or view.

Procedure Grants

For database procedures, the only valid permission is the execute permission, which allows the grantees to execute the procedure. Granting permission to execute a procedure makes database queries contained in the procedure code available to grantees. Granting execute permission to a database procedure also allows grantees to create rules that trigger the procedure. For more information on rules, see the chapter "Ensuring Data Integrity."

The execute permission is an enabling permission—if it is not specifically granted, the default is to prohibit execution. This permission is not enforced for the owner of the procedure.

Permission to create procedures in the database is described in Database Grants.

Database Event Grants

Database events are discussed in detail in the chapter "Ensuring Data Integrity." The valid database event permissions are summarized below:

• Raise

  Allows grantees to raise the database event (using the raise dbevent statement).

• Register

  Allows grantees to register to receive the database event (using the register dbevent statement).

These are enabling permissions—if not specifically granted, the default is to prohibit execution. Database event permissions are not enforced for the owner of the event.

Role Grants

When a role is created, an implicit grant is issued on the role to the user creating the role. Role access must be granted to other users (or public) before they can use the role. Role access is an enabling permission—if it is not specifically granted, the default is to prohibit access to the role.

Grants and Data Access Restriction

Grants allow for data access to be restricted in the following ways:

• Operational restrictions (for example, select, insert, update and delete permissions applied to some or all of the columns of a table)
• Data value restrictions (data restrictions), which are implemented through views.
• Resource restrictions, which are permissions defined for the database as a whole, rather than individual tables or columns.

In a session where permissions are in effect, when you issue a query (for example, from the SQL Scratchpad window in VDBA or from an application) the query is passed to the Ingres DBMS Server. Ingres then evaluates the grants on the tables involved in the query. If an operation does not pass an operational restriction, an error message is returned.

If an operation does not pass a data restriction, it means that views are being used and grants have been placed on the views, but the user authorization does not pass the grants on the data. In this case no error is returned, but the number of rows returned is affected. For example, if Mary is accessing a view that returns rows only from the Shoe department, then if she asks for information from the Toy department, no rows are returned.