Share via

Column level security not working in Synapse Analytics

Neil Pereira 81 Reputation points
2020-09-11T17:32:43.54+00:00

Hi Team,

I tried to implement column level security on a table in synapse analytics by skipping one column that shouldn't be viewed by a specific user,
but still this user is able to view this column from the table.

The query I used to implement this is: GRANT SELECT ON Table (column1, column2) TO User;

Some extra background:
This user is created in the current DB and has his login created in master DB.
The login is SQL Authentication.

Any idea what is the work around?

Thanks,
Neil.

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,687 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments
Accepted answer
  1. David Browne - msft 3,771 Reputation points
    2020-09-12T13:38:54.417+00:00

    As @Erland Sommarskog says, the user is getting the permissions from somewhere else. This is a scenario where you should use DENY, which overrides any GRANTs the user might have. eg 

    deny select on SomeTable (secretColumn) to SomeUser

    And it's simpler because you only have to list the columns you want to prevent the user from seeing.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107K Reputation points
    2020-09-12T12:27:52.517+00:00

    First of all, I don't use Synapse or Azure Data Warehouse, so this answer is based on general experience of the regular SQL Server database engine.

    How is the user accessing the table? Directly, or through a view or a stored procedures.

    Generally, when things like this happens, the user typically has permission from somewhere else. That could be membership in a role, or it could be that you have granted permission on the entire table to public.

  2. Erland Sommarskog 107K Reputation points
    2020-09-14T21:43:45.693+00:00

    If the user is in db_datareader, this explains why the user sees the column. Adding a user to db_datareader means that you grant that user right SELECT permission on any table in the database. And all columns on all tables.

    If you want to keep the user in db_datareader, you will need to use DENY as suggested by David.

    But you may also having second thoughts of adding users to db_datareader. You can also grant SELECT permissions on schema level, and stick sensitive information in separate schemas. Obviously, this would require to put the column in this case in a separate table. Or move the entire table to a separate schema, and add a view to the dbo schema that only exposes the public columns.

    I like to point out that the ideas I float in the previous section are not necessarily your best choices. I am just presenting some alternatives. It could be that DENY is the best for you, but I have given you a caution.