This topic describes the differences between PolarDB databases compatible with Oracle and native Oracle databases.

Databases

  • By default, each Oracle instance that uses a database platform earlier than Oracle Database 12c contains only one database. Oracle Database 12c and later versions support container databases (CDBs). Each CDB can include multiple pluggable databases (PDBs).
  • An Apsara PolarDB cluster is equivalent to an Oracle instance. You can create multiple databases for each database cluster.

Users

  • Similarities:

    PolarDB databases compatible with Oracle and native Oracle databases share the concept of users. Users are owners of database objects and have access to databases.

  • Differences:

    A native Oracle database user can log on to a database only after being granted the CREATE SESSION privilege. By default, a PolarDB database user is granted the LOGIN privilege to log on to a database.

To create a user, you can execute the CREATE USER statement. CREATE USER has the following syntax:

  • 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 databases compatible with Oracle support the syntax of CREATE USER for PostgreSQL. For more information, visit SQL Commands.

Roles

  • For native Oracle databases, 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.
  • For PolarDB databases compatible with Oracle, 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. A role must be granted the LOGIN privilege to obtain access to databases. A user has the LOGIN privilege.

To create a role, you can execute the CREATE ROLE statement. CREATE ROLE has the following syntax:

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

    The syntax of CREATE ROLE is the same as 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:
    Oracle PolarDB
    You cannot 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 with the same name as the database username. Each database has a default schema named PUBLIC. You can use SET SEARCH_PATH TO 'xxx '; to modify the current default schema.

Privileges

Similar to native Oracle databases, Apsara PolarDB provides 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 include some administrator privileges:

      • SYSDBA and SYSOPER: authorized to manage almost all database objects. You are authorized to perform some standard database operations, such as starting and shutting down databases, creating server parameter files (SPFiles), and changing database archived log.
      • SYSBACKUP: backs up and restores databases.
      • SYSDG: implements the Data Guard solution.
      • SYSKM: manages transparent data encryption (TDE) wallets.
      • SYSRAC: manages Oracle Real Application Clusters (RACs).
    • PolarDB
  • Object privileges

    Object privileges specify actions on 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, depending on the object type.

    • Oracle

      Users are granted privileges on all objects for a schema.

    • PolarDB

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

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

Monitoring and O&M