The ALL_USER_NAMES function in Snowflake is a powerful tool for retrieving a list of all user names within the current account. This feature simplifies user management and access control by providing administrators and users with an easy way to identify and list all user accounts associated with their Snowflake environment.
Syntax
The syntax for the ALL_USER_NAMES function is straightforward:
Arguments
This function doesn’t require any arguments.
Returns
The function returns an array containing user names. The data type of the returned value is ARRAY.
Usage Notes
Here are some important usage notes regarding the ALL_USER_NAMES function:
- User Privileges: Users with any active role can retrieve the list of all usernames in the current account. However, it’s essential to understand that merely knowing the usernames does not grant a role the ability to perform further actions on these users. User management requires a minimum set of privileges, which should be carefully assigned.
- Usernames vs. Login Names: In Snowflake, usernames (the
NAMEproperty value) serve as the unique identifier for user objects, while login names (theLOGIN_NAMEproperty value) are used for authentication. Usernames are not sensitive data and can be returned by various commands and functions, such asSHOW GRANTS. On the other hand, login names are sensitive and should be handled with care. - Best Practices: As a best practice, it is recommended to keep username and login name values different. To update existing username or login name values, you can execute the
ALTER USERcommand. When creating new users using theCREATE USERcommand, ensure that theNAMEandLOGIN_NAMEvalues are distinct to avoid confusion.
Examples
To illustrate the usage of the ALL_USER_NAMES function, consider the following example:
-- Return all user names for the current account
SELECT ALL_USER_NAMES();
+---------------------------+
| ALL_USER_NAMES() |
+---------------------------+
| [ "admin", "sachin", ... ] |
+---------------------------+
Snowflake important urls to refer
