Secure objects: Redaction of information in error messages (Pending)

Error messages related to secure objects behave as follows:

Before the change:

Error messages related to secure objects show the full message.

After the change:

Error messages related to secure objects might be redacted.

The change applies to error messages related to the following types of objects:

For more information about secure objects, see Use secure objects to control data access.

When an error is detected during the expansion or evaluation of a secure object, the error message is considered for redaction. When an error message is redacted, the error code remains unchanged.

Two types of changes to error messages are possible: redaction during execution and redaction in metadata after execution. These types of changes are described in the following sections.

Redaction during execution

A whole error message or a part of an error message can be redacted when the error is returned during an operation. Generally, this type of error message redaction occurs when a user tries to use a secure object without having the OWNERSHIP privilege on the secure object.

Redaction in metadata after execution

Users can view metadata about errors after they occur, including the error messages. For example, users can view this metadata in the Query History page in Snowsight, or by querying views and calling functions in the Snowflake Information Schema. When an error message is redacted during execution, the error message is always redacted in the metadata after execution for all users.

When an error message isn’t redacted during execution, the message appears unchanged in the metadata for some users and is redacted for other users. The error message is unchanged in the metadata in either of the following cases:

  • The user viewing the metadata has the AUDIT privilege.

  • The user viewing the metadata executed the statement that caused the error.

In all other cases, the error message is redacted in the metadata. Redacted error messages include the text: Error in secure object.

Examples of error message redaction

The following examples show error messages that are redacted. The redaction can occur during execution or in metadata after execution.

Example 1: Querying a secure view

In the following example, a user with SELECT privilege on a secure view executes a query on the view that returns an error.

Create the secure view:

CREATE SECURE VIEW myview
  AS SELECT a FROM mytable;
Copy

Drop the table used in the view query:

DROP TABLE mytable;
Copy

Execute a query on the view:

SELECT * FROM myview;
Copy

Error message displayed to all users before the change

002037 (42601): SQL compilation error:
Failure during expansion of view 'MYVIEW': SQL compilation error:
Object 'DB.SC.MYTABLE' does not exist or not authorized.

Redacted error message displayed to some users after the change

002037 (42601): SQL compilation error:
Failure during expansion of view 'MYVIEW': Error in secure object

Example 2: Running a query that calls a secure function

In the following examples, a user with USAGE privilege on a secure function executes a query that calls the secure function, but the secure function returns an error.

Example 2a: The function arguments result in an error

Create the secure function:

CREATE SECURE FUNCTION myfunction1(x FLOAT, y FLOAT)
  RETURNS FLOAT
  LANGUAGE SQL
AS
  'SELECT x / y';
Copy

Execute a query that calls the secure function:

SELECT myfunction1(1, 0);
Copy
Error message displayed to all users before the change
100051 (22012): Division by zero
Redacted error message displayed to some users after the change
100051 (22012): Error in secure object

Example 2b: An object the function depends on is deleted

Create the secure function:

CREATE SECURE FUNCTION myfunction2()
  RETURNS TABLE (a NUMBER)
  LANGUAGE SQL
AS
  'SELECT * FROM mytable';
Copy

Drop the table used in the function:

DROP TABLE mytable;
Copy

Execute a query that calls the secure function:

SELECT * FROM TABLE(myfunction2());
Copy
Error message displayed to all users before the change
002003 (42S02): SQL compilation error:
Object 'DB.SC.MYTABLE' does not exist or not authorized
Redacted error message displayed to some users after the change
002003 (42S02): Error in secure object

Example 3: A masking policy returns an error

In the following example, a user runs a query on a view with a masking policy that encounters an error.

Create a masking policy:

CREATE MASKING POLICY allowed_role_names_mp as (val NUMBER) RETURNS NUMBER ->
  CASE
    WHEN EXISTS
      (SELECT role FROM allowed_roles WHERE role = CURRENT_ROLE()) THEN val
    ELSE '********'
  END;
Copy

Create a view and set the masking policy on a column in the view:

CREATE TABLE test_masking_policy(x NUMBER) AS
  SELECT * FROM VALUES (1), (2), (3);

CREATE VIEW myview_mp
  AS SELECT * FROM test_masking_policy;

ALTER VIEW myview_mp
  MODIFY COLUMN x SET MASKING POLICY allowed_role_names_mp;
Copy

Drop the table used in the masking policy:

DROP TABLE allowed_roles;
Copy

Execute a query on the view as a user that doesn’t have ownership privileges on the masking policy:

SELECT * FROM myview_mp;
Copy

Error message displayed to all users before the change

002003 (42S02): SQL compilation error:
Object 'DB.SC.ALLOWED_ROLES' does not exist or not authorized.

Redacted error message displayed to some users after the change

002003 (42S02): Error in secure object

Example 4: A row access policy returns an error

In the following example, a user runs a query on a view with a row access policy and encounters an error.

Create a row access policy:

CREATE OR REPLACE ROW ACCESS POLICY myrap AS (role NUMBER) RETURNS BOOLEAN ->
  EXISTS (
    SELECT 1 FROM allowed_roles
      WHERE role::STRING = CURRENT_ROLE());
Copy

Create a view and add the row access policy on the view:

CREATE TABLE test_row_access_policy(x NUMBER) AS
  SELECT * FROM VALUES (1), (2), (3);

CREATE VIEW myview_rap
  AS SELECT * FROM test_row_access_policy;

ALTER VIEW myview_rap
  ADD ROW ACCESS POLICY myrap ON (x);
Copy

Drop the table used in the row access policy:

DROP TABLE allowed_roles;
Copy

Query the view as a user that doesn’t have OWNERSHIP privileges on the row access policy:

SELECT * FROM myview_rap;
Copy

Error message displayed to all users before the change

002003 (42S02): SQL compilation error:
Object 'DB.SC.ALLOWED_ROLES' does not exist or not authorized.

Redacted error message displayed to some users after the change

002003 (42S02): Error in secure object

Ref: 1858

Language: English