This variant of the GRANT
command grants membership in a role to one or more other roles. Membership in a role is significant because it conveys the privileges granted to a role to each of its members.
If the WITH ADMIN OPTION
is specified, the member may in turn grant membership in the role to others, and revoke membership in the role as well. Without the admin option, ordinary users cannot do that.
Granting the CONNECT
role is equivalent to giving the grantee the LOGIN
privilege. The grantor must have the CREATEROLE
Granting the RESOURCE
role is equivalent to granting the CREATE
privileges on a schema that has the same name as the grantee. This schema must exist before the grant is given. The grantor must have the privilege to grant CREATE
privileges on this schema to the grantee.
Granting the DBA
role is equivalent to making the grantee a superuser. The grantor must be a superuser.
command is used to revoke access privileges.
When a non-owner of an object attempts to GRANT
privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object. As long as a privilege is available, the command will proceed, but it will grant only those privileges for which the user has grant options. The GRANT ALL PRIVILEGES
forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur.)
If a superuser chooses to issue a GRANT
command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner. (For role membership, the membership appears to have been granted by the containing role itself.)
can also be done by a role that is not the owner of the affected object, but is a member of the role that owns the object, or is a member of a role that holds privileges WITH GRANT OPTION
on the object. In this case the privileges will be recorded as having been granted by the role that actually owns the object or holds the privileges WITH GRANT OPTION
For example, if table t1
is owned by role g1
, of which role u1
is a member, then u1
can grant privileges on t1
, but those privileges will appear to have been granted directly by g1
. Any other member of role g1
could revoke them later.
If the role executing GRANT
holds the required privileges indirectly via more than one role membership path, it is unspecified which containing role will be recorded as having done the grant. In such cases it is best practice to use SET ROLE
to become the specific role you want to do the GRANT
Currently, Advanced Server
does not support granting or revoking privileges for individual columns of a table. One possible workaround is to create a view having just the desired columns and then grant privileges to that view.
privilege to user joe