Security SQL

--Employees
--Clients
--Donors
--public
--client Needs
--See what kinds of grants
--See the summary views
--They need to be able to register
 -- insert into person
 --insert into personAddress
 --insert into contact
--edit own contact information
--Apply for a grant
  --insert into grant Request
  --See own grant and check status
      --view grants (their own) grant Review
--schema collection of object
--Role collection of permission

Create schema GrantClientSchema;
Go
Create view GrantClientSchema.vw_ViewGrantTypes;
As
Select GrantTypeName Name, 
GrantTypeMaximum [One Time Maximum],
GrantTypeLifetimeMaximum [Life Time Maximum], 
GrantTypeDescription [Description]
From GrantType;
go
Select * from GrantClientSchema.vw_ViewGrantTypes;
go
Create proc usp_NewRegister
@PersonLastName nvarchar(255), 
@PersonFirstName nvarchar(255), 
@PersonEmail nvarchar(255), 
@PersonPlainPassWord nvarchar(50), 
@PersonAddressApt nvarchar(255) =null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255)='Seattle', 
@PersonAddressState nchar(2)='WA', 
@PersonAddressZip nvarchar(11),
@homephone nvarchar(255) = null,
@Workphone nvarchar(255)=null
As
--check to make sure they don't exist
if not exists
  (Select PersonKey From Person
     Where PersonEmail=@PersonEmail
  And PersonLastName=@PersonLastName)
Begin --begin if they don't exist
--declare and set variables
Declare @seed int = dbo.fx_GetSeed()
Declare @hash varbinary(500) = 
dbo.fx_hashPassword(@seed
, @PersonPlainPassword)
Declare @PersonKey int
Declare @CurrentDate Datetime = GetDate()
Declare @HomePhoneType int =1
Declare @WorkPhoneType int =2

Begin tran--begin transaction
Begin try--begin try/catch
--insert into person
Insert into Person(PersonLastName, 
PersonFirstName, PersonEmail, 
PersonPassWord, PersonEntryDate, PersonPassWordSeed)
Values(@PersonLastName, @PersonFirstName,
@PersonEmail,@Hash, @CurrentDate,@Seed)
--get PersonKey
Set @PersonKey = IDENT_CURRENT('Person')
--insert into personAddress
Insert into PersonAddress(PersonAddressApt, 
PersonAddressStreet, PersonAddressCity, 
PersonAddressState, PersonAddressZip, PersonKey)
Values(@PersonAddressApt, 
@PersonAddressStreet, @PersonAddressCity, 
@PersonAddressState, @PersonAddressZip, @PersonKey)
--check on phones
if @homePhone is not null
Begin
  Insert into contact(ContactNumber, 
  ContactTypeKey, PersonKey)
  Values(@homephone,@HomePhoneType,@PersonKey)
End
if @WorkPhone is not null
Begin
  Insert into contact(ContactNumber, 
  ContactTypeKey, PersonKey)
  Values(@workphone,@WorkPhoneType,@PersonKey)
end
Commit tran
End Try
Begin Catch
Rollback Tran
print 'The insert was unsuccessful'
print error_message()
return error_number() 
End Catch

End --end if they don't exist
Else
Begin
print 'Person already exists'
End
  
exec usp_NewRegister
@PersonLastName = 'Nelson', 
@PersonFirstName='Miriam', 
@PersonEmail='MNelson@gmail.com', 
@PersonPlainPassWord='NelsonPass', 
@PersonAddressStreet='4412 South Sound Street', 
@PersonAddressZip='98100', 
@homephone='2065550975' 


Select * from Contact where PersonKey=150

Go
Create proc GrantClientSchema.usp_ViewGrantStatus
@PersonKey int
As 
Select [GrantRequestDate][Date], 
GrantTypeName [GrantType],
[GrantRequestExplanation] Explanation,
[GrantRequestAmount] Request,
[GrantReviewDate] [Review Date],
[GrantRequestStatus] [Status],
[GrantAllocationAmount] Allocation
From GrantRequest req
inner join Granttype gt
on req.GrantTypeKey=gt.GrantTypeKey
inner join GrantReview rev
on rev.GrantRequestKey=req.GrantRequestKey
Where PersonKey =@PersonKey

Exec GrantClientSchema.usp_ViewGrantStatus 1

Create role GrantClientRole

Grant Select, Execute on 
 Schema::GrantClientSchema to GrantClientRole

 Grant Select on vw_Donations to GrantClientRole
 Grant Select on vw_GrantTypeTotals to GrantClientRole
Go
Create proc usp_UpdateAddress
@PersonAddressKey int,
@PersonAddressApt nvarchar(255) = null, 
@PersonAddressStreet nvarchar(255), 
@PersonAddressCity nvarchar(255), 
@PersonAddressState nchar(2), 
@PersonAddressZip nchar(11), 
@PersonKey int
As
UpDate PersonAddress
Set PersonAddressApt=@PersonAddressApt,
PersonAddressStreet=@PersonAddressStreet,
PersonAddressCity=@personAddressCity,
PersonAddressState=@PersonAddressState,
PersonAddressZip=@PersonAddressZip
Where PersonKey =@PersonKey
And PersonAddressKey=@PersonAddressKey

Select * from PersonAddress

Exec [dbo].[usp_UpdateAddress]
@PersonAddressKey =1, 
@PersonAddressApt=null,   
@PersonAddressStreet='1002 North Mann Street', 
@PersonAddressCity='Seattle', 
@PersonAddressState='Wa', 
@PersonAddressZip='98001', 
@PersonKey=1