Data Access Policies in Snowflake

Data Breaches in the Healthcare Sector

In 2018, Singapore witnessed its most severe cyber-attack to date when SingHealth fell victim to a data breach. The attack resulted in the theft of sensitive data from 1.5 million patients, including outpatient prescriptions belonging to prominent political figures. It seems that this is a common occurrence in the Healthcare industry as Personal Health Information (PHI) is more valuable on the black market than regular Personally Identifiable Information (PII), giving criminals more incentive to target medical databases.

The vulnerability of healthcare systems to attacks could be attributed to a multitude of factors such as legacy infrastructure and hardware, shortage of security talent and employee error. Due to the high impact and critical nature of healthcare services, it is imperative that healthcare organisations take necessary measures to arm themselves against these attacks. One powerful protective measure suggested by industry experts is micro-segmentation which is a principle of least privilege and zero-trust; allowing employees access only to data that is necessary for them to do their job.

Data Governance with Snowflake

Micro-segmentation can be easily and efficiently applied to highly sensitive PHI and PII on platforms such as Snowflake. Through robust Data Access Policies, which make up a pivotal component of Snowflake’s Data Governance features, businesses can ensure secure, compliant and controlled data management throughout the entire data lifecycle. Snowflake’s Data Access Policies include:

Column-Level Security: Allows the application of a masking policy to a column within a table or view.

Row-Level Security: Allows the application of a row access policy to a table or view to determine which rows are visible in the query result.

This makes up a powerful duo of enhancing data security, limiting access to highly sensitive data only to authorised personnel in the organisation. For example, even though all healthcare employees obtain information from the same database, the data that they are allowed to have access to differs based on the role of the employee. 

Security teams can establish data governance policies on securable objects*, like databases, schemas and tables, which are data resources in Snowflake. These policies help control sensitive data exposure, ensuring that even the owners of these objects have limited access to sensitive information.  This ensures that in the case of an attack, the attacker does not immediately have access to all data.


*Objects in Snowflake 

Objects, also known as securable objects, in Snowflake refer to various data resources, such as databases, schemas or tables which can be controlled and secured using Snowflake’s security features like Role-Based Access Control (RBAC) and privileges.

Roles and Privileges in Snowflake

In Snowflake, a role is an entity to which privileges can be granted, while privileges define the level of access each role has to an object (e.g., Database). Roles are then granted to users. For example, hospital employees with the role of administrative staff will be granted privileges to access a database containing patient information as well as privileges to perform SELECT operations which allows them to retrieve information about patients. Learn more about role structures and hierarchies in Snowflake here.

Managing Security Policy Privileges

In Snowflake, the implementation of security policies is highly versatile, allowing organisations to apply them through a myriad of methods, so they can tailor a solution that best suits their unique needs. Snowflake offers a comprehensive set of security features, enabling businesses to establish robust data protection measures that align with their specific requirements and regulatory obligations.

Centralised Management

In a centralised management approach, the responsibility for creating and applying masking policies to columns in tables or views is solely entrusted to the security officer’s custom role; which is often denoted as ‘security officer’ or ‘privacy officer’. This deliberate delegation of authority to a dedicated security officer ensures the most consistent and stringent management of masking policies, particularly when it comes to protecting sensitive data.

Hybrid Management

In a hybrid management approach, an effective collaboration between the security officer’s custom role (e.g., security officer) and individual teams (e.g., finance, payroll, human resources) is established to implement data masking policies within the organisation. In this model, the security officer role takes on the crucial responsibility of creating masking policies, while individual teams are entrusted with applying these policies to columns in tables or views that they own.

Decentralised Approach

In a decentralised management approach, responsibility for creating and implementing masking policies is distributed among individual teams, leading to a potential lack of consistency in policy management. With each team assuming accountability for masking sensitive data in their respective tables or views, there is a risk of improper or inconsistent application of masking policies, which may result in inadequate protection of sensitive information.

The following examples showcase the utilisation of a custom role named ‘Privacy Officer’ to establish and enforce diverse column and row-level policies. This ensures enhanced protection of patient information, also known as the centralised approach.

Implementing Data Access Policies with Snowflake

Column Level Security

Dynamic Data Masking is a column-level security feature that uses masking policies to selectively mask plain-text data in columns, within tables and views, according to the role of the user. This means only users with authorised access will be able to access the full view of sensitive data; while unauthorised users will see data masked to varying degrees as follows:   

Masked Data: Data Masking is applied to columns in tables to replace sensitive values with a masking format, such as asterisks or random characters to protect sensitive information.

Partially Masked Data: Partially masked data is a subset of masked data where specific portions of sensitive information are concealed while other parts remain visible.

Tokenized Data: External tokenization removes sensitive by replacing it with an undecipherable token before loading it into Snowflake and detokenizing the data at query runtime. External Tokenization makes use of masking policies with external functions.

Snowflake supports creating masking policies using the statement CREATE MASKING POLICY. For example:

Masking Policy One | Address Masking

Masking Policy One grants full access to columns containing information about patients’ address and postal code to users with an ‘Administrative Role’ as they are responsible for contacting and mailing patients while the rest of the roles have obfuscated or partial access only to such personal information.

Masking Policy Two | Postal Code Masking

Masking Policy Two grants full access to columns containing information about patients’ health records such as patients’ complaints and symptoms, prescriptions and lab results to doctors and nurses. The rest of the roles have obfuscated access only to health-related information.

Once the policies have been created, they can be applied to respective columns in the tables.

Once the masking policies have been applied, the columns will only be fully or partially viewable to authorised roles.

As health professionals, doctors and nurses will be able to view all health-related information and some personal information about the patients. 

As the point of contact between the hospital and the patients, administrative staff will be able to view all contact information and no health-related information about the patients.

Patient records may also need to be viewed by third-party personnel such as insurance companies and agents who are responsible for filing insurance claims for their clients and checking the eligibility of claims. In those cases, it is vital that the agents only have access to information necessary for filing claims. For example, the patient’s diagnosis may be granted to them.

Masking Policy Three | Third-Party Access Masking

Masking Policy Three grants full access to columns containing information about patients’ diagnoses to doctors, nurses and third-party roles such as insurance agents. Insurance personnel who are helping to file claims on behalf of patients will gain access to the required information while the rest of the roles have obfuscated access only.

Row Level Security

It is imperative that third parties such as insurance companies only have access to records of patients for whom they are responsible for making claims. Access to records of any other patients, partial or full, should be strictly prohibited.

To implement this, Snowflake supports row level security, using row access policies to determine which rows to return in the query result using Conditional Expression Functions and Context Functions.

The following statement creates a row policy that grants doctors, nurses and administrative staff access to all patient records while third parties such as insurance agents only have access to records of patients who are simultaneously their clients for making claims.

As a combined result of the column and row level access policies, insurance agents will only be able to view selected records displaying some health information and some personal information of the patients.

The implementation of these security policies not only enhances data privacy and protection but also facilitates compliance with data privacy regulations, such as PDPA. Organisations can ensure that they are adhering to legal requirements by controlling data access and implementing security measures at both column and row levels.

Other components of Data Governance with Snowflake

In addition to column and row level security, Snowflake offers a comprehensive suite of data governance features. This provides organisations with the control and visibility needed to meet regulatory requirements and maintain data confidentiality, integrity and availability throughout their data lifecycle.

Access Control Framework: Snowflake’s approach to access controls combines aspects from:

  • Discretionary Access Control (DAC): Each object has an owner who can grant access to that object.
  • Role-based Access Control (RBAC): Access privileges are assigned to roles, which are assigned to users.

Time Travel & Fail-Safe: Snowflake’s Time Travel feature enables users to access historical versions of data, allowing for easy data recovery and auditability. Additionally, its Fail-Safe capability automatically preserves data for seven days, ensuring data integrity even in the event of accidental deletions or errors.

Zero-Copy Cloning: With Snowflake’s Zero-Copy Cloning, organisations can create clones of entire databases or specific data sets without duplicating the data. This capability significantly reduces storage costs and improves data governance efficiency.

Auditing and Tracking: Snowflake’s built-in auditing and logging capabilities enable organisations to track data access and changes effectively. This ensures transparency and accountability, making it easier to investigate any data-related incidents or anomalies.


In a world where data breaches pose significant risks, robust data security measures are paramount. With Snowflake’s powerful data governance capabilities, including data access policies, organisations can fortify their defenses and safeguard sensitive data against evolving cyber threats. Contact us today to unlock the power of Snowflake’s data security solutions to stay ahead of the curve and keep your data safe!