Security

Here are the somewhat fragmentary notes from our discussion on Security

Prevent Damage to Database
malicious attacks--
Accidents -- update table
Authentication--password username, windows

There are two basic kinds of Authentication

Windows, mapping windows user accounts to SQl Server
Sql Server Authentication-- a password and user name

2 step authentication, biometrics, voice, facial
Certificates Encryption

Authorization--What permissions do you have

Select read
Insert--write
update--write change
Delete remove data
execute run procedures
create--create database objects
Alter --modify database objects
Drop--remove database object

Actors--who is going use the database

---

Actor  Fan
Tables Select Insert Update Delete Notes
Venue   X   
Artist  X
Fan     x        x     x           Update own records
                                    only

Here is the code for creating a login, a user, a role, granting permissions to the role, and adding the user to the role

Grant create Table  to DonorRole 

Create Login George with password='Pass'
Use CommunityAssist
Create User George for login George

Create Role ClientRole

Grant select, insert on ServiceGrant to ClientRole
Grant Select on CommunityService to ClientRole

exec sp_addrolemember Clientrole, George

triggers

--triggers
--stored procedure triggered by an event
--insert, update, delete
--for, after, instead of
-- for and after let the event happen and then
--execute the trigger code
--instead of intercepts the event and does
--the trigger code instead of the event
Create trigger tr_BigDonation on Donation
for insert 
As
if not exists
  (Select name from Sys.Tables
  Where name='BigDonations')
Begin
Create table BigDonations
(
 DonationKey int, 
 DonationDate DateTime, 
 DonationAmount money, 
 PersonKey int
)
End
Declare @DonationAmount money
Declare @Threshold money =500
Select @DonationAmount = DonationAmount
From Inserted
if @DonationAmount >= @Threshold
Begin
Insert into BigDonations(DonationKey,DonationDate,DonationAmount,
PersonKey)
Select DonationKey, DonationDate, DonationAmount,
PersonKey from Inserted
End

Select * from Person
Insert into Donation(DonationDate, DonationAmount, PersonKey)
Values(GetDate(), 550.75, 136)

Select * from Donation
Select * from BigDonations
Go
--Here is an alternate, more efficent version of that trigger
Create trigger tr_BigDonation on Donation
for insert 
As
--declare variables
Declare @DonationAmount money
Declare @Threshold money =500
--get value from temporary Inserted table
Select @DonationAmount = DonationAmount
From Inserted
--if the donation amount is 500 or above
if @DonationAmount >= @Threshold
Begin --outer if
if not exists
  (Select name from Sys.Tables
  Where name='BigDonations')
Begin--inner if
Create table BigDonations
(
 DonationKey int, 
 DonationDate DateTime, 
 DonationAmount money, 
 PersonKey int
)
End --end inner if
Insert into BigDonations(DonationKey,DonationDate,DonationAmount,
PersonKey)
Select DonationKey, DonationDate, DonationAmount,
PersonKey from Inserted
End --end outer if

go

Alter trigger tr_NoDelete on Donation
instead of Delete, update
As
  if not exists
     (Select name from Sys.Tables
      Where name='DeletedDonations')
   --if not create it
       Begin 
           Create table DeletedDonations
            (
           DonationKey int, 
           DonationDate DateTime, 
           DonationAmount money, 
           PersonKey int
             )    
       End
   Insert into DeletedDonations         (DonationKey,DonationDate, 
   DonationAmount, PersonKey)
 Select DonationKey, DonationDate, DonationAmount,
 PersonKey from Deleted



--
Delete from Donation where donationKey=47

Select * From Donation

Select * From DeletedDonations
go
--disable trigger to allow deletions
Disable trigger tr_NoDelete on Donation
go
--re enable trigger
enable Trigger tr_NoDelete on Donation

Stored Procedures

Use CommunityAssist
go
-- stored procedures
create proc usp_Donations
@PersonKey int
As
Select DonationKey, 
DonationDate, 
DonationAmount
From Donation
Where PersonKey = @PersonKey 

exec usp_Donations @PersonKey=3

Go
--add new person
--Get all the parameters for Person,
--personAddress and PersonContact
--Make sure the person is not in the database
--already
--begin a transaction
--Begin a try--
--hash their password
--insert into person
--insert into personAddress --getting the person key
--insert into personContact with key
--commit tran
--or catch the errors and rollaback

Version 1

Create proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
Declare @PersonKey int = Ident_Current('Person')
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)

exec Usp_AddPerson
@PersonLastName='Anderson', 
@PersonFirstName='Marty', 
@PersonUsername='MAnderson@gmail.com', 
@PersonPlainPassword='martyPass', 
@Street='1002 South Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559873', 
 @WorkPhone='2065558769'

Version 2

 Go
 Alter proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
--Declare variables
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =6
Declare @WorkPhoneType int =2
Begin Tran --start transaction
Begin try--start Try
--insert into Person
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
--get person key
Declare @PersonKey int = Ident_Current('Person')
--insert int personAddress
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
--insert home phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
--insert work phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)
commit tran --all is good write it
End try
Begin catch --if an error will fall here
Rollback Tran--undo the transaction
print 'Transaction rolled back'
print Error_Message();
End Catch

exec Usp_AddPerson
@PersonLastName='Munse', 
@PersonFirstName='Nelson', 
@PersonUsername='NMunse@gmail.com', 
@PersonPlainPassword='MunsePass', 
@Street='1004 North Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559773', 
 @WorkPhone='2065558729'

Third Version

  Alter proc usp_AddPerson
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonUsername nvarchar(25), 
@PersonPlainPassword nvarchar(50), 
@Street nvarchar(255), 
@Apartment nvarchar(255) = null, 
@State nvarchar(2) = 'WA', 
@City nvarchar(255) = 'Seattle', 
@Zip nvarchar(10), 
@HomePhone nvarchar(255) = null,
@WorkPhone nvarchar(255)=null
As
--Declare variables
Declare @Passkey int =dbo.fx_Seed()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2
if exists
 (Select PersonKey from Person
 Where PersonLastName =@PersonLastName
 And PersonFirstname=@PersonFirstName
 And PersonUserName=@PersonUsername)
Begin --begin if
Print 'Person already exists'
end --end if
Else
Begin--begin else
Begin Tran --start transaction
Begin try--start Try
--insert into Person
Insert into Person
(PersonLastName, PersonFirstName, PersonUsername, 
PersonPlainPassword, 
Personpasskey, PersonEntryDate, PersonUserPassword)
Values(@PersonLastName, @PersonFirstName,
@PersonUsername, @PersonPlainPassword,
@Passkey, GetDate(),dbo.fx_hashPass(@PassKey, @PersonPlainPassword))
--get person key
Declare @PersonKey int = Ident_Current('Person')
--insert int personAddress
Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values (@Street, @Apartment,@State, @City,@Zip, @PersonKey)
--insert home phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @HomePhone, @HomePhoneType)
--insert work phone
Insert into PersonContact(PersonKey, ContactInfo, ContactTypeKey)
Values(@PersonKey, @WorkPhone, @WorkPhoneType)
commit tran --all is good write it
End try
Begin catch --if an error will fall here
Rollback Tran--undo the transaction
print 'Transaction rolled back'
print Error_Message();
End Catch
end --end else

exec Usp_AddPerson
@PersonLastName='Munse', 
@PersonFirstName='Nelson', 
@PersonUsername='NMunse@gmail.com', 
@PersonPlainPassword='MunsePass', 
@Street='1004 North Somewhere', 
 @Zip='98100', 
 @HomePhone='2065559773', 
 @WorkPhone='2065558729'

ERD Individual Assignment 2 Diagrams

Recipe Ingredient Entities

---

Student Course Entities

---

Dentist office Entities

functions

--temp tables
use CommunityAssist

