How to show which authentication policy is applied to a user in Snowflake

Question

We can now set authentication policies on Snowflake users:

ALTER USER some_user SET AUTHENTICATION POLICY some_policy;

But how can we see which authentication policy is assigned to which user?

There is nothing in SHOW PARAMETERS IN USER some_user; (like there is for network policies).

Otherwise, we run into this frustrating error (which prevents us from replacing the existing policy without unsetting it first...):

⚠️ Object SOME_USER already has a AUTHENTICATION_POLICY.

Only one AUTHENTICATION_POLICY is allowed at a time.

Answer 1

You can use POLICY_REFERENCES

Returns a row for each object that has the specified policy assigned to the object or returns a row for each policy assigned to the specified object. source

Syntax:

POLICY_REFERENCES(
    REF_ENTITY_NAME => '<username>' ,
    REF_ENTITY_DOMAIN => 'USER'
    )

Usage:

select POLICY_NAME
from table(
           information_schema.policy_references(
                       ref_entity_name => 'some_user', 
                       ref_entity_domain => 'USER')
            )
;