Marmanold.com

Recently I’ve been working with secure data sharing in Snowflake.

We’ve not yet shared our data using a normal private share to another Snowflake account, but I’ve tested everything. Using SIMULATED_DATA_SHARING_CONSUMER and setting the consumer id to my expected account, I was getting data back from the share and everything was working fine.

Today, however, we got a request to share our data to a managed reader account. I created the account, logged in, copied the share into a database, and could see all of my secure views. But, when I went to query the view, some of the views that were previously working, suddenly were not. All I got back was an ominous “Error in secure object.”

My view looked something like the below. It was a simple view, no private functions, and nothing fancy.

CREATE
OR REPLACE SECURE VIEW secure.student AS
SELECT
    ss.id AS "student_id",
    ss."name" AS "student_name"
    sv.groups As "group"
FROM private.students AS ss
INNER JOIN private.v_student_groups AS sv ON
    sv.student_id = ss.id
INNER JOIN private.sharing_access AS sa ON
    sa.school_id = ss.school_id
    AND sa.snowflake_account = current_account();

After much trial and error, I finally figured out what the issue was. I’m not sure if this applies only to managed reader accounts or all shares, but at least in my case there are a few things that just aren’t allowed in a Snowflake secure view.

1. ss."name" had to be changed to ss.name; I.e no quoted column references
2. My join to v_student_groups (a view) had to go.; You can join to tables, but views are out of the question, it would seem.

I didn’t see any of this documented anywhere, so I hope this gets indexed and helps the next guy banging his head against this issue.

tldr; If you’re getting an Error in secure object response when querying a secure view in a Snowflake managed reader account, you need to make some minor changes to the SQL in your views before it’ll run.