System privileges

When you migrate from an Oracle database or manage permissions at scale, granting access privileges to objects or schemas for each user is a tedious process. To simplify this process, PolarDB provides a system privilege framework that is compatible with Oracle. A single command can grant a user global permissions for a class of objects, such as all tables or all views. This improves the efficiency of database operations and management.

Applicability

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

Note

You can view the minor engine version number in the console or run the SHOW polardb_version; statement to check the version. If your cluster does not meet the version requirement, upgrade the minor engine version.

Enable and use system privileges

This section describes the entire process: enabling the feature, creating a user, granting permissions, and verifying the permissions.

Step 1: Enable the system privilege feature

The system privilege feature is disabled by default. Before you can use this feature, go to the Settings and Management > Parameters page in the PolarDB console to enable the polar_enable_system_privilege parameter.

Step 2: Grant system privileges

Prerequisites

Only the following three types of users can run GRANT statements for system privileges:

A privileged account.
A standard account that is granted the GRANT ANY PRIVILEGE permission.
A standard account that is granted a system privilege with the WITH ADMIN OPTION clause. This account can only grant the specific privilege it holds.

Syntax

GRANT sys_priv1 [, sys_priv2 ...] TO my_user_or_role1 [, my_user_or_role2 ...] [ WITH ADMIN OPTION ];

You can grant one or more system privileges to one or more users or roles at a time.
The system privilege sys_priv can be one of several types. For more information about the available privileges and their functions, see System privileges.
If you specify the WITH ADMIN OPTION clause, the grantee can grant the privilege to other users or roles.

Example

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

Create a standard account for testing.

CREATE USER dev_user WITH PASSWORD 'Your_Secure_Password123';

Grant dev_user the system privileges to create and query tables across schemas.
```
GRANT CREATE ANY TABLE, SELECT ANY TABLE TO dev_user;
```

(Optional) If you want dev_user to be able to grant these permissions to other users, add the WITH ADMIN OPTION clause.

-- 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 the dev_user user.

Try to create a table and insert data into a schema that you do not own.

CREATE TABLE public.my_test_table (id INT);
INSERT INTO public.my_test_table VALUES (1);

Query the table to verify the SELECT ANY TABLE permission.
```
SELECT * FROM public.my_test_table;
```
The expected output is:
```
 id 
----
  1
```

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

Step 4: Manage and query permissions

View or revoke granted system privileges.

Query permissions

Query the DBA_SYS_PRIVS view to see the system privileges of all users.

Note

When you query for a specific user, convert the username to uppercase.

-- Run this statement as a privileged account.
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DEV_USER';

The expected output is as follows:

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

You can view the current user's system permissions in the USER_SYS_PRIVS view.

-- Executed by dev_user
SELECT PRIVILEGE, ADMIN_OPTION FROM USER_SYS_PRIVS;

Expected output:

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

In the results, grantee is the user or role that was granted the privilege. privilege is the name of the privilege. If admin_option is YES, the grantee can grant the privilege to other users or roles.

Revoke permissions

Syntax

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

Example

Use the REVOKE command to revoke system privileges from a user.

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

The following table lists the system privileges supported by PolarDB and their functions.

Category	Privilege name	Authorized operation
`DATABASE LINKS`	`CREATE DATABASE LINK`, `CREATE PUBLIC DATABASE LINK`	Create a private or public DBLink. PolarDB does not distinguish between them.
	`ALTER DATABASE LINK`, `ALTER PUBLIC DATABASE LINK`	Alter a private or public DBLink. PolarDB does not distinguish between them.
	`DROP PUBLIC DATABASE LINK`	Drop a private or public DBLink.
`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.
`OPERATORS`	`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.
`PROCEDURES`	`CREATE ANY PROCEDURE`	Create stored procedures, functions, and packages in any user schema.
`ROLES & USERS`	`CREATE ROLE`, `CREATE USER`	Create a user or role. PolarDB does not distinguish between them for permission verification.
	`ALTER ANY ROLE`, `ALTER USER`	Alter a user or role.
	`DROP ANY ROLE`, `DROP USER`	Drop a user or role.
	`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 tables 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. The permission check is performed on the schema of the table where the trigger resides. 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. This also requires the `CREATE TRIGGER` or `CREATE ANY TRIGGER` privilege.
`TYPES`	`CREATE TYPE`	Create a type in the user's own schema.
`TYPES`	`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.