--this creates a temp table that is isolated
--to a single session 
Create table #ZPeople
(
    FirstName nvarchar(255),
 Lastname nvarchar(255)

)

Insert into #ZPeople(Firstname, Lastname)
Select PersonFirstName, PersonLastName
From Person
Where PersonLastName like 'Z%'

Select * From #ZPeople

--this creates a global temporary table
--that crosses sessions
Create table ##ZPeople
(
    FirstName nvarchar(255),
 Lastname nvarchar(255)

)

Insert into ##ZPeople(Firstname, Lastname)
Select PersonFirstName, PersonLastName
From Person
Where PersonLastName like 'Z%'

--functions

Go
--a simple function
create function fx_cube
(@number int) --parameters to be passed in
returns int --return type
As
Begin --start blocl
Declare @cube int --declare a varible
Set @Cube = @number * @number * @number
--return the value
return @Cube
End; --end function block
go
--use function
Select dbo.fx_Cube(2)
go

--get a random seed
Create Function fx_Seed
()
returns int
As
Begin
Declare @Time DateTime = GetDate();
Declare @Mille int = Datepart(MILLISECOND, @Time)
Return @Mille

End
go
Select dbo.fx_Seed()
go
--this function hashes a password
Create function fx_hashPass
(@Seed int, @Password nvarchar(50))
Returns Varbinary(500)
As
Begin
Declare @SeedChar nvarchar(4) = Cast(@seed as nvarchar(4))
Declare @CharToHash nvarchar(54) = @SeedChar+@Password
Declare @Hash varbinary(500)
set @Hash=hashbytes('sha2_512', @CharToHash)
return @Hash
End
go
Select dbo.fx_HashPass(dbo.fx_Seed(), 'P@ssw0rd1')
Select dbo.fx_HashPass(dbo.fx_Seed(), 'P@ssw0rd1')

go
--this sees if the passwords match 
Create function fx_Login
(@userName nvarchar(255) ,@password nvarchar(50))
returns int
As
Begin
Declare @seed int
Declare @DBHash varbinary(500)
Declare @Key int
--select values from the database
--and assign them to the variables
Select @key=PersonKey,@seed = PersonPassKey, @DBHash=PersonUserPassword       from Person
 Where PersonUsername=@UserName
 --concatinate the seed and password
Declare @Concat nvarchar(54)=Cast(@seed as nvarchar(4)) + @Password
Declare @NewHash varbinary(500)= hashbytes('Sha2_512',@Concat)
--see if they match
if Not @DBHash=@NewHash
Begin
--if not then just return 0
Set @key = 0
End
Return @Key
end

--test functions and login
Insert into person(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, Personpasskey, PersonEntryDate, PersonUserPassword)
Values ('Smith', 'Bob', 'bobSmith@gmail.com', 'BobPass',
dbo.fx_Seed(), GetDate(), 
dbo.fx_hashPass(dbo.fx_Seed(), 'BobPass'))

Select * From Person

Select dbo.fx_Login('bobSmith@gmail.com','BobPass')

USE [CommunityAssist]
GO

/****** Object:  UserDefinedFunction [dbo].[fx_MedianAvg]    Script Date: 2/18/2016 11:32:43 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--this function returns a median average
Create Function [dbo].[fx_MedianAvg]
(@Max decimal(16,4), @Min Decimal(16,4))
returns decimal(16,4)
As
Begin
Declare @Range decimal(16,4)
Declare @Median Decimal(16,4)
Select @Range= @Max-@min
Set @Median = @Range /2
Return @Median
End
GO


Select Avg(GrantAllocation) from Servicegrant
Select dbo.fx_MedianAvg(max(GrantAllocation),min(GrantAllocation))
From ServiceGrant

SQL 2

This includes SQL 1

--set the database context
Use CommunityAssist

/*Over view or SQL commands
Part 1 */

--basic statement [Select columns from Table name]
--the * is a wild card for all
Select * from Person;


Select PersonLastName, PersonFirstName,
PersonUserName
From Person;

