GRANT

>>-GRANT-------------------------------------------------------->
 
      (1)                                   (2)
>--+------+-+-| Database-Level Privileges |------+--TO--+-PUBLIC---------+-+------+-><
   |      | |                             (3)    |      | .-,----------. | |      |
   |      | '-DEFAULT ROLE--| Role Name |--------'      | V            | | |      |
   |      |                                             '-----'user'---+-' |      |
   |      |               (3)                                              |      |
   |      '-| Role Name |-------| TO Options |-----------------------------'      |
   |                              (4)                                             |
   '-+-| Table-Level Privileges |-------------------------------+--| TO Options |-'
     |  (1)                                (5)                  |
     '------+-| Routine-Level Privileges |--------------------+-'
            |  (6)                                   (7)      |
            '--------+-| Language-Level Privileges |------+---'
                     |                           (8)      |
                     +-| Type-Level Privileges |----------+
                     |                               (9)  |
                     '-| Sequence-Level Privileges |------'
 
TO Options:
 
|--TO--+-+-PUBLIC---------+--+-------------------+-+--+---------------+--|
       | | .-,----------. |  '-WITH GRANT OPTION-' |  '-AS--'grantor'-'
       | | V            | |                        |
       | '-----'user'---+-'                        |
       | .-,----------.                            |
       | V            |                            |
       '---+-'role'-+-+----------------------------'
           '-'user'-'

Notes:

Informix extension
See page Database-Level Privileges
See page Role Name
See page Table-Level Privileges
See page Routine-Level Privileges
Dynamic Server only
See page Sequence-Level Privileges (IDS)
See page Type-Level Privileges (IDS)
See page Sequence-Level Privileges (IDS)

Element	Description	Restrictions	Syntax
grantor	Authorization identifier of a user who can use REVOKE to cancel the effects of this GRANT statement. If AS clause is omitted, default is login name of user issuing this statement	Must be valid user name (not a role name)	Owner Name, p. Owner Name
role	Name of an existing role to which you grant one or more access privileges, or to which you assign another role	Must exist in the database	Owner Name, p. Owner Name
user	Authorization identifier of a user to whom you grant one or more access privileges, or to whom you assign a role	Same as for grantor	Owner Name, p. Owner Name

Usage

The GRANT statement extends access privileges to other users that would normally accrue only to the DBA or to the creator of an object. Subsequent GRANT statements do not affect privileges that have already been granted to a user.

You can use the GRANT statement for operations like the following:

Authorize others to use or administer a database that you create
Allow others to view, alter, or drop a table, synonym, view or (for Dynamic Server only) a sequence object that you create
Allow others to use a data type or the SPL language, or (for Dynamic Server only) to execute a user-defined routine (UDR) that you create
Assign a role and its privileges to users, or to PUBLIC, or to another role
Assign a default role to one or more users or to PUBLIC

You can grant privileges to a previously created role or to a built-in role. You can grant a role to PUBLIC, to individual users, or to another role.

If you enclose grantor, role, or user in quotation marks, the name is case sensitive and is stored exactly as you typed it. In an ANSI-compliant database, if you do not use quotation marks as delimiters, the name is stored in uppercase letters.

Privileges that you grant remain in effect until you cancel them with a REVOKE statement. Only the grantor of a privilege can revoke that privilege. The grantor is the person who issues the GRANT statement, unless the AS grantor clause transfers the right to revoke those privileges to another user.

Only the owner of an object or a user to whom privileges were explicitly granted with the WITH GRANT OPTION keywords can grant privileges on an object. Having DBA privileges is not sufficient. As DBA, however, you can grant a privilege on behalf of another user by using the AS grantor clause. For privileges on database objects whose owner is not a user recognized by the operating system (for example, user informix), the AS grantor clause is useful.

The keyword PUBLIC extends the specified privilege or role to all users. If you intend to restrict privileges that PUBLIC already holds to only a subset of users, you must first revoke those privileges from PUBLIC.

When database-level privileges conflict with table-level privileges, the more restrictive privileges take precedence.

To grant privileges on one or more fragments of a table that has been fragmented by expression, see GRANT FRAGMENT.

Database-Level Privileges

Database-Level Privileges:
 
|--+-CONNECT--+-------------------------------------------------|
   +-RESOURCE-+
   '-DBA------'

