In the Snowflake model, Role Based Access Control is framework allows the access to securable objects via privileges assigned to roles. However ,which are in turn assigned to other roles or users.
In addition, Framework restricts access based on a person’s role within an organization. Employees are only allowed to access the information necessary to effectively perform their job duties. Access can be based on several factors, such as authority, responsibility, and job competency.
Using Roles will help in securing your company’s sensitive data and important applications.
During this post we will talk about one close to Real time example where we will create multiple level hierarchy along with privileges ,assigned to the Roles based on their Business Functional portfolio.
REPS : collection Representative who talks to the Customer to recover the amount.
FLM : First Line Manager ,REPS reports to FLM. Every organization has hierarchy and here REPS reports to FLM for any escalation.
OPS: Operational Manager : FLM reports to OPS, so via the virtue of FLM OPS have full control on the objects governed by REPS.
REPS will have Read only privilege’s to the Tables.
FLM will be having Read/Update privilege’s via the virtue of Recovery Team and Billing Team roles.
OPS have all privileges i.e. INSERT,UPDATE,DELETE via Support team, RTEAM,BTEAM and REP Roles
Please find below technical steps to achieve the above Scenario:
Now Login to the REP1 and see the results:
Login to the REP2 and see the results:
Login to the REP3 and see the results:
Login to the FLM1 and see the results:
Login to the FLM2 and see the results:
Login to the OPS and see the results:
In conclusion, Role based access control provide the object security based on the person’s role.