SQL VIEWS and Security
What View do ?
• First, It help you break down complex queries.
• Secong, Query reusability.
• Third, Security.(not fully recommendable)
What is View ?
View is just a stored query. Can also be said as virtual
table. They don't hold any data into them.Data comes from
real tables.SQL select query operator (WHERE, ORDER BY, etc)
can be used with it.
Script:
File 1:
--GENERAL QUERY
SELECT Ord.OrderID,Ord.CustomerID,Ord.ShipAddress,Ord.ShipCity,Ord.ShipCountry,Ord.ShipName
, Ord.Freight,Ord_d.ProductID,Ord_d.Quantity,Ord_d.UnitPrice FROM Orders Ord inner join
[Order Details] Ord_d
on Ord.OrderID=Ord_d.OrderID inner join Customers Cust
on Cust.CustomerID=Ord.CustomerID
--Creating VIEW from query
CREATE VIEW Order_Data
as
SELECT Ord.OrderID,Ord.CustomerID,Ord.ShipAddress,Ord.ShipCity,Ord.ShipCountry,Ord.ShipName
, Ord.Freight,Ord_d.ProductID,Ord_d.Quantity,Ord_d.UnitPrice FROM Orders Ord inner join
[Order Details] Ord_d
on Ord.OrderID=Ord_d.OrderID inner join Customers Cust
on Cust.CustomerID=Ord.CustomerID
--Selecting from VIEW
select * from Order_Data
--JOIN using VIEW
select ord.*,prod.ProductName,prod.CategoryID from Order_Data ord inner join Products prod
on ord.ProductID=prod.ProductID
--Creating VIEW from query above
CREATE VIEW Order_Data_With_Product_Name
as
select ord.*,prod.ProductName,prod.CategoryID from Order_Data ord inner join Products prod
on ord.ProductID=prod.ProductID
--Queriing VIEW
select * from Order_Data_With_Product_Name
--JOIN using VIEW
select ord.*,cat.CategoryName from Order_Data_With_Product_Name ord inner join Categories cat
on ord.CategoryID=cat.CategoryID
--Creating VIEW from query above
ALTER VIEW Order_Data_With_Product_and_Category
as
select ord.*,cat.CategoryName from Order_Data_With_Product_Name ord inner join Categories cat
on ord.CategoryID=cat.CategoryID
--Using other operator
Select * from Order_Data_With_Product_and_Category
Select cust.CompanyName,SUM(freight) [Sum Total] FROM Order_Data_With_Product_and_Category ord
inner join Customers cust
on cust.CustomerID=ord.CustomerID group by cust.CompanyName
order by cust.CompanyName
File 2:
-- Security context
--Query from one table
Select * from Categories
--VIEW from above query
CREATE VIEW Category_View
as
Select * from Categories
--Querying view
select * from Category_View
--Updating using VIEW
UPDATE Category_View set categoryname='Beverages' WHERE categoryid=1
--Inerting using VIEw
insert into Category_View values('Test Cat','test',null)
select * from Category_View order by categoryid desc
delete from Category_View where categoryid=9
--Securing Column Identity
--Query
select CategoryID [ID],CategoryName [Name] from Categories
--creating VIEW
Create VIEW Cat_View_Identity
as
select CategoryID [ID],CategoryName [Name] from Categories
select * from Cat_View_Identity
--updating with unreal identity
update Cat_View_Identity set name='Beverages' where ID=1
--Query with join
select CategoryName,ProductName from Categories cat inner join Products prod
on prod.CategoryID=cat.CategoryID
--creating VIEW from query above
CREATE VIEW Cat_and_Prod
as
select CategoryName [cname],ProductName [pname] from Categories cat inner join Products prod
on prod.CategoryID=cat.CategoryID
select * from Cat_and_Prod
--try updating using view
update Cat_and_Prod set [pname]='test' where [cname]='Beverages'
--VIEW Query
select * from Order_Data_With_Product_Name
update Order_Data_With_Product_Name set shipcity =shipcity + 'U',freight=0,productname='prd' where customerid='VINET'
