How to grant application role to application in snowddl

[y-tee]

https://docs.snowflake.com/en/user-guide/performance-explorer#required-privileges

hi, any idea how can i grant the above to allow user to access performance explorer?

also on database role, https://docs.snowddl.com/basic/yaml-configs/database-role, the documentation says

DATABASE_ROLE is intended to be used for [outbound SHAREs](https://docs.snowddl.com/basic/yaml-configs/share-outbound) only.

It is not a part of SnowDDL [role hierarchy](https://docs.snowddl.com/guides/role-hierarchy) and should not be used to grant access outside of shares.

should i be using the database_role.yml file?

[littleK0i]

You may grant share role via parameter share_read. For example:

share_read:
  - SNOWFLAKE.USAGE_VIEWER

Default SNOWFLAKE database is a share.

Application roles are currently not implemented directly, but maybe now is a good time to add it. Applications were problematic and unstable initially, so no serious accounts ever used them. But now it should be fine. I'll add it with the next update.

In the meantime, you may sidestep all permission-related issues and limitations by using global_roles. Create a role manually and grant it to SnowDDL business roles or schema roles via global_roles parameter.

[littleK0i]

The main question regarding applications is how they should be treated in context of "env prefix".

Can we create multiple instances of the same application? Is it a good idea?

Alternatively, should we let admins create one application per account and let all dev environments share single instance of application? Is it dangerous? Does it cause any problems?

Everything else should be relatively straightforward.

[littleK0i]

I've pushed a small update with application roles support.

Try this for business role:

my_role:
  application_role:
    - SNOWFLAKE.APP_USAGE_VIEWER
  share_read:
    - SNOWFLAKE.USAGE_VIEWER

[y-tee]

Hi thanks for the prompt reply + feature roll out within the same day. day saver!
While my team is using it, we encountered this error:

WARNING - Resolved ROLE [MONITORING_ROLE__B_ROLE]: ERROR
(
    message   =>  SQL compilation error:
Application role 'SNOWFLAKE.APP_USAGE_VIEWER' does not exist or not authorized.
    errno     =>  2003
    sqlstate  =>  02000
    sfqid     =>  01c1be24-3202-3d7f-000f-34c20212da4a
    sql       =>  GRANT APPLICATION ROLE "SNOWFLAKE"."APP_USAGE_VIEWER" TO ROLE "MONITORING_ROLE__B_ROLE"
)

Noticed that this is a different behavior than the other objects that requires accountadmin role like ip, and and account_params. In those objects, i notice that it would not throw error or warning, but it would show up in suggested query when i snowddl apply and says x suggested query. After applying it, these suggestions will be gone. We are using it as a very good indicator for version controlling our non snowddl role applied items after manually applying it with accountadmin. Wonder if this could behave similar?

Also, I believe i asked elsewhere before. As we are using it more and more, I found out that there are alot more of objects that is actually reliant on accountadmin to apply. While i understand that those are critical objects that should be handle with care and not use snowddl in your point of view, we also want to version control and keep it tracked and allow the team to be able to alter it without over-relying on the few accountadmins that we have granted the role to. Any suggestions on how we can do this with or without snowddl?

[littleK0i]

I guess, Snowflake requires ACCOUNTADMIN role to apply. Which goes completely against their own MANAGE GRANTS permission, which technically should allow to grant anything.

It probably needs to be raised with Snowflake, since they do not respect their own design in this specific case.

The approach with "global role" should still work as workaround. You may create a global role using ACCOUNTADMIN and grant it to other roles.

[littleK0i]

In theory, you may grant ACCOUNTADMIN to SnowDDL admin role by following this instruction: https://docs.snowddl.com/guides/other-guides/admin#id-2-role-with-accountadmin

But it creates another set of problems.

1. If something goes wrong, you may drop something important unintentionally.
2. ACCOUNTADMIN won't be the highest role in the hierarchy anymore, which causes object visibility problems. Objects created by SnowDDL admin won't be visible to ACCOUNTADMIN.

Fundamentally, it all boils down to poor design on Snowflake side. There is no good reason to have hundres of granular permissions for most object types, but still attach some specific actions to ACCOUNTADMIN. Especially stuff like RESOURCE MONITORS.