System privileges

Updated at:

System privileges grant global access to a class of schema objects—such as all tables or all views—across any user schema. PolarDB for PostgreSQL (Compatible with Oracle) implements an Oracle-compatible system privilege framework, so a single GRANT statement replaces per-object permission assignments for every user.

Applicability

Your PolarDB for PostgreSQL (Compatible with Oracle) cluster must run minor engine version 2.0.14.18.37.0 or later.

View the minor engine version in the console, or run SHOW polardb_version;. If your cluster does not meet the requirement, upgrade the minor engine version.

Enable and use system privileges

The sections below cover the full workflow: enabling the feature, creating a user, granting permissions, verifying them, querying them, and revoking them.

Step 1: Enable the system privilege feature

The system privilege feature is disabled by default. Go to Settings and Management > Parameters in the PolarDB console and enable the polar_enable_system_privilege parameter.

Step 2: Grant system privileges

Prerequisites

Only the following account types can run GRANT for system privileges:

  • A privileged account.

  • A standard account that holds the GRANT ANY PRIVILEGE permission.

  • A standard account that was granted a specific system privilege with WITH ADMIN OPTION. This account can only re-grant that specific privilege.

Syntax

GRANT sys_priv1 [, sys_priv2 ...] TO my_user_or_role1 [, my_user_or_role2 ...] [ WITH ADMIN OPTION ];
  • Grant one or more system privileges to one or more users or roles in a single statement.

  • For the full list of available privileges and their authorized operations, see System privileges reference.

  • Adding WITH ADMIN OPTION lets the grantee re-grant the privilege to other users or roles.

Example

Grant the developer dev_user the permissions to create and query tables in any schema.

  1. Create a standard account for testing.

    CREATE USER dev_user WITH PASSWORD 'Your_Secure_Password123';
  2. Grant dev_user the system privileges to create and query tables across schemas.

    GRANT CREATE ANY TABLE, SELECT ANY TABLE TO dev_user;
  3. (Optional) To let dev_user grant these privileges to other users, add WITH ADMIN OPTION.

    -- Grant privileges to dev_user and allow the user to grant them to others.
    GRANT CREATE ANY TABLE, SELECT ANY TABLE TO dev_user WITH ADMIN OPTION;

Step 3: Verify the permissions

Confirm that dev_user has the granted permissions.

  1. Reconnect to the database as dev_user.

  2. Create a table and insert data in a schema you do not own.

    CREATE TABLE public.my_test_table (id INT);
    INSERT INTO public.my_test_table VALUES (1);
  3. Query the table to confirm the SELECT ANY TABLE permission.

    SELECT * FROM public.my_test_table;

    Expected output:

     id
    ----
      1

If all three operations succeed, the system privileges are in effect.

Step 4: Manage permissions

Query permissions

Two views are available for inspecting system privileges:

View Use when
DBA_SYS_PRIVS You need to see the privileges of any user—run as a privileged account.
USER_SYS_PRIVS A user wants to see their own privileges—run as the target user.

Query all users (privileged account)

When you query for a specific user, convert the username to uppercase.
-- Run as a privileged account.
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DEV_USER';

Expected output:

 grantee  |    privilege     | admin_option
----------+------------------+--------------
 DEV_USER | CREATE ANY TABLE | YES
 DEV_USER | SELECT ANY TABLE | YES
  • grantee: the user or role that holds the privilege.

  • privilege: the name of the privilege.

  • admin_option: YES means the grantee can grant this privilege to others.

Query your own privileges

-- Run as dev_user.
SELECT PRIVILEGE, ADMIN_OPTION FROM USER_SYS_PRIVS;

Expected output:

     privilege     | admin_option
------------------+--------------
 CREATE ANY TABLE | YES
 SELECT ANY TABLE | YES

Revoke permissions

Syntax

REVOKE sys_priv1 [, sys_priv2 ...] FROM my_user_or_role1 [, my_user_or_role2 ...];

Example

-- Run as a privileged account.
REVOKE CREATE ANY TABLE, SELECT ANY TABLE FROM dev_user;

System privileges reference

The following table lists all system privileges supported by PolarDB and their authorized operations.

Category Privilege Authorized operation
DATABASE LINKS CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK Create a private or public database link. PolarDB does not distinguish between them.
ALTER DATABASE LINK, ALTER PUBLIC DATABASE LINK Alter a private or public database link. PolarDB does not distinguish between them.
DROP PUBLIC DATABASE LINK Drop a private or public database link.
DIRECTORIES CREATE ANY DIRECTORY Create a directory.
INDEXES CREATE ANY INDEX Create an index in any user schema.
ALTER ANY INDEX Alter an index in any user schema.
DROP ANY INDEX Drop an index in any user schema.
MATERIALIZED VIEWS CREATE MATERIALIZED VIEW Create a materialized view in the user's own schema.
CREATE ANY MATERIALIZED VIEW Create a materialized view in any user schema.
ALTER ANY MATERIALIZED VIEW Alter a materialized view in any user schema.
DROP ANY MATERIALIZED VIEW Drop a materialized view in any user schema.
OPERATORS CREATE OPERATOR Create an operator in the user's own schema.
CREATE ANY OPERATOR Create an operator in any user schema.
PRIVILEGES GRANT ANY PRIVILEGE Grant any system privilege.
PROCEDURES CREATE PROCEDURE Create stored procedures, functions, and packages in the user's own schema.
CREATE ANY PROCEDURE Create stored procedures, functions, and packages in any user schema.
ROLES & USERS CREATE ROLE Create a role. PolarDB does not distinguish between CREATE ROLE and CREATE USER for permission verification.
CREATE USER Create a user. PolarDB does not distinguish between CREATE ROLE and CREATE USER for permission verification.
ALTER ANY ROLE Alter a role.
ALTER USER Alter a user.
DROP ANY ROLE Drop a role.
DROP USER Drop a user.
GRANT ANY ROLE Grant a role to a user or another role.
SEQUENCES CREATE SEQUENCE Create a sequence in the user's own schema.
CREATE ANY SEQUENCE Create a sequence in any user schema.
ALTER ANY SEQUENCE Alter a sequence in any user schema.
DROP ANY SEQUENCE Drop a sequence in any user schema.
SYNONYMS CREATE SYNONYM Create a synonym in the user's own schema.
CREATE ANY SYNONYM Create a synonym in any user schema.
CREATE PUBLIC SYNONYM Create a public synonym.
DROP ANY SYNONYM Drop a synonym in any user schema.
DROP PUBLIC SYNONYM Drop a public synonym.
TABLES CREATE TABLE Create a table in the user's own schema.
CREATE ANY TABLE Create a table in any user schema.
ALTER ANY TABLE Alter a table or view in any user schema.
DROP ANY TABLE Delete or truncate (TRUNCATE) tables in any user schema.
COMMENT ANY TABLE Add comments to tables, views, and materialized views in any user schema.
SELECT ANY TABLE Query tables, views, and materialized views in any user schema.
INSERT ANY TABLE Insert rows into a table in any user schema.
UPDATE ANY TABLE Update rows in a table in any user schema.
DELETE ANY TABLE Delete rows from a table in any user schema.
TRIGGERS CREATE TRIGGER Create a DML trigger in the user's own schema (permission check applies to the schema of the table where the trigger resides), or create a system trigger in any user schema.
CREATE ANY TRIGGER Create a DML trigger or a system trigger in any user schema.
ADMINISTER DATABASE TRIGGER Create a database-level system trigger. Also requires CREATE TRIGGER or CREATE ANY TRIGGER.
TYPES CREATE TYPE Create a type in the user's own schema.
CREATE ANY TYPE Create a type in any user schema.
VIEWS CREATE VIEW Create a view in the user's own schema.
CREATE ANY VIEW Create a view in any user schema.
DROP ANY VIEW Drop a view in any user schema.