TechSapphire Tuesday, 2018-07-17, 2:40 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • 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'

     

    Click to download script

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018