--alias column names, the 'as'  keyword is optional
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person;

--Order by sorts a result set
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by PersonLastName

--Order by Desc

Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by [Last Name] Desc, PersonFirstName Desc

--where clause limits the rows to those that match the criteria
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName ='Tanner'
Order by [Last Name] Desc

Select * From ServiceGrant
Where GrantAmount > 500

Select * from ServiceGrant where GrantDate>'8/9/2013'

Select * From ServiceGrant where GrantDate 
Between '9/1/2013' and '10/1/2013'

--you can calculate
Select 50 * ((23/2.0 )+ 27) - 3

--The LIKE keyword lets you search for patterns 
--in this case everybody whose name begins with 'AND.'
--the % is a wildcard for any number of characters
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName Like 'A%'

--nulls cannot be = to anything you search for nulls
--with the IS keyword
Select * From PersonAddress
Where Apartment is null

--is not null
Select * From PersonAddress
Where Apartment is not null

Select * from ServiceGrant

--date Functions
Select Distinct Year(GrantDate) From ServiceGrant
Select Distinct Month(GrantDate) From ServiceGrant
Select Distinct Day(GrantDate) From ServiceGrant

Select Cast(Day(GrantDate)as varchar(2))+ '/' 
+ cast(Month(GrantDate) as varchar(2)) + '/' 
+ cast(Year(GrantDate)as varchar(4)) as Year 
From ServiceGrant

Select GrantDate From ServiceGrant


--the datediff function subtracts on date from another in the specified units 
--dd is days, yy years, mm months
Select GrantReviewDate, GrantDate, DateDiff(dd,GrantDate,GrantReviewDate) as [Days before Review]
From ServiceGrant
Where DateDiff(dd,GrantDate,GrantReviewDate) > 2

--aggregate functions are functions that work
--on sets of rows at a time
Select format(Sum(GrantAllocation),'$#,###.00') as total from ServiceGrant 
Select Avg(GrantAllocation)as total from ServiceGrant 

Select Count(GrantAllocation)as total from ServiceGrant 
Where GrantAllocation > 500

Select Max(GrantAllocation)as total from ServiceGrant 
Select Min(GrantAllocation)as total from ServiceGrant 

--when mixing aggregate functions such as sum
--with non-aggregate functions or fields like Month(GrantDate)
--you have to GROUP BY all the non-aggregate values
Select Month(GrantDate) as [Month], Sum(GrantAllocation) as total
From ServiceGrant
Group by Month(GrantDate)

--having works like a where by you use it when you have
--an aggregate function in the criteria
Select ServiceKey, Sum(GrantAllocation)
From ServiceGrant
Group by ServiceKey
 Having sum(GrantAllocation) > 2000

--inner join joins two tables. the keyword INNER is optional
Select ServiceName, Sum(GrantAllocation) as Total
From ServiceGrant
inner join CommunityService
on ServiceGrant.ServiceKey=CommunityService.ServiceKey
Group by ServiceName

--another inner join
Select p.PersonKey,PersonLastName, PersonFirstName, Street, City,[State],zip
From Person as p
join PersonAddress as pa
on p.PersonKey=pa.PersonKey

Select * From ServiceGrant

--joining multiple tables
--the person table is joined twice
--once for the client and once for the employee
Select GrantAmount, GrantDate, p.PersonLastName as Client, ServiceName, p2.PersonLastName as EmployeeName, GrantAmount
From Person as p
join ServiceGrant as sg
on p.PersonKey=sg.PersonKey
join CommunityService as cs
on cs.ServiceKey=sg.ServiceKey
join Employee as e
on e.EmployeeKey=sg.EmployeeKey
inner join person p2
on p2.PersonKey = e.PersonKey

--Data Manipulation queries
Insert into Person ( PersonLastName, PersonFirstName, PersonUsername)
Values('Kim','Jim','jimkim'),
('Morgan','Randy','rmorgan')

Select * from Person

