SQL Statements
GRANT
Use the GRANT statement to:
Syntax
|
Usage
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 or a DBA 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.
Database-Level Privileges
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.
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 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.
Examples
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.
To grant the same privileges as those above to all authorized users, use the keyword PUBLIC as shown in the following example:
Behavior of the ALL Keyword
The ALL keyword grants all table-level privileges to the specified user. If any a grantee lacks additional privileges required to use a table-level privilege, the GRANT statement with the ALL keyword succeeds, but the following SQLSTATE warning is returned:
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:
This statement executes successfully but returns SQLSTATE code 01007 for the following reasons:
Table Reference
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.
Privileges on Table Name and Synonym Name
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:
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.
Privileges on a View
You must have at least the Select privilege on a table or columns to create a view on that table. 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, or References privileges on a view (or the All privilege because All includes Index, References, and Alter).
Type-Level Privileges
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 and CREATE DISTINCT TYPE in this manual, the Chapter 2, "Data Types" in the Informix Guide to SQL: Syntax, andChapter 10, "Understanding Complex Data Types" in the Informix Guide to SQL: Tutorial.
Routine-Level Privileges
The generic term user-defined routine refers to both a user-defined function and a user-defined procedure. A function returns one or more values; a procedure does not.
When you create a user-defined routine with the CREATE FUNCTION or CREATE PROCEDURE statement, you own, and automatically receive the Execute privilege on, that routine. The Execute privilege allows you to invoke the user-defined routine with an EXECUTE FUNCTION or EXECUTE PROCEDURE statement, or with a CALL statement in an SPL routine. The Execute privilege also permits use of a function in an expression, as in the following example:
If both a function and a 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 keyword FUNCTION, PROCEDURE, or SPECIFIC.
To limit the Execute privilege to a user-defined routine that accepts particular data types as arguments, include the data types as the routine parameter list in the GRANT statement or use the SPECIFIC keyword if a specific name exists for that routine and parameter list.
The requirement to grant the Execute privilege explicitly depends on the following conditions:
User List
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:
 In an ANSI-compliant database, if you do not use quotes around user, the name of the user is stored in uppercase letters.
