[Feature] Support Snowflake Database Roles in grants

### Is this your first time submitting a feature request?

- [X] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [X] I have searched the existing issues, and I could not find an existing issue for this feature
- [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

Snowflake supports the concept of a [database role](https://docs.snowflake.com/user-guide/security-access-control-overview#label-access-control-overview-role-types), a role that can only be assigned permissions on a database, but no account level permissions like managing users.

However, this comes with a slightly altered SQL-Query where GRANTS and REVOKES are concerned.
```
# Database role grant
GRANT SELECT ON DB.SCHEMA.TABLE to database role ROLE_NAME;
# Regular role grant
GRANT SELECT ON DB.SCHEMA.TABLE to [role] ROLE_NAME;
# Database role revoke
REVOKE SELECT ON DB.SCHEMA.TABLE from database role ROLE_NAME;
# Regular role revoke
REVOKE SELECT ON DB.SCHEMA.TABLE from [role] ROLE_NAME;
```
From the logs I gather that DBT does not specify the object type in the Query and thus implicitly refers to roles. In fact, when a databse role grant has been (manually) added to a table this leads to an error:

> show grants on [Model]
09:25:06.053642 [debug] [Thread-1 (]: SQL status: SUCCESS 3 in 0.0 seconds
[...]
revoke SELECT on [Model] from [DATABASE_ROLE_NAME];
09:25:06.164517 [debug] [Thread-1 (]: Snowflake adapter: Snowflake query id: 01b67eb5-0302-f4cc-0002-501e0063758a
09:25:06.166090 [debug] [Thread-1 (]: Snowflake adapter: Snowflake error: 002003 (02000): SQL compilation error:
Role '[DATABASE_ROLE_NAME]' does not exist or not authorized.

For revokes the neccessary information can be gathered from the `granted_to` column in the `show grants` response:
|created_on|privilege|granted_on|name|granted_to|grantee_name|grant_option|granted_by|granted_by_role_type
|---|---|---|---|---|---|---|---|---
|[...]|SELECT|TABLE|[TABLE_ID]|ROLE|[ROLE_NAME]|false|ACCOUNTADMIN|ROLE
|[...]|SELECT|TABLE|[TABLE_ID]|DATABASE_ROLE|[DATABASE_ROLE_NAME]|false|ACCOUNTADMIN|ROLE

However I can not think of a concise way to configure this distinction in the models.

One option would be to duplicate the `grants` keyword:
```
{{
config(
materialized='incremental',
grants = {
'select': '[ROLE_NAME]'
},
database_role_grants = {
'select': '[DATABASE_ROLE_NAME]'
}
)
}}
```
Alternatively a special notation could be used:
```
{{
config(
materialized='incremental',
grants = {
'select': 'DATABSE_ROLE.[DATABASE_ROLE_NAME]'
},
)
}}
```

As none of these options are particularly elegant, I would understand a reluctance to implement this feature. I'm opening this issue mostly for documentation purposes since I was unable to find anything on this topic online.

In that sense I would suggest adding this limitation to the DBT grants documentation page since it already contains [a database specific section](https://docs.getdbt.com/reference/resource-configs/grants#database-specific-requirements-and-notes)

### Describe alternatives you've considered

_No response_

### Who will this benefit?

_No response_

### Are you interested in contributing this feature?

_No response_

### Anything else?

_No response_