SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Grant SELECT on SQL View but Prevent from Underlying Database Tables

It is possible to grant SELECT data from a SQL database view and at the same time prevent queries on underlying database tables for SQL developers. Database programmers and SQL Server database administrators can prevent personal or sensitive data stored in certain table columns and enable other users to execute SQL SELECT queries on the same database tables at the same time. In this SQL tutorial, I want to show the steps of SQL View configuration for SELECT permissions on view level and column level so that while keeping private data safe let other SQL users to query table data using SQL views.

Database application developers can secure data by providing access to it by different SQL users via different database views. SQL views are not just stored queries that can be called repeatedly bu can be a means of providing secure access to data just like database schemas. Fine-grained permissions enable developers to build more secure database applications. In this SQL Server tutorial, let's see how permissions on SQL views and view columns can be used.

Create following SQL table and populate with sample data on your SQL Server database.

create table Customer (
 id int not null identity(1,1),
 fullname nvarchar(200) not null,
 email varchar(256),
 accountno varchar(12),
 accountlimit decimal(17,3),
 country varchar(3)
);
go

insert into Customer select N'Darth Vader','Vader@Sith.Mustafar',500,10000.00,'TR'
insert into Customer select N'Mace Windu','Mace@Jedi.HaruunKal',200,8000.00,'TR'
insert into Customer select N'Palpatine','Mace@Sith.Naboo',666,12000.00,'GER'
insert into Customer select N'Darth Maul','Maul@Sith.Dathomir',800,6000.00,'US'
insert into Customer select N'Padmé Amidala','Padme@Jedi.Naboo',400,7000.00,'GER'
insert into Customer select N'Luke Skywalker','Luke@Jedi.Tatooine',150,13000.00,'TR'
insert into Customer select N'Yoda','Yoda@Jedi.Dagobah',100,11000.00,'TR'

SQL developers can now create SQL view which will query Customer database table and filter unwanted data rows and sensitive columns that they want to hide from the consumers of this database view.

create view CustomersList
as
select
 id, fullname, email, accountno, country -- exclude accountlimit
from Customer
where country <> 'US'

go

grant select on CustomersList to testuser;

If SQL database user "testuser" executes following SELECT query on sample database view, you can see he will be able to view data from CustomersList SQL view.

select * from CustomersList;

As you see below records filtered by WHERE clause are not displayed in the output. Additionally, the column "accountlimit" that is assumed to include sensitive data is not in the select list of the database view.

data from SQL view preventing access to underlying database tables

Let's now check if the underlying table "Customer" can be queried by SQL database user "testuser"

select * from Customer;

The SELECT permission was denied on the object

You will see the SELECT permission is denied. SQL Server engine is raising below exception:
Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'Customer', database 'deleteme', schema 'dbo'.

It is also possible to arrange permissions of test database user using SQL Server Management Studio IDE. The dbo owner user can grant permissions or explicitely deny specific permissions for test database user.

Right click on SQL database view and choose "Properties" on context menu

SQL Server database view properties

On View Properties window switch to "Permissions" tab

Select the database user that you want to configure its permissions on the SQL view.

select database user to configure permissions on SQL view

After the database user is selected, in section at the lower part of the screen it is possible to explicitely grant or deny certain permissions by just marking checkboxes easily.

If "SELECT" permission is granted to the user, that specific database user will be able to query SQL view and display every column of the database view.

grant select permission on SQL view for database user

It is also possible to limit the user permission further and explicitely deny some columns of the SQL view.
For example, in below configuration screen which will be opened when "Column Permissions..." button is clicked when SELECT permission is highlighted in the list.

grant or deny column level SQL Select permission for database views

By marking the checkboxes on "Grant" column of the SQL view columns we ensure that the target user is able to view and query that column data.

The owner of the SQL view can exclude some columns with sensitive data by marking the Deny column check. This configuration will prevent database user to display or select that column data in his/her queries.

For example, now the dbo has enabled SELECT on following columns "id, fullname, accountno, country" of SQL view "CustomersList"
The column name "email" is explicitely excluded from the SELECT granted columns and SELECT is denied

Following SELECT SQL statement executes successfully since the user is quering the columns that he/she is granted to SELECT

select id, fullname, accountno, country from CustomersList;

But the below SQL queries will fail with the same error message:
"The SELECT permission was denied on the column 'email' of the object 'CustomersList', database 'kodyaz', schema 'dbo'."

select email from CustomersList;
select * from CustomersList;

The reason of the error is because the email column can not be displayed by the executing user since the email column DENY option is checked for SELECT permission.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems