In this post, we’re focusing on replacing stored procedures with policy as code for databases. We’ll take a look at:
- A simple use case for stored procedures
- The changes that are required to existing application code to implement a new stored procedure
- How Cyral can help reduce complexity by implementing policy as code in place of these stored procedures
As we look at stored procedures and how Cyral can address them with policy as code, we’ll focus on stored procedures that are being used to:
- Help control access to data
- Make use of parameters to determine what data users are allowed to see
While this isn’t the only use case for using Cyral, we can focus on these specific use cases to show where Cyral can be used to help reduce complexities and ensure proper enforcement of data governance policies.
What Are Stored Procedures?
Before we go any further, it is important to understand the definition of a stored procedure. W3Schools defines a stored procedure as “a prepared SQL code that you can save, so the code can be reused over and over again”.
Let’s look at a simple example of a stored procedure that will return the number 1 when called.
DELIMITER //
CREATE PROCEDURE return_one()
BEGIN
SELECT 1;
END //
DELIMITER ;We can then call this procedure with any application that connects to the database.
CALL return_one()
1|
-+
1|Practical Example of a Stored Procedure
In order to continue looking at stored procedures and moving into policy as code for databases, we’ll use a sample database for better reference. Consider the below sample table called patients from our telesales database.
| id | first_name | last_name | address1 | city | state_province | postal_code | country |
| 1 | Franca | Rentsch | 6131 Breslauer Straße | Medebach | Nordrhein-Westfalen | 45117 | Germany |
| 2 | Mathéo | Dufour | 6027 Esplanade du 9 Novembre 1989 | Bordeaux | Lot | 46888 | France |
| 3 | Caroline | Wagner | 5212 Brick Kiln Road | St Albans | West Midlands | M4Y 4PD | United Kingdom |
| 4 | Archer | Wright | 8991 Northcote Road | Invercargill | Taranaki | 20167 | New Zealand |
| 5 | Hiske | Scheepens | 8712 De Broekhuizen | Merslo | Limburg | 43849 | Netherlands |
| 6 | Ilija | Meinhardt | 419 Burgstraße | Uhingen | Sachsen | 12035 | Germany |
| 7 | Braxton | Davies | 1115 Devon Street | Napier | Southland | 13983 | New Zealand |
| 8 | Eugene | Barrett | 4936 Preston Rd | Davenport | Kentucky | 56893 | United States |
| 9 | Bastian | Niehoff | 1242 Mühlenstraße | Herzberg am Harz | Bayern | 27523 | Germany |
| 10 | Blake | Johnson | 2915 Brock Rd | Shelbourne | Northwest Territories | U0T 0X4 | Canada |
A simple example of a stored procedure that retrieves all data from this table would be defined like the below get_patients procedure:
DELIMITER //
CREATE PROCEDURE get_patients()
BEGIN
SELECT id,first_name,last_name,address1,city,state_province,postal_code,country from telesales.patients;
END //
DELIMITER ;Once again, we can call this procedure to get the results of the procedure’s query.
call get_patients()
id |first_name |last_name |address1 |city |state_province |postal_code|country |
---+-----------+----------+------------------+-------------+------------+-------+--------------+
1|Franca |Rentsch |6131 Breslauer Straße |Medebach |Nordrhein-Westfalen |45117 |Germany |
2|Mathéo |Dufour |6027 Esplanade du 9 Novembre 1989|Bordeaux |Lot |46888 |France |
3|Caroline |Wagner |5212 Brick Kiln Road |St Albans |West Midlands |M4Y 4PD |United Kingdom|
4|Archer |Wright |8991 Northcote Road |Invercargill |Taranaki |20167 |New Zealand |
5|Hiske |Scheepens |8712 De Broekhuizen |Merslo |Limburg |43849 |Netherlands |
6|Ilija |Meinhardt |419 Burgstraße |Uhingen |Sachsen |12035 |Germany |
7|Braxton |Davies |1115 Devon Street |Napier |Southland |13983 |New Zealand |
8|Eugene |Barrett |4936 Preston Rd |Davenport |Kentucky |56893 |United States |
9|Bastian |Niehoff |1242 Mühlenstraße |Herzberg am Harz |Bayern |27523 |Germany |
10|Blake |Johnson |2915 Brock Rd |Shelbourne |Northwest Territories |U0T 0X4 |Canada |This looks great because we have a procedure that can be updated as our table changes and the needs of the application grows. This can eventually lead to some problems and overhead associated with using a stored procedure.
Extending the Functionality of our Stored Procedure
This stored procedure is rather useful but we don’t want it to return all data every time the procedure is called. We’d like to be able to only return specific countries based upon a parameter supplied by the caller. An example of this update to the stored procedure looks like the following:
DELIMITER //
CREATE PROCEDURE get_patients(IN countryName VARCHAR(255))
BEGIN
SELECT id,first_name,last_name,address1,city,state_province,postal_code,country from telesales.patients where country = countryName;
END //
DELIMITER ;With this updated procedure in place, we can call the procedure with the given country like the below example:
call get_patients('Germany')
id |first_name |last_name |address1 |city |state_province |postal_code|country |
---+-----------+----------+------------------+-------------+------------+-------+--------------+
1|Franca |Rentsch |6131 Breslauer Straße |Medebach |Nordrhein-Westfalen |45117 |Germany |
6|Ilija |Meinhardt |419 Burgstraße |Uhingen |Sachsen |12035 |Germany |
9|Bastian |Niehoff |1242 Mühlenstraße |Herzberg am Harz |Bayern |27523 |Germany |This does help solve the problem of returning all rows in the table. Clients are unable to return all rows from the table in a single query. A problem that still exists is that these parameters are provided by the end user or the application. Clients can simply call the procedure iteratively with different countries as the parameter. This means that we could still have an underlying data exfiltration issue.
The Challenges of Using Stored Procedures
Now that we have a stored procedure created, we can look at the various challenges associated with using the stored procedures.
Adapting them to changing business logic needs is challenging
As the organization’s needs change due to security maturity or adherence to new compliance and regulatory policies, these procedures will need to be updated. These changes could require developers to update their code to call different stored procedures. Access to data may not be fully understood or known. As the changes are implemented, applications could lose access to data that was unintentional.
Difficult to connect with logic in other systems
With a stored procedure that can accept parameters, we can limit the results returned to whatever calls the procedure. We still have the problem of users in different groups across the organization needing different access to the data. Multiple stored procedures could be created to handle this. Database administrators could choose to create views to help restrict access. As requirements, users, and roles change, this could lead to a complex mesh of procedures and views that become tedious to maintain.
Stored Procedures can be exploited for exfiltration
As mentioned in the above section, as we extended the logic of our stored procedure by accepting parameters, we were able to reduce the amount of data that is returned to the caller. While this does prevent the entire table from being returned in a single query, callers could still exfiltrate data by simply calling the function multiple times to obtain all of the data from the table.
Lack of uniformity across databases
Most companies leverage multiple database platforms for their data. The examples used above are from a MySQL database. What if we also have data in PostgreSQL or additional data that is replicated to a data lake like Snowflake? This could present issues for database administrators having to maintain multiple stored procedures across different database platforms. Worse yet, the stored procedures might not support the same capability across each platform.
Solutions by Moving to a Policy as Code for Databases Model
Cyral can help simplify these challenges using policy as code for databases rather than having to rely on complexities that can grow out of control. We can also help to leverage resources across multiple teams in the organization so that the responsibility for implementation and upkeep doesn’t fall solely on database administrators.
Policies Can be Assigned to Labels and Tags and Updated via DevOps Integrations
Cyral makes use of Data Labels and Tags to help identify data within databases. These labels allow compliance and security teams to connect data types defined in organizational, compliance, and regulatory policies to database schemas, tables, and columns.
By making this connection, we are helping to abstract away the physical location of data understood by database administrators so that they can be handled in terms understood by security and compliance teams.
These labels and tags are then used as the foundation for policies defined within the Cyral ecosystem. With policies configured against labels and tags, DevOps teams only ever need to label and tag data as databases are added or changed. With the labels and tags in place, the policies are enforced in accordance with the policy assigned to those labels and tags.
Using our example data above, we could create the following labels with their associated location like the below table:
| Cyral Label | Cyral Tag | Database Location |
|---|---|---|
| ADDRESS | PII | telesales.patients.address1 |
| ZIPCODE | PRIVATE | telesales.patients.postal_code |
| COUNTRY | PRIVATE | telesales.patients.country |
| FIRST_NAME | PII | telesales.patients.first_name |
| LAST_NAME | PII | telesales.patients.last_name |
Policies that can Leverage Integrations with IDP and that Support Multiple Criteria
Cyral can integrate with a number of different identity providers (IDPs) to help leverage existing roles and groups defined with the organization’s identity provider. These existing users, groups, and roles can be added to Cyral policies to help reduce the complexities of managing user access to data.
Using our previous example table above, a policy can be configured that checks the IDP group that a particular user is a member of to determine access. As user roles change within the organization and their group memberships are updated in the IDP, the policies will also respond differently.
| IDP Group | Access Granted |
|---|---|
| Data Scientists | read-only |
| Data Owners | read-only, update |
| Administrators | read-only, update, delete |
As a user’s role changes in the organization from a member of Data Scientists to a member of Data Owners, their access grants will also change. No updates to the policies or databases would be required. Once the IAM administrator changes the user’s group member from Data Scientists to Data Owners, the user will receive read-only and update access to the data.
Complex Policies Implemented in Simple to Understand Logic
You further extend the security capabilities of these policies by implementing additional features like masking, row limiting, rate limiting, and dataset rewriting. These features can be combined into a single policy to ensure that the appropriate level of access as defined by organizational policies is being granted to users.
You can enforce a policy that follows the logic defined in the statement below.
Policies Determine User Access to Data Rather than Relying on User Provided Parameters
Since the data labels, tags, and IDP group memberships are configured by system administrators, we are no longer relying upon user provided parameters to a stored procedure. Access is not determined by these parameters provided by users but rather by the characteristics of the policies. This can help prevent data exfiltration as users can be restricted based upon their organizational roles and the data they are attempting to access.
Single Policies Implemented Within Cyral and Enforced Equally on Multiple Database Platforms
Cyral’s policies are enforced by the Cyral sidecar and not at the database. This allows for a single policy to be defined and enforced uniformly across different database types. This helps to eliminate the challenges of maintaining different procedures and views in multiple database platforms. Instead, you only maintain labels for those databases and can know that the policies will be applied uniformly.