Role Name
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.
Granting a Role to a User or Another Role
The CREATE ROLE statement (page 1-193) must add a role name to the database before anyone can use that role name in a GRANT statement.
A DBA has the authority to grant a new role to another user. If a user receives a role WITH GRANT OPTION (page 1-477), 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.
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.
The DBA or maryf can activate the role with the following statement:
User maryf has the WITH GRANT OPTION authorization to grant payables to other employees who pay accounts.
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:
If you attempt to grant a role to itself, either directly or indirectly, the database server generates an error.
Granting a Privilege to a Role
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:
The following example grants the table-level privilege Insert on the supplier table to the role payables:
Anyone granted the role of payables can now insert into supplier.
WITH GRANT OPTION Clause
The WITH GRANT OPTION clause creates a chain of grantors. When you include this clause to a GRANT statement, you grant privileges to user and authorize user to grant the same privileges to others.
If you use the WITH GRANT OPTION to grant privileges, you forfeit control over the future dissemination of those privileges.
If you revoke a privilege you granted with the WITH GRANT OPTION, you revoke the privilege from all users who received it as a result of the WITH GRANT OPTION chain that you initiated. (See "Revoking Privileges Granted WITH GRANT OPTION" for examples.)
If you want to create a chain of privileges with another user as the source of the privilege, use the AS grantor clause. In that case, grantor can revoke all privileges along the WITH GRANT OPTION chain.
AS grantor Clause
If you issue a GRANT command with the AS grantor clause, you relinquish the right to revoke the privileges that you grant to the named grantor. The login given in the AS grantor clause replaces your login in the systabauth system catalog table.
The remaining code fragments in this section illustrate the effects of AS grantor.
As owner of the items table, you grant all privileges to the user tom.
The system catalog systabauth shows your login as grantor; you retain the right to revoke all privileges on items from the user tom.
You also grant Select and Update privileges to the user jim, but you specify tom as grantor.
The system catalog systabauth shows tom as grantor; only tom can revoke Select and Update privileges on items from the user jim. Later, you decide to revoke privileges on the items table from the user tom, so you issue the following statement:
When you try to revoke the privileges on items from the user jim, the database server returns an error, as the following example shows:
The database server issues the error because it has tom recorded as the original grantor, which you cannot change. Even a table owner cannot revoke a privilege that another user granted.
References
See the GRANT FRAGMENT, REVOKE, and REVOKE FRAGMENT statements in this manual.
For more information about routines and parameter lists, see the CREATE FUNCTION and CREATE PROCEDURE in this manual.
For information on roles, see the CREATE ROLE, DROP ROLE, and SET ROLE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussions of database-level privileges and table-level privileges in Chapter 4 and the discussion of privileges and security in Chapter 11.
GRANT FRAGMENT
The GRANT FRAGMENT statement enables you to grant Insert, Update, and Delete privileges on individual fragments of a fragmented table.
Syntax
Usage
The GRANT FRAGMENT statement is similar to the GRANT statement. Both statements grant privileges to users. The difference between the two statements is that you use GRANT to grant privileges on a table while you use GRANT FRAGMENT to grant privileges on table fragments.
Use the GRANT FRAGMENT statement to grant the Insert, Update, or Delete privilege on one or more fragments of a table to one or more users.
The GRANT FRAGMENT statement is valid only for tables that are fragmented according to an expression-based distribution scheme. For an explanation of expression-based distribution schemes, see the ALTER FRAGMENT statement on page 1-29.
Fragment-Level Privileges
The following table defines each of the fragment-level privileges.
Definition of Fragment-Level Authority
When a fragmented table is created in an ANSI-compliant database, the table owner implicitly receives all table-level privileges on the new table, but no other users receive privileges.
When a fragmented table is created in a database that is not ANSI compliant, the table owner implicitly receives all table-level privileges on the new table, and other users (that is, PUBLIC) receive the following default set of privileges on the table: Select, Update, Insert, Delete, and Index. The privileges granted to PUBLIC are explicitly recorded in the systabauth system catalog table.
A user who has table privileges on a fragmented table has the privileges implicitly on all fragments of the table. These privileges are not recorded in the sysfragauth system catalog table.
Whether or not the database is ANSI compliant, you can use the GRANT FRAGMENT statement to grant explicit Insert, Update, and Delete privileges on one or more fragments of a table that is fragmented by expression. The privileges granted by the GRANT FRAGMENT statement are explicitly recorded in the sysfragauth system catalog table.
The Insert, Update, and Delete privileges that are conferred on table fragments by the GRANT FRAGMENT statement are collectively known as fragment-level privileges or fragment-level authority.
Role of Fragment-Level Authority in Command Validation
Fragment-level authority lets users execute INSERT, DELETE, and UPDATE statements on table fragments even if they lack Insert, Update, and Delete privileges on the table as a whole. Users who lack privileges at the table level can insert, delete, and update rows in authorized fragments because of the algorithm by which INFORMIX-Universal Server validates commands. This algorithm consists of the following checks:
1. When a user executes an INSERT, DELETE, or UPDATE statement, the database server first checks whether the user has the table authority necessary for the operation attempted. If the table authority exists, the command continues processing.
2. If the table authority does not exist, the database server checks whether the table is fragmented by expression. If the table is not fragmented by expression, the database server returns an error to the user. This error indicates that the user does not have the privilege to execute the command.
3. If the table is fragmented by expression, the database server checks whether the user has the fragment authority necessary for the operation attempted. If the fragment authority exists, the command continues processing. If the fragment authority does not exist, the database server returns an error to the user. This error indicates that the user does not have the privilege to execute the command.
Duration of Fragment-Level Authority
The duration of fragment-level authority is tied to the duration of the fragmentation strategy for the table as a whole.
If you drop a fragmentation strategy by means of a DROP TABLE statement or the INIT, DROP, or DETACH clauses of an ALTER FRAGMENT statement, you also drop any authorities that exist for the affected fragments. Similarly, if you drop a dbspace, you also drop any authorities that exist for the fragment that resides in that dbspace.
Tables that are created as a result of a DETACH or INIT clause of an ALTER FRAGMENT statement do not keep the authorities that the former fragment or fragments had when they were part of the fragmented table. Instead, such tables assume the default table authorities.
If a table with fragment authorities defined on it is changed to a table with a round-robin strategy or some other expression strategy, the fragment authorities are also dropped, and the table assumes the default table authorities.
Granting Privileges on One Fragment or a List of Fragments
You can grant fragment-level privileges on one fragment of a table or on a list of fragments.
Granting Privileges on One Fragment
The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in dbsp1 to the user larry:
Granting Privileges on More Than One Fragment
The following statement grants the Insert, Update, and Delete privileges on the fragments of the customer table in dbsp1 and dbsp2 to the user millie:
Granting Privileges on All Fragments of a Table
If you want to grant privileges on all fragments of a table to the same user or users, you can use the GRANT statement instead of the GRANT FRAGMENT statement. However, you can also use the GRANT FRAGMENT statement for this purpose.
Assume that the customer table is fragmented by expression into three fragments, and these fragments reside in the dbspaces named dbsp1, dbsp2, and dbsp3. You can use either of the following statements to grant the Insert privilege on all fragments of the table to the user helen:
Granting Privileges to One User or a List of Users
You can grant fragment-level privileges to a single user or to a list of users.
Granting Privileges to One User
The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in dbsp3 to the user oswald:
Granting Privileges to a List of Users
The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in dbsp3 to the users jerome and hilda:
Granting One Privilege or a List of Privileges
When you specify fragment-level privileges in a GRANT FRAGMENT statement, you can specify one privilege, a list of privileges, or all privileges.
Granting One Privilege
The following statement grants the Update privilege on the fragment of the customer table in dbsp1 to the user ed:
Granting a List of Privileges
The following statement grants the Update and Insert privileges on the fragment of the customer table in dbsp1 to the user susan:
Granting All Privileges
The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in dbsp1 to the user harry:
WITH GRANT OPTION Clause
By including the WITH GRANT OPTION clause in the GRANT FRAGMENT statement, you convey the specified fragment-level privileges to a user and the right to grant those same privileges to other users.
The following statement grants the Update privilege on the fragment of the customer table in dbsp3 to the user george and gives this user the right to grant the Update privilege on the same fragment to other users:
AS grantor Clause
The AS grantor clause is optional in a GRANT FRAGMENT statement. Use this clause to specify the grantor of the privilege.
Including the AS grantor Clause
When you include the AS grantor clause in the GRANT FRAGMENT statement, you specify that the user who is named in the grantor parameter is listed as the grantor of the privilege in the grantor column of the sysfragauth system catalog table.
In the following example, the DBA grants the Delete privilege on the fragment of the customer table in dbsp3 to the user martha. In the GRANT FRAGMENT statement, the DBA uses the AS grantor clause to specify that the user jack is listed as the grantor of the privilege in the sysfragauth system catalog table.
Omitting the AS grantor Clause
When a GRANT FRAGMENT statement does not include the AS grantor clause, the user who issues the statement is the default grantor of the privileges that are specified in the statement.
In the following example, the user grants the Update privilege on the fragment of the customer table in dbsp3 to the user fred. Because this statement does not specify the AS grantor clause, the user who issues the statement is listed by default as the grantor of the privilege in the sysfragauth system catalog table.
Consequences of the AS grantor Clause
If you omit the AS grantor clause, or if you specify your own user name in the grantor parameter, you can later revoke the privilege that you granted to the specified user. However, if you specify someone other than yourself as the grantor of the specified privilege to the specified user, only that grantor can revoke the privilege from the user.
For example, if you grant the Delete privilege on the fragment of the customer table in dbsp3 to user martha but specify user jack as the grantor of the privilege, user jack can revoke that privilege from user martha, but you cannot revoke that privilege from user martha.
References
See the GRANT and REVOKE FRAGMENT statements in this manual.
|