When you create a database with the CREATE DATABASE statement, you are the owner and automatically receive all database-level privileges.

The database remains inaccessible to any other users until you, as DBA, grant database privileges to them.

As database owner, you also receive table-level privileges on all tables in the database automatically. For more information about table-level privileges, see Table-Level Privileges.

Recommendation:

Only user informix can modify system catalog tables directly. Except as noted specifically in your database server documentation, however, do not use DML statements to insert, delete, or update rows of system catalog tables directly, because modifying data in these tables can destroy the integrity of the database.

Database access levels are, from lowest to highest, Connect, Resource, and DBA. Use the corresponding keyword to grant a level of access privilege.

Privilege	Effect
CONNECT	Lets you query and modify data You can modify the database schema if you own the database object that you intend to modify. Any user with the Connect privilege can perform the following operations: Connect to the database with the CONNECT statement or another connection statement Execute SELECT, INSERT, UPDATE, and DELETE statements, provided the user has the necessary table-level privileges Create views, provided the user has the Select privilege on the underlying tables Create synonyms Create temporary tables and create indexes on the temporary tables Alter or drop a table or an index, provided the user owns the table or index (or has Alter, Index, or References privileges on the table) Grant privileges on a table or view, provided the user owns the table (or was given privileges on the table with the WITH GRANT OPTION keywords)
RESOURCE	Lets you extend the structure of the database In addition to the capabilities of the Connect privilege, the holder of the Resource privilege can perform the following functions: Create new tables Create new indexes Create new UDRs Create new data types
DBA	Has all the capabilities of the Resource privilege and can perform the following additional operations: Grant any database-level privilege, including the DBA privilege, to another user Grant any table-level privilege to another user or to a role Grant a role to a user or to another role Revoke a privilege whose grantor you specify as the revoker in the AS clause of the REVOKE statement Restrict the Execute privilege to DBAs when registering a UDR Execute the SET SESSION AUTHORIZATION statement Create any database object Create tables, views, and indexes, designating another user as owner of these objects Alter, drop, or rename database objects, regardless of who owns them Execute the DROP DISTRIBUTIONS option of the UPDATE STATISTICS statement 3Execute DROP DATABASE and RENAME DATABASE statements

Privilege

Effect

CONNECT

Lets you query and modify data

You can modify the database schema if you own the database object that you intend to modify. Any user with the Connect privilege can perform the following operations:

Connect to the database with the CONNECT statement or another connection statement
Execute SELECT, INSERT, UPDATE, and DELETE statements, provided the user has the necessary table-level privileges
Create views, provided the user has the Select privilege on the underlying tables
Create synonyms
Create temporary tables and create indexes on the temporary tables
Alter or drop a table or an index, provided the user owns the table or index (or has Alter, Index, or References privileges on the table)
Grant privileges on a table or view, provided the user owns the table (or was given privileges on the table with the WITH GRANT OPTION keywords)

RESOURCE

Lets you extend the structure of the database In addition to the capabilities of the Connect privilege, the holder of the Resource privilege can perform the following functions:

Create new tables
Create new indexes
Create new UDRs
Create new data types

DBA

Has all the capabilities of the Resource privilege and can perform the following additional operations:

Grant any database-level privilege, including the DBA privilege, to another user
Grant any table-level privilege to another user or to a role
Grant a role to a user or to another role
Revoke a privilege whose grantor you specify as the revoker in the AS clause of the REVOKE statement
Restrict the Execute privilege to DBAs when registering a UDR
Execute the SET SESSION AUTHORIZATION statement
Create any database object
Create tables, views, and indexes, designating another user as owner of these objects
Alter, drop, or rename database objects, regardless of who owns them
Execute the DROP DISTRIBUTIONS option of the UPDATE STATISTICS statement
3Execute DROP DATABASE and RENAME DATABASE statements

User informix has the privilege required to alter the tables of the system catalog, including the systables table.

The following example uses the PUBLIC keyword to grant the Connect privilege on the currently active database to all users:

GRANT CONNECT TO PUBLIC

You cannot grant database-level privileges to a role. Only individual users or PUBLIC can hold database-level privileges.

Table-Level Privileges

