Oracle Cloud Infrastructure Documentation

Scenario: Grant a Database Privilege to a Flow Runtime

Connect as the IoT domain workspace user, grant a Flow Runtime access to a workspace procedure, verify the grant, and revoke it when no longer needed.

Use this scenario when a flow needs to execute a procedure in the workspace schema of its IoT domain. The workspace user grants EXECUTE to the database user of a Flow Runtime in that domain.

Database grants are separate from IAM policies that authorize access to IoT resources. Give the specific Flow Runtime only the access required by the flow.

For the database nodes used by the flow, see Oracle Database Nodes in the Flow Runtime Node Reference.

Before You Begin

  • An existing IoT domain and a Flow Runtime belonging to that domain.
  • An existing workspace procedure required by the flow. The workspace user must own the procedure or have authority to grant its EXECUTE privilege.
  • Access to SQL in the domain workspace through APEX SQL Workshop or an authorized database client. Configure only the prerequisites for the connection method you use, as described below.

Use the actual database identifiers, including their case. Replace <domainShortId>, <flowRuntimeShortId>, and <procedureName> in the examples with your values.

Access the IoT Domain Workspace

Choose one of the following ways to run SQL as the domain workspace user. Creating a Database Tools connection is not required for privilege delegation.

Continue with Grant a Database Privilege to a Flow Runtime. The workspace user needs authority to grant the privilege regardless of the SQL client used.

Grant a Database Privilege to a Flow Runtime

Give a Flow Runtime access to a workspace procedure that its flows need.

Choose a method to access the domain workspace. The workspace user must own the procedure or have authority to grant its EXECUTE privilege. Use an existing procedure in the workspace schema and a Flow Runtime belonging to the same IoT domain.

Grant only the object privileges required by the flow. Database grants are separate from IAM policies that authorize access to IoT resources. Remove a grant when the flow no longer needs that access.

  1. Record the IoT domain short ID and Flow Runtime short ID.

    The workspace user is <domainShortId>__WKSP. The Flow Runtime database user is <domainShortId>_<flowRuntimeShortId>__FR. Use the actual database identifiers, including their case; these names are different from the AQ subscriber name.

  2. Open SQL for the domain workspace and confirm the workspace context.

    In APEX SQL Workshop, select the <domainShortId>__WKSP schema before running statements in SQL Commands. For a direct database connection, connect as the workspace user and confirm the session identity:

    SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') AS current_user
FROM dual;

    For the direct connection, continue only when the current user is the intended workspace user.

  3. Grant EXECUTE on the required workspace procedure to the specific Flow Runtime user.

    Replace all placeholders with the actual schema, procedure, and user names.

    GRANT EXECUTE ON <domainShortId>__WKSP.<procedureName>
TO <domainShortId>_<flowRuntimeShortId>__FR;
  4. Check the grant and verify that the intended flow can use the procedure. 
    SELECT grantee, table_name, privilege
FROM user_tab_privs_made
WHERE grantee = '<domainShortId>_<flowRuntimeShortId>__FR'
  AND table_name = '<procedureName>'
  AND privilege = 'EXECUTE';

    Use the stored identifier case in this query. A grant record confirms the privilege; verify the flow separately with inputs appropriate to the procedure.

  5. When the flow no longer needs access, revoke the privilege as the workspace user. 
    REVOKE EXECUTE ON <domainShortId>__WKSP.<procedureName>
FROM <domainShortId>_<flowRuntimeShortId>__FR;

    Repeat the grant query to confirm that the direct grant is absent, and check any flows that depended on it before resuming their operation.