Database management: PolarDB vs. Oracle - PolarDB - Alibaba Cloud Documentation Center

PolarDB for PostgreSQL (Compatible with Oracle) and Oracle differ in how they handle databases, users, roles, schemas, privileges, and monitoring. This topic maps Oracle concepts to their PolarDB equivalents and compares syntax for each area.

Concept mapping

Oracle concept	PolarDB equivalent	Difference
Instance (pre-12c: one database)	Cluster (multiple databases)	A PolarDB cluster corresponds to an Oracle instance but supports multiple databases per cluster
CDB/PDB (12c+)	Cluster with multiple databases	Oracle uses container databases (CDBs) with pluggable databases (PDBs); PolarDB uses a flat multi-database model
User	User (LOGIN granted by default)	Oracle users require CREATE SESSION; PolarDB users get LOGIN automatically
Role (privileges only)	Role (equivalent to user)	PolarDB roles can own objects and receive grants from other roles or users
Schema = User (created together)	Schema (created independently)	Oracle ties schemas to users; PolarDB lets you create schemas separately with CREATE SCHEMA
SYSDBA, SYSOPER	POLAR_SUPERUSER	PolarDB uses POLAR_SUPERUSER instead of Oracle administrative privileges

Databases

Oracle instances before Oracle Database 12c support only one database by default. Starting with Oracle Database 12c, the multitenancy architecture allows each container database (CDB) to include multiple pluggable databases (PDBs).

A PolarDB cluster corresponds to an Oracle instance. Unlike pre-12c Oracle, each PolarDB cluster supports multiple databases without requiring a CDB/PDB architecture.

Users

Both PolarDB and Oracle have the user concept. Users own database objects and have access to databases. The main difference is in default access:

Aspect	Oracle	PolarDB
Default access	Users must be granted the CREATE SESSION privilege to log on	Users are granted the LOGIN privilege by default

CREATE USER syntax comparison

Oracle:

CREATE USER user
   IDENTIFIED { BY password
              | EXTERNALLY [ AS 'certificate_DN' ]
              | GLOBALLY [ AS '[ directory_DN ]' ]
              }
   [ DEFAULT TABLESPACE tablespace
   | TEMPORARY TABLESPACE
        { tablespace | tablespace_group_name }
   | QUOTA { size_clause | UNLIMITED } ON tablespace
     [ QUOTA { size_clause | UNLIMITED } ON tablespace ]...
   | PROFILE profile
   | PASSWORD EXPIRE
   | ACCOUNT { LOCK | UNLOCK }
   ]... ;

PolarDB:

CREATE USER|ROLE name [[WITH] option [...]] [IDENTIFIED BY password]

-- Compatible options:
--   PROFILE profile_name
--   ACCOUNT {LOCK|UNLOCK}
--   PASSWORD EXPIRE [AT 'timestamp']

-- Non-compatible options:
--   LOCK TIME 'timestamp'

PolarDB is also compatible with the PostgreSQL CREATE USER syntax. For more information, see SQL Commands.

Common user operations

Operation	Oracle	PolarDB
Create a user	`CREATE USER test_user IDENTIFIED BY password;`	`CREATE USER test_user IDENTIFIED BY password;`
Grant access	`GRANT CREATE SESSION TO test_user;`	LOGIN is granted by default
Lock a user	`ALTER USER test_user ACCOUNT LOCK;`	`ALTER USER test_user ACCOUNT LOCK;`
Unlock a user	`ALTER USER test_user ACCOUNT UNLOCK;`	`ALTER USER test_user ACCOUNT UNLOCK;`
Set password expiry	`ALTER USER test_user PASSWORD EXPIRE;`	`ALTER USER test_user VALID UNTIL 'timestamp';`
Assign a profile	`ALTER USER test_user PROFILE custom_profile;`	`ALTER USER test_user PROFILE custom_profile;`

Roles

In Oracle, a role is strictly a group of privileges. A role cannot own database objects, cannot receive grants from other roles, and cannot access databases directly.

In PolarDB, a role is equivalent to a user. A role can own database objects, receive grants from other roles or users, and access databases. The only difference between a role and a user in PolarDB is that a role does not have the LOGIN privilege by default, while a user does.

Capability	Oracle role	PolarDB role
Group of privileges	Yes	Yes
Own database objects	No	Yes
Receive grants from other roles	No	Yes
Access databases	No	Yes (after LOGIN is granted)
LOGIN by default	N/A	No (users have LOGIN by default)

CREATE ROLE syntax comparison

Oracle:

CREATE ROLE role
   [ NOT IDENTIFIED
   | IDENTIFIED { BY password
                | USING [ schema. ] package
                | EXTERNALLY
                | GLOBALLY
                }
   ] ;

PolarDB:

The CREATE ROLE syntax is consistent with the CREATE USER syntax shown above.

Schemas

In both Oracle and PolarDB, a schema is a logical collection of database objects such as tables, indexes, and views. These objects are also called schema objects.

Aspect	Oracle	PolarDB
Create a schema	Not supported as a standalone operation. A schema is automatically created with the same name as the user when you create a database user.	Use `CREATE SCHEMA` to create a schema independently of a user.
Default schema	The schema matching the current user	Each database has a default schema named PUBLIC. Change it with `SET SEARCH_PATH TO 'schema_name';`
User-schema relationship	One-to-one: each user has exactly one schema with the same name	Independent: users and schemas are separate entities

Note In Oracle, creating a user automatically creates a schema with the same name. In PolarDB, users and schemas are more independent. You can create schemas explicitly with CREATE SCHEMA. However, when you create a role or user with the IDENTIFIED BY clause, PolarDB also automatically creates a schema with the same name as the role.

Privileges

Privileges in both PolarDB and Oracle are divided into system privileges and object privileges.

System privileges

Oracle

System privileges allow specific administrative actions such as CREATE USER, CREATE TABLE, and CREATE TABLESPACE. Oracle also provides the following administrative privileges:

Privilege	Scope
SYSDBA, SYSOPER	Privileges on almost all database objects. Authorized to start and shut down databases, create server parameter files (SPFILEs), and change archived logs.
SYSBACKUP	Backup and restoration operations
SYSDG	Data Guard operations
SYSKM	Transparent data encryption (TDE) wallet management
SYSRAC	Real Application Clusters (RAC) operations

PolarDB

PolarDB supports the following system privileges, which are specified when you run CREATE ROLE or CREATE USER:

Privilege	Description
LOGIN	Allows the role to log on to a database
POLAR_SUPERUSER	Grants superuser-level access
CREATEDB	Allows creation of databases
CREATEROLE	Allows creation of roles

Object privileges

Object privileges control operations on specific database objects including tables, views, sequences, large objects, schemas, functions, and procedural language. Available object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES, and EXECUTE.

Aspect	Oracle	PolarDB
Grant scope	Users are granted privileges on all objects within a schema	Only the object owner and superuser can modify or delete objects

Note A superuser is a user who has the POLAR_SUPERUSER privilege.

Monitoring and O&M

Oracle

For monitoring and O&M information, see Oracle documentation.

PolarDB

PolarDB provides two categories of monitoring:

Metric monitoring: Includes performance monitoring, alerts, and performance insights. For more information, see Performance monitoring and Performance insight.
Log monitoring: Includes slow query logs and SQL Explorer. For more information, see SQL Explorer.