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. For Oracle Cloud Infrastructure Identity and Access Management (OCI IAM), choose the data role mapping prefix according to the token form that supplies the group information: IAM_OAUTH_GROUP=<group_name> for an OCI IAM group claim carried in an OAuth 2.0 access token. The group is resolved within the single identity domain configured by IDENTITY_PROVIDER_OAUTH_CONFIG.domain_url, so the mapping string does not include a domain prefix. IAM_GROUP_NAME=<group_name> for OCI IAM group information carried in an OCI IAM auth token when the group is in the default identity domain. IAM_GROUP_NAME=<domain>/<group_name> for OCI IAM group information carried in an OCI IAM auth token when the group is in a named identity domain. If the domain prefix is omitted, the database assumes the default identity domain. An OCI IAM auth token is a scoped proof-of-possession token issued by OCI IAM for database authentication. See Key Terminology. ROLE=<realm_role_name> for the configurable OAuth 2.0 provider. The realm role must appear in the top-level array claim specified by roles_claim. The default claim name is roles. Nested role claims are not supported. The single-domain restriction noted for IAM_OAUTH_GROUP applies to the OAuth 2.0 token path, where the database validates the token against one configured identity domain. The OCI IAM auth token path resolves group information through the OCI IAM data plane and therefore supports cross-domain mapping by using the <domain>/<group_name> form. 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 the configurable OAuth 2.0 provider, currently only realm roles can be mapped to data roles. The mapping cannot include an application qualifier.

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

Example: 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: Create a data role for OCI IAM group in an OAuth 2.0 token

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: Create a data role for OCI IAM group in an OCI IAM auth token

Create data roles that map to the OCI IAM group information carried in an OCI IAM auth token. Use this mapping form for any deployment that connects end users to the database with OCI IAM auth tokens.

-- Group in the default domain. The domain prefix is optional.
CREATE DATA ROLE employee_role
  MAPPED TO 'IAM_GROUP_NAME=employee';

-- The same group, with an explicit default-domain prefix.
CREATE DATA ROLE employee_role
  MAPPED TO 'IAM_GROUP_NAME=default/employee';

-- Group in a non-default identity domain.
CREATE DATA ROLE sales_employee_role
  MAPPED TO 'IAM_GROUP_NAME=sales/employee';

Example: Create a data role for a Keycloak realm role

Create a data role that maps to the EMPLOYEE realm role in Keycloak. The token must include EMPLOYEE in the top-level array claim specified by roles_claim.

CREATE DATA ROLE employee_role MAPPED TO 'ROLE=EMPLOYEE';

Example: 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: 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;