Hello friends, today I am going to talk about one of my
favorite topic “Views” in SQL Server.
This topic is one of my favorite topics. Whenever I ask somebody why we need
Views in SQL Server, I never get a satisfactory answer. Hence I thought to
share my thoughts about this topic. Let’s see the definition of View first then we
learn why we need it.
What is a View: - A
View in SQL Server can be defined as a logical/virtual table structure whose content
(rows and columns) is defined by a select statement of the View.
Need of View: - It
is important to understand the need of Views, below are the points which define
why a View is important.
Security Mechanism: -
The most important fact of existence of a View is, its Security Mechanism. To
understand the security mechanism of a View, let’s take an example. I have
client’s financial database with me and I do not want to reveal all the
sensitive information of their employees (in
Employee table) to all of the developers in my development team. What would I do?
Solution :- I will create a View on Employee table with only limited set of columns(which are not
confidential) of "Employee" table and
create a new login and grant permission to access only the View not the "Employee" table. To understand this lets
take an example.
Create table Employee(Eid int,Name varchar(50),Basic_Sal int,HRA int)
Insert into Employee values(101,'Neeraj',50000,10000)
Insert into Employee values(102,'Ram',30000,5000)
Insert into Employee values(103,'Rachit',24000,4900)
Insert into Employee values(104,'Akshay',49000,23000)
Insert into Employee values(105,'Ankit',38000,4300)
Let’s create a View using Below SQL Statement (Syntax of
Creating a Simple View):-
Create view vw_Employeeà [Create View followed by View Name]
as
select Eid,Name from Employee à[Statement defining the View]
Let’s create a Login name “User” by following below steps:-
We will login to SQL Server using “Sa” credential or “Windows
Authentication” as these two credentials are having full access to the
Server. Then right click on the login folder and click on “New Login” menu as shown
in the below screenshot.
Once we click on the “New Login” Menu, a New screen will
appear as shown in the below screenshot.
In the above
screenshot, there are five circles. In first circle, I have put the name of the Login
“User”.In the
second circle, I have selected the “SQL Server authentication” mode. In third
circle, I have set the password and confirmed the password. In fourth circle, I
have unchecked the “Enforced password policy”.
Click on the Menu “User Mapping” (fifth circle) that will
bring a new Screen as shown in the below screenshot.
Check all the "checkboxes" (encircled by blue color) to give
access of the all databases and then check the "checkbox" db_owner encircled by red
color.Click on the “OK” button which is at the bottom right corner.Since we have made the Login (User) as “Db_owner”, we will
be able to access any object and can do any operation (Select,Insert,Update,Delete)
on any object. Now we will revoke all the rights from the "Employee" table for
login(User) by following below steps.
Right click on “Employee” tableàProperties as shown in the
below screenshot.
Once we click on “properties” Menu, this action will bring a
new screen to us as shown in the below screenshot.
Click on the “permissions” Menu, which will bring a new look
to the current screen as shown in the below screenshot.
Click on “Search” button(encircled by red color), which will
bring a new screen as shown below.
We will put the login name “User” in the text field and
click on “Check names” button to verify the login name entered in the text
field is correct and then press “OK” button. Once we click on the “OK” button, the current screen will be closed and we will be on the previous screen.
In the permission section for the user, we can choose which
permission we want to give the user and which we do not. For convenience purpose,
I have checked all the checkboxes of “Deny” column, to revoke all the
permissions from the Login/User on “Employee” table. Finally click “OK”. Now
lets check if the permission is revoked for table “Employee”. To check this, we
need to login to the SQL using “User” login.
In the above screenshot, we can see that, I am logged in to
the SQL Server using “User” login and I have run the select query on table “Employee”
which is throwing an error message stating that “The select permission was
denied on the object Employee, Database Demo, schema, dbo”.
Since the Login(User) is a db owner, It can access all the
objects, except “Employee” table as we have explicitly removed all the rights
from “Employee” table. Lets try to access the View.
Even though we do not have access to the base table “Employee”, we
are able to access the data of the employee table using View.This is the most important feature of View which is ignored
by most of us.
Conclusion: - Views are used to apply row level or column
level security on the base tables. The example shown above was related to
column level security. Similarly we can apply row level security by selecting
the specific no of rows of table in a View.
I will explain rest of features of View in my next article.
Please feel free to ask any question related to this topic and share your
feedback on this topic.
