Use Data Grants with Analytical SQL

18.2 Use Data Grants with Analytical SQL

Learn how you can use data grants with analytical SQL to create secure reports, dashboards, and analytical applications. This section covers aggregation, window calculations, cross-tabulation, ranking, and the effects of column- and cell-level authorization on query results.

The examples throughout this chapter use the following sales_app.sales sample table:

TENANT_ID  CATEGORY  MONTH_NO  AMOUNT  CUSTOMER_ID  SECRET_NOTE
---------  --------  --------  ------  -----------  --------------
10         A         1             10  101          TEN10-A-1
10         A         2             20  102          TEN10-A-2
10         A         3           NULL  102          TEN10-A-3
10         A         4             40  103          TEN10-A-4
10         B         1              5  104          TEN10-B-1
10         B         3             15  104          TEN10-B-3
20         A         1            900  901          TEN20-SECRET-1
20         A         2            950  902          TEN20-SECRET-2
20         B         1            990  903          TEN20-SECRET-3

Topics:

18.2.1 Determine the Data Available to Analytical Queries

Data grants determine the rows and column values that an analytical query can read for the current end user. A data grant row predicate determines the rows, while the column list and cell conditions determine which column values are returned. Analytical functions operate exclusively on those permitted rows and values.

Create a data grant

For the sales_app.sales table, the following data grant authorizes the sales_analyst_role to select all columns except secret_note from tenant-10 rows:

CREATE OR REPLACE DATA GRANT sales_app.tenant_sales_analytics AS
  SELECT (ALL COLUMNS EXCEPT secret_note)
  ON sales_app.sales
  WHERE tenant_id = 10
  TO sales_analyst_role;

When an end user with the sales_analyst_role queries the table, this data grant returns the six rows where tenant_id = 10, with secret_note returned as NULL. Other rows are not returned.

The following sections illustrate the behavior of various analytical operations on this restricted dataset.

Aggregate by category

The following query counts the sales rows and non-NULL amount values in each category, and calculates the total amount.

SELECT category,
       COUNT(*) AS sales_rows,
       COUNT(amount) AS amount_values,
       SUM(NVL(amount, 0)) AS total_amount
FROM sales_app.sales
GROUP BY ALL
ORDER BY category;

CATEGORY  SALES_ROWS  AMOUNT_VALUES  TOTAL_AMOUNT
--------  ----------  -------------  ------------
A                  4              3            70
B                  2              2            20

Category A has four rows, three non-NULL amount values, and a total of 70. Category B has two rows and a total of 20, bringing the category total to 90. The rows with tenant_id = 20 do not contribute to these results because they are filtered out by the data grant.

Running total

The following query partitions the available rows by category and calculates a cumulative amount in the month_no order.

SELECT category, month_no, amount,
       SUM(NVL(amount, 0)) OVER (
         PARTITION BY category
         ORDER BY month_no
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales_app.sales
ORDER BY category, month_no;

CATEGORY  MONTH_NO      AMOUNT  RUNNING_TOTAL
--------  ------------  ------  -------------
A                    1      10             10
A                    2      20             30
A                    3    NULL             30
A                    4      40             70
B                    1       5              5
B                    3      15             20

The running total for category A ends at 70, and the running total for category B ends at 20. The NVL function treats the stored NULL amount for category A, month 3, as zero for this calculation. The rows with tenant_id = 20 are excluded and do not contribute to the cumulative totals.

Cross-tabulation

The following query uses PIVOT to aggregate the available amounts into month columns.

SELECT *
FROM (
  SELECT category, month_no, amount
  FROM sales_app.sales
)
PIVOT (
  SUM(amount)
  FOR month_no IN (1 AS m1, 2 AS m2, 3 AS m3, 4 AS m4)
)
ORDER BY category;

CATEGORY  M1    M2    M3    M4
--------  --  ----  ----  ----
A         10    20  NULL    40
B          5  NULL    15  NULL

The result contains categories A and B from tenant 10. The M3 value for category A is NULL because the source amount is stored as NULL. The M2 and M4 values for category B are NULL because there are no tenant-10 rows for those category-month combinations. The larger tenant-20 amounts do not appear in any month column.

Top row in each category

In Oracle AI Database 26ai, the QUALIFY clause is used to filter the result of an analytic function. The following query returns the row with the highest available amount in each category.

SELECT category, month_no, amount,
       ROW_NUMBER() OVER (
         PARTITION BY category
         ORDER BY amount DESC NULLS LAST
       ) AS rn
FROM sales_app.sales
QUALIFY rn = 1
ORDER BY category;

CATEGORY  MONTH_NO      AMOUNT  RN
--------  ------------  ------  --
A                    4      40   1
B                    3      15   1

The ROW_NUMBER function ranks only the rows available through the data grant. QUALIFY therefore returns an amount of 40 for category A and 15 for category B. Tenant-20 values cannot affect the ranking.

Note:

Do not include analytical operations (such as window functions, aggregate functions, or QUALIFY clauses) inside a data grant predicate. Define these operations in the query that reads the object.

18.2.2 Apply Column- and Cell-Level Authorization

Column- and cell-level authorization can affect analytical results without filtering out the entire row from a query.

In the previous sales_app.tenant_sales_analytics data grant example, the row predicate ensures all six tenant-10 rows remain available, but the column list excludes the secret_note column. As a result, secret_note is returned as NULL in every row. Oracle SQL simply applies its standard NULL handling to any expressions that read that column, while all other authorized columns remain fully available for window and aggregate calculations.

Compare returned rows with available column values

The following query compares the total number of rows returned through the data grant against the number of non-NULL secret_note values.

SELECT COUNT(*) AS row_count,
       COUNT(secret_note) AS note_count,
       MAX(LENGTH(secret_note)) AS max_note_length
FROM sales_app.sales;

ROW_COUNT  NOTE_COUNT  MAX_NOTE_LENGTH
---------  ----------  ---------------
        6           0             NULL

In this result, COUNT(*) returns six because the row predicate makes all six tenant-10 rows available. However, COUNT(secret_note) returns zero and MAX(LENGTH(secret_note)) returns NULL because the data grant masks every secret_note value as NULL. As demonstrated in the previous analytical queries (such as the running total and PIVOT operations), all other column values remain fully accessible to analytical expressions.

Tip:

Explicitly list columns in your data grant when only a few columns should be accessible. Use

ALL COLUMNS
                                                EXCEPT

when most columns should be accessible and only a small number of columns should be hidden. Keep in mind that if a column is excluded by one data grant, another applicable data grant can still authorize access to it.

18.2.3 Apply Attribute-Based Access Control

While previous examples used a fixed tenant_id for simplicity, real-world applications typically filter rows based on the current user's identity or business context. You can achieve this by referencing end-user context attributes directly in your data grant predicates.

For an end user authenticating with an OAuth 2.0 access token, the system-managed USER.DEFAULT and USER.TOKEN contexts contain system attributes that identify the end user and claims extracted directly from the end user's OAuth 2.0 access token. To store other business attributes (such as tenant_id, emp_id, or org_id), create a custom end-user context. See Read End-User Context Attributes to learn how to reference these attributes in a data grant predicate.

Replacing fixed predicates with context attributes

Assume a custom context named sales_app.sales_context is configured to define an integer tenant_id attribute. The following statement updates the previous sales_app.tenant_sales_analytics data grant so that the row predicate dynamically reads the user's attribute rather than a hard-coded value. The column authorization remains unchanged. See Load and Use Custom End-User Context Attributes for Analytics for a complete configuration example.

CREATE OR REPLACE DATA GRANT sales_app.tenant_sales_analytics AS
  SELECT (ALL COLUMNS EXCEPT secret_note)
  ON sales_app.sales
  WHERE tenant_id =
        ORA_END_USER_CONTEXT.sales_app.sales_context.tenant_id
  TO sales_analyst_role;

If sales_app.sales_context.tenant_id evaluates to ten here, the data grant returns the same six rows as the previous, hard-coded example. Using CREATE OR REPLACE is essential in this case as it ensures the new dynamic policy overwrites the previous static one.

Note:

To query ORA_END_USER_CONTEXT directly or use it in a data grant predicate, set the database instance COMPATIBLE initialization parameter to 20.0 or greater.