Users Sessions
Exported Procedures
Base_Users_Sessions_Replace
DROP PROCEDURE Base_Users_Sessions_Replace;
DELIMITER //
CREATE PROCEDURE Base_Users_Sessions_Replace
(
$email CHAR(99),
$password CHAR(99)
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
SET @status = "";
SET @sessionid = "";
SET @USER = 0;
SET @idtype = "";
CALL Base_Users_Sessions_Replace_Inout( $email, $password, @status, @sessionid, @USER, @idtype );
SELECT @status AS status, @sessionid AS sessionid, @USER AS USER, @idtype AS idtype;
END
//
DELIMITER ;
CREATE OR ALTER PROCEDURE Base_Users_Sessions_Replace
(
@email CHAR(99),
@password CHAR(99)
)
AS
BEGIN
SET @status = "";
SET @sessionid = "";
SET @USER = 0;
SET @idtype = "";
CALL Base_Users_Sessions_Replace_Inout( $email, $password, @status, @sessionid, @USER, @idtype );
SELECT @status AS status, @sessionid AS sessionid, @USER AS USER, @idtype AS idtype;
END
//
DELIMITER ;
Deprecated
DROP PROCEDURE Users_Sessions_Replace;
DELIMITER //
CREATE PROCEDURE Users_Sessions_Replace
(
$email CHAR(99),
$password CHAR(99)
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
SET @status = "";
SET @sessionid = "";
SET @USER = 0;
SET @idtype = "";
CALL Base_Users_Sessions_Replace_Inout( $email, $password, @status, @sessionid, @USER, @idtype );
SELECT @status AS status, @sessionid AS sessionid, @USER AS USER, @idtype AS idtype;
END
//
DELIMITER ;
Base_Users_Authenticate
DROP PROCEDURE Base_Users_Authenticate;
DELIMITER //
CREATE PROCEDURE Base_Users_Authenticate
(
$Sid TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT Base_Users_Sessions_Verify( $Sid ) THEN
CALL Base_Users_Sessions_Terminate( $Sid );
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_SESSION IN Base_Users_Authenticate';
ELSE
CALL Base_Users_Sessions_Extend_Expiry( $Sid );
SELECT email, USER, given_name, family_name, type AS idtype, group_code, last_login, user_status, user_hash, 0 AS read_only
FROM base_users_sessions
LEFT JOIN view_base_users USING (email)
WHERE sid = $Sid;
END IF;
END
//
DELIMITER ;
Deprecated.
DROP PROCEDURE Users_Authenticate;
DELIMITER //
CREATE PROCEDURE Users_Authenticate
(
$Sid CHAR(64)
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
CALL Base_Users_Authenticate( $Sid );
END
//
DELIMITER ;
Base_Users_Authorize_SessionID
DROP PROCEDURE Base_Users_Authorize_SessionID;
DELIMITER //
CREATE PROCEDURE Base_Users_Authorize_SessionID
(
$Sid CHAR(64),
OUT $Email CHAR(99),
OUT $USER INT(11),
OUT $IDType VARCHAR(20)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, $Email, $USER, $IDType );
END IF;
END
//
DELIMITER ;
Base Users Sessions
Base_Users_Sessions
DROP PROCEDURE Base_Users_Sessions;
DELIMITER //
CREATE PROCEDURE Base_Users_Sessions
(
$Sid CHAR(64),
$USER CHAR(64),
$user_hash CHAR(64),
$order CHAR(99),
$limit INT(11),
$offset INT(11)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
END IF;
END
//
DELIMITER ;
Base_Users_Sessions_Replace_Inout
DROP PROCEDURE Base_Users_Sessions_Replace_Inout;
DELIMITER //
CREATE PROCEDURE Base_Users_Sessions_Replace_Inout
(
$email CHAR(99),
$password CHAR(99),
INOUT $status CHAR(99),
INOUT $sessionid CHAR(64),
INOUT $USER INT(11),
INOUT $idtype CHAR(20)
)
SQL SECURITY INVOKER
BEGIN
CALL Base_Users_Sessions_Replace_w_DeviceID_Inout
(
'',
$email,
$password,
'',
'',
$status,
$sessionid,
$USER,
$idtype
);
END
//
DELIMITER ;
Base_Users_Sessions_Replace_w_DeviceID_Inout
DROP PROCEDURE Base_Users_Sessions_Replace_w_DeviceID_Inout;
DELIMITER //
CREATE PROCEDURE Base_Users_Sessions_Replace_w_DeviceID_Inout
(
$deviceid CHAR(99),
$email CHAR(99),
$password CHAR(99),
$provisional_id CHAR(99),
$code CHAR(99),
OUT $status CHAR(99),
OUT $sessionid CHAR(64),
OUT $USER INT(11),
OUT $idtype CHAR(20)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $start TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
DECLARE $authenticated BOOL DEFAULT 0;
DECLARE $salt TEXT DEFAULT '';
DECLARE $phash1 TEXT DEFAULT '';
DECLARE $phash2 TEXT DEFAULT '';
DECLARE $invalid INT DEFAULT 1;
DECLARE $group_code TEXT DEFAULT '';
DECLARE $csrf TEXT DEFAULT '';
DECLARE $SESSION_ID INT DEFAULT 0;
DECLARE $user_mfa_type TEXT DEFAULT 0;
DECLARE $extend INT DEFAULT 14400;
SET $status = '';
SET $sessionid = '';
SET $USER = 0;
SET $idtype = '';
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
IF "" != $provisional_id OR "" != $code THEN
SELECT SESSION_ID
INTO $SESSION_ID
FROM base_users_sessions
WHERE sid = $provisional_id
AND second_factor_code = $code
AND expiry = 0
FOR UPDATE;
IF $SESSION_ID THEN
UPDATE base_users_sessions
SET expiry = UNIX_TIMESTAMP() + 3600
WHERE SESSION_ID = $SESSION_ID;
SET $sessionid = $provisional_id;
ELSE
DO SLEEP( $invalid - TIMESTAMPDIFF( SECOND, NOW(), $start ) );
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CREDENTIALS';
END IF;
END IF;
#
# Housekeeping
#
DELETE FROM base_users_sessions WHERE expiry < UNIX_TIMESTAMP();
IF ("" = $deviceid AND "" = $email) OR "" = $password THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CREDENTIALS';
ELSE
IF EXISTS( SELECT * FROM base_users_mobiles WHERE user_mobile_device_id=$email ) THEN
SELECT USER INTO $USER FROM base_users_mobiles WHERE user_mobile_deleted = 0 AND user_mobile_device_id = $email;
SELECT user_mobile_salt INTO $salt FROM base_users_mobiles WHERE user_mobile_deleted = 0 AND user_mobile_device_id = $email;
SELECT user_mobile_hash INTO $phash1 FROM base_users_mobiles WHERE user_mobile_deleted = 0 AND user_mobile_device_id = $email;
ELSEIF EXISTS( SELECT * FROM base_users_device_logins WHERE user_device_login_guid = $deviceid ) THEN
SELECT USER INTO $USER FROM base_users_device_logins WHERE user_device_login_deleted = 0 AND user_device_login_guid = $deviceid ORDER BY user_device_login_created DESC LIMIT 1;
SELECT user_device_login_salt INTO $salt FROM base_users_device_logins WHERE user_device_login_deleted = 0 AND user_device_login_guid = $deviceid ORDER BY user_device_login_created DESC LIMIT 1;
SELECT user_device_login_hash INTO $phash1 FROM base_users_device_logins WHERE user_device_login_deleted = 0 AND user_device_login_guid = $deviceid ORDER BY user_device_login_created DESC LIMIT 1;
ELSE
SET $USER = 0;
SELECT USER, user_salt, password_hash, user_mfa_type
INTO $USER, $salt, $phash1, $user_mfa_type
FROM base_users
WHERE user_deleted = 0
AND email = $email
ORDER BY USER
LIMIT 1;
IF IsLocalCaller() THEN
SELECT $USER;
END IF;
END IF;
#
# Check MFA policy is being respected
#
IF $user_mfa_type LIKE 'MFA%' AND "" = $sessionid THEN
DO SLEEP( $invalid - TIMESTAMPDIFF( SECOND, NOW(), $start ) );
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CREDENTIALS';
ELSE
SET $phash2 = ComputeHash( $salt, $password );
IF $phash1=$phash2 THEN
SET $authenticated = TRUE;
ELSE
SET $authenticated = FALSE;
END IF;
IF NOT $USER THEN
IF NOT EXISTS( SELECT * FROM base_users_invalid WHERE email=$email ) THEN
REPLACE INTO base_users_invalid
( email, invalid_logins, invalid_sent )
VALUES
( $email, 0, 0 );
END IF;
SELECT invalid_logins INTO $invalid FROM base_users_invalid WHERE email=$email LIMIT 1;
UPDATE base_users_invalid SET invalid_logins = invalid_logins + 1, invalid_sent=0 WHERE email=$email;
DO SLEEP( $invalid - TIMESTAMPDIFF( SECOND, NOW(), $start ) );
IF $invalid > 4 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_LOGINS';
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CREDENTIALS';
END IF;
ELSE
SELECT email, invalid_logins INTO $email, $invalid FROM base_users WHERE USER=$USER;
#
# First, if unauthenticated, update invalid_logins so that this is promplty incremented when brute-forced.
#
IF NOT $authenticated THEN
UPDATE base_users SET invalid_logins = invalid_logins + 1 WHERE USER=$USER;
END IF;
#
# Second, do annoying sleep.
#
DO SLEEP( $invalid - TIMESTAMPDIFF( SECOND, NOW(), $start ) );
#
# Third, if passed magic threshold of invalid logins fail on INVALID_LOGINS (ignore empty password).
#
IF $invalid > 4 AND "" != $password THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_LOGINS';
END IF;
#
# Fourth, if unauthenticated, fail on password.
#
IF NOT $authenticated THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CREDENTIALS';
ELSE
SELECT group_code
INTO $group_code
FROM base_groups_members
LEFT JOIN base_groups USING (GROUP_ID)
WHERE USER = $USER;
#
# Ensure unique sessionid
#
IF "" = $sessionid THEN
SET $sessionid = GenerateSalt();
WHILE EXISTS( SELECT * FROM base_users_sessions WHERE sid=$sessionid ) DO
SET $sessionid = GenerateSalt();
END WHILE;
SET $csrf = GenerateSalt();
WHILE EXISTS( SELECT * FROM base_users_sessions WHERE csrf=$csrf ) DO
SET $csrf = GenerateSalt();
END WHILE;
REPLACE INTO base_users_sessions
( sid, csrf, AUTH_USER, email, group_code, created, updated, expiry )
VALUES
( $sessionid, $csrf, $USER, $email, $group_code, NOW(), NOW(), UNIX_TIMESTAMP() + 3600 );
ELSE
UPDATE base_users_sessions
SET
group_code = $group_code,
created = NOW()
WHERE SESSION_ID = $SESSION_ID;
SELECT csrf
INTO $csrf
FROM base_users_sessions
WHERE SESSION_ID = $SESSION_ID;
END IF;
UPDATE base_users SET invalid_logins = 0, last_login=NOW(), visits = visits + 1 WHERE USER=$USER;
REPLACE INTO base_users_sessions_log
( sid, AUTH_USER, email, group_code, created, updated, expiry )
VALUES
( $sessionid, $USER, $email, $group_code, NOW(), NOW(), UNIX_TIMESTAMP() + $extend );
IF IsLocalCaller() THEN SELECT "Here #1"; END IF;
SELECT "OK", type
INTO $status, $idtype
FROM view_base_users
WHERE USER = $USER;
END IF;
END IF;
END IF;
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Sessions_Retrieve_Current
DROP PROCEDURE Base_Users_Sessions_Retrieve_Current;
DELIMITER //
CREATE PROCEDURE Base_Users_Sessions_Retrieve_Current
(
$Sid CHAR(64)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT * FROM view_base_users_sessions WHERE sessionid=$Sid;
END IF;
END
//
DELIMITER ;
Base_Users_Sessions_Terminate
DROP PROCEDURE Base_Users_Sessions_Terminate;
DELIMITER //
CREATE PROCEDURE Base_Users_Sessions_Terminate
(
$Sid CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
DELETE FROM base_users_sessions WHERE sid=$Sid;
END IF;
END
//
DELIMITER ;
Base_Users_Sessions_Verify
DROP FUNCTION Base_Users_Sessions_Verify;
DELIMITER //
CREATE FUNCTION Base_Users_Sessions_Verify
(
$Sid CHAR(64)
)
RETURNS BOOLEAN
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
DECLARE $expiry INT;
DECLARE $now INT;
DECLARE $ret BOOL DEFAULT FALSE;
SET $now = UNIX_TIMESTAMP();
SET $ret = False;
SELECT expiry INTO $expiry FROM base_users_sessions WHERE sid=$Sid;
IF $now < $expiry THEN
SET $ret = True;
END IF;
return $ret;
END
//
DELIMITER ;
Deprecated.
DROP FUNCTION Users_Sessions_Verify;
DELIMITER //
CREATE FUNCTION Users_Sessions_Verify
(
$Sid CHAR(64)
)
RETURNS BOOLEAN
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
DECLARE $expiry INT;
DECLARE $now INT;
DECLARE $ret BOOL DEFAULT FALSE;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SET $now = UNIX_TIMESTAMP();
SET $ret = False;
SELECT expiry INTO $expiry FROM base_users_sessions WHERE sid=$Sid;
IF $now < $expiry THEN
SET $ret = True;
END IF;
END IF;
return $ret;
END
//
DELIMITER ;
Base_Users_Sessions_Extend_Expiry
DROP PROCEDURE base_users_sessions_extend_expiry;
DELIMITER //
CREATE PROCEDURE base_users_sessions_extend_expiry
(
$Sid CHAR(64)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $expiry INT;
DECLARE $now INT;
DECLARE $ret BOOL;
DECLARE read_only BOOL;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT expiry
INTO $expiry
FROM base_users_sessions
WHERE sid = $Sid;
SET $now = UNIX_TIMESTAMP();
IF $now < $expiry THEN
UPDATE base_users_sessions
SET
updated = NOW(),
expiry = $now + 14400
WHERE sid = $Sid;
ELSE
CALL Base_Users_Sessions_Terminate( $Sid );
END IF;
END IF;
END
//
DELIMITER ;