This topic describes the differences between PolarDB for PostgreSQL(Compatible with Oracle) and native Oracle on database management from several aspects.

Databases

  • By default, one Oracle instance of the versions earlier than Oracle Database 12c has only one database. The versions later than Oracle Database 12c provide the multitenancy concept. Each container database (CDB) can include multiple pluggable databases (PDBs).
  • A PolarDB cluster corresponds to an Oracle instance. You can create multiple databases for each database cluster.

Users

  • Similarities:

    Both PolarDB and Oracle have the user concept. Users are owners of database objects and have access to databases.

  • Differences:

    An Oracle user can log on to a database only after this user is granted the CREATE SESSION privilege. By default, PolarDB users are granted the LOGIN privilege to log on to a database.

The following syntax that is used to create a user is available:

  • Oracle syntax: 
    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 }
     [ 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 syntax: 
    CREATE USER|ROLE name [[WITH] option […]] [IDENTIFIED BY password]
where option can be the following compatible clauses:
 PROFILE profile_name
 | ACCOUNT {LOCK|UNLOCK}
 | PASSWORD EXPIRE [AT 'timestamp']
or option can be the following non-compatible clauses:
 | LOCK TIME 'timestamp'

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

Roles

  • In Oracle, a role is a group of privileges and cannot be regarded as an owner of database objects. This role cannot be granted privileges of other roles and does not have access to databases.
  • In PolarDB, a role is equivalent to a user. This role can be regarded as an owner of database objects, can be granted privileges of other roles or users, and can have access to databases. By default, a role does not have the LOGIN privilege compared with a user. A role can access databases only after the role is granted the LOGIN privilege. A user has the LOGIN privilege.

The following syntax that is used to create a role is available:

  • Oracle syntax: 
    CREATE ROLE role
   [ NOT IDENTIFIED
   | IDENTIFIED { BY password
                | USING [ schema. ] package
                | EXTERNALLY
                | GLOBALLY
                }
   ] ;
  • PolarDB syntax:

    The syntax of CREATE ROLE is consistent with that of CREATE USER.

Schemas

  • Similarities:

    A schema is a logical concept that represents a collection of database objects, such as tables, indexes, and views. These objects are also called schema objects.

  • Differences:
    OraclePolarDB
    You cannot separately create a schema.You can execute the CREATE SCHEMA statement to create a schema.
    When you create a database user, the system automatically creates a schema that has the same name as the username.Each database has a default schema that is named PUBLIC. You can use SET SEARCH_PATH TO 'xxx '; to modify the current default schema.

Privileges

The privileges of PolarDB are similar to those of Oracle. The privileges are divided into system privileges and object privileges.

  • System privileges
    • Oracle

      System privileges allow you to perform specific actions, such as CREATE USER, CREATE TABLE, and CREATE TABLESPACE.

      System privileges also include some administrative rights:

      • SYSDBA and SYSOPER: have the privileges of almost all the database objects. You are authorized to perform some standard database operations, such as starting and shutting down databases, creating server parameter files (SPFILEs) of a database, and changing database archived logs.
      • SYSBACKUP: performs backup and restoration operations.
      • SYSDG: performs the Data Guard operations.
      • SYSKM: manages transparent data encryption (TDE) wallets.
      • SYSRAC: performs the operations on Oracle Real Application Clusters (RACs).
    • PolarDB

      PolarDB supports multiple system privileges, such as LOGIN, POLAR_SUPERUSER, CREATEDB, and CREATEROLE. When you execute the CREATE ROLE or CREATE USER statement, you can specify whether the user has the corresponding privileges.

  • Object privileges

    Object privileges are the privileges to perform operations on specified objects. Database objects include tables, views, sequences, large objects, schemas, functions, and procedural language. Object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES, and EXECUTE. The object privilege varies based on the object type.

    • Oracle

      Users are granted privileges on all the objects for a schema.

    • PolarDB

      Only the object owner and the superuser are authorized to modify or delete objects.

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

Monitoring and O&M