The Database Server Administrator (3DBSA), by default user informix, can grant the built-in EXTEND role to one or more users or to PUBLIC with the GRANT EXTEND TO user-list statement. If the IFX_EXTEND_ROLE configuration parameter is set to ON, only users who hold the EXTEND role can create or drop UDRs that are written in C or Java, external languages that can support shared libraries. The SET ROLE statement is not required for the EXTEND role to have this effect; it is sufficient for a user to hold the EXTEND role without using SET ROLE to enable it. The following example grants this role to user max:
GRANT EXTEND TO 'max'
This statement enables user max to create or drop UDRs, without requiring max to issued the SET ROLE EXTEND statement. (Here the quotation marks preserve the lowercase letters in the authorization identifier max.)
In databases for which this security feature is not needed, the DBSA can disable this restriction on who can create or drop external UDRs by setting the IFX_EXTEND_ROLE configuration parameter to OFF in the ONCONFIG file. When IFX_EXTEND_ROLE is set to OFF, any user who holds the Resource privilege can create or drop external UDRs. See Database-Level Privileges for information about the Resource privileges.
The WITH GRANT OPTION keywords convey the privilege or role to user with the right to grant the same privileges or role to other users. You create a chain of privileges that begins with you and extends to user as well as to whomever user subsequently conveys the right to grant privileges. If you include WITH GRANT OPTION, 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 user mary:
REVOKE ALL ON items FROM PUBLIC; GRANT SELECT, UPDATE ON items TO mary WITH GRANT OPTION
User mary uses her 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 revoke the access privileges for 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 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.
The WITH GRANT OPTION keywords are valid only for users. They are not valid when you use GRANT to convey privileges or another role to a role.
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.
After 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 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 user tom 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 user jim, but you specify that the grant is made as user tom. (The records of the database server show that 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 user tom, so you issue the following statement:
REVOKE ALL ON items FROM tom
If instead, however, you try to revoke privileges from user jim with a similar statement, the database server returns an error, as the next example shows:
REVOKE SELECT, UPDATE ON items FROM jim 580: Cannot revoke permission.
You receive an error because the database server record shows the original grantor as user tom, and you cannot revoke the privilege. Although you are the DBA, you cannot revoke a privilege that another user granted.
In Extended Parallel Server, the AS grantor clause is not valid in the GRANT ROLE statement or the GRANT DEFAULT ROLE statement.
In Dynamic Server, the AS grantor clause is not valid in the GRANT DEFAULT ROLE statement.
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 IBM Informix Database Design and Implementation Guide, see the discussion of privileges.
For a discussion of how to embed GRANT and REVOKE statements in programs, see the IBM Informix Guide to SQL: Tutorial.