ALTER ROLE: Managing database link and DBMS_RLS privileges v17

EDB Postgres Advanced Server includes extra syntax not offered by Oracle for the ALTER ROLE command. This syntax can be useful when assigning privileges related to creating and dropping database links compatible with Oracle databases and fine-grained access control using DBMS_RLS.

A user who holds the CREATE DATABASE LINK privilege can create a private database link. The following ALTER ROLE command grants privileges to an EDB Postgres Advanced Server role that allow the specified role to create a private database link:

ALTER ROLE role_name
    WITH [CREATEDBLINK | CREATE DATABASE LINK]

This command is the functional equivalent of:

GRANT CREATE DATABASE LINK to role_name

Use the following command to revoke the privilege:

ALTER ROLE role_name
  WITH [NOCREATEDBLINK | NO CREATE DATABASE LINK]
Note

The CREATEDBLINK and NOCREATEDBLINK keywords are deprecated syntaxes. We recommend using the CREATE DATABASE LINK and NO CREATE DATABASE LINK syntax options.

A user who holds the CREATE PUBLIC DATABASE LINK privilege can create a public database link. The following ALTER ROLE command grants privileges to an EDB Postgres Advanced Server role that allow the specified role to create a public database link:

ALTER ROLE role_name
  WITH [CREATEPUBLICDBLINK | CREATE PUBLIC DATABASE LINK]

This command is the functional equivalent of:

GRANT CREATE PUBLIC DATABASE LINK to role_name

Use the following command to revoke the privilege:

ALTER ROLE role_name
  WITH [NOCREATEPUBLICDBLINK | NO CREATE PUBLIC DATABASE LINK]
Note

The CREATEPUBLICDBLINK and NOCREATEPUBLICDBLINK keywords are deprecated syntaxes. We recommend using the CREATE PUBLIC DATABASE LINK and NO CREATE PUBLIC DATABASE LINK syntax options.

A user who holds the DROP PUBLIC DATABASE LINK privilege can drop a public database link. The following ALTER ROLE command grants privileges to an EDB Postgres Advanced Server role that allow the specified role to drop a public database link:

ALTER ROLE role_name
  WITH [DROPPUBLICDBLINK | DROP PUBLIC DATABASE LINK]

This command is the functional equivalent of:

GRANT DROP PUBLIC DATABASE LINK to role_name

Use the following command to revoke the privilege:

ALTER ROLE role_name
  WITH [NODROPPUBLICDBLINK | NO DROP PUBLIC DATABASE LINK]
Note

The DROPPUBLICDBLINK and NODROPPUBLICDBLINK keywords are deprecated syntaxes. We recommend using the DROP PUBLIC DATABASE LINK and NO DROP PUBLIC DATABASE LINK syntax options.

EXEMPT ACCESS POLICY

A user who holds the EXEMPT ACCESS POLICY privilege is exempt from fine-grained access control (DBMS_RLS) policies. A user who holds these privileges can view or modify any row in a table constrained by a DBMS_RLS policy. The following ALTER ROLE command grants privileges to an EDB Postgres Advanced Server role that exempt the specified role from any defined DBMS_RLS policies:

ALTER ROLE role_name
    WITH [POLICYEXEMPT | EXEMPT ACCESS POLICY]

This command is the functional equivalent of:

GRANT EXEMPT ACCESS POLICY TO role_name

Use the following command to revoke the privilege:

ALTER ROLE role_name
  WITH [NOPOLICYEXEMPT | NO EXEMPT ACCESS POLICY]
Note

The POLICYEXEMPT and NOPOLICYEXEMPT keywords are deprecated syntaxes. We recommend using the EXEMPT ACCESS POLICY and NO EXEMPT ACCESS POLICY syntax options.

See also

CREATE ROLE, DROP ROLE, GRANT, REVOKE, SET ROLE