What is the SQL Server Security Model
SQL Server line by line access
The SQL Server security model allows you to grant access to a view without granting access to the underlying tables.
Because sample code is a great way to show a concept, here are some things to keep in mind with a table and view:
We create a login and a user and then assign that user the rights to select rows from the view without having rights to view the table itself.
Now let's add two test lines:
This tests the security model. The first statement succeeds because it is selected from view, while the second statement fails because the user does not have direct access to the table.╔══════════╦══════════════╦═══════════════════════ ══╗ ║ Username ║ EmailAddress ║ LastLoggedInAt ║ ╠══════════╬══════════════╬═════════════ ══════════ ══╣ ║ User y ║ [email protected] ║ 02/15/2018 07: 36: 54.490 ║ ╚══════════╩══════ ════════╩═══════════════════════ ══╝
Notice that the results exclude from view the row that the value is on, as required by your question.
The second statement on the underlying table returns an error:
Message 229, Level 14,
Status 5, line 28 The SELECT authorization was denied for the object 'LoginDetails', database 'tempdb', schema 'dbo'.
To clean up:
Alternatively, if you have SQL Server 2016 or later, you can use a row-level security predicate to prevent certain users from seeing rows with a NULL value.
First we create the table, a login, a user for this login and grant access to the table:
Next, let's add some example lines. One row with a zero and one with a non-zero value for this column.
Here we create a schema-bound table-valued function that returns a row with 0 or 1 depending on the value of the variable and the variable passed to the function. This function is used by a filter predicate to remove the rows that we want to hide from certain users.
This is the security filter that removes lines from statements executed on the table:
The above filter uses the function by passing the name of the current user along with the values from each row for the column from the table.
If we query the table as a normal user:
we see all the lines:╔══════════╦══════════════╦═══════════════════════ ══╗ ║ Username ║ EmailAddress ║ LastLoggedInAt ║ ╠══════════╬══════════════╬═════════════ ══════════ ══╣ ║ User x ║ [email protected] ║ NULL ║ ║ User y ║ [email protected] ║ 2018-02-15 13: 53: 42.577 ║ ╚═══ ═══════╩══════════════╩═══════════════════════ ══╝
However, if we test the following:
We only see "valid" lines:╔══════════╦══════════════╦═══════════════════════ ══╗ ║ Username ║ EmailAddress ║ LastLoggedInAt ║ ╠══════════╬══════════════╬═════════════ ══════════ ══╣ ║ User y ║ [email protected] ║ 02/15/2018 13: 42: 02.023 ║ ╚══════════╩══════ ════════╩═══════════════════════ ══╝
And we clean up:
Note that the schema that binds a function to the table in this way makes it impossible to change the definition of the table without first clearing the filter predicate and the function.
- What is the best city car to buy
- What makes German the most precise language
- What is Touchpal 1
- What does DL stand for
- Can you go to college for astrology
- What are the qualifications for a good reporter
- Girls enjoy sex with their partners
- Is carbon monoxide polar If so, why
- What is the Navier Stokes problem
- What are the cons of a child prodigy
- What was Lilith's last name
- How safe is MIT for Muslim students
- Why should taxes be taught in school
- You can have lupus without a rash
- How is the Tamil Nadu Express train
- What is 10 b 9
- Is Pope Francis a communist
- What is the changing side of marketing
- How many space stations are currently active
- Why are people often judged
- What do you mean by parliament?
- Is the University of Pennsylvania overrated
- Quora is an American company
- What are some creative uses of anagrams