Informix Guide to SQL: Syntax SQL Statements

Use the GRANT statement to:

• authorize others to use, develop, or administrate a database that you create.
• allow others to view, alter, or drop a table, synonym, or view that you create.
• allow others to use a data type or execute a user-defined routine (UDR) that you create.
• give a role name and its privileges to one or more users.

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

You can grant privileges to a previously created role. You can grant a role to individual users or to another role.

Privileges 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 normally the person who issues the GRANT statement. To transfer the right to revoke, name another user as grantor when you issue a GRANT statement.

The keyword PUBLIC extends a GRANT to all users. If you want to restrict privileges to a particular user that public already has, you must first revoke the right of public to those privileges.

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

When you create a database with the CREATE DATABASE statement, you are the owner. As the database owner, you automatically receive all database-level privileges. The database remains inaccessible to other users until you, as DBA, grant database privileges.

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

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

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

Warning: Although the user informix and DBAs can modify most system catalog tables (only user informix can modify systables), Informix strongly recommends that you do not update, delete, or alter any rows in them. Modifying the system catalog tables can destroy the integrity of the database.

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

GRANT  CONNECT TO  PUBLIC

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 table ownership to another user, but you can grant table-level privileges to another user or to a role.

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

The table that follows lists keywords for granting table-level privileges.

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

Specify keyword PUBLIC as user if you want a GRANT statement to apply to all users.

Some simple examples can help to illustrate how table-level privileges are granted 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 shown in the following example:

GRANT  DELETE,  SELECT,  UPDATE  (customer_num,  fname,  lname)

	ON  customer  TO  PUBLIC 

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.

The ALL keyword grants all table-level privileges to the specified user. If any or all of the table-level privileges do not exist for the grantor, the GRANT statement with the ALL keyword succeeds, but the following SQLSTATE warning is returned:

01007 - Privilege not granted.

For example, assume that the user ted has the Select and Insert privileges on the customer table with the authority to grant those privileges to other users. User ted wants to grant all table-level privileges to user tania. So user ted issues the following GRANT statement:

GRANT ALL ON customer TO tania

This statement executes successfully but returns SQLSTATE code 01007 for the following reasons:

• The statement succeeds in granting the Select and Insert privileges to user tania because user ted has those privileges and the right to grant those privileges to other users.
• The other privileges implied by the ALL keyword were not grantable by user ted and, therefore, were not granted to user tania.

If you grant all table-level privileges with the ALL keyword, the grant includes the Under privilege only if the table is a typed table. The grant of ALL privileges does not include the Under privilege if the table is not based on a row type.

If the table owner grants ALL privileges on a traditional relational table and later changes that table to a typed table, the table owner must explicitly grant the Under privilege to allow other users to create subtables under it.

You grant table-level privileges directly by referencing the table name or an existing synonym. You can also grant table-level privileges on a view.

The table, view, or synonym on which you grant privileges must reside in the current database.

Normally, when you create a table in a database that is not ANSI compliant, public receives Select, Insert, Delete, Under, and Update privileges for that table and its synonyms. (The NODEFDAC environment variable, when set to yes, prevents public from automatically receiving table-level privileges.)

To allow access to only certain users, explicitly revoke those privileges public automatically receives and then grant only those you want, as the following example shows:

REVOKE  ALL  ON  customer  FROM  PUBLIC;

GRANT  ALL  ON  customer  TO  john,  mary;

GRANT  SELECT  (fname,  lname,  company,  city) 

	ON  customer  TO  PUBLIC 

If you create a table in an ANSI-compliant database, only you, as table owner, have any table-level privileges until you explicitly grant privileges to others.

As explained in the next section, Privileges on a View, public does not automatically receive any privileges for a view that you create.

You must have at least the Select privilege on a table or columns to create a view on that table.

For views that reference only tables in the current database, if the owner of a view loses the Select privilege on any table underlying the view, the view is dropped.

You have the same privileges for the view that you have for the table or tables contributing data to the view. For example, if you create a view from a table to which you have only Select privileges, you can select data from your view but you cannot delete or update data.

For detailed information on how to create a view, see CREATE VIEW.

When you create a view, only you have access to table data through that view. Even users who have privileges on the base table of the view do not automatically receive privileges for the view.

You can grant (or revoke) privileges on a view only if you are the owner of the underlying tables or if you received these privileges on the table with the right to grant them (the WITH GRANT OPTION keyword). You must explicitly grant those privileges within your authority; public does not automatically receive privileges on a view.

The creator of a view can explicitly grant Select, Insert, Delete, and Update privileges for the view to other users or to a role name. You cannot grant Index, Alter, Under, or References privileges on a view (or the All privilege because All includes Index, References, and Alter).

You can specify two privileges on data types:

• You can specify the Usage privilege on a user-defined data type.
• You can specify the Under privilege on a named-row type.

  Element	Purpose	Restrictions	Syntax
  row_type_name	Name of the named-row type on which the Under privilege is granted	The specified named-row type must exist.	Data Type, p. 4-53
  type_name	The name of the user-defined data type on which the Usage privilege is granted	The specified data type must exist.	Data Type, p. 4-53

To find out what privileges exist on a particular type, check the sysxtdtypes system catalog table for the owner name and the sysxtdtypeauth system catalog table for additional type privileges that might have been granted. For more information on system catalog tables, see the Informix Guide to SQL: Reference.

You own a user-defined data type that you create. As owner, you automatically receive the Usage privilege on that data type and can grant the Usage privilege to others so that they can reference the type name or reference data of that type in SQL statements. DBAs can also grant the Usage privilege for user-defined data types.

If you grant the Usage privilege to a user or role that has Alter privileges, that person can add a column to the table that contains data of your user-defined type.

Without a GRANT statement, any user can create SQL statements that contain built-in data types. By contrast, a user must receive an explicit Usage privilege from a GRANT statement to use a distinct data type, even if the distinct type is based on a built-in type.

For more information about user-defined types, see CREATE OPAQUE TYPE, CREATE DISTINCT TYPE, the discussion of data types in the Informix Guide to SQL: Reference, and the discussion of data types in the Informix Guide to Database Design and Implementation.

You own a named-row type that you create. If you want other users to be able to create subtypes under this named-row type, you must grant these users the Under privilege on your named-row type.

For example, suppose that you have created a row type named rtype1:

CREATE ROW TYPE rtype1

	(cola INT, colb INT)

If you want another user named kathy to be able to create a subtype under this named-row type, you must grant the Under privilege on this named-row type to user kathy:

GRANT UNDER on rtype1 to kathy

Now user kathy can create another row type under the rtype1 row type even though kathy is not the owner of the rtype1 row type:

CREATE ROW TYPE rtype2

	(colc INT, cold INT)

	UNDER rtype1

For more information about named-row types, see CREATE ROW TYPE, the discussion of data types in the Informix Guide to SQL: Reference, and the discussion of data types in the Informix Guide to Database Design and Implementation.

When you create a user-defined routine (UDR) with the CREATE FUNCTION or CREATE PROCEDURE statement, you own, and automatically receive the Execute privilege on that UDR.

The Execute privilege allows you to invoke the UDR with an EXECUTE FUNCTION or EXECUTE PROCEDURE statement, whichever is appropriate, or with a CALL statement in an SPL routine. The Execute privilege also allows you to use a user-defined function in an expression, as in the following example:

SELECT * FROM table WHERE in_stock(partnum) < 20

   

 




 



Element

Purpose

Restrictions

Syntax


routine

Name of a user-defined routine created with the CREATE FUNCTION or CREATE PROCEDURE statement

The identifier must refer to an existing user-defined routine.

In an ANSI-compliant database, specify the owner as the prefix to the routine name.

Database Object Name, p.  4-50


SPL_routine

Name of an SPL routine that was created with the CREATE PROCEDURE statement

The SPL routine must exist. 

The SPL routine cannot be overloaded. That is, the name must be unique in the database.

Database Object Name, p.  4-50

The requirement to grant the Execute privilege explicitly depends on the following conditions:

• If you have DBA-level privileges, you can use the DBA keyword of CREATE FUNCTION or CREATE PROCEDURE to restrict the default Execute privilege to users with the DBA database-level privilege. You must explicitly grant the Execute privilege on that UDR to users who do not have the DBA privilege.
• If you have the Resource database-level privilege, but not the DBA privilege, you cannot use the DBA keyword when you create a UDR:
• When you create a UDR in a database that is not ANSI compliant, public can execute that UDR. You do not need to issue a GRANT statement for the Execute privilege.
• The NODEFDAC environment variable, when set to yes, prevents public from executing your UDR until you explicitly grant the Execute privilege.
• In an ANSI-compliant database, the creator of a UDR must explicitly grant the Execute privilege on that UDR.

Because of routine overloading, you can grant the Execute privilege on more than one UDR at a time. The following table explains the purpose of the keywords that you specify.

If both a user-defined function and a user-defined procedure have the same name and list of parameter types, you can grant the Execute privilege to both with the keyword ROUTINE. To limit the Execute privilege to one version of the same routine name, use the FUNCTION, PROCEDURE, or SPECIFIC keyword.

To limit the Execute privilege to a UDR that accepts particular data types as arguments, include the routine parameter list or use the SPECIFIC keyword to introduce the specific name of a particular UDR.

Tip: If an external function has a negator function, you must grant the Execute privilege on both the external function and its negator function before users can execute the external function.

Language-Level Privileges

A user must have the Usage privilege on a language to register a user-defined routine (udr) that is written in that language.

When a user executes a CREATE FUNCTION or CREATE PROCEDURE statement to register a UDR, the database server verifies that the user has the Usage privilege on the language in which the UDR is written.For information on other privileges that these statements require, see CREATE FUNCTION and CREATE PROCEDURE.

Only user informix or a user who was granted the Usage privilege WITH GRANT OPTION can grant the Usage privilege on an external language to another user. If user informix grants the Usage privilege on an external language to the DBA WITH GRANT OPTION, the DBA can then grant the Usage privilege on the external language to another user.

In the following example, user informix grants the Usage privilege on both available external languages (C and Java) to user joy:

GRANT USAGE ON LANGUAGE C TO joy;
GRANT USAGE ON LANGUAGE JAVA TO joy;

Only user informix, the DBA, or a user who was granted the Usage privilege WITH GRANT OPTION can grant the Usage privilege on the Stored Procedure Language (SPL) to another user.

The Usage privilege on SPL is granted to PUBLIC by default.

In the following example, assume that the default Usage privilege on SPL was revoked from PUBLIC and the DBA wants to grant the Usage privilege on SPL to the role named developers:

GRANT USAGE ON LANGUAGE SPL TO developers

You can grant privileges to an individual user or a list of users. You can also use the PUBLIC keyword to grant privileges to all users.

The following example grants the table-level privilege Insert on table1 to the user named mary in a database that is not ANSI compliant:

GRANT INSERT ON table1 TO mary

In an ANSI-compliant database, if you do not use quotes around user, the name of the user is stored in uppercase letters.

You can identify one or more users by a name that describes their function, or role. You create the role, then grant the role to one or more users. You can also grant a role to another role.

After you create and grant a role, you can grant certain privileges to the one or more users associated with that role name.

You must add a role name to the database before anyone can use that role name in a GRANT statement. For more information, see CREATE ROLE.

A DBA has the authority to grant a new role to another user. If a user receives a role WITH GRANT OPTION, that user can grant the role to other users or to another role. Users keep a role granted to them until a REVOKE statement breaks the association between their login names and the role name.

Important: CREATE ROLE and GRANT do not activate the role. A role has no effect until the SET ROLE statement enables it. A role grantor or a role grantee can issue the SET ROLE.

The following example shows the sequence required to grant and activate the role payables to a group of employees who perform account payables functions. First the DBA creates role payables, then grants it to maryf.

CREATE ROLE payables;
GRANT payables TO maryf WITH GRANT OPTION

The DBA or maryf can activate the role with the following statement:

SET ROLE payables

User maryf has the WITH GRANT OPTION authorization to grant payables to other employees who pay accounts.

GRANT payables TO charly, gene, marvin, raoul

