12.1 Create Data Role

Use the CREATE DATA ROLE command to create a data role in the database.

Required privilege

The CREATE DATA ROLE system privilege.

Syntax

CREATE [ OR REPLACE ] DATA ROLE [ IF NOT EXISTS ] data_role_name
  { external_role_clause | local_role_clause };
 
external_role_clause ::=
  MAPPED TO 'identifier_string'
 
local_role_clause ::=
  [ { ENABLED | DISABLED } ]

Parameters

Parameter Description

data_role_name

The name of the data role to be created.

identifier_string
The external provider mapping string (for externally mapped roles only). The supported formats are:
  • AZURE_ROLE=<role_name> or AZURE_APP=<aud>:AZURE_ROLE=<role_name> for Microsoft Entra ID.
  • IAM_OAUTH_GROUP=<group_name> for Oracle Cloud Infrastructure Identity and Access Management (OCI IAM).

The identifier_string parameter must be fewer than 1024 characters. The database raises ORA-28303 if this limit is exceeded.

ENABLED | DISABLED

The option that specifies whether a data role (that is managed locally in the database) is enabled or disabled on creation. The default value is ENABLED if this option is omitted.

After a data role is granted to an application identity, you cannot set it to the ENABLED or DISABLED state.

Usage notes and restrictions

  • When IF NOT EXISTS is specified:
    • If the data role exists, the statement is a no-op. No error is raised.
    • If the data role does not exist, it is created.
  • When IF NOT EXISTS is omitted:
    • If the data role already exists, an error is raised.
    • If the data role does not exist, it is created.
  • When OR REPLACE is specified:
    • If the data role already exists, the options are replaced.
    • If the data role does not exist, it is created.
  • When OR REPLACE is omitted:
    • If the data role already exists, an error is raised.
    • If the data role does not exist, it is created.
  • OR REPLACE and IF NOT EXISTS are mutually exclusive. Using both in the same statement raises the ORA-11541 error.
  • A data role can map to at most one external application role. Attempting to create a data role with an external identifier string already in use raises an error. The identifier string comparison is case-insensitive.
  • You cannot replace a data role that is externally mapped with a data role that is locally managed, or vice versa. The database raises an error if you attempt such a replacement.
  • After you grant a data role that is managed locally to an application identity, only that application can enable it. You cannot grant that role to other end users or data roles. After a data role is granted to an application identity, you cannot set it to the ENABLED or DISABLED state. The database raises ORA-52539 if you attempt to do so.

For syntax diagrams and additional details, see CREATE DATA ROLE in Oracle AI Database SQL Language Reference.

Example 12-1 Create a data role for Microsoft Entra ID role

Create a data role employee_role that maps to a Microsoft Entra ID role called employee.

For Microsoft Entra ID, the mapping must use one of the following formats.
  • Using the role name only:
    CREATE DATA ROLE employee_role MAPPED TO 'AZURE_ROLE=employee';
  • Using the application audience and role name:
    CREATE DATA ROLE employee_role MAPPED TO
  'AZURE_APP=https://supremo.onmicrosoft.com/2edc9c9f-8e1e-4ade-8a4a-cc286ed1b899:AZURE_ROLE=employee';

The database first checks for the AZURE_APP prefix. If it is not present, the database matches using AZURE_ROLE alone. The optional AZURE_APP prefix uses the end-user token's audience (aud) claim to distinguish between application roles that share the same name across different application registrations.

Example 12-2 Create a data role for OCI IAM group

Create a data role employee_role for an OCI IAM group called employee. 

CREATE DATA ROLE employee_role MAPPED TO 'IAM_OAUTH_GROUP=employee';

An application identifier prefix is not required for OCI IAM because groups are shared across applications within the same identity domain, and the database can only be configured with a single domain.

Example 12-3 Create data role that is managed locally in the database

Create a data role, it_support_role, with default settings. 

CREATE DATA ROLE it_support_role;

Example 12-4 Create a data role as disabled

Create a data role (that is managed locally in the database) in the disabled state.

CREATE DATA ROLE temp_role DISABLED;