AS grantor clause

When you grant discretionary access privileges to other users, roles, or to PUBLIC, 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 that you are granting.

When you use the AS grantor 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 operation. Even a DBA cannot revoke a privilege unless that DBA is listed in the system catalog table as the user 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 next example illustrates a different situation. You also grant Select and Update privileges to user jim, but you specify that the privileges are granted as user tom. (The records of the database server in the systabauth system catalog table show that user tom is the grantor of those privileges, 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.

The AS grantor clause is not valid in the GRANT DEFAULT ROLE statement.

For contexts where the AS grantor clause is required, rather than optional, see Granting the Execute privilege to PUBLIC.