|Home||Contents||Index||Master Index||New Book|
GRANTUse the GRANT statement to:
Table-Level PrivilegesWhen 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.
You can narrow the scope of a Select, Update, or References privilege by naming the specific columns to which the privilege applies.
Behavior of the ALL KeywordThe 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 ReferenceYou grant table-level privileges directly by referencing the table name or an existing synonym. You can also grant table-level privileges on a view.
GRANT ALL ON customer TO john, mary
GRANT SELECT (fname, lname, company, city)
ON customer TO PUBLIC
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.
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.
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.
User ListYou 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.
In an ANSI-compliant database, if you do not use quotes around user, the name of the user is stored in uppercase letters.
Role NameYou 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.
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 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 RoleYou 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:
Anyone granted the role of payables can now insert into supplier.
WITH GRANT OPTION ClauseThe 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 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.
ReferencesSee 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.
UsageThe 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.
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 AuthorityThe 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 FragmentsYou can grant fragment-level privileges on one fragment of a table or on a list of fragments.
Granting Privileges on One FragmentThe 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 FragmentThe 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 TableIf 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:
GRANT INSERT ON customer TO helen;
Granting Privileges to One User or a List of UsersYou can grant fragment-level privileges to a single user or to a list of users.
Granting Privileges to One UserThe 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 UsersThe 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 PrivilegesWhen you specify fragment-level privileges in a GRANT FRAGMENT statement, you can specify one privilege, a list of privileges, or all privileges.
Granting One PrivilegeThe following statement grants the Update privilege on the fragment of the customer table in dbsp1 to the user ed:
Granting a List of PrivilegesThe following statement grants the Update and Insert privileges on the fragment of the customer table in dbsp1 to the user susan:
Granting All PrivilegesThe 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 ClauseBy 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 ClauseThe AS grantor clause is optional in a GRANT FRAGMENT statement. Use this clause to specify the grantor of the privilege.
Including the AS grantor ClauseWhen 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 ClauseWhen 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 ClauseIf 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.
ReferencesSee the GRANT and REVOKE FRAGMENT statements in this manual.