Untitled

mail@pastecode.io avatar
unknown
sqlserver
2 years ago
16 kB
1
Indexable
Never
-- which DB to use
use SQLProject

GO
-- TABLE DROP
drop table LoginAttemptsLog;
drop table UserLog;
drop table PasswordRestore;
drop table Users;
drop table RestoreQuestions;
drop table LoginStatus;
drop table Roles;
GO 
-- table deletion
--DELETE LoginAttemptsLog;
--DELETE UserLog;
--DELETE Users;
GO

-- TABLE CREATION
CREATE TABLE Roles(
		-- 0 = guest, 1 = user, 2 = admin
		role_ID TINYINT PRIMARY KEY,
		role_name VARCHAR(16)
	)

CREATE TABLE LoginStatus (
	-- 0 = blocked, 1 = success, 2 = fail, 3 = waiting for release, 4 = Registered
	Status_ID TINYINT PRIMARY KEY,
	Status_name VARCHAR(16)
)

CREATE TABLE RestoreQuestions(
	Q_ID TINYINT IDENTITY(1,1) PRIMARY KEY,
	Question NVARCHAR(256) not null
)

CREATE TABLE Users(
	email VARCHAR(256) PRIMARY KEY,
	psw NVARCHAR(256) not null, -- add check for length and characters?
	first_name VARCHAR(20) not null,
	last_name VARCHAR(20) not null,
	-- salt VARCHAR(32) not null, -- need to check how implement in c#
	dob date, -- add check for date 'smaller' than today
	user_role TINYINT not null,
	-- constraints
	CHECK (email LIKE '_%@_%._%'),
	CHECK (DATEDIFF(YEAR, dob, GETDATE()) > 1),
	-- foreign keys
	FOREIGN KEY (user_role) REFERENCES Roles (role_ID) ON DELETE CASCADE ON UPDATE CASCADE
	)

CREATE TABLE PasswordRestore(
	email VARCHAR(256),
	Q_ID TINYINT,
	answer VARCHAR(256) not null,
	--constraints
	FOREIGN KEY (email) REFERENCES Users (email) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (Q_ID) REFERENCES RestoreQuestions (Q_ID) ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY (email, Q_ID)
)

CREATE TABLE UserLog(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	email VARCHAR(256),
	log_time datetime not null,
	passed bit not null,
	--constraints
	FOREIGN KEY (email) REFERENCES Users (email) ON DELETE CASCADE ON UPDATE CASCADE,
)

CREATE TABLE LoginAttemptsLog (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	email VARCHAR(256),
	log_time datetime not null,
	Attempt_status TINYINT,
	--constraints
	FOREIGN KEY (email) REFERENCES Users (email) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (Attempt_status) REFERENCES LoginStatus (Status_ID) ON DELETE CASCADE ON UPDATE CASCADE,
)

GO
-- TRIGGERS
-- used when user registers, it inserts a successful attempt log to negate null cases
CREATE OR ALTER TRIGGER RegisterTrg
ON Users
AFTER INSERT
AS
	declare @email VARCHAR(256)
begin
	select *
	into #NewUserstable
	from inserted

	while((SELECT COUNT(*) FROM #NewUserstable) > 0)
	begin
		SELECT TOP 1 @email = email
		FROM #NewUserstable
		INSERT INTO LoginAttemptsLog (email, log_time, Attempt_status) values (@email, GETDATE(), 4)
		DELETE #NewUserstable WHERE email = @email
	end

	drop table #NewUserstable

end

GO
-- adds a log to the UserLog table based on entry from LoginAttemptsLog table
CREATE OR ALTER TRIGGER LoginAttemptTrg
ON LoginAttemptsLog
AFTER INSERT
AS
	declare @email VARCHAR(256)
	declare @loginResult tinyint
	declare @loginStatus bit
	declare @id int

begin
	select *
	into #logintable
	from inserted

	while((SELECT COUNT(*) FROM #logintable) > 0)
	begin
		SELECT TOP 1 @id = ID, @email = email, @loginResult = Attempt_status
		FROM #logintable

		if @loginResult = 1 OR @loginResult = 4
		begin
			set @loginStatus = 'TRUE'
		end
		else
		begin
			set @loginStatus = 'FALSE'
		end

		INSERT INTO UserLog (email, log_time, passed) VALUES (@email, GETDATE(), @loginStatus)
		DELETE #logintable WHERE ID = @id
	end

	DROP table #logintable
end

GO
-- DATA INSERTS
--ROLES
insert into Roles (role_ID, role_name) values (0, 'Guest');
insert into Roles (role_ID, role_name) values (1, 'User');
insert into Roles (role_ID, role_name) values (2, 'Admin');
-- LOGIN STATUS
insert into LoginStatus (Status_ID, Status_name) values (0, 'Blocked');
insert into LoginStatus (Status_ID, Status_name) values (1, 'Success');
insert into LoginStatus (Status_ID, Status_name) values (2, 'Fail');
insert into LoginStatus (Status_ID, Status_name) values (3, 'awaiting release');
insert into LoginStatus (Status_ID, Status_name) values (4, 'Registered');
--RESTORE QUESTIONS
insert into RestoreQuestions (Question) values ('what is the name of your partner?');
insert into RestoreQuestions (Question) values ('what is the name of your pet?');
insert into RestoreQuestions (Question) values ('what is the name of your highschool?');
insert into RestoreQuestions (Question) values ('what is the name of your birth place?');
insert into RestoreQuestions (Question) values ('what is your favorite book?');
insert into RestoreQuestions (Question) values ('what is your favorite movie?');
insert into RestoreQuestions (Question) values ('where is your favorite place to vacation?');
insert into RestoreQuestions (Question) values ('what is your favorite food?');
insert into RestoreQuestions (Question) values ('what was your first workplace?');
insert into RestoreQuestions (Question) values ('what is your favorite game?');
insert into RestoreQuestions (Question) values ('what was your childhood nickname?');
insert into RestoreQuestions (Question) values ('what is the name of your best friend?');
insert into RestoreQuestions (Question) values ('what is your dream job');
--USERS
insert into users (email, psw, first_name, last_name, dob, user_role)
values ('or.alush@gmail.com', 'qwerty123', 'Or' , 'Alush', '1994-10-27', 2);

insert into users (email, psw, first_name, last_name, dob, user_role)
values ('gal.erez@gmail.com', '123456qwe', 'Gal' , 'Erez', '1996-04-19', 2);

insert into users (email, psw, first_name, last_name, dob, user_role)
values ('john.belushi@gmail.com', 'blues', 'John' , 'Belushi', '1949-01-24', 1);

insert into users (email, psw, first_name, last_name, dob, user_role)
values ('dan.aykroyd@gmail.com', 'brothers', 'Dan' , 'Aykroyd', '1952-07-01',1);

insert into users (email, psw, first_name, last_name, dob, user_role)
values ('randi.random@gmail.com', 'q1w2e3r4t5y6', 'Randi' , 'Random', '1995-12-30', 1);

GO
-- views
CREATE OR ALTER VIEW CurrentUserStatus AS
	SELECT email, log_time, Attempt_status
	FROM LoginAttemptsLog
	Where ID in (
		SELECT MAX(ID)
		FROM LoginAttemptsLog
		GROUP BY email)
		
	

GO
CREATE OR ALTER VIEW LastSuccessfulUserStatus AS
	SELECT email, log_time
	FROM LoginAttemptsLog
	Where ID in (
		SELECT MAX(ID)
		FROM LoginAttemptsLog
		WHERE Attempt_status = 1
		GROUP BY email)
	

GO
CREATE OR ALTER VIEW BlockedUsers AS
	SELECT email, log_time
	FROM LoginAttemptsLog
	Where ID in (
		SELECT MAX(ID)
		FROM LoginAttemptsLog
		WHERE Attempt_status = 0 and DATEDIFF(MINUTE,log_time, GETDATE()) < 20
		GROUP BY email)
	

GO
CREATE OR ALTER VIEW UserRole AS
	SELECT email, user_role
	FROM Users

GO
-- PROCEDURES
-- checks login attempts with a set of conditions
CREATE OR ALTER PROCEDURE UserLoginProc @email VARCHAR(256), @psw NVARCHAR(256)
as
declare @valid_user bit = (SELECT COUNT(*) -- check if username matches password
						   FROM Users
						   WHERE email = @email and psw =@psw)

declare @attempts_count int = (SELECT COUNT(*) -- check how many login attempts were made in the last 3 minutes
							   FROM UserLog
							   WHERE email = @email and passed = 0 and DATEDIFF(MINUTE, log_time, GETDATE()) <= 3)

declare @current_status int = (SELECT Attempt_status
								  	     FROM CurrentUserStatus
										 WHERE email = @email)


declare	@current_status_time datetime = (SELECT log_time
								  	     FROM CurrentUserStatus
										 WHERE email = @email)

if @current_status = 3 -- meaning user is blocked but attempted a correct login before 20 minutes elapsed
begin
	set @current_status = 0
	set @current_status_time = (SELECT log_time
								FROM BlockedUsers
								WHERE email = @email)
end

-- username and passwords check, under 3 attempts and not blocked
if @valid_user = 1 and @attempts_count <= 3 and @current_status != 0 
begin
	INSERT INTO LoginAttemptsLog (email, log_time, Attempt_status) values (@email, GETDATE(), 1) --succesful login
end
--username and passwords fails, under 3 attempts and not blocked
else if @valid_user = 0 and @attempts_count <= 3 and @current_status != 0 
begin
	INSERT INTO LoginAttemptsLog (email, log_time, Attempt_status) values (@email, GETDATE(), 2) --failed login
end
--username and passwords fails, over 3 attempts and not blocked
else if @valid_user = 0 and @attempts_count > 3 and @current_status != 0 
begin
	INSERT INTO LoginAttemptsLog (email, log_time, Attempt_status) values (@email, GETDATE(), 0) --user blocked
end
--username and passwords checks and blocked
else if @valid_user = 1 and @current_status = 0 
begin
	if DATEDIFF(MINUTE, @current_status_time, GETDATE()) > 20 -- for over 20 minutes
	begin
		INSERT INTO LoginAttemptsLog (email, log_time, Attempt_status) values (@email, GETDATE(), 1)
	end
	else -- under or at 20 minutes
	begin
		INSERT INTO LoginAttemptsLog (email, log_time, Attempt_status) values (@email, GETDATE(), 3)
	end
end
--username and passwords fails and blocked
else if @valid_user = 0 and @current_status = 0 
begin
	INSERT INTO LoginAttemptsLog (email, log_time, Attempt_status) values (@email, GETDATE(), 0)
end
GO

-- check if last successful login was made within the last 30 seconds
-- from HW2
-- CREATE OR ALTER PROCEDURE LogInStatusProc @email VARCHAR(256)
-- as
-- begin
--	if exists( SELECT *
--			   FROM LastSuccessfulUserStatus
--			   where email = @email and DATEDIFF(SECOND, log_time, GETDATE()) <= 30)
--	begin
--		print 'Successful Login'
--	end
--	else
--	begin
--		print 'Login failed'
--	end
--end

-- changed version for HW 3
-- changed the return value of the procedure to be amount of rows of requested query
CREATE OR ALTER PROCEDURE LogInStatusProc @email VARCHAR(256)
as
	declare @returnValue int = (SELECT count(*)
								FROM LastSuccessfulUserStatus
								WHERE email = @email and DATEDIFF(SECOND, log_time, GETDATE()) <= 30)
	declare @failed int = (SELECT Attempt_status
							FROM CurrentUserStatus
							WHERE email = @email)
begin
	if (@failed != 1)
	begin
		RETURN 0
	end
	RETURN @returnValue
end
GO


--get list of all blocked users within the last 20 minutes
CREATE OR ALTER PROCEDURE GetBlockedUsersProc @email VARCHAR(256)
as
declare @role tinyint = (SELECT user_role
						 FROM Users
						 WHERE email = @email)
if @role = 2
begin
	SELECT email
	FROM BlockedUsers
end
else
begin
	print 'Access Denied'
end

GO
--get list of the last successful attempts of all users
CREATE OR ALTER PROCEDURE GetSuccessfulUsersProc @email VARCHAR(256)
as
declare @role tinyint = (SELECT user_role
						 FROM Users
						 WHERE email = @email)
if @role = 2
begin
	SELECT email, CAST(log_time as smalldatetime) as 'Last Login Time'
	FROM LastSuccessfulUserStatus
end
else
begin
	print 'Access Denied'
end

GO
-- get history of own login attempts
CREATE OR ALTER PROCEDURE GetLoginHistoryProc @email VARCHAR(256)
as
declare @role tinyint = (SELECT user_role
						 FROM Users
						 WHERE email = @email)
if @role != 0
begin
	SELECT log_time as "Log Time", Status_name as "Login Status"
	FROM LoginAttemptsLog as LA inner join LoginStatus as LS on LA.Attempt_status = LS.Status_ID
	WHERE email = @email
end
else
begin
	print 'Access Denied'
end

GO
--/*
-- execute procedure
-- WARNING -> DO NOT EXECUTE ALL AT THE SAME TIME, THIS CAUSES BUGS
-- UserLoginProc
exec UserLoginProc @email = 'or.alush@gmail.com', @psw = 'qwerty123' -- correct entry
exec UserLoginProc @email = 'or.alush@gmail.com', @psw = 'qwerty122' -- false entry
exec UserLoginProc @email = 'oralush@gmail.com', @psw = 'qwerty121' -- false entry
exec UserLoginProc @email = 'or.alush@gmail.com', @psw = 'qwerty12'  -- false entry and blocked
exec UserLoginProc @email = 'or.alush@gmail.com', @psw = 'qwerty12' -- new blocked entry
exec UserLoginProc @email = 'or.alush@gmail.com', @psw = 'qwerty123' -- awaiting release entry

-- LogInStatusProc - in this 
DECLARE @test int
exec @test = LogInStatusProc @email = 'or.alush@gmail.com' -- check status - should be true for testing purpose
SELECT 'return value' = @test

-- GetBlockedUsersProc
exec GetBlockedUsersProc @email = 'or.alush@gmail.com' -- check status - should be true for testing purpose

-- GetSuccessfulUsersProc
exec GetSuccessfulUsersProc @email = 'or.alush@gmail.com' -- check status - should be true for testing purpose

-- GetLoginHistoryProc
exec GetLoginHistoryProc @email = 'or.alush@gmail.com' -- check status - should be true for testing purpose
--*/
GO
-- HW3
-- procedures
--changes to user table
CREATE OR ALTER PROCEDURE UsersTableChangeProc
	(@email VARCHAR(256) = '',
	 @psw NVARCHAR(256) = '',
	 @f_name VARCHAR(20) = '',
	 @l_name VARCHAR(20) = '',
	 @dob date = NULL,
	 @actionType NVARCHAR(20) = '')
AS
BEGIN
	IF @actionType = 'Insert'
	BEGIN
		INSERT INTO Users (email, psw, first_name, last_name, dob, user_role)
		VALUES			  (@email, @psw, @f_name, @l_name, @dob, 1)
	END
	ELSE IF @actionType = 'Update'
	BEGIN
		UPDATE Users
		SET	first_name = @f_name,
			last_name = @l_name,
			dob = @dob
		WHERE email = @email
	END
	ELSE IF @actionType = 'Delete'
	BEGIN
		DELETE FROM Users
		WHERE email = @email
	END
	ELSE IF @actionType = 'NewPass'
		UPDATE Users
		SET psw = @psw
		WHERE email = @email
END

GO

--insert to PasswordRestore table <-> link questions to user
CREATE OR ALTER PROCEDURE PasswordRestoreTableInsertProc
	(@email VARCHAR(256),
	 @qid TINYINT,
	 @ans VARCHAR(256))
AS
BEGIN
	INSERT INTO PasswordRestore (email, Q_ID, answer)
	VALUES						(@email, @qid, @ans)
END

GO

--get user questions and answers
CREATE OR ALTER PROCEDURE QNAProc @email varchar(256)
AS
DECLARE @isUserExist INT = (SELECT COUNT(*)
							FROM Users
							WHERE email = @email)
BEGIN
	IF @isUserExist = 0
	BEGIN
		SELECT Question
		FROM RestoreQuestions
	END
	ELSE
	BEGIN
		SELECT Question, PR.Q_ID, answer
		FROM PasswordRestore as PR inner join RestoreQuestions as RQ on PR.Q_ID = RQ.Q_ID
		WHERE email = @email
	END
	
END
GO

CREATE OR ALTER PROCEDURE CurrentUserStatusProc
AS
BEGIN
	SELECT *
	FROM CurrentUserStatus
END
GO

CREATE OR ALTER PROCEDURE UserRoleProc @email varchar(256)
AS
BEGIN
	SELECT *
	FROM UserRole
	Where email = @email
END
GO


CREATE OR ALTER PROCEDURE GetQuestionsProc
AS
BEGIN
	SELECT *
	FROM RestoreQuestions
END
GO

CREATE OR ALTER PROCEDURE UserExistsProc @email varchar(256)
AS
BEGIN
	SELECT email
	FROM Users
	WHERE email = @email
END
GO


--HW4
DROP LOGIN DBAdmin
DROP USER The_Admin
DROP LOGIN DBUser
DROP USER The_User
DROP LOGIN DBGuest
DROP USER The_Guest
-- admin user
CREATE LOGIN DBAdmin WITH PASSWORD = '12cd';
USE SQLProject
GO
CREATE USER The_Admin FOR LOGIN DBAdmin
GO
-- regular user
CREATE LOGIN DBUser WITH PASSWORD = '1234';
USE SQLProject
GO
CREATE USER The_User FOR LOGIN DBUser
GO
-- guest user
CREATE LOGIN DBGuest WITH PASSWORD = 'abcd';
USE SQLProject
GO
CREATE USER The_Guest FOR LOGIN DBGuest
GO

-- access
USE SQLProject
GRANT EXEC ON GetBlockedUsersProc TO The_Admin
GRANT EXEC ON CurrentUserStatusProc TO The_Admin
GRANT EXEC ON GetLoginHistoryProc TO The_Admin
GRANT EXEC ON GetSuccessfulUsersProc TO The_Admin
GRANT EXEC ON LogInStatusProc TO The_Admin


GRANT EXEC ON GetLoginHistoryProc TO The_User
GRANT EXEC ON PasswordRestoreTableInsertProc TO The_User
GRANT EXEC ON UsersTableChangeProc TO The_User
GRANT EXEC ON UserRoleProc TO The_User

GRANT EXEC ON UserLoginProc TO The_Guest
GRANT EXEC ON QNAProc TO The_Guest
GRANT INSERT ON Users TO The_Guest
GRANT EXEC ON LogInStatusProc TO The_Guest
GRANT EXEC ON GetQuestionsProc TO The_Guest
GRANT EXEC ON UserExistsProc TO The_Guest
GRANT EXEC ON UsersTableChangeProc TO The_Guest
GRANT EXEC ON PasswordRestoreTableInsertProc TO The_Guest