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

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.

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.

• snowflake-cloud-data-platform

Share Improve this question Follow edited Jan 15 at 18:14 Marco Roy asked Jan 15 at 3:07 Marco RoyMarco Roy 5,34588 gold badges4444 silver badges6666 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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')
            )
;