If you grant privileges for one role to another role, the recipient role has a combined set of privileges. The following example grants the role petty_cash to the role payables:

CREATE ROLE petty_cash;
SET ROLE petty_cash;
GRANT petty_cash TO payables

If you attempt to grant a role to itself, either directly or indirectly, the database server generates an error.

You can grant table-, type-, and routine-level privileges to a role if you have the authority to grant these same privileges to login names or PUBLIC. A role cannot have database-level privileges.

When you grant a privilege to a role:

• you can specify the AS grantor clause

In this way, the people who have the role can revoke these same privileges. For more information, see AS grantor Clause.

• you cannot include the WITH GRANT OPTION clause.

A role cannot, in turn, grant the same table-, type-, or routine-level privileges to another user.

The following example grants the table-level privilege Insert on the supplier table to the role payables:

GRANT INSERT ON supplier TO payables

Anyone granted the role of payables can now insert into supplier.

Using the WITH GRANT OPTION keyword conveys the specified privilege to user along with the right to grant those same privileges to other users. You create a chain of privileges that begins with you and extends to user as well as to whomever user conveys the right to grant privileges. If you use the WITH GRANT OPTION keyword, you can no longer control the dissemination of privileges.

If you revoke from user the privilege that you granted using the WITH GRANT OPTION keyword, you sever the chain of privileges. That is, when you revoke privileges from user, you automatically revoke the privileges of all users who received privileges from user or from the chain that user created (unless user, or the users who received privileges from user, were granted the same set of privileges by someone else). The following examples illustrate this situation. You, as the owner of the table items, issue the following statements to grant access to the user mary:

REVOKE  ALL  ON  items  FROM  PUBLIC;

GRANT  SELECT, UPDATE  ON  items  TO  mary WITH GRANT OPTION

The user mary uses her new privilege to grant users cathy and paul access to the table.

GRANT  SELECT, UPDATE  ON items TO cathy;

GRANT SELECT ON items TO paul

Later you issue the following statement to cancel access privileges for the user mary on the items table:

REVOKE SELECT, UPDATE ON items FROM mary

This single statement effectively revokes all privileges on the items table from the users mary, cathy, and paul.

If you want to create a chain of privileges with another user as the source of the privilege, use the AS grantor clause.

When you grant privileges, by default, you are the one who can revoke those privileges. The AS grantor clause lets you establish another user as the source of the privileges you are granting. When you use this clause, the login provided in the AS grantor clause replaces your login in the appropriate system catalog table.

You can use this clause only if you have the DBA privilege on the database.

Once you use this clause, only the specified grantor can REVOKE the effects of the current GRANT. Even a DBA cannot revoke a privilege unless that DBA is listed in the appropriate system catalog table as the source who granted the privilege.

The following example illustrates this situation. You are the DBA and you grant all privileges on the items table to the user tom, along with the right to grant all privileges:

REVOKE  ALL  ON  items  FROM  PUBLIC;

GRANT  ALL  ON  items  TO  tom WITH GRANT OPTION

The following example illustrates a different situation. You also grant Select and Update privileges to the user jim, but you specify that the grant is made as the user tom. (The records of the database server show that the user tom is the grantor of the grant in the systabauth system catalog table, rather than you.)

GRANT SELECT, UPDATE ON items TO jim AS tom

Later, you decide to revoke privileges on the items table from the user tom, so you issue the following statement:

REVOKE ALL  ON items FROM tom

When you try to revoke privileges from the user jim with a similar statement, however, the database server returns an error, as the following example shows:

REVOKE SELECT, UPDATE ON items FROM jim



580: Cannot revoke permission.

You get an error because the database server record shows the original grantor as the user tom, and you cannot revoke the privilege. Although you are the DBA, you cannot revoke a privilege that another user granted.

Related statements: GRANT FRAGMENT, REVOKE, and REVOKE FRAGMENT

For information about roles, see the following statements: CREATE ROLE, DROP ROLE, and SET ROLE.

In the Informix Guide to Database Design and Implementation, see the discussion of privileges.

For a discussion of how to embed GRANT and REVOKE statements in programs, see the Informix Guide to SQL: Tutorial.