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'

Code

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;

Code

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;

Code

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;

Code

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;

Code

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;

Code

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.