Author: Harshita Verma
INTRODUCTION
It is a security approach that, inside an organization, authorizes and restricts system access to users based on their role(s). This approach allows users to access the data and applications needed to fulfill their job requirements, and the risk of unauthorized employees accessing sensitive information or performing unauthorized tasks gets minimal.
Discretionary Access Control (DAC): Each object has an owner who has the authority to allow access to it.
Role-based Access Control (RBAC): Roles are given with access privileges. Users are subsequently assigned these roles.
System-Defined Roles
ORGADMIN
The role that manages operations at the organizational level. More specifically, this role:
Can create accounts in the organization.
Can view all accounts in the organization as well as all regions enabled for the organization (using SHOW REGIONS).
Can view usage information across the organization.
ACCOUNT ADMIN
This role has the top priority in the hierarchy, which encapsulates the SYSADMIN and SECURITYADMIN system-defined roles. Access to this role should be granted only to a limited/controlled number of users in your account.
SECURITYADMIN
The role can manage any object grant globally, create, monitor, and manage users and roles. It has the privilege to be able to modify any grant, including revoking it. This role works as a parent role to the USERADMIN role via the system role hierarchy.
USERADMIN
The role is dedicated to users and roles only. This role mainly works to CREATE USER and CREATE ROLE security privileges. Only the roles which are higher in the hierarchy have the privilege to modify the object properties.
SYSADMIN
The role has the privilege of create warehouses and databases in an account. If, as recommended, you create a role hierarchy that assigns all custom roles to the SYSADMIN role, this role can also grant privileges on warehouses, databases, and other objects to other roles.
PUBLIC
The PUBLIC role can own securable objects; it's a default role, just like any other role by definition, available to other users and roles in your account because it's in the least place in the hierarchy. This role is typically used in cases where access control is not needed, and all users are viewed as equal to their access rights.
CUSTOM ROLES
Custom roles can be created by the SECURITYADMIN roles or by any role that has the privilege CREATE ROLE when creating roles that will serve as the owners of objects in the system with the top-most custom role assigned to the system role SYSADMIN.
PRIVILEGES
There is a set of privileges that can be granted to it for each securable object. Privileges must be granted on individual objects for existing objects to simplify grant management and future grants allow. Defining an initial of privileges on objects created in a schema. The GRANT and REVOKE commands are managed, providing the privileges.
ROLE HIERARCHY
Below given diagram illustrates the hierarchy of the system-defined roles, along with
the recommended structure for additional, user-defined custom roles:
The goal is to automate the RBAC creation and maintenance process in Snowflake with this RBAC Accelerator application. This application will be built upon Snowflake, which will have a user interface where a user can configure the RBAC for their Snowflake instance easily without writing any code, which would be reflected in the backend, i.e., the Snowflake instance. The connection from the user interface to Snowflake is achieved via APIs, which will call the stored procedures created in the Snowflake. The accelerator has three components. Procedures in the backend are created using Snowflake Javascript Stored Procedures. Connected to Snowflake using Flask APIs.
As per the above image, you can see in the RBAC accelerator, we have individual tabs to create, drop, and grant, and we don't need to write a query for those actions.
PYTHON WITH FLASK
Flask is a small and lightweight microweb framework written in Python. It provides many useful tools and features that help us create web applications in Python very easily. This command installs the flask module.pip install flask. This is what a basic flask API would look like.
@blueprint_x.route('/test', methods=['GET'])
def test():
output = {"msg": "I’m the test endpoint from blueprint_x."}
return jsonify(output)
To perform some tasks, we use App routing is used to map the specific URL with the associated function
The code supports all types of HTTP requests. ['POST', 'GET',’DELETE’,’PUT’]
PYTHON CONNECTOR
Python applications that can connect to python applications that can connect to Snowflake and perform all standard operations. The connector python package has no dependencies on JDBC or ODBC. We are using pip on Linux, macOS, and Windows platforms where Python is installed with the supported version. Developing applications using the Python Database API v2 specification, including the standard API objects supported by the connector:
CONNECTION OBJECTS FOR CONNECTING TO SNOWFLAKE
Cursor objects for executing DDL/DML statements and queries.
To install the connector, run the following command.
pip install snowflake-connector-python==<version>
To use this connector in your python flask application, import the module as shown below:
import snowflake.connector
Below demonstrates the connection created using the module and the process of using SQL queries with the help of the connection:
ctx = snowflake.connector.connect
(
user='<user_name>',
password='<password>',
account='<account_identifier>’
)
cs = ctx.cursor()
try:
cs.execute("SELECT current_version()")
one_row = cs.fetchone()
print(one_row[0])
finally:
cs.close()
ctx.close()
Replace <account_identifier> with your account identifier.Replace <user_name> with the user name and <password> with the password that you use to connect to Snowflake.
Calling Stored Procedures and JSONifying the Response Data:
connection = <snowflake connection object>
cursor = connection.cursor()
return_databases_query = “CALL SP_RETURN_DATABASES();”
response = cursor.fetchone()
response_ = json.loads(response[0])
return jsonify(response_)
json.loads()
The method can be used to parse a valid JSON string and convert it into a Python Dictionary.
jsonify converts a json output into a response object with application/json.
AWS account
Create an account with AWS and create an IAM policy, then create the user; when you create that policy, it will generate an access key and secret key, then go to AWS amplify and deploy from there.
And fill those keys in ubuntu as per ask one by one. Then further choose language and option as per convenience. Now, search AWS amplify in the AWS account, and select the region, set up amplify studio by choosing deploy without git-provider.
Key Generation
AWS Amplify
Prerequisites Objects:
Create the database and schema to store the procedures created using the role SYSADMIN.
USE ROLE SYSADMIN;
CREATE DATABASE "ACCELERATOR_DB";
CREATE SCHEMA "ACCELERATOR_DB"."RBAC";
The actions like creating roles, creating users, and managing grants are done by securityadmin, So it needs privileges on the database and schema created. The following scripts do the same.
GRANT USAGE ON DATABASE ACCELERATOR_DB TO ROLE SECURITYADMIN;
GRANT USAGE,CREATE TEMPORARY TABLE ON SCHEMA RBAC TO ROLE SECURITYADMIN;
Grant creates tag privilege for securityadmin to create object tags.
USE ROLE SECURITYADMIN;
USE SCHEMA ACCELERATOR_DB.RBAC;
GRANT CREATE TAG ON SCHEMA ACCELERATOR_DB.RBAC TO ROLE SECURITYADMIN;
Create Tags with specified allowed values.
CREATE TAG environment ALLOWED_VALUES 'dev','qa', 'prod', 'system';
CREATE TAG role_category ALLOWED_VALUES 'fr','ar';
CREATE TAG role_type ALLOWED_VALUES 'ro','rw','all';
Set tag values for Snowflake-defined system roles.
USE ROLE ACCOUNTADMIN;
ALTER ROLE ACCOUNTADMIN SET TAG environment = 'system';
ALTER ROLE SECURITYADMIN SET TAG environment = 'system';
ALTER ROLE USERADMIN SET TAG environment = 'system';
ALTER ROLE SYSADMIN SET TAG environment = 'system';
ALTER ROLE PUBLIC SET TAG environment = 'system';
ALTER ROLE ORGADMIN SET TAG environment = 'system';
*This may not be available for trial accounts, you can skip this statement if it throws an error.
Stored Procedure in Snowflake:
A stored procedure is a pre-compiled object of the database, which is a group of SQL statements. A stored procedure can be written in two ways by using SQL or the other you can use JavaScript.
In the stored procedure, we use JavaScript, which Snowflake provides a way to use. We are discussing this functionality only.
We can create a database, table, view, function, and stored procedure. To work with these objects, we run the query under the following context,
Role: role is the database role like an owner, admin, dev role
Warehouse: You can simply create a warehouse. Actually, the main use of it is computing.
Database: It stores all the tables and Database objects.
Schema: schema can be created under the database. The table and view will be situated inside the schema only.
Insert records into a table using the stored procedure in Snowflake
Create a table in Snowflake
CREATE OR REPLACE TABLE Employee(employee_id INT, employee_name varchar,employee_address varchar);
Create an identity column or sequence on the table
CREATE SEQUENCE if not does not exist employee_id;
Create a stored procedure like below
The Javascript code should be between $$ ..$$. Important thing that needs to be noted here is that the parameters are in lowercase, but in Snowflake, inside the stored procedure, you have to use the parameter as a capital letter.
CREATE OR REPLACE PROCEDURE employee_insert(name varchar,address varchar)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var command = "INSERT INTO Employee (emp_id, emp_name,emp_address) VALUES (emp_id.nextval, '"+NAME+"','"+ADDRESS+"')";
var cmd1_dict = {sqlText: command};
var stmt = snowflake.createStatement(cmd1_dict);
var rs = stmt.execute();
return 'success';
$$;
Call the stored procedure:
CALL employee_insert('Nitesh','Hyderabad');
RBAC HIERARCHY:
The below image shows the RBAC role hierarchy tree, which is the first thing seen on the landing page after successful login. Notice how every role has a certain color implying that they belong to a certain group like QA, PROD, etc., which has been given in the legend above the graph.
CONCLUSION
The main purpose of using the RBAC accelerator is to avoid lots of queries for every role, warehouse, user, and privilege. Here we create a UI that is directly connected to the Snowflake account. Whenever we want to grant privileges or assign roles, we can do it directly from the UI. The person who is technical or not can perform actions from the RBAC accelerator. The whole mapping of roles will be here on a single page which will be more useful for the client side, and it will take less time taking.