ALTER ROLE: Managing database link and DBMS_RLS privileges v16
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.
CREATE DATABASE LINK
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.
CREATE PUBLIC DATABASE LINK
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.
DROP PUBLIC DATABASE LINK
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.