Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :

Previous PageTable Of ContentsNext Page

3.3.47 GRANT on System Privileges

This variant of the GRANT command gives a role the ability to perform certain “system” operations within a database. These privileges are added to those already granted, if any.

Only database superusers can grant system privileges.

The following system privileges that can be granted relate to the ability to create or delete certain database objects that are not necessarily within the confines of one schema:

CREATE [ PUBLIC ] DATABASE LINK

Allows the role to create a database link. If PUBLIC is specified, then the role can create a public database link. If PUBLIC is omitted, then the role can create a private database link.

DROP PUBLIC DATABASE LINK

Allows the role to drop a public database link. There is no system privilege that needs to be granted for dropping a private database link. The owner of a database link can always drop it.

Another system privilege, EXEMPT ACCESS POLICY, exempts the grantee from all restrictions implemented by security policies. See Section 7.7 for information on security policies.

EXEMPT ACCESS POLICY

Allows the role to execute a SQL command without invoking any policy function that may be associated with the target database object. That is, the role is exempt from all security policies in the database.

Notes

The REVOKE command is used to revoke system privileges.

For database links, the Postgres Plus Advanced Server ALTER ROLE command also supports the equivalent privileges of CREATEPUBLICDBLINK, CREATEDBLINK, and DROPPUBLICDBLINK that can be used instead of the Oracle compatible GRANT command. The ALTER ROLE command also supports the use of NOCREATEPUBLICDBLINK, NOCREATEDBLINK, and NODROPPUBLICDBLINK to revoke the respective privileges.

The EXEMPT ACCESS POLICY privilege is not inheritable by membership to a role that has the EXEMPT ACCESS POLICY privilege. For example, the following sequence of GRANT commands does not result in user joe obtaining the EXEMPT ACCESS POLICY privilege even though joe is granted membership to the enterprisedb role, which has been granted the EXEMPT ACCESS POLICY privilege:

GRANT EXEMPT ACCESS POLICY TO enterprisedb;
GRANT enterprisedb TO joe;

The rolpolicyexempt column of the system catalog table pg_authid is set to true if a role has the EXEMPT ACCESS POLICY privilege.

Examples

Grant CREATE PUBLIC DATABASE LINK privilege to user joe:

GRANT CREATE PUBLIC DATABASE LINK TO joe;

Grant DROP PUBLIC DATABASE LINK privilege to user joe:

GRANT DROP PUBLIC DATABASE LINK TO joe;

Using the ALTER ROLE command, the results of the preceding two GRANT commands can be accomplished by the following:

ALTER ROLE joe WITH CREATEPUBLICDBLINK;
ALTER ROLE joe WITH DROPPUBLICDBLINK;

Grant the EXEMPT ACCESS POLICY privilege to user joe:

GRANT EXEMPT ACCESS POLICY TO joe;

See Also

REVOKE

Previous PageTable Of ContentsNext Page