# 2021-02-01 SQL Training Day 3

## Views
在建 View 的時候，用的物件都必須存在，被儲存的是物件，而不是語法

create view vw
as
  select * from t -- 不應該用 select *
go
alter table t add c2 int
select * from vw -- c2 不會出現
exec sp_refreshview 'vw'
select * from vw

資料凍結的時候建 indexed view 可加快查詢/報表執行速度，適合用在 BI

sys
information_schema -- 跨不同 DB 種類存取時，要有一致的 DB 架構資訊用的

view 可直接 alter，不需要 drop 再 create(避免安全設定被刪除)

create or alter

WITH SCHEMABINDING -- 下層的 schema 就不能動了

create or alter view vw
    with SCHEMABINDING
as
    select c1,c2 from dbo.t
    where c1>10
go    

WITH CHECK option -- 不符合 view 的條件不能操作

create table t(c1 int, c2 int)
go
create or alter view vw
as
    select c1,c2 from dbo.t
    where c1>10
    with check point
go

insert t values (8, 10)

### Ownership Chains and Views

create user u without login
create user v without login
drop table if exists t
create table t(c1 int, c2 int)
go
create or alter view vw
as
    select c1 from dbo.t
go    
grant select on vw to u
insert t values(1,1)
exec('select user.* from vw') as user = 'u'
exec('select user.* from t') as user = 'u'
alter authorization on t to v
exec('select user.* from vw') as user = 'u'
exec('grant select on t to u') as user = 'v'
exec('select user.* from vw') as user = 'u'

## Stored Procedures

declare @sql='select * from northwind.dbo.customers where countyr =''' + 'usa' + ''''
exec (@sql) --->此寫法不佳 (會有 sql injection)

declare @sql nvarchar(max) = 'select * from northwind.dbo.customers where countyr=@country'
declare @sql2 nvarchar(max) = 'exec northwind.dbo.sp_executesql N''' + @sql + ''' ,N''@countyr nvarchar(50)'' ,''usa'''
exec(@sql2) --->較佳寫法

create or alter proc sp1
as 
    begin tran
        select @@TRANCOUNT
        exec sp2
        select @@TRANCOUNT
    commit tran -- @@TRANCOUNT - 1
go
create or alter proc sp2
as 
    begin tran
        select @@TRANCOUNT
    rollback tran -- @@TRANCOUNT 歸零
    begin tran 
go
exec sp1
--->會有兩個錯誤

set nocount on -- (8 rows affected) 這種回應就不會出現

子 context 的設定不影響父 context
跑 SP 的時候會建立子 context，跑完就會結束此子 context

declare @p2 int=111,@p33 int,@ret int
exec @ret=sp default,@p2,@p3 output
select @ret,@p3 -->by position

declare @p22 int=111,@p33 int,@ret int
exec @ret=sp @p3=@p33 output,@p2=@p22
select @ret,@p33 -->by name

加密前資料：應在 user 手上
加密後資料：應在 DB，DBA 手上
加密演算法：應在系統，AP 手上
加密的鑰匙：應在安全官手上

## User defined function
選擇要的

function 不可增、刪、修資料表

use tempdb

create table t3(c1 nvarchar(10))
alter table t3 add(c2 as substring(c1,2,1))
create index idx on t3(c2)
alter table t3 add c3 as rand()
create index idx2 on t3(c3)
select * from t3 where substring(c1,2,1)='a'

## SQL Server Security

drop login l
create login l with password='password',check_pollicy=off
create user l for login l

授權是授權在 user 上，不是在 login 上

Partially Contained Databases: 換 server 時帳密可以帶著走

## Authorizing Users to Access Resources

use tempdb
drop user u 
drop user v
create user u without login
create user v without login
go
create schema s
go
create table s.t(c1 int)
go
insert s.t values(1)
create role r
grant select on schema::s to r
exect('select user.* from s.t') as user='u'
alter role r add member u
deny select on s.t to u
revoke select on s.t to u

role bades security
row level security

DENY > GRANT > REVOKE

create server role s
use master
grant control server to s
create login l2 with password='password',check_policy=off
alter server role s add member l2
exec('create database demo') as login='l2'
deny create any database to l2
drop database demo
exec('create database demo') as login='l2'
alter server role sysadmin add member l2 -- 之後就不做任何檢查
exec('create database demo') as login='l2'