When you create a table with the CREATE TABLE statement, you are the table owner and automatically receive all table-level privileges. You cannot transfer ownership to another user, but you can grant table-level privileges to another user or to a role. (See, however, RENAME TABLE, which can change both the name and the ownership of a table.)

A user with the database-level DBA privilege automatically receives all table-level privileges on every table in that database.

Table-Level Privileges:
 
          .-PRIVILEGES-.
|--+-ALL--+------------+----------------------------------------+-->
   | .-,------------------------------------------------------. |
   | V                                                        | |
   '---+-INSERT---------------------------------------------+-+-'
       +-DELETE---------------------------------------------+
       |                                               (1)  |
       +-+-UPDATE--------------+--+------------------+------+
       | |  (1)                |  |    .-,------.    |      |
       | '------+-SELECT-----+-'  |    V        |    |      |
       |        '-REFERENCES-'    '-(----column-+--)-'      |
       |  (1)                                               |
       '------+-ALTER--------+------------------------------'
              +-INDEX--------+
              |  (2)         |
              '--------UNDER-'
 
>--ON--+-----------+--+-table---+-------------------------------|
       '-owner-- .-'  +-view----+
                      '-synonym-'

Notes:

Informix extension
Dynamic Server only

Element	Description	Restrictions	Syntax
column	Column on which the References, Select, or Update privilege is granted. Default scope is all columns of table, view, or synonym.	Must be a column of the table, view, or synonym	Identifier, p. Identifier
owner	Name of the user who owns the table, view, or synonym	Must be a valid authorization identifier	Owner Name, p. Owner Name
synonym, table, view	Synonym, table, or view on which privileges are granted. (This can be qualified by owner. name.)	Must exist in the current database	Identifier, p. Identifier

The GRANT statement can list one or more of the following keywords to specify the table privileges that you grant to the same users or roles.

Privilege	Effect
INSERT	Lets you insert rows
DELETE	Lets you delete rows
SELECT	Lets you access any column in SELECT statements. You can restrict the Select privilege to one or more columns by listing the columns.
UPDATE	Lets you access any column in UPDATE statements. You can restrict the Update privilege to one or more columns by listing the columns.
REFERENCES	Lets you define referential constraints on columns. You must have the Resource privilege to take advantage of the References privilege. (You can add, however, a referential constraint during an ALTER TABLE statement without holding the Resource privilege on the database.) You need only the References privilege to indicate cascading deletes. You do not need the Delete privilege to place cascading deletes on a table. You can restrict the References privilege to one or more columns by listing the columns.
INDEX	Lets you create permanent indexes. You must have the Resource privilege to use the Index privilege. (Any user with the Connect privilege can create an index on temporary tables.)
ALTER	Lets you add or delete columns, modify column data types, add or delete constraints, change the locking mode of the table from PAGE to ROW, or add or drop a corresponding ROW data type for your table. It also lets you enable or disable indexes, constraints and triggers, as described in SET Database Object Mode. You must have the Resource privilege to use the Alter privilege. In addition, you also need the Usage privilege for any user-defined data type affected by the ALTER TABLE statement.
UNDER	Lets you create subtables under a typed table (for IDS only)
ALL	Provides all privileges listed above. The PRIVILEGES keyword is optional.

You can narrow the scope of a Select, Update, or References privilege by specifying the columns to which the privilege applies.

Specify the keyword PUBLIC as user if you intend the GRANT statement to apply to all users.

Some simple examples that follow illustrate how to give table-level privileges with the GRANT statement.

The following statement grants the privilege to delete and select values in any column in the table customer to users mary and john. It also grants the Update privilege, but only for columns customer_num, fname, and lname:

GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname)
   ON customer TO mary, john;

To grant the same privileges as those above to all authorized users, use the keyword PUBLIC as the following example shows:

GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname)
   ON customer TO PUBLIC;

For a Dynamic Server database, suppose a user named mary has created a typed table named tab1. By default, only user mary can create subtables under the tab1 table. If mary wants to grant the ability to create subtables under the tab1 table to a user named john, mary must enter the following GRANT statement:

GRANT UNDER ON tab1 TO john

After receiving the Under privilege on table tab1, user john can create one or more subtables under tab1.

GRANT

Syntax

Usage

Database-Level Privileges

Table-Level Privileges