System privileges
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 PRIVILEGEpermission. -
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 OPTIONlets 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.
-
Create a standard account for testing.
CREATE USER dev_user WITH PASSWORD 'Your_Secure_Password123'; -
Grant
dev_userthe system privileges to create and query tables across schemas.GRANT CREATE ANY TABLE, SELECT ANY TABLE TO dev_user; -
(Optional) To let
dev_usergrant these privileges to other users, addWITH 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.
-
Reconnect to the database as
dev_user. -
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); -
Query the table to confirm the
SELECT ANY TABLEpermission.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:YESmeans 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. |