Enforce Schema Access Control via GRANT Privileges & Roles in PolarDB for Oracle - PolarDB

Grants privileges on database objects, role membership, or system-level permissions.

Syntax

Object privileges (table, view, sequence, or program):

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES }
  [,...] | ALL [ PRIVILEGES ] }
  ON tablename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { { INSERT | UPDATE | REFERENCES } (column [, ...]) }
  [, ...]
  ON tablename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { SELECT | ALL [ PRIVILEGES ] }
  ON sequencename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  ON FUNCTION progname
    ( [ [ argmode ] [ argname ] argtype ] [, ...] )
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  ON PROCEDURE progname
    [ ( [ [ argmode ] [ argname ] argtype ] [, ...] ) ]
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  ON PACKAGE packagename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

Role membership:

GRANT role [, ...]
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH ADMIN OPTION ]

System privileges:

GRANT { CONNECT | RESOURCE | DBA } [, ...]
  TO { username | groupname } [, ...]
  [ WITH ADMIN OPTION ]

GRANT CREATE [ PUBLIC ] DATABASE LINK
  TO { username | groupname }

GRANT DROP PUBLIC DATABASE LINK
  TO { username | groupname }

GRANT EXEMPT ACCESS POLICY
  TO { username | groupname }

Parameters

Parameter	Description
`SELECT`	Allows reading rows from the table, view, or sequence.
`INSERT`	Allows inserting rows into the table.
`UPDATE`	Allows updating rows in the table.
`DELETE`	Allows deleting rows from the table.
`REFERENCES`	Allows creating foreign key constraints that reference the table or specific columns.
`EXECUTE`	Allows calling the function, procedure, or package.
`ALL [PRIVILEGES]`	Grants all privileges applicable to the object type. `PRIVILEGES` is optional.
`tablename`	The table or view to grant privileges on.
`sequencename`	The sequence to grant privileges on.
`progname`	The name of the function or procedure.
`packagename`	The name of the package.
`username`	The user to receive the privileges.
`groupname`	The group to receive the privileges.
`PUBLIC`	Grants the privilege to all users.
`WITH GRANT OPTION`	Allows the grantee to grant the same privileges to others. Cannot be granted to `PUBLIC`.
`WITH ADMIN OPTION`	Allows the grantee to grant the role to other users.
`CONNECT`	Oracle-compatible built-in role that allows a user to connect to the database.
`RESOURCE`	Oracle-compatible built-in role that allows a user to create database objects.
`DBA`	Oracle-compatible built-in role with full administrative privileges.

Description

GRANT has three variants:

Object privileges: Grant specific privileges (SELECT, INSERT, UPDATE, DELETE, REFERENCES, or EXECUTE) on a table, view, sequence, function, procedure, or package to a user, group, or all users (PUBLIC).
Role membership: Add a user or group as a member of a role, so they inherit the role's privileges.
System privileges: Grant Oracle-compatible system roles (CONNECT, RESOURCE, DBA) or specific system-level permissions such as creating or dropping database links and exempting access policies.

Users, groups, and roles

In PolarDB for PostgreSQL(Compatible with Oracle), users and groups are unified into a single entity type called a role:

A user is a role with the LOGIN attribute. It can create a session and connect to an application.
A group is a role without the LOGIN attribute. It cannot create a session or connect to an application.

A role can be a member of one or more other roles, and users can belong to both users and groups, forming a general multi-level hierarchy. Because usernames and group names share the same namespace, GRANT does not distinguish between them — the syntax is identical for both.

Usage notes

ALL PRIVILEGES with insufficient grant options: If you hold only partial grant options on an object, GRANT ALL PRIVILEGES grants only the privileges for which you have grant options and issues a warning for the rest. It does not fail outright.
Column-level vs. table-level privileges: A user can perform INSERT or UPDATE on a column if they hold the privilege at either the column level or the table level. Revoking a column-level privilege does not remove an existing table-level privilege for that column.
PUBLIC and WITH GRANT OPTION: Grant options cannot be granted to PUBLIC. Only named roles can receive grant options.
Non-owner grants: If you do not own an object but hold some privileges with grant options, GRANT succeeds for those privileges only. If you hold no grant options at all, the command fails.

Examples

Grant table privileges

Grant SELECT and INSERT on the orders table to user alice:

GRANT SELECT, INSERT ON orders TO alice;

After this grant, alice can read and insert rows in orders but cannot update or delete them.

Grant column-level privileges

Grant UPDATE on specific columns of the employees table to user bob:

GRANT UPDATE (salary, department_id) ON employees TO bob;

bob can update salary and department_id but no other columns.

Grant with the right to re-grant

Grant SELECT on the products table to user carol, and allow her to grant the same privilege to others:

GRANT SELECT ON products TO carol WITH GRANT OPTION;

Grant role membership

Add user dave as a member of the reporting_role role:

GRANT reporting_role TO dave;

dave inherits all privileges held by reporting_role.

Grant system roles

Grant the CONNECT and RESOURCE system roles to user eve:

GRANT CONNECT, RESOURCE TO eve;

Grant database link privileges

Allow user frank to create a public database link:

GRANT CREATE PUBLIC DATABASE LINK TO frank;