It is indeed possible to create a new Named Right that allows users to interact with data via a Snowflake External Account without requiring full Admin access. This approach ensures a secure and role-based method for standard users to run their BAU queries. Here's how you can approach it:
1. Define Role-Based Access
Named Rights can be created to define specific permissions tied to a Snowflake External Account. To achieve this:
- Identify the exact operations or queries users need to perform via the external account.
- Limit access to only the necessary actions to maintain a principle of least privilege.
2. Use Snowflake’s Role Hierarchy
Snowflake allows for granular permission control using roles. Create a new role for standard users and assign permissions that align with BAU activities:
- Grant access to the external account.
- Assign specific privileges such as USAGE, SELECT, or EXECUTE on the required objects (databases, schemas, or tables).
3. Example Configuration
Below is an example of how to configure a Named Right for this purpose:
-- Create a custom role for BAU queries CREATE ROLE BAU_QUERY_ROLE; -- Grant privileges to the role for external account access GRANT USAGE ON EXTERNAL ACCOUNT <External_Account_Name> TO ROLE BAU_QUERY_ROLE; GRANT SELECT ON DATABASE <Database_Name> TO ROLE BAU_QUERY_ROLE; -- Assign the role to the necessary users GRANT ROLE BAU_QUERY_ROLE TO USER <User_Name>;
4. Testing and Validation
Once the Named Right and roles are created:
- Test access with a standard user account assigned to the new role.
- Confirm users can execute their BAU queries via the external account.
- Verify that they are restricted from performing admin-level tasks.
5. Document and Monitor
Clearly document this new Named Right for governance purposes and regularly monitor its use to ensure compliance with your organization's data access policies.
