Managing Snowflake users, roles, and grants with Python¶

You can use Python to manage Snowflake users, roles, and grants. For more information about managing users and their privileges in Snowflake, see User management.

Prerequisites¶

The examples in this topic assume that you’ve added code to connect with Snowflake and to create a Root object from which to use the Snowflake Python APIs.

For example, the following code uses connection parameters defined in a configuration file to create a connection to Snowflake:

from snowflake.core import Root
from snowflake.snowpark import Session

session = Session.builder.config("connection_name", "myconnection").create()
root = Root(session)

Copy

Using the resulting Session object, the code creates a Root object to use the API’s types and methods. For more information, see Connect to Snowflake with the Snowflake Python APIs.

Managing users¶

You can manage users in Snowflake. A user is an account-level object in Snowflake. The Snowflake Python APIs represents users with two separate types:

User: Exposes a user’s properties, such as its name.
UserResource: Exposes methods you can use to fetch a corresponding User object and to drop the user.

Creating a user¶

You can create a user by calling the UserCollection.create method and passing a User object that represents the user you want to create. To create a user, first create a User object that specifies the user name.

Code in the following example creates a User object representing a user named my_user and then creates the user by passing the User object to the UserCollection.create method:

from snowflake.core.user import User

my_user = User(name="my_user")
root.users.create(my_user)

Copy

Getting user details¶

You can get information about a user by calling the UserResource.fetch method, which returns a User object.

Code in the following example gets information about a user named my_user:

my_user = root.users["my_user"].fetch()
print(my_user.to_dict())

Copy

Listing users¶

You can list users using the iter method, which returns a PagedIter iterator.

Code in the following example lists users whose name begins with my:

users = root.users.iter(like="my%")
for user in users:
  print(user.name)

Copy

Dropping a user¶

You can drop a user using the UserResource.drop method.

Code in the following example drops the my_user user:

my_user_res = root.users["my_user"]
my_user_res.drop()

Copy

Managing roles¶

You can manage roles in Snowflake. A role is an account-level object. The Snowflake Python APIs represents roles with two separate types:

Role: Exposes a role’s properties, such as its name.
RoleResource: Exposes methods you can use to fetch a corresponding Role object and to drop the role.

Creating a role¶

To create a role, first create a Role object that specifies the role name.

Code in the following example creates a Role object representing a role named my_role:

from snowflake.core.role import Role

my_role = Role(name="my_role")
root.roles.create(my_role)

Copy

The code then creates the role by passing the Role object to the RoleCollection.create method.

Using a role in a session¶

Code in the following example applies the role my_role in the current session.

root.session.use_role("my_role")

Copy

Listing roles¶

You can list the roles in an account using the iter method. The method returns a PagedIter iterator of Role objects.

Code in the following example lists all role names in an account:

role_list = root.roles.iter()
for role_obj in role_list:
  print(role_obj.name)

Copy

Dropping a role¶

You can drop a role using the RoleResource.drop method.

Code in the following example drops the my_role role:

my_role_res = root.roles["my_role"]
my_role_res.drop()

Copy

Managing grants¶

You can execute GRANT <privileges> operations to grant access privileges on a securable Snowflake object to a role.

Note

Grant operations currently have the following limitations:

SHOW GRANTS ON is not supported.
SHOW GRANTS TO is supported through the Grants.to method.

Granting privileges¶

To grant privileges on a Snowflake object, you first create a Grant object that specifies the following attributes:

grantee: The role or user that is being granted the privileges.
securable: The Snowflake object that is being secured by the privileges.
privileges: The privileges that are being granted to a role.

Granting CREATE privileges in an account to a role¶

Code in the following example creates a Grant object representing a grant operation that grants the privileges create_database and create_warehouse to the role my_role in the current Snowflake account. The code executes the operation using the root.grants.grant method.

from snowflake.core.grant import Grant
from snowflake.core.grant._grantee import Grantees
from snowflake.core.grant._privileges import Privileges
from snowflake.core.grant._securables import Securables

root.grants.grant(
  Grant(
    grantee=Grantees.role(name='my_role'),
    securable=Securables.current_account,
    privileges=[Privileges.create_database,
                Privileges.create_warehouse],
  )
)

Copy

Granting privileges on a database to a role¶

Code in the following example grants imported privileges on the database my_db to the role my_role:

from snowflake.core.grant import Grant
from snowflake.core.grant._grantee import Grantees
from snowflake.core.grant._privileges import Privileges
from snowflake.core.grant._securables import Securables

root.grants.grant(
  Grant(
    grantee=Grantees.role('my_role'),
    securable=Securables.database('my_db'),
    privileges=[Privileges.imported_privileges],
  )
)

Copy

Granting a role to another role¶

You can assign a role to another role to create a “parent-child” relationship between the roles (also referred to as a role hierarchy).

Code in the following example grants the my_role user role to the ACCOUNTADMIN system role:

from snowflake.core.grant import Grant
from snowflake.core.grant._grantee import Grantees
from snowflake.core.grant._securables import Securables

root.grants.grant(
  Grant(
    grantee=Grantees.role('ACCOUNTADMIN'),
    securable=Securables.role('my_role'),
  )
)

Copy