In a relational database, a table is made up of rows of data. Each row can have many columns. Once your data has been ingested and transformed and is ready for consumption, you may need to apply some additional security at the row or column level. Row‐level security (RLS) is very similar to a filter commonly implemented using the WHERE clause. This works fine as long as no one has direct access to the data and can run queries that circumvent this level of projection. In this case, if you need to restrict access on a row level, and you have clients that connect directly to your database, then you need to apply RLS. Implementing RLS requires what is called a FILTER PREDICATE, which is applied using theCREATE SECURITY POLICY statement. Consider a scenario where you have implemented a global brain wave repository that allows anyone to upload their brain wave readings to your datastore for processing and data analytics. Consider taking it one step further and provide the option for those individuals to perform analytics on their scenarios. As the platform owner, you would want to see all readings from all those who have uploaded data; however, you would want to restrict individuals’ access to only their data. RLS is a means for achieving just that. Consider the following SQL statement:
 CREATE SECURITY POLICY BrainwavesFilter_ext
 ADD FILTER PREDICATE Security.fn_securitypredicate(brainwaveProducer)
 ON dbo.Brainwaves_ext
 WITH (STATE = ON);

The statement creates a policy named BrainwavesFilter_ext and adds a predicate based on a value stored on a table named Brainwave_ext. The value in column brainwaveProducer is the user account ID of the person who uploaded brain waves. When CRUD queries are executed against the Brainwave_ext table, the policy uses the predicate to filter, change, remove, insert, and/or deliver the data for the user who uploaded it.

Another feature that you can apply to the data is called column‐level security. Recall from the SUBJECTS table a few columns are worthy of a Confidential sensitivity level. Columns like USERNAME, ZIPCODE, EMAIL, and BIRTHDATE contain data that should not be accessible to the public or generally available. If you have a user in your Azure Synapse Analytics dedicated SQL pool named brainjammer and do not want that user to have access to these columns, you can execute the following command to exclude the columns from the list:

 GRANT SELECT ON SUBJECTS
   (ID, FIRSTNAME, LASTNAME, EMAIL, COUNTRY, CREATE_DATE) TO brainjammer;

If that user then attempts the following SELECT statement, an error would be rendered stating that access is denied:

 SELECT * FROM SUBJECTS

In the Implement Row‐Level and Column‐Level Security section you will perform an exercise and experience column‐level security firsthand.