Users
Users external procedures
Base_Users_Current
DROP PROCEDURE Base_Users_Current;
DELIMITER //
CREATE PROCEDURE Base_Users_Current
(
$Sid CHAR(64)
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF NOT( @idtype LIKE '%USER%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Users_Current';
ELSE
SELECT
USER,
email,
given_name,
family_name
FROM base_users WHERE USER=@USER;
END IF;
END
//
DELIMITER ;
Base_Users_Update
DROP PROCEDURE Base_Users_Update;
DELIMITER //
CREATE PROCEDURE Base_Users_Update
(
$Sid CHAR(64),
$USER INT(11),
$email CHAR(99),
$given_name CHAR(50),
$family_name CHAR(50)
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
DECLARE $old_email CHAR(99);
DECLARE $email_provisional CHAR(99);
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT @USER THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Users_Update';
ELSEIF NOT( $USER = @USER OR @idtype LIKE '%ADMIN%' ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION2 IN Base_Users_Update';
ELSE
SELECT email INTO $old_email FROM base_users WHERE USER=$USER;
SELECT email_provisional INTO $email_provisional FROM base_users WHERE USER=$USER;
IF $email != $old_email THEN
SET $email_provisional = $email;
END IF;
UPDATE base_users
SET
email_provisional = $email_provisional,
given_name = $given_name,
family_name = $family_name
WHERE USER = $USER;
END IF;
END
//
DELIMITER ;
Users internal procedures
Base_Users_Create
DROP PROCEDURE Base_Users_Create;
DELIMITER //
CREATE PROCEDURE Base_Users_Create
(
$Email CHAR(99),
$Password CHAR(99),
$Given_name CHAR(50),
$Family_name CHAR(50),
$Type CHAR(20)
)
SQL SECURITY INVOKER
BEGIN
CALL Base_Users_Create_Quietly( $Email, $Password, $Given_name, $Family_name, $Type, TRUE );
END
//
DELIMITER ;
Base_Users_Create_Quietly
This is similar to 'base_users_create' except will also set 'confirmation_sent' to now if $Send is false.
DROP PROCEDURE Base_Users_Create_Quietly;
DELIMITER //
CREATE PROCEDURE Base_Users_Create_Quietly
(
$Email CHAR(99),
$Password CHAR(99),
$Given_name CHAR(50),
$Family_name CHAR(50),
$Type CHAR(20),
$Send BOOL
)
SQL SECURITY INVOKER
BEGIN
DECLARE $USER INT DEFAULT 0;
DECLARE $COPY INT DEFAULT 0;
DECLARE $salt TEXT DEFAULT '';
DECLARE $uhash TEXT DEFAULT '';
DECLARE $phash TEXT DEFAULT '';
SET $Password = TRIM( $Password );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF "" = $Email OR EXISTS( SELECT * FROM base_users WHERE user_deleted=0 AND email=$Email ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_EMAIL';
ELSEIF NOT "" = $Password AND "WEAK" = CalculatePasswordStrength( $Password ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'WEAK_PASSWORD';
ELSEIF !Base_Users_Exists( $Email ) THEN
CALL spgen_base_users_uids_replace( $USER, $Type );
SET $USER = LAST_INSERT_ID();
SET $COPY = $USER;
IF NOT $USER THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UNEXPECTED_STATE';
ELSE
SET $salt = GenerateSalt();
SET $uhash = ComputeHash( $salt, $Email );
SET $phash = ComputeHash( $salt, $Password );
IF "" = $Password THEN
SET $phash = "";
END IF;
CALL spgen_base_users_replace
(
$COPY, # USER
'', # email
$Email, # email_provisional
'', # mobile
'', # mobile_provisional
NOW(), # created
0, # last_login
0, # invalid_logins
NULL, # user_mfa_type
$salt, # user_salt
$uhash, # user_hash
$phash, # password_hash
'UNCONFIRMED', # user_status
$Send, # send_confirmation
0, # sent
0, # confirmation_sent
0, # confirmed
0, # DELETED_BY
$Given_name, # given_name
$Family_name, # family_name
0, # visits
NOW() # ts_users
);
IF NOT $COPY THEN
SELECT "Warning Will Robertson";
END IF;
END IF;
END IF;
DO LAST_INSERT_ID( $USER );
END
//
DELIMITER ;
Base_Users_Create
DROP PROCEDURE Base_Users_Save;
DELIMITER //
CREATE PROCEDURE Base_Users_Save
(
$USER INT,
$name TEXT,
$value TEXT
)
SQL SECURITY INVOKER
BEGIN
CASE $name
WHEN 'mobile' THEN UPDATE base_users SET mobile = $value WHERE USER = $USER AND user_deleted = 0;
WHEN 'mobile_provisional' THEN UPDATE base_users SET mobile_provisional = $value WHERE USER = $USER AND user_deleted = 0;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CASE_OPTION IN Base_Users_Create';
END CASE;
END
//
DELIMITER ;
base_users_reset_password
DROP PROCEDURE Base_Users_Set_Password;
DELIMITER //
CREATE PROCEDURE base_users_Set_Password
(
$USER INT,
$new_password TEXT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $email TEXT;
DECLARE $salt TEXT;
DECLARE $uhash TEXT;
DECLARE $phash TEXT;
SET $new_password = TRIM( $new_password );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF "WEAK" = CalculatePasswordStrength( $new_password ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'WEAK_PASSWORD';
ELSE
SELECT email INTO $email FROM base_users WHERE USER=$USER;
SET $salt = GenerateSalt();
SET $uhash = ComputeHash( $salt, $email );
SET $phash = ComputeHash( $salt, $new_password );
IF "" = $new_password THEN
SET $phash = "";
END IF;
UPDATE base_users
SET
user_salt = $salt,
user_hash = $uhash,
password_hash = $phash,
invalid_logins = 0
WHERE USER = $USER;
END IF;
END
//
DELIMITER ;
base_users_reset_password
DROP PROCEDURE Base_Users_Reactivate;
DELIMITER //
CREATE PROCEDURE Base_Users_Reactivate
(
$email TEXT
)
SQL SECURITY DEFINER
COMMENT 'PUBLIC'
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT EXISTS
(
SELECT *
FROM base_users
WHERE email = $email
AND confirmed = 0
)
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_EMAIL IN Base_Users_Reactivate';
ELSE
UPDATE base_users
SET
send_confirmation = 1,
sent = 0
WHERE email = $email
AND confirmed = 0;
END IF;
END
//
DELIMITER ;
Deprecated Procedures
Base_Users_Activation_Sent
DROP PROCEDURE Base_Users_Activation_Sent;
DELIMITER //
CREATE PROCEDURE Base_Users_Activation_Sent
(
$Email CHAR(99),
$Password CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
IF Users_Vefify_Credentials( $Email, $Password ) THEN
UPDATE base_users SET sent=1 WHERE email=$Email;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION';
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Resend_Activation
DROP PROCEDURE Base_Users_Resend_Activation;
DELIMITER //
CREATE PROCEDURE Base_Users_Resend_Activation
(
$email CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $USER INT;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT USER INTO $USER FROM base_users WHERE email=$email;
IF EXISTS( SELECT * FROM base_users_activations WHERE USER=$USER ) THEN
UPDATE base_users SET sent=0 WHERE email=$email;
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Retrieve_Unsent
DROP PROCEDURE Base_Users_Retrieve_Unsent;
DELIMITER //
CREATE PROCEDURE Base_Users_Retrieve_Unsent
()
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF IsRootCaller() THEN
SELECT * FROM view_base_users WHERE sent=0;
END IF;
END
//
DELIMITER ;
Base_Users_Retrieve_Unsent_With_Names
DROP PROCEDURE Base_Users_Retrieve_Unsent_With_Names;
DELIMITER //
CREATE PROCEDURE Base_Users_Retrieve_Unsent_With_Names
()
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF IsRootCaller() THEN
SELECT * FROM view_base_users WHERE sent=0 AND NOT given_name='' AND NOT family_name='';
END IF;
END
//
DELIMITER ;
Base_Users_Update_Unsent
DROP PROCEDURE Base_Users_Update_Sent;
DELIMITER //
CREATE PROCEDURE Base_Users_Update_Sent
(
$Email CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF IsRootCaller() THEN
UPDATE base_users SET sent=1, confirmation_sent=NOW() WHERE email=$Email OR email_provisional=$Email;
END IF;
END
//
DELIMITER ;
Users Retrieve and Update
Base_Users (new)
DROP PROCEDURE Base_Users;
DELIMITER //
CREATE PROCEDURE Base_Users
(
$Sid CHAR(64),
$USER INT(11)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, @E, @U, @I );
IF 'LOCAL' = $Sid AND IsLocalCaller() AND $USER THEN
SELECT * FROM base_users WHERE USER=$USER;
ELSEIF 'LOCAL' = $Sid AND IsLocalCaller() THEN
SELECT * FROM base_users;
ELSEIF $USER = @U THEN
SELECT * FROM base_users WHERE USER=$USER;
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Retrieve
DROP PROCEDURE Base_Users_Retrieve;
DELIMITER //
CREATE PROCEDURE Base_Users_Retrieve
(
$Sid CHAR(64),
$USER INT(11)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $E CHAR(99) DEFAULT NULL;
DECLARE $U INT(11) DEFAULT NULL;
DECLARE $I CHAR(99) DEFAULT NULL;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, $E, $U, $I );
IF $USER = $U THEN
SELECT * FROM base_users WHERE USER=$USER;
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Retrieve_Single
DROP PROCEDURE Base_Users_Retrieve_Single;
DELIMITER //
CREATE PROCEDURE Base_Users_Retrieve_Single
(
$Sid CHAR(64),
$USER INT(11)
)
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 );
IF @USER = $USER THEN
SELECT * FROM base_users WHERE USER=$USER LIMIT 1;
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Retrieve_Signups
DROP PROCEDURE Base_Users_Retrieve_Signups;
DELIMITER //
CREATE PROCEDURE Base_Users_Retrieve_Signups
(
$days INT(11)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF IsRootCaller() THEN
SELECT email, sent, user_status, given_name, family_name, user_hash
FROM base_users
WHERE created > DATE_SUB( NOW(), INTERVAL $days DAY );
END IF;
END
//
DELIMITER ;
Base_Users_Update_Name
DROP PROCEDURE Base_Users_Update_Name;
DELIMITER //
CREATE PROCEDURE Base_Users_Update_Name
(
$Sid CHAR(64),
$USER INT(11),
$given_name CHAR(50),
$family_name CHAR(50)
)
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 );
IF @USER = $USER THEN
UPDATE base_users SET given_name=$given_name, family_name=$family_name WHERE USER=$USER;
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Retrieve_By_User_Hash
DROP PROCEDURE Base_Users_Retrieve_By_User_Hash;
DELIMITER //
CREATE PROCEDURE Base_Users_Retrieve_By_User_Hash
(
$sid CHAR(64),
$user_hash CHAR(64)
)
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 );
IF "" != @idtype THEN
SELECT * FROM base_users WHERE user_hash=$user_hash;
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Change_Password
DROP PROCEDURE Base_Users_Change_Password;
DELIMITER //
CREATE PROCEDURE Base_Users_Change_Password
(
$Email CHAR(99),
$OldPassword CHAR(99),
$NewPassword CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
DECLARE ret BOOL;
DECLARE salt TEXT;
DECLARE uhash TEXT;
DECLARE phash TEXT;
SET ret = False;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF "WEAK" = CalculatePasswordStrength( $NewPassword ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'WEAK_PASSWORD';
ELSE
IF base_users_verify_credentials( $Email, $OldPassword ) THEN
SET salt = GenerateSalt();
SET uhash = ComputeHash( salt, $Email );
SET phash = ComputeHash( salt, $NewPassword );
UPDATE base_users
SET user_salt=salt, user_hash=uhash, password_hash=phash
WHERE email=$Email;
SET ret = True;
END IF;
SELECT ret AS success;
END IF;
END
//
DELIMITER ;
Base_Users_Change_Password_With_USER
DROP PROCEDURE base_users_change_password_with_USER;
DELIMITER //
CREATE PROCEDURE base_users_change_password_with_USER
(
$USER INT(11),
$OldPassword CHAR(99),
$NewPassword CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $email TEXT;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT email INTO $email FROM base_users WHERE USER=$USER;
IF "" != $email THEN
CALL base_users_change_password( $email, $OldPassword, $NewPassword );
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Confirm_Account
DROP PROCEDURE base_users_confirm_account;
DELIMITER //
CREATE PROCEDURE base_users_confirm_account
(
$USER INT,
$email_provisional TEXT,
OUT $valid_email BOOL
)
SQL SECURITY INVOKER
BEGIN
SET $valid_email = FALSE;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF "" != TRIM( $email_provisional ) THEN
IF EXISTS
(
SELECT *
FROM base_users
WHERE USER = $USER
AND email_provisional = TRIM( $email_provisional )
FOR UPDATE
)
THEN
UPDATE base_users
SET
user_status = 'CONFIRMED',
email = email_provisional,
email_provisional = ''
WHERE USER = $USER
AND email_provisional = $email_provisional;
SET $valid_email = TRUE;
END IF;
END IF;
END
//
DELIMITER ;
Activations internal procedures
Users_Activations_Create
DROP PROCEDURE Base_Users_Activations_Create;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Create
(
$USER INT,
$email TEXT
)
BEGIN
DECLARE $token TEXT;
CALL Base_Users_Activations_Create_Out( $USER, $email, $token );
SELECT $token AS token;
END
//
DELIMITER ;
Users_Activations_Create
DROP PROCEDURE Base_Users_Activations_Create_Out;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Create_Out
(
$USER INT,
$email TEXT,
OUT $token TEXT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $USER_ACTIVATION_ID INT DEFAULT 0;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
IF $USER THEN
SET $token = GenerateSalt();
CALL spgen_base_users_activations_replace
(
$USER,
$USER_ACTIVATION_ID,
$email,
$token,
NOW(),
0,
0
);
END IF;
END IF;
END
//
DELIMITER ;
Users_Activations_Confirm_Account
DROP PROCEDURE Base_Users_Activations_Confirm_Account;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Confirm_Account
(
$token TEXT,
$password TEXT
)
SQL SECURITY DEFINER
COMMENT 'PUBLIC'
BEGIN
DECLARE $USER INT DEFAULT 0;
DECLARE $user_activation_email TEXT DEFAULT '';
DECLARE $valid_email BOOL DEFAULT 0;
SELECT USER, user_activation_email
INTO $USER, $user_activation_email
FROM base_users_activations
WHERE user_activation_completed = 0
AND user_activation_token = TRIM( $token );
IF @@read_only THEN
CALL Throw( 'READ_ONLY' );
ELSEIF NOT $USER THEN
CALL Warning( 'Base_Users_Activations_Confirm_Account: INVALID_TOKEN' );
ELSEIF "WEAK" = CalculatePasswordStrength( $password ) THEN
CALL Throw( 'Sorry, please entere a stronger password.' );
ELSEIF NOT "" = TRIM( $password ) THEN
#
# Confirm the account
#
CALL Base_Users_Confirm_Account( $USER, $user_activation_email, $valid_email );
IF $valid_email THEN
#
# Update password
#
CALL base_users_set_password( $USER, $password );
#
# Mark activation token as used
#
UPDATE base_users_activations
SET user_activation_completed = NOW()
WHERE user_activation_completed = 0
AND user_activation_token = $token;
END IF;
END IF;
END
//
DELIMITER ;
Users_Activations_Confirm_Account_And_Authenticate
DROP PROCEDURE Base_Users_Activations_Confirm_Account_And_Authenticate;
DELIMITER //
CREATE PROCEDURE Base_Users_Activations_Confirm_Account_And_Authenticate
(
$token CHAR(64)
)
BEGIN
DECLARE $USER INT;
DECLARE $email TEXT;
DECLARE $email_provisional TEXT;
DECLARE $sessionid TEXT;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT USER INTO $USER FROM base_users_activations WHERE user_activation_token=$token;
SET $sessionid = "";
IF 0 != $USER THEN
SELECT email, email_provisional INTO $email, $email_provisional
FROM base_users WHERE USER=$USER;
IF "" != $email_provisional THEN
SET $email = $email_provisional;
END IF;
UPDATE base_users SET email=$email, email_provisional='', user_status='CONFIRMED' WHERE USER=$USER;
DELETE FROM base_users_activations WHERE user_activation_token=$token;
SET $sessionid = MD5( concat( $token, NOW() ) );
REPLACE INTO base_users_sessions
( sid, email, created, updated, expiry )
VALUES
( $sessionid, $email, NOW(), NOW(), UNIX_TIMESTAMP() + 1000 );
END IF;
SELECT $sessionid AS sessionid;
END IF;
END
//
DELIMITER ;
Users alternate emails internal procedures
Users_Alternate_Emails_Create
DROP PROCEDURE Users_Alternate_Emails_Create;
DELIMITER //
CREATE PROCEDURE Users_Alternate_Emails_Create
(
$Sid CHAR(64),
$USER INT(11),
$email VARCHAR(99)
)
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF @USER = $USER THEN
REPLACE INTO users_alternate_emails VALUES ( $USER, $email, GenerateSalt() );
END IF;
END IF;
END
//
DELIMITER ;
Users_Alternate_Emails_Delete
DROP PROCEDURE Users_Alternate_Emails_Delete;
DELIMITER //
CREATE PROCEDURE Users_Alternate_Emails_Delete
(
$Sid CHAR(64),
$USER INT(11),
$Email VARCHAR(99)
)
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF @USER = $USER THEN
DELETE FROM users_alternate_emails WHERE USER=$USER AND email=$Email;
END IF;
END IF;
END
//
DELIMITER ;
Users_Alternate_Emails_Retrieve_By_USER
DROP PROCEDURE Users_Alternate_Emails_Retrieve_By_USER;
DELIMITER //
CREATE PROCEDURE Users_Alternate_Emails_Retrieve_By_USER
(
$Sid CHAR(64),
$USER INT(11)
)
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF @USER = $USER THEN
SELECT * FROM users_alternate_emails WHERE USER=$USER ORDER BY email;
END IF;
END IF;
END
//
DELIMITER ;
Users deleted internal procedures
Users_Delete
DROP PROCEDURE Users_Delete;
DELIMITER //
CREATE PROCEDURE Users_Delete
(
$Sid CHAR(64),
$USER INT(11)
)
BEGIN
DECLARE $email TEXT;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
IF "" != $Sid THEN
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
INSERT INTO users_deleted VALUES ( @USER, $USER );
END IF;
SELECT email INTO $email FROM users WHERE USER=$USER;
DELETE FROM users_activations WHERE USER=$USER;
DELETE FROM users_alternate_emails WHERE USER=$USER;
DELETE FROM users_send_resets WHERE USER=$USER;
DELETE FROM users_sessions WHERE email=$email;
DELETE FROM users_uids WHERE USER=$USER;
DELETE FROM users_termination_schedule WHERE USER=$USER;
DELETE FROM users WHERE USER=$USER;
END IF;
END
//
DELIMITER ;
Users mobiles internal procedures
- Base_Users_Mobiles_Replace is called when a user's mobile phone number is set.
- Base_Users_Mobiles_Enrolments_Create is called from the mobile device to enrol a phone
- Once an SMS is received, Base_Users_Mobiles_Enrolments_Confirm is called from mobile device to confirm phone.
Note: currently, as notifications is implemented in the user project, that must occur there.
DROP PROCEDURE Base_Users_Mobiles_Replace;
DELIMITER //
CREATE PROCEDURE Base_Users_Mobiles_Replace
(
$Sid CHAR(64),
$USER INT,
$user_mobile CHAR(20)
)
SQL SECURITY DEFINER
BEGIN
SET $user_mobile = Internationalise_Mobile( $user_mobile );
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF FALSE THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Users_Mobiles_Replace';
ELSE
REPLACE INTO base_users_mobiles
( USER, user_mobile, user_mobile_device_id )
VALUES
( $USER, $user_mobile, '' );
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Users_Mobiles_Enrolments_Create;
DELIMITER //
CREATE PROCEDURE Base_Users_Mobiles_Enrolments_Create
(
$user_mobile CHAR(20),
$user_mobile_device_id CHAR(255)
)
SQL SECURITY DEFINER
BEGIN
DECLARE $USER INT DEFAULT 0;
DECLARE $sms_code CHAR(4) DEFAULT '';
SET $sms_code = SUBSTRING( GenerateSalt(), 1, 4 );
SET $user_mobile = Internationalise_Mobile( $user_mobile );
SELECT USER INTO $USER FROM base_users_mobiles WHERE user_mobile = $user_mobile;
REPLACE INTO base_users_mobiles_enrolments
( USER, user_mobile, user_mobile_device_id, sms_code, sms_code_created )
VALUES
( $USER, $user_mobile, $user_mobile_device_id, $sms_code, NOW() );
END
//
DELIMITER ;
DROP PROCEDURE Base_Users_Mobiles_Enrolments_Confirm;
DELIMITER //
CREATE PROCEDURE Base_Users_Mobiles_Enrolments_Confirm
(
$user_mobile CHAR(20),
$user_mobile_device_id CHAR(255),
$sms_code CHAR(20)
)
SQL SECURITY DEFINER
BEGIN
DECLARE $USER INT DEFAULT 0;
DECLARE $verified BOOL DEFAULT 0;
DECLARE $salt CHAR(64) DEFAULT '';
DECLARE $token CHAR(64) DEFAULT '';
SET $user_mobile = Internationalise_Mobile( $user_mobile );
SELECT USER INTO $USER FROM base_users_mobiles WHERE user_mobile = $user_mobile;
SET $verified = EXISTS
(
SELECT *
FROM base_users_mobiles_enrolments
WHERE USER = $USER
AND user_mobile = $user_mobile
AND user_mobile_device_id = $user_mobile_device_id
AND sms_code = $sms_code
AND sms_code_verified = 0
);
IF NOT $verified THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_PARAMETERS IN Base_Users_Mobiles_Enrolments_Confirm';
ELSE
#
# Finalise enrolment
#
UPDATE base_users_mobiles_enrolments
SET
sms_code_verified = NOW()
WHERE USER = $USER
AND user_mobile = $user_mobile
AND user_mobile_device_id = $user_mobile_device_id;
#
# Generate mobile authentication token
#
SET $salt = GenerateSalt();
SET $token = GenerateSalt();
UPDATE base_users_mobiles
SET
user_mobile_device_id = $user_mobile_device_id,
user_mobile_salt = $salt,
user_mobile_hash = ComputeHash( $salt, $token )
WHERE USER = $USER
AND user_mobile = $user_mobile;
SELECT $token AS token;
END IF;
END
//
DELIMITER ;
Users invitations internal procedures
Users_Requested_Invites_Replace
DROP PROCEDURE Users_Requested_Invites_Replace;
DELIMITER //
CREATE PROCEDURE Users_Requested_Invites_Replace
(
$email CHAR(99)
)
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
IF NOT EXISTS( SELECT * FROM users_requested_invites WHERE email=$email ) THEN
REPLACE INTO users_requested_invites
( REQUEST, email, time_of_request, invite_sent )
VALUES ( 0, $email, NOW(), 0 );
END IF;
END IF;
END
//
DELIMITER ;
Users_Requested_Invites_Retrieve
DROP PROCEDURE Users_Requested_Invites_Retrieve;
DELIMITER //
CREATE PROCEDURE Users_Requested_Invites_Retrieve
(
$sid CHAR(64)
)
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF "SID" = @idtype THEN
IF NOT EXISTS( SELECT * FROM users_requested_invites WHERE email=@email ) THEN
SELECT * FROM users_requested_invites ORDER BY time_of_request;
END IF;
END IF;
END IF;
END
//
DELIMITER ;
Users send resets internal procedures
Base_Users_Send_Resets_Replace
DROP PROCEDURE Base_Users_Send_Resets_Replace;
DELIMITER //
CREATE PROCEDURE Base_Users_Send_Resets_Replace
(
$email CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $USER INT DEFAULT 0;
DECLARE $USER_SEND_RESET_ID INT DEFAULT 0;
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSE
SELECT USER
INTO $USER
FROM base_users
WHERE (email = $email OR email_provisional = $email)
AND user_deleted = 0
ORDER BY USER
LIMIT 1;
IF $USER THEN
CALL spgen_base_users_send_resets_replace
(
$USER,
$USER_SEND_RESET_ID,
GenerateSalt(),
NOW(),
0,
0
);
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Send_Resets_Replace
DROP PROCEDURE Base_Users_Send_Resets_Replace_OTC;
DELIMITER //
CREATE PROCEDURE Base_Users_Send_Resets_Replace_OTC
(
$mobile TEXT,
$email TEXT,
$otc TEXT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $USER INT DEFAULT 0;
DECLARE $token TEXT DEFAULT '';
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT USER
INTO $USER
FROM base_users
WHERE user_deleted = 0
AND
(
('' = $email AND email = $mobile)
OR
('' = $email AND mobile = $mobile)
OR
(email = $email AND mobile = $mobile)
)
ORDER BY USER
LIMIT 1;
IF IsLocalCaller() AND NOT $USER THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_USER';
END IF;
IF $USER THEN
REPLACE INTO base_users_send_resets VALUES ( $USER, NOW(), $otc, 0 );
CALL Base_Messaging_Create
(
$USER,
'SMS',
'0412669210',
$mobile,
'',
'',
'',
'',
'otc',
TO_BASE64
(
CONCAT_WS
(
" ",
"Minobs one-time-code",
$otc
)
),
'',
NOW()
);
END IF;
END IF;
END
//
DELIMITER ;
Base_Users_Send_Resets_Retrieve
DROP PROCEDURE Base_Users_Send_Resets_Retrieve;
DELIMITER //
CREATE PROCEDURE Base_Users_Send_Resets_Retrieve
()
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT * FROM base_users_send_resets LEFT JOIN users USING (USER) WHERE users_send_resets.sent=0;
END IF;
END
//
DELIMITER ;
Base_Users_Send_Resets_Sent
DROP PROCEDURE Base_Users_Send_Resets_Sent;
DELIMITER //
CREATE PROCEDURE Base_Users_Send_Resets_Sent
(
$USER_SEND_RESET_ID INT
)
SQL SECURITY INVOKER
BEGIN
UPDATE base_users_send_resets
SET user_send_reset_sent = NOW()
WHERE USER_SEND_RESET_ID = $USER_SEND_RESET_ID;
END
//
DELIMITER ;
Base_Users_Send_Resets_Reset_Password
DROP PROCEDURE Base_Users_Send_Resets_Reset_Password;
DELIMITER //
CREATE PROCEDURE Base_Users_Send_Resets_Reset_Password
(
$user_send_reset_token CHAR(64),
$password CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
SET $password = TRIM( $password );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF "WEAK" = CalculatePasswordStrength( $password ) THEN
CALL Info( 'WEAK_PASSWORD' );
ELSE BEGIN
DECLARE $USER INT DEFAULT 0;
SELECT USER
INTO $USER
FROM base_users_send_resets
WHERE user_send_reset_completed = 0
AND user_send_reset_token = $user_send_reset_token
LIMIT 1;
IF NOT $USER THEN
CALL Info( 'INVALID_TOKEN' );
ELSE BEGIN
DECLARE $email TEXT DEFAULT '';
DECLARE $salt TEXT DEFAULT '';
DECLARE $uhash TEXT DEFAULT '';
DECLARE $phash TEXT DEFAULT '';
SELECT IF( NOT Empty( email_provisional ), email_provisional, email )
INTO $email
FROM base_users
WHERE user_deleted = 0
AND USER = $USER;
IF LENGTH( TRIM( $email ) ) THEN
SET $salt = GenerateSalt();
SET $uhash = ComputeHash( $salt, $email );
SET $phash = ComputeHash( $salt, $password );
IF Empty( $password ) THEN
SET $phash = "";
END IF;
UPDATE base_users
SET
email = $email,
email_provisional = "",
user_salt = $salt,
user_hash = $uhash,
password_hash = $phash,
invalid_logins = 0
WHERE USER = $USER;
UPDATE base_users_send_resets
SET user_send_reset_completed = NOW()
WHERE user_send_reset_token = $user_send_reset_token;
END IF;
END; END IF;
END; END IF;
END
//
DELIMITER ;
Base_Users_Send_Resets_Reset_Password_OTC
DROP PROCEDURE Base_Users_Send_Resets_Reset_Password_OTC;
DELIMITER //
CREATE PROCEDURE Base_Users_Send_Resets_Reset_Password_OTC
(
$token CHAR(64),
$deviceid TEXT,
$password TEXT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $USER INT;
DECLARE $email TEXT;
DECLARE $salt TEXT;
DECLARE $uhash TEXT;
DECLARE $phash TEXT;
SET $deviceid = TRIM( $deviceid );
SET $password = TRIM( $password );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF "WEAK" = CalculatePasswordStrength( $password ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'WEAK_PASSWORD';
ELSE
SELECT USER INTO $USER FROM base_users_send_resets WHERE token=$token;
IF NOT $USER THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_TOKEN';
ELSE
SELECT email INTO $email FROM base_users WHERE USER=$USER;
SET $salt = GenerateSalt();
SET $uhash = ComputeHash( $salt, $email );
SET $phash = ComputeHash( $salt, $Password );
IF $password = "" THEN
SET $phash = "";
END IF;
IF "" = $deviceid THEN
UPDATE base_users
SET user_salt=$salt, user_hash=$uhash, password_hash=$phash, invalid_logins=0
WHERE USER=$USER;
ELSE
CALL Base_Users_Device_Logins_Replace
(
$USER,
0,
$deviceid,
$salt,
$phash
);
END IF;
DELETE FROM base_users_send_resets WHERE token=$token;
END IF;
END IF;
END
//
DELIMITER ;
Users send resets event schedule
Base_Users_Send_Resets_Replace
DROP PROCEDURE Base_Users_Send_Resets_Auto_Message;
DELIMITER //
CREATE PROCEDURE Base_Users_Send_Resets_Auto_Message
()
SQL SECURITY INVOKER
BEGIN
DECLARE $base_url TEXT DEFAULT 0;
DECLARE $template TEXT DEFAULT '';
DECLARE $cur CURSOR FOR
SELECT
USER,
USER_SEND_RESET_ID,
user_send_reset_token,
given_name,
IF( NOT Empty( email_provisional ), email_provisional, email ) AS email
FROM base_users_send_resets
LEFT JOIN base_users USING (USER)
WHERE user_deleted = 0
AND user_send_reset_deleted = 0
AND user_send_reset_sent = 0
AND user_send_reset_send < NOW();
DECLARE CONTINUE HANDLER FOR NOT FOUND #
BEGIN # Handles no data error when fetch
BEGIN END; # runs out of tuples.
END; #
SELECT base_url
INTO $base_url
FROM base
WHERE base_deleted = 0
ORDER BY BASE_ID
LIMIT 1;
SET $template = Base_Templates_Retrieve_Txt( 'PW.Reset' );
IF NOT Empty( $base_url ) AND NOT Empty( $template ) THEN
OPEN $cur; BEGIN
DECLARE $USER INT;
DECLARE $USER_SEND_RESET_ID INT;
DECLARE $user_send_reset_token TEXT;
DECLARE $given_name TEXT;
DECLARE $email TEXT;
DECLARE $content TEXT;
DECLARE $MESSAGE_ID INT;
DECLARE $URL TEXT;
REPEAT
SET $USER_SEND_RESET_ID = 0;
FETCH $cur INTO $USER, $USER_SEND_RESET_ID, $user_send_reset_token, $given_name, $email;
IF $USER_SEND_RESET_ID THEN
SET $URL = CONCAT( 'https://', $base_url, '/redirect/change_password/?token=', $user_send_reset_token );
SET $content = $template;
SET $content = REPLACE( $content, '%given_name%', $given_name );
SET $content = REPLACE( $content, '%URL%', $URL );
SET $MESSAGE_ID = 0;
CALL Base_Messaging_Create
(
$USER,
'EMAIL',
CONCAT_WS( '@', 'noreply', $base_url ),
$email,
'', # $message_cc
'', # $message_bcc
'', # $message_reply_to
'Password Reset', # $message_subject
'RESET', # $message_tags
TO_BASE64( $content ), # $message_content_txt64
'', # $message_content_htm64
NOW() # $message_send_at
);
SET $MESSAGE_ID = LAST_INSERT_ID();
IF $MESSAGE_ID THEN
CALL Base_Users_Send_Resets_Sent( $USER_SEND_RESET_ID );
END IF;
END IF;
UNTIL NOT $USER_SEND_RESET_ID END REPEAT;
END; CLOSE $cur;
END IF;
END
//
DELIMITER ;
DROP EVENT Base_Users_Send_Resets_Auto_Message;
CREATE EVENT Base_Users_Send_Resets_Auto_Message ON SCHEDULE EVERY 1 MINUTE DISABLE DO CALL Base_Users_Send_Resets_Auto_Message;
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 INVOKER
COMMENT 'EXPORTABLE'
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 ;
Base_Users_Authenticate
DROP PROCEDURE Base_Users_Authenticate;
DELIMITER //
CREATE PROCEDURE Base_Users_Authenticate
(
$Sid TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
IF @@read_only THEN
CALL Throw( 'READ_ONLY' );
ELSEIF NOT Base_Users_Sessions_Verify( $Sid ) THEN
CALL Base_Users_Sessions_Terminate( $Sid );
CALL Notice( 'Base_Users_Authenticate: INVALID_SESSION' );
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 ;
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
COMMENT 'EXPORTABLE'
BEGIN
IF @@read_only THEN
CALL Throw( 'READ_ONLY' );
ELSE
CALL Base_Users_Authorise_Sessionid( $Sid, $Email, $USER, $IDType );
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
CALL Throw( '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 ) );
CALL Throw( 'INVALID_CREDENTIALS' );
END IF;
END IF;
#
# Housekeeping
#
DELETE FROM base_users_sessions WHERE expiry < UNIX_TIMESTAMP();
IF ("" = $deviceid AND "" = $email) OR "" = $password THEN
CALL Throw( '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 ) );
CALL Throw( '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
CALL Throw( 'INVALID_LOGINS' );
ELSE
CALL Throw( '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
CALL Throw( 'INVALID_LOGINS' );
END IF;
#
# Fourth, if unauthenticated, fail on password.
#
IF NOT $authenticated THEN
CALL Throw( '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
CALL Throw( '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
CALL Throw( 'READ_ONLY' );
ELSE
DELETE FROM base_users_sessions WHERE sid=$Sid;
END IF;
END
//
DELIMITER ;
Base_Users_Sessions_Extend_Expiry
DROP PROCEDURE Base_Users_Sessions_Extend_Expiry;
DELIMITER //
CREATE PROCEDURE Base_Users_Sessions_Extend_Expiry
(
$Sid TEXT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $expiry INT;
DECLARE $now INT;
DECLARE $ret BOOL;
DECLARE read_only BOOL;
IF @@read_only THEN
CALL Throw( '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 ;
Users termination scheudle internal procedures
Base_Users_Termination_Schedule_Replace
DROP PROCEDURE Base_Users_Termination_Schedule_Replace;
DELIMITER //
CREATE PROCEDURE Base_Users_Termination_Schedule_Replace
(
$Sid CHAR(64),
$USER INT(11),
$password CHAR(99)
)
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SET @success = 0;
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF @USER = $USER THEN
IF ( base_users_verify_credentials( @email, $password ) ) THEN
REPLACE INTO base_users_termination_schedule
( USER, mark, time_of_termination )
VALUES
( $USER, NOW(), date_add( NOW(), INTERVAL 1 DAY ) );
SET @success = 1;
END IF;
END IF;
SELECT @success AS success;
END IF;
END
//
DELIMITER ;
Base_Users_Termination_Schedule_Retrieve
DROP PROCEDURE Base_Users_Termination_Schedule_Retrieve;
DELIMITER //
CREATE PROCEDURE Base_Users_Termination_Schedule_Retrieve
()
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
SELECT USER, time_of_termination, email
FROM base_users_termination_schedule LEFT JOIN base_users USING (USER)
WHERE NOW() > time_of_termination;
END IF;
END
//
DELIMITER ;
Users Tokens internal procedures
Definer Procedures
DROP PROCEDURE Base_Users_Tokens_Sent;
DELIMITER //
CREATE PROCEDURE Base_Users_Tokens_Sent
(
$token CHAR(64)
)
SQL SECURITY DEFINER
BEGIN
UPDATE base_users_tokens SET token_sent = NOW() WHERE token = $token AND 0 = token_sent;
END
//
DELIMITER ;
DROP PROCEDURE Base_Users_Tokens_Used;
DELIMITER //
CREATE PROCEDURE Base_Users_Tokens_Used
(
$token CHAR(64)
)
SQL SECURITY DEFINER
BEGIN
UPDATE base_users_tokens SET token_used = NOW() WHERE token = $token AND 0 = token_used AND NOW() <= token_expiry;
END
//
DELIMITER ;
Invoker Procedures
DROP PROCEDURE Base_Users_Tokens_Create;
DELIMITER //
CREATE PROCEDURE Base_Users_Tokens_Create
(
$token_type CHAR(50),
$USER INT,
$token_expiry_days INT,
OUT $token CHAR(64)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $token_expiry DATETIME DEFAULT 0;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT( IsEventScheduler() OR IsRootCaller() ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Users_Tokens_Create';
ELSE
#
# Ensure unique token.
#
SET $token = GenerateSalt();
WHILE EXISTS( SELECT * FROM base_users_tokens WHERE token=$token ) DO
SET $token = GenerateSalt();
END WHILE;
#
# Set 3 day token expiry
#
SET $token_expiry = DATE_ADD( NOW(), INTERVAL $token_expiry_days DAY );
REPLACE INTO base_users_tokens
( TOKEN_ID, USER, token_created, token_expiry_days, token_expiry, token_type, token )
VALUES
( 0, $USER, NOW(), $token_expiry_days, $token_expiry, $token_type, $token );
END IF;
END
//
DELIMITER ;
Users uids internal procedures
base_users_uid_create
DROP PROCEDURE base_users_uid_create;
DELIMITER //
CREATE PROCEDURE base_users_uid_create( $Type VARCHAR(20) )
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
INSERT INTO base_users_uids (type) VALUES ( $Type );
SELECT LAST_INSERT_ID() AS USER;
END IF;
END
//
DELIMITER ;