0

I understand Column level security works on Table in Synapse Data warehouse but I am looking for applying Column level security to External table which references data from Azure Data Lake Gen 1.

I am unable to find details relating to External tables, reply if any one tried this option.

Kalyan Chanumolu-MSFT
  • 1,063
  • 6
  • 12
Venk AV
  • 67
  • 1
  • 10

2 Answers2

0

Azure Data Lake Store service only knows about files and folders and the authorization in the store is ACLs at the file and folder level only.

Azure Data Lake Storage Gen1 is a hierarchical file system like Hadoop Distributed File System (HDFS), and it supports POSIX ACLs. It controls read (r), write (w), and execute (x) permissions to resources for the Owner role, for the Owners group, and for other users and groups. In Data Lake Storage Gen1, ACLs can be enabled on the root folder, on subfolders, and on individual files.

For more information on how ACLs work in context of Data Lake Storage Gen1, see Access control in Data Lake Storage Gen1.

Column Level Security applies to the following services.

  • SQL Server (all supported versions)
  • Azure SQL Database
  • Azure SQL Managed Instance
  • Azure Synapse Analytics Data Warehouse
  • Parallel Data Warehouse

Reference: Azure Synapse Analytics - Column-level Security

CHEEKATLAPRADEEP
  • 12,191
  • 1
  • 19
  • 42
0

Column Level Security is not possible for External Tables. But there is a workaround to achieve this.

A Logical view can be created referencing External Tables and then apply Column Level DENY Statement to see it working. I have verified the same.

Venk AV
  • 67
  • 1
  • 10
  • Glad to know that your issue has resolved. You can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in). This can be beneficial to other community members. Thank you. – CHEEKATLAPRADEEP Nov 06 '20 at 04:20