question

SenemAkgn-3137 avatar image
0 Votes"
SenemAkgn-3137 asked OlafHelper-2800 answered

SQL Server Table Partitioning Authorization

Can partition-based user privilege be given? Is partition-based user authorization possible? Can we do partition-based authorization in SQL Server, not table-based?

For example; You define 1 table to consist of 5 partitions. Then we want to define different user privileges for these partitions. Is this possible?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered TomPhillips-1744 commented

No, that's not possible.
Partitioning is a mechanism to handle storage, has nothing with permissions to do.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the answer. Actually, I do country-based partitioning. I don't want the countries I partitioned to see each other's data.For example, we have 5 countries, we have 5 partitions. I'm talking about defining user privilege to give Russia partition read permission for Russia.

0 Votes 0 ·

What you are referring too is called "Row Level Security" It really has nothing to do with partitioning. Partitioning is a storage mechanism.

In your example, I would HIGHLY suggest you do NOT partition. You are likely gaining nothing but headache by trying to partition on country.

Please see:
https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @SenemAkgn-3137,

Welcome to Microsoft Q&A!

No, it's not support. Table Partition is the logical division of information with the physical distribution in the filegroups. A table can be partitioned by applying the partition schema over the table schema. The role of the partition function is to divide the information in a logical division by partition range and partition scheme indexing the partition range to the filegroup. Please refer to How to automate Table Partitioning in SQL Server.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the answers. Actually, I do country-based partitioning. I don't want the countries I partitioned to see each other's data. For example, we have 5 countries, we have 5 partitions. I'm talking about defining user privilege to give Russia partition read permission for Russia.

0 Votes 0 ·

Hi @SenemAkgn-3137, after doing some research, good to know that you have been get a workaround. Post the link here in case someone have similar question: https://stackoverflow.com/questions/68113638/sql-server-table-partitioning-authorization

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

I'm talking about defining user privilege to give Russia partition read permission for Russia.

If you have dedicated data per user group then why do you want to have all in one table? Implementing Row-Level-Security isn't that easy.

Create schema per user group and create identically tables in the schema; as super-user you can create a view querying all data from all tables in the schemas.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.