1. Redshift security
Let's explore a few of Amazon Redshift's security capabilities.
2. Redshift security
From column-level access control to row-level security policies and data masking capabilities which are all part of access control, Redshift offers comprehensive tools to safeguard sensitive data and ensure compliance with regulatory requirements in the audit and encryption domains as well. By understanding these security features effectively, we'll comprehend query outputs affected by different security practices.
3. Column level permissions
Redshift can hide columns entirely from specific users or roles, restricting their visibility and ensuring data confidentiality. This can also cause us some confusion when we don't see the columns we expect. We can verify these permissions using the SVV-COLUMN-PRIVILEGES system view, which provides insights into the privileges granted to different identities for specific columns in our database tables. Here we can see that amelia and anyone with the analytics role can only see the product_name column in the product table.
4. Row-level security
With Redshift's row-level security feature, we can define policies that prefilter data, allowing users to access only the rows that meet specific criteria. This filter enhances data confidentiality and ensures that users only see the information relevant to their roles or permissions within the organization. For example, we have a policy that limits the category of our products table to just 'Dark Academia' as illustrated in the CREATE RLS POLICY statement. If a user with that policy applied queries the table, they will only get rows where the category is just that.
5. Row-level security
The SVV-RLS-POLICY view provides valuable insights into the current policies, including the policy name, column details, and the conditions for filtering data. For example, here is what the policy from the prior slide that limits the category to just dark academia looks like. This view only shows the policy itself, not what it's applied to.
6. Row-level security admin view
As users, we can't see the row-level securities applied; however, it's essential to understand what to ask administrators for when debugging missing query rows. Administrators have a view that shows queries affected by row-level security policies named SVV-RLS-APPLIED-POLICY. Here, we can see that Aashvi had a select query affected by our dark academia books policy, as depicted by the s in the output's command column.
7. Dynamic Masking overview
Dynamic masking is a powerful technique for obscuring sensitive data returned by queries, such as National ID numbers or Credit card information. By applying masking policies, we can ensure that only authorized users can view the original values of sensitive fields. Masking helps mitigate the risk of unauthorized access to sensitive information and enhances data privacy within our Redshift environment. Here, we are querying a customers table with a sensitive field that has a mask applied to protect the full value of the social_security_number.
8. Let's practice!
So, let's dive in and put your newfound knowledge to the test!