Which option will you choose to setup the access?

Suppose you have two databases D1 and D2. Theses databases contain data required by business analysts in your organization. Based on their functional responsibilities, entry level analysts should have read only access to D1, but access to D2 should be given to advanced analysts only.

Which option will you choose to setup the access?

Option 1:

Option 2:

Option 3:

A . Option 1
B. Option 2
C. Option 3

View Answer

Answer: C

Explanation:

This is a very very very important concept to understand . Please look at the key words role hierarchy and privilege inheritance below. This is the recommended option to setup access in snowflake. Aligning Object Access with Business Functions

Consider taking advantage of role hierarchy and privilege inheritance to align access to database objects with business functions in your organization. In a role hierarchy, roles are granted to other roles to form an inheritance relationship. Privileges granted to roles at a lower level are inherited by roles at a higher level.

As a simple example, suppose two databases, d1 and d2, contain data required by business analysts in your organization. Based on their functional responsibilities, entry-level analysts should have read-only access to d1, but access to d2 should be limited to advanced analysts. A recommended approach to configuring security on these databases would involve creating a combination of object access roles and

business function roles for optimal control.

To configure access in this example:

As a security administrator (user with the SECURITYADMIN role) or another role with the CREATE ROLE privilege on the account, create roles analyst_basic and analyst_adv. These roles correspond to the business functions of your organization and serve as a catch-all for any object access roles required for these functions. Because basic analyst functions are also required by advanced analysts, grant the analyst_basic role to the analyst_adv role.

Following best practices for role hierarchies, grant analyst_adv to the system administrator (SYSADMIN)

role. System administrators can then grant privileges on database objects to any roles in this hierarchy.

create role analyst_basic;

create role analyst_adv;

grant role analyst_basic to role analyst_adv;

grant role analyst_adv to role sysadmin;

Using the same role as in Step 1, create object access roles db1_read_only and db2_read_only and grant these roles to the business function roles that require them. In this case, grant the db1_read_only to the analyst_basic role, and grant the db2_read_only role to the analyst_adv role. create role db1_read_only;

create role db2_read_only;

grant role db1_read_only to role analyst_basic;

grant role db2_read_only to role analyst_adv;

As a security administrator (user with the SECURITYADMIN role) or another role with the MANAGE

GRANTS privilege on the account, grant db1_read_only and db2_read_only read-only access to

databases d1 and d2, respectively. For more information, see Creating Read-Only Roles. These roles

define a set of grants to access data objects.

grant <privileges> to role db1_read_only;

grant <privileges> to role db2_read_only;

As a security administrator (user with the SECURITYADMIN role) or another role with the MANAGE GRANTS privilege on the account, grant the business function roles to the users who perform those functions:

grant role analyst_basic to user user1;

grant role analyst_adv to user user2;

Privileges granted to the lower-level (in the role hierarchy) object access roles db1_read_only and db2_read_only are inherited by the higher-level business function roles analyst_basic and analyst_adv roles, respectively. Also, because analyst_basic is granted to analyst_adv, any privileges granted to db1_read_only or analyst_basic are inherited by analyst_adv.

Users granted the analyst_adv role can access both db1 and db2; however, users granted the analyst_basic role can only access db1.