Create Table PeopleTemp
(
 PersonKey int,
 PersonLastName nvarchar(255),
 PersonFirstName nvarchar(255)
)

Insert into PeopleTemp(personKey, PersonLastName, PersonFirstName)
Select PersonKey PersonLastName, PersonFIrstName
From Person

Select * from PeopleTemp

Insert into Person ( PersonLastName, PersonFirstName, PersonUsername)
Values('Lawrence', 'Ahab', 'Alaw')
Insert into PersonAddress([Street],[City],[Zip], PersonKey)
Values('1001 Westlake','Seattle','98001', ident_current('Person'))

Select * from Person
Select * From PersonAddress

update peopleTemp
Set PersonLastName= 'Smith'

Update Person
Set PersonLastName='Alberts',
PersonFirstName='Jay'
where PersonKey=1

Begin Tran

Rollback Tran
commit tran

Delete From Person Where Personkey =1

Drop table PeopleTemp

Select * from Person

Set operators, Windows Functions, Data Modification

--Set operators
--Data manipulation
-- Windows Functions
-- union Intersect Except

use CommunityAssist
--union
Select PersonLastName, PersonFirstName,
EmployeeHireDate
From Person p
join Employee  e on p.PersonKey=e. PersonKey
Union
Select EmployeeLastName, EmployeeFirstName, 
EmployeeHireDate
From MetroAlt.dbo.Employee
use MetroAlt
Select PersonLastName, PersonFirstName,
EmployeeHireDate
From CommunityAssist.dbo.Person p
join CommunityAssist.dbo.Employee  e on p.PersonKey=e. PersonKey
Union All
Select EmployeeLastName, EmployeeFirstName, 
EmployeeHireDate
From MetroAlt.dbo.Employee

--intersect
Select City from PersonAddress
Intersect
Select EmployeeCity from MetroAlt.dbo.Employee

--except
Select City from PersonAddress
Except 
Select EmployeeCity from MetroAlt.dbo.Employee

Select EmployeeCity from MetroAlt.dbo.Employee
Except
Select City from PersonAddress

--ranking functions
Select GrantKey, ServiceKey, GrantAllocation,
Row_Number() over (Order by GrantAllocation desc) as RowNumber,
Rank() over (Order by GrantAllocation desc) as [Rank],
Dense_Rank() over (Order by GrantAllocation desc) as [DenseRank],
NTILE(10) over (Order by GrantAllocation desc) as [NTILE]
From ServiceGrant
Order by GrantAllocation desc

--windows partition functions
Select Grantkey, ServiceKey, GrantAllocation,
Sum(GrantAllocation) over () as TotalAllocations,
Sum(GrantAllocation) over(Partition by ServiceKey) as PerService
From ServiceGrant
order by GrantAllocation Desc

--pivot
Select EmployeeKey, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select EmployeeKey,  ServiceKey, GrantKey From
dbo.ServiceGrant) as a
pivot (Count(GrantKey) for ServiceKey in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) as b

-- modifying data

Insert into Person  (PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, Personpasskey, PersonEntryDate, PersonUserPassword)
Values('Alberts','Larry','larryA@gmail.com',null, 12345678,
GetDate(), dbo.fx_HashPassword('AlbertPass')),
('Able','Luke','luke.able@gmail.com',null, 12345678,
GetDate(), dbo.fx_HashPassword('AblePass'))

Select * from Person order by PersonLastName

Begin tran
Update Person 
Set PersonFirstName = 'Jason',
PersonPlainpassword='JasonPass'
Where Personkey=1
Commit Tran
Rollback tran

Delete from Person where Personkey =1
 Begin Tran
 Truncate Table GrantReview

 Select * From GrantReview

 Rollback Tran

 Insert into person(personLastname, PersonFirstName)
 Values('Lucas', 'George')
 Insert into Donation(DonationDate, DonationAmount, PersonKey)
 Values(GetDate(), 1500,Ident_Current('Person'));
 @@Identity

 Select * from Person
 Select * From Donation