Forum Discussion
Post ProdigyColumn-level security allows to select all table columns yet
I must admit the documentation is a bit confusing on this topic imo:
"If you'd like to set up a user's granular permissions before allowing them to connect to the warehouse, permissions can first be set up within SQL. Then, they can be given access by assigning them to a Workspace role or granting item permissions." https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions
This seems to imply that sql granular permissions can be used to modify (limit) the T-SQL permission of users with workspace roles. Is that correct? I have had the assumption that workspace roles override sql granular permissions. But that quote from the docs seems to contradict that.
Anyway, I guess the workspace users (except viewer) will be able to access the data using Spark, regardless of SQL permissions.
Also, restricting T-SQL permissions wouldn't restrict the other permissions that belong to workspace roles: https://learn.microsoft.com/en-us/fabric/get-started/roles-workspaces#-workspace-roles
These docs are probably also highly relevant: https://learn.microsoft.com/en-us/fabric/data-warehouse/workspace-roles#workspace-roles
-------
Here is a part of the docs which seems to insist on giving workspace access. I think this is confusing and I believe this is a bug in the docs:
"CREATE USER cannot be explicitly executed currently. When GRANT or DENY is executed, the user is created automatically. The user will not be able to connect until sufficient workspace level rights are given." https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions#limitations
--------
The two main points that confuse me, are:
- Is it possible to restrict SQL permissions, e.g. DENY, RLS or CLS, for workspace users (viewer, contributor, member and admin)?
- Is it possible to restrict SQL permissions, e.g. DENY, RLS or CLS, for users with item level ReadData permission?
I'm hoping the docs can become clearer on this.
------
"In my opinion, it would be better to start with access at the Warehouse level and then GRANT on the relevant ojects/columns." AndyDDC
I think this approach makes very good sense!
I think this is achieved by sharing the warehouse with a user (a user who doesn't have any workspace role), and leaving all Additional Permissions unchecked.
"If no additional permissions are selected - The shared recipient by default receives "Read" permission, which only allows the recipient to connect to the SQL analytics endpoint, the equivalent of CONNECT permissions in SQL Server. The shared recipient won't be able to query any table or view or execute any function or stored procedure unless they're provided access to objects within the Warehouse using T-SQL GRANT statement." https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions#fabric-security-roles
Start at zero, and add T-SQL permissions as required by using T-SQL GRANT statements.
Most Valuable ProfessionalWorkspace roles override item level permissions. Not sure how much more I can add other than this permissions doc that talks about workspace and item level.
the workspace role matrix shows that as a Viewer you can read all data. If you need more granular, use item level and assign permissions to objects
https://learn.microsoft.com/en-us/fabric/security/permission-model
Community ChampionI must admit the documentation is a bit confusing on this topic imo:
"If you'd like to set up a user's granular permissions before allowing them to connect to the warehouse, permissions can first be set up within SQL. Then, they can be given access by assigning them to a Workspace role or granting item permissions." https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions
This seems to imply that sql granular permissions can be used to modify (limit) the T-SQL permission of users with workspace roles. Is that correct? I have had the assumption that workspace roles override sql granular permissions. But that quote from the docs seems to contradict that.
Anyway, I guess the workspace users (except viewer) will be able to access the data using Spark, regardless of SQL permissions.
Also, restricting T-SQL permissions wouldn't restrict the other permissions that belong to workspace roles: https://learn.microsoft.com/en-us/fabric/get-started/roles-workspaces#-workspace-roles
These docs are probably also highly relevant: https://learn.microsoft.com/en-us/fabric/data-warehouse/workspace-roles#workspace-roles
-------
Here is a part of the docs which seems to insist on giving workspace access. I think this is confusing and I believe this is a bug in the docs:
"CREATE USER cannot be explicitly executed currently. When GRANT or DENY is executed, the user is created automatically. The user will not be able to connect until sufficient workspace level rights are given." https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions#limitations
--------
The two main points that confuse me, are:
- Is it possible to restrict SQL permissions, e.g. DENY, RLS or CLS, for workspace users (viewer, contributor, member and admin)?
- Is it possible to restrict SQL permissions, e.g. DENY, RLS or CLS, for users with item level ReadData permission?
I'm hoping the docs can become clearer on this.
------
"In my opinion, it would be better to start with access at the Warehouse level and then GRANT on the relevant ojects/columns." AndyDDC
I think this approach makes very good sense!
I think this is achieved by sharing the warehouse with a user (a user who doesn't have any workspace role), and leaving all Additional Permissions unchecked.
"If no additional permissions are selected - The shared recipient by default receives "Read" permission, which only allows the recipient to connect to the SQL analytics endpoint, the equivalent of CONNECT permissions in SQL Server. The shared recipient won't be able to query any table or view or execute any function or stored procedure unless they're provided access to objects within the Warehouse using T-SQL GRANT statement." https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions#fabric-security-roles
Start at zero, and add T-SQL permissions as required by using T-SQL GRANT statements.
pmscorcaHi, thanks for your reply.
I've solved the issue about the applying CLS to a warehouse in this way:
- first, running a GRANT SELECT ON ... TO ... statement,
- second, sharing the warehouse to the user as a item (so applying the item level security) with only read permission,
in agreement with this
For an higher role it occurs to run a DENY statement, for a lower role it occurs to run a GRANT statement.
Thanks