13.1 Grant Data Role

Use the GRANT DATA ROLE command to grant one or more data roles that are managed locally in the database to local end users, other data roles (that are managed locally), or application identities.

The GRANT DATA ROLE command supports two distinct use cases:
  • Granting a data role to a local end user or another data role (managed locally). The grantee inherits the privileges associated with the granted data role.
  • Granting a data role to an application identity. The application can then enable the data role in its database session.

Required privilege

The GRANT ANY DATA ROLE system privilege.

Syntax

GRANT DATA ROLE [ IF EXISTS ] data_role_list
  TO grantee_list
  [ START TIME timestamp ] [ END TIME timestamp ];
 
data_role_list ::= data_role [, data_role ]...
 
grantee_list  ::= grantee [, grantee ]...
 
grantee       ::= { end_user | application_identity | data_role }

Parameters

Parameter Description

data_role_list

A comma-separated list of data roles to be granted. Only data roles that are managed locally in the database are permitted; data roles that are externally mapped cannot be granted through this command.

grantee_list

A comma-separated list of grantees. Each grantee can be a local end user, an application identity, or a data role that is managed locally.

START TIME

The time stamp from which the grant becomes effective, in TIMESTAMP WITH TIME ZONE format. Optional. Not applicable when granting to an application identity.

END TIME

The time stamp on which the grant becomes ineffective, in TIMESTAMP WITH TIME ZONE format. Optional. Not applicable when granting to an application identity.

Usage notes and restrictions

  • When IF EXISTS is specified:
    • If any data role or grantee in the statement does not exist, the grant is skipped for those non-existing entries and no error is raised.
    • If any data role grant already exists, its effective dates are updated.
  • When IF EXISTS is omitted:
    • If any data role or grantee in the statement does not exist, an error is raised.
    • If the data role grant already exists, its effective dates are updated.
  • A single GRANT DATA ROLE statement cannot mix application identities and local end users or data roles in the same grantee list. If a mixture is specified, an error is raised. This restriction exists because data roles granted to application identities operate differently from those granted to local end users or data roles.
  • A data role granted to a local end user or data role cannot be granted to an application identity, and vice versa.
  • START TIME and END TIME do not apply when granting a data role to an application identity. Application-managed data roles are enabled and disabled dynamically by the application at runtime.

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

Example 13-1 Grant data role to end user

Grant a data role manager_role to a local end user, Marvin. 

GRANT DATA ROLE manager_role TO marvin;

Example 13-2 Grant one data role to another

Grant a data role employee_role to another data role manager_role, so manager_role inherits the privileges of employee_role. 

GRANT DATA ROLE employee_role TO manager_role;

Example 13-3 Grant multiple data roles to multiple end users

Grant multiple data roles to multiple local end users in a single statement.

GRANT DATA ROLE hr_rep_role, it_support_role
  TO emma, jdoe;

Example 13-4 Grant data role to application identity

Grant a data role to an application identity, hcm_app. 

GRANT DATA ROLE hcm_role TO hcm_app;