Auth
Auth exported procedures
DROP PROCEDURE Auth_Login;
DELIMITER //
CREATE PROCEDURE Auth_Login
(
$apikey TEXT,
$deviceid TEXT,
$auth_org_name TEXT,
$username TEXT,
$password TEXT,
$provisional_id TEXT,
$code TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $status TEXT DEFAULT '';
DECLARE $sessionid TEXT DEFAULT '';
DECLARE $idtype TEXT DEFAULT '';
DECLARE $USER INT DEFAULT 0;
SET $username = TranslateToOrgUsername( $username, $auth_org_name );
CALL _Base_Users_Sessions_Replace_w_DeviceID_Inout
(
TRIM( $deviceid ),
TRIM( $username ),
TRIM( $password ),
TRIM( $provisional_id ),
TRIM( $code ),
$status, # INOUT
$sessionid, # INOUT
$USER, # INOUT
$idtype # INOUT
);
IF NOT '' = TRIM( $apikey ) AND EXISTS( SELECT * FROM base_apikeys WHERE $apikey LIKE CONCAT( apikey, '%' ) ) THEN
#
# Disable CSRF checks because is being called from
# a mobile (or other) device that does not need CSRF Token.
#
UPDATE base_users_sessions SET csrf='' WHERE sid = $sessionid;
END IF;
SELECT
email,
given_name,
$status AS status,
$sessionid AS sessionid,
SUBSTRING( $sessionid, 1, 32 ) AS accessid,
$idtype AS idtype,
IFNULL( group_code, '' ) AS group_code
FROM base_users
LEFT JOIN base_groups_members USING (USER)
LEFT JOIN base_groups USING (GROUP_ID)
WHERE user_deleted = 0
AND USER = $USER;
END
//
DELIMITER ;
DROP PROCEDURE Auth_Login_Local;
DELIMITER //
CREATE PROCEDURE Auth_Login_Local
(
$apikey TEXT,
$deviceid TEXT,
$username TEXT,
$password TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $status TEXT DEFAULT '';
DECLARE $sessionid TEXT DEFAULT '';
DECLARE $idtype TEXT DEFAULT '';
DECLARE $USER INT DEFAULT 0;
CALL _Base_Users_Sessions_Replace_w_DeviceID_Inout
(
TRIM( $deviceid ),
TRIM( $username ),
$password,
$status, # IN OUT
$sessionid, # IN OUT
$USER, # IN OUT
$idtype # IN OUT
);
UPDATE base_users_sessions SET csrf='' WHERE sid = $sessionid;
SELECT
email,
given_name,
$status AS status,
$sessionid AS sessionid,
SUBSTRING( $sessionid, 1, 32 ) AS accessid,
$idtype AS idtype,
IFNULL( group_code, '' ) AS group_code
FROM base_users
LEFT JOIN base_groups_members USING (USER)
LEFT JOIN base_groups USING (GROUP_ID)
WHERE user_deleted = 0
AND USER = $USER;
END
//
DELIMITER ;
This procedure allows authentication (and authorisation) by using a one-time-code. When completed the one-time-code is used to reset the password to a random string, which is passed back to the authenticating device to perform login.
DROP PROCEDURE Auth_OTC;
DELIMITER //
CREATE PROCEDURE Auth_OTC
(
$mobile TEXT,
$email TEXT,
$deviceid TEXT,
$otc TEXT,
$generate INT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $count INT DEFAULT 0;
SET $mobile = TRIM( $mobile );
SET $deviceid = TRIM( $deviceid );
SET $email = TRIM( $email );
IF $generate THEN
SET $otc = RandomNumber( 9999 ); # Create 4 number one-time-code.
IF "" = $email THEN
SELECT COUNT(*) INTO $count
FROM base_users
WHERE email = $mobile OR mobile = $mobile
AND user_deleted = 0;
END IF;
IF $count > 1 THEN
CALL Error( 'Auth_OTC: TOO_MANY_ADDRESSES' );
ELSE
CALL Base_Users_Send_Resets_Replace_OTC( $mobile, $email, $otc );
END IF;
ELSEIF "" = TRIM( $otc ) THEN
CALL Notice( 'Auth_OTC: INVALID_OTC' );
ELSE
BEGIN
DECLARE $username TEXT DEFAULT '';
DECLARE $password TEXT DEFAULT '';
SELECT email
INTO $username
FROM base_users
WHERE USER = Base_Users_Send_Resets_Get_User( $otc )
AND user_deleted = 0;
SET $password = GenerateSalt();
CALL Base_Users_Send_Resets_Reset_Password_OTC( $otc, $deviceid, $password );
SELECT $username AS username, $password AS password;
END;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Auth_Session;
DELIMITER //
CREATE PROCEDURE Auth_Session
(
$Sid TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $Rid TEXT DEFAULT '';
SET $Rid = Base_Users_Sessions_Resolve_Sid( $Sid );
IF "" = $Rid AND NOT "" = TRIM( $Sid ) THEN
CALL Notice( 'Auth_Session: INVALID_SESSIONID' );
ELSE
CALL Base_Users_Sessions_Extend_Expiry( $Rid );
SELECT
view_base_users.email,
view_base_users.given_name,
view_base_users.family_name,
view_base_users.user_hash,
view_base_users.type AS idtype,
@@read_only AS read_only
FROM base_users_sessions
LEFT JOIN view_base_users ON (AUTH_USER=USER)
WHERE sid = $Rid
AND user_deleted = 0
LIMIT 1;
END IF;
END
//
DELIMITER ;
The passed 'apikey' must now have a apikey type of 'ACCESS'.
DROP PROCEDURE Auth_Access;
DELIMITER //
CREATE PROCEDURE Auth_Access
(
$apikey TEXT,
$server_name CHAR(99),
$remote_ip CHAR(45),
$Aid CHAR(32)
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $access TEXT DEFAULT '';
DECLARE $connection_csrf_token TEXT DEFAULT '';
DECLARE $group_code TEXT DEFAULT '';
DECLARE $idtype TEXT DEFAULT '';
DECLARE $keys TEXT DEFAULT '';
DECLARE $USER INT DEFAULT 0;
DECLARE $expiry INT DEFAULT 0;
CALL _Base_Users_Authorise_APIKey_Type( 'ACCESS', $apikey, @email, @idtype, @apikey_type, @USER, @ORG_ID, @PROJECT_ID );
IF NOT 'ACCESS' = @apikey_type THEN
CALL Error( 'Auth_Access: INVALID_APIKEY' );
ELSE
CALL _Base_Web_Connections_Create_Out
(
$server_name,
$remote_ip,
$connection_csrf_token
);
END IF;
IF "" != $Aid THEN
SELECT expiry, AUTH_USER, group_code
INTO $expiry, $USER, $group_code
FROM base_users_sessions
WHERE SUBSTRING( sid, 1, 32 ) = $Aid;
END IF;
IF $USER AND UNIX_TIMESTAMP() < $expiry THEN
SET $access = "PERMITTED";
SELECT type
INTO $idtype
FROM base_users_uids
WHERE USER = $USER;
SET $keys = CONCAT_WS( '-', OrNull( $idtype ), OrNull( $group_code ) );
ELSE
SET $access = "DENIED";
END IF;
SELECT
$access AS access,
$connection_csrf_token AS CSRF_Token,
$keys AS Access_Keys;
END
//
DELIMITER ;
DROP PROCEDURE Auth_Logout;
DELIMITER //
CREATE PROCEDURE Auth_Logout
(
$Sid TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $AUTH_USER INT DEFAULT 0;
DECLARE $Rid TEXT DEFAULT '';
SET $Rid = Base_Users_Sessions_Resolve_Sid( $Sid );
SELECT AUTH_USER
INTO $AUTH_USER
FROM base_users_sessions
WHERE sid = $Rid
ORDER BY created
LIMIT 1;
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT $AUTH_USER THEN
CALL Warning( 'Auth_Logout: INVALID_PARAMETERS' );
ELSE
CALL Base_Users_Sessions_Terminate( $Rid );
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Accounts_Logout_All;
DELIMITER //
CREATE PROCEDURE Accounts_Logout_All
(
$Sid TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $AUTH_USER INT DEFAULT 0;
SET $AUTH_USER = Base_Users_Sessions_Resolve_User( $Sid );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT $AUTH_USER THEN
CALL Warning( 'Accounts_Logout_All: INVALID_PARAMETERS' );
ELSE
DELETE
FROM base_users_sessions
WHERE AUTH_USER = $AUTH_USER;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Accounts_Logout_Other;
DELIMITER //
CREATE PROCEDURE Accounts_Logout_Other
(
$Sid CHAR(64)
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $AUTH_USER INT DEFAULT 0;
SET $AUTH_USER = Base_Users_Sessions_Resolve_User( $Sid );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT $AUTH_USER THEN
CALL Warning( 'Accounts_Logout_All: INVALID_PARAMETERS' );
ELSE
DELETE
FROM base_users_sessions
WHERE AUTH_USER = $AUTH_USER
AND NOT sid = $Sid;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Auth_OAuth2_token;
DELIMITER //
CREATE PROCEDURE Auth_OAuth2_token
(
$client_id TEXT,
$client_secret TEXT,
$scope TEXT,
$remote_ip TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $connection_csrf_token TEXT DEFAULT '';
DECLARE $apikey TEXT DEFAULT '';
SELECT apikey
INTO $apikey
FROM base_apikeys
WHERE APIKEY_ID = 1
AND USER_OWNER = 1;
CALL _Base_Users_Sessions_Replace_w_DeviceID_Inout
(
'',
TRIM( $client_id ),
$client_secret,
@status, # IN OUT
@sessionid, # IN OUT
@USER, # IN OUT
@idtype # IN OUT
);
CALL _Base_Web_Connections_Create_Out
(
$apikey,
$scope,
$remote_ip,
$connection_csrf_token
);
SELECT CONCAT( SUBSTRING( @sessionid, 1, 32 ), SUBSTRING( $connection_csrf_token, 1, 32 ) ) AS access_token;
END
//
DELIMITER ;
Auth Negotiate Session
DROP PROCEDURE Auth_Negotiate_Session;
DELIMITER //
CREATE PROCEDURE Auth_Negotiate_Session
(
$apikey TEXT,
$auth_org_name TEXT,
$auth_service TEXT,
$auth_action TEXT,
$auth_login_url TEXT,
$username TEXT,
$password TEXT,
$wab_csrf_token TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $use_csrf BOOL DEFAULT 0;
CALL _Base_Users_Authorise_APIKey( $apikey, @email, @idtype, @apikey_type, @USER, @ORG_ID, @PROJECT_ID );
SET $use_csrf = Base_Web_Connections_Verify( $wab_csrf_token );
IF NOT( "AUTH" = @apikey_type AND "admin" = @email ) THEN
CALL Error( "Auth_Negotiate_Session: INVALID_APIKEY" );
ELSEIF NOT( Base_Web_Connections_Verify( $wab_csrf_token ) OR Authorise_App_APIKey( $wab_csrf_token, 'WEB' ) ) THEN
CALL Notice( "Auth_Negotiate_Session: INVALID_CSRF_TOKEN" );
ELSEIF "SSO" = $auth_action THEN
CALL _Auth_Negotiate_Oauth_Session
(
$auth_org_name,
$auth_service,
$auth_action,
$auth_login_url,
$username,
$use_csrf
);
ELSE
CALL _Auth_Negotiate_Session
(
$auth_org_name,
$auth_service,
$auth_action,
$username,
$password,
$use_csrf
);
END IF;
END
//
DELIMITER ;
Auth Oauth Session Conform
DROP PROCEDURE Auth_Oauth_Session_Confirm;
DELIMITER //
CREATE PROCEDURE Auth_Oauth_Session_Confirm
(
$apikey TEXT,
$provisional_sid TEXT,
$email TEXT,
$email2 TEXT,
$given_name TEXT,
$family_name TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $USER INT DEFAULT 0;
DECLARE $auth_org_name TEXT DEFAULT '';
DECLARE $SESSION_ID INT DEFAULT 0;
DECLARE $username TEXT DEFAULT '';
CALL _Base_Users_Authorise_APIKey( $apikey, @email, @idtype, @apikey_type, @USER, @ORG_ID, @PROJECT_ID );
IF NOT( "AUTH" = @apikey_type AND "admin" = @email ) THEN
CALL Notice( "Auth_Oauth_Session_Confirm: INVALID_APIKEY" );
ELSE
SELECT AUTH_USER
INTO $USER
FROM base_users_sessions
WHERE sid = $provisional_sid
AND expiry = 0
ORDER BY SESSION_ID DESC
LIMIT 1;
IF $USER THEN
SELECT group_code
INTO $auth_org_name
FROM base_groups_members
LEFT JOIN base_groups USING (GROUP_ID)
WHERE USER = $USER;
IF "" = TRIM( $auth_org_name ) THEN
SELECT org_code
INTO $auth_org_name
FROM base_organisations_users
LEFT JOIN base_organisations USING (ORG_ID)
WHERE USER = $USER;
END IF;
END IF;
IF "" != TRIM( $auth_org_name ) THEN
SET $username = TranslateToOrgUsername( $email, $auth_org_name );
SELECT SESSION_ID
INTO $SESSION_ID
FROM base_users_sessions
WHERE sid = $provisional_sid
AND email = $username
AND expiry = 0
ORDER BY SESSION_ID DESC
LIMIT 1;
END IF;
IF NOT $SESSION_ID THEN
IF EXISTS
(
SELECT *
FROM base_users_sessions
WHERE sid = $provisional_sid
AND expiry = 0
)
THEN
CALL Throw( "Sorry, you are logged into an account that differs from the email address suppled." );
ELSE
CALL Throw( "Sorry, are you doing something naughty?" );
END IF;
ELSE
UPDATE base_users_sessions
SET expiry = UNIX_TIMESTAMP() + 3600
WHERE SESSION_ID = $SESSION_ID;
SELECT
accessid,
email,
given_name,
group_code,
idtype,
sessionid,
"OK" AS status
FROM view_base_users_sessions
WHERE SESSION_ID = $SESSION_ID;
END IF;
END IF;
END
//
DELIMITER ;
Auth Internal Procedures
DROP PROCEDURE _Auth_Negotiate_Session;
DELIMITER //
CREATE PROCEDURE _Auth_Negotiate_Session
(
$auth_org_name TEXT,
$auth_service TEXT,
$auth_action TEXT,
$username TEXT,
$password TEXT,
$use_csrf BOOL
)
SQL SECURITY INVOKER
BEGIN
DECLARE $SESSION_ID INT DEFAULT 0;
DECLARE $USER INT DEFAULT 0;
DECLARE $user_mfa_type CHAR(20) DEFAULT "";
DECLARE $mobile CHAR(20) DEFAULT "";
DECLARE $code CHAR(20) DEFAULT "";
DECLARE $text64 TEXT DEFAULT "";
DECLARE $csrf TEXT DEFAULT "";
IF $use_csrf THEN
SET $csrf = GenerateSalt();
END IF;
SET $username = TranslateToOrgUsername( $username, $auth_org_name );
SELECT USER, user_mfa_type, mobile
INTO $USER, $user_mfa_type, $mobile
FROM base_users
WHERE user_deleted = 0
AND email = $username
ORDER BY USER
LIMIT 1;
IF NOT Base_Users_Verify_Credentials( $username, $password ) THEN
CALL Throw( "_Auth_Negotiate_Session: INVALID_CREDENTIALS" );
ELSEIF NOT $USER THEN
CALL Throw( "_Auth_Negotiate_Session: INVALID_USER" );
ELSEIF Is_Empty( $user_mfa_type ) THEN
SELECT
$auth_org_name AS auth_org_name,
$auth_service AS auth_service,
'PASSWORD' AS auth_action,
'' AS user_mfa_type,
'' AS username,
'' AS mobile,
'' AS code,
'' AS provisional_id;
ELSE
IF $user_mfa_type LIKE '%SMS%' AND "Valid Australian mobile number" != ValidateAustralianMobile( $mobile ) THEN
CALL Throw( "_Auth_Negotiate_Session: INVALID_MOBILE" );
ELSE
SET $code = RandomNumber( 99999 );
DO LAST_INSERT_ID( 0 );
REPLACE INTO base_users_sessions
(
sid,
csrf,
AUTH_USER,
email,
group_code,
second_factor_code
)
VALUES
(
GenerateSalt(),
$csrf,
$USER,
$username,
'',
$code
);
SET $SESSION_ID = LAST_INSERT_ID();
SET $text64 = TO_BASE64( CONCAT( "Your login code is: ", $code ) );
SELECT
$auth_org_name AS auth_org_name,
$auth_service AS auth_service,
$auth_action AS auth_action,
$user_mfa_type AS user_mfa_type,
$username AS username,
$mobile AS mobile,
$code AS code,
sid AS provisional_id
FROM base_users_sessions
WHERE SESSION_ID = $SESSION_ID;
END IF;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Auth_Negotiate_Oauth_Session;
DELIMITER //
CREATE PROCEDURE _Auth_Negotiate_Oauth_Session
(
$auth_org_name TEXT,
$auth_service TEXT,
$auth_action TEXT,
$auth_login_url TEXT,
$username TEXT,
$use_csrf BOOL
)
SQL SECURITY INVOKER
BEGIN
DECLARE $SESSION_ID INT DEFAULT 0;
DECLARE $USER INT DEFAULT 0;
DECLARE $user_mfa_type CHAR(20) DEFAULT "";
DECLARE $mobile CHAR(20) DEFAULT "";
DECLARE $code CHAR(20) DEFAULT "";
DECLARE $text64 TEXT DEFAULT "";
DECLARE $csrf TEXT DEFAULT "";
IF $use_csrf THEN
SET $csrf = GenerateSalt();
END IF;
SET $username = TranslateToOrgUsername( $username, $auth_org_name );
SELECT USER, user_mfa_type, mobile
INTO $USER, $user_mfa_type, $mobile
FROM base_users
WHERE user_deleted = 0
AND email = $username
ORDER BY USER
LIMIT 1;
IF NOT $USER THEN
CALL Throw( "_Auth_Negotiate_Session: INVALID_USER" );
ELSEIF Is_Empty( $user_mfa_type ) THEN
SELECT
$auth_org_name AS auth_org_name,
$auth_service AS auth_service,
'PASSWORD' AS auth_action,
'' AS user_mfa_type,
'' AS username,
'' AS mobile,
'' AS code,
'' AS provisional_id;
ELSE
DO LAST_INSERT_ID( 0 );
REPLACE INTO base_users_sessions
(
sid,
csrf,
AUTH_USER,
email,
group_code,
second_factor_code
)
VALUES
(
GenerateSalt(),
$csrf,
$USER,
$username,
'',
''
);
SET $SESSION_ID = LAST_INSERT_ID();
SELECT
$auth_login_url AS auth_login_url,
$auth_org_name AS auth_org_name,
$auth_service AS auth_service,
$auth_action AS auth_action,
$user_mfa_type AS user_mfa_type,
$username AS username,
$mobile AS mobile,
$code AS code,
sid AS provisional_id
FROM base_users_sessions
WHERE SESSION_ID = $SESSION_ID;
END IF;
END
//
DELIMITER ;
Authorisation
The following procedures are called by exported stored procedures to ensure that callers are appropriately authorised.
DROP PROCEDURE _Base_Users_Authorise_SessionID;
DELIMITER //
CREATE PROCEDURE _Base_Users_Authorise_SessionID
(
$Xid CHAR(64),
OUT $Email CHAR(99),
OUT $USER INT(11),
OUT $IDType VARCHAR(20)
)
SQL SECURITY INVOKER
BEGIN
DECLARE $sid CHAR(64) DEFAULT 0;
SET $sid = Base_Users_Sessions_Resolve_Sid( $Xid );
SET $Email = "";
SET $USER = "";
SET $IDType = "";
IF Base_Users_Sessions_Verify( $sid ) THEN
IF NOT @@read_only THEN
CALL Base_Users_Sessions_Extend_Expiry( $sid );
END IF;
SELECT AUTH_USER INTO $USER FROM base_users_sessions WHERE sid = $sid;
SELECT email INTO $Email FROM base_users WHERE USER = $USER;
SELECT type INTO $IDType FROM base_users_uids WHERE USER = $USER;
IF $Email IS NULL THEN
SET $Email = "";
END IF;
IF $IDType IS NULL THEN
SET $IDType = "";
END IF;
ELSE
CALL Base_Users_Sessions_Terminate( $sid );
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Base_Users_Authorise_Sessionid_Or_APIKey;
DELIMITER //
CREATE PROCEDURE _Base_Users_Authorise_Sessionid_Or_APIKey
(
$Sid CHAR(64),
$apikey_at_ip TEXT,
OUT $email TEXT,
OUT $USER INT,
OUT $idtype TEXT
)
SQL SECURITY INVOKER
BEGIN
SET $email = "";
SET $USER = 0;
SET $idtype = "";
IF "" != TRIM( $Sid ) THEN
CALL _Base_Users_Authorise_Sessionid( $Sid, $email, $USER, $idtype );
END IF;
#
# Only lookup API key if:
#
# 1) above has returned $USER = 0,
# 2) an apikey_at_ip has been passed
#
IF 0 = $USER AND "" != TRIM( $apikey_at_ip ) THEN
BEGIN
DECLARE $apikey TEXT DEFAULT '';
DECLARE $ip_address TEXT DEFAULT '';
SET $apikey = SUBSTRING_INDEX( $apikey_at_ip, '@', 1 ); # Extract apikey
SET $ip_address = SUBSTRING_INDEX( $apikey_at_ip, '@', -1 ); # Extract ip address
SELECT
email,
USER_OWNER,
type
INTO
$email,
$USER,
$idtype
FROM base_apikeys
LEFT JOIN view_base_users ON (USER_OWNER=USER)
WHERE apikey = $apikey
AND (NOT IsNotEmpty(ip_address) OR ip_address = $ip_address);
END;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Base_Users_Authorise_Sessionid_And_APIKey;
DELIMITER //
CREATE PROCEDURE _Base_Users_Authorise_Sessionid_And_APIKey
(
$Sid CHAR(64),
$apikey_at_ip TEXT,
OUT $email TEXT,
OUT $USER INT,
OUT $idtype TEXT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $USER_OWNER INT DEFAULT 0;
DECLARE $apikey TEXT DEFAULT '';
DECLARE $ip_address TEXT DEFAULT '';
SET $email = "";
SET $USER = 0;
SET $idtype = "";
SET $apikey = SUBSTRING_INDEX( $apikey_at_ip, '@', 1 ); # Extract apikey
SET $ip_address = SUBSTRING_INDEX( $apikey_at_ip, '@', -1 ); # Extract ip address
SELECT USER_OWNER
INTO $USER_OWNER
FROM base_apikeys
WHERE apikey=$apikey
AND (NOT IsNotEmpty(ip_address) OR ip_address = $ip_address);
IF $USER_OWNER THEN
CALL _Base_Users_Authorise_Sessionid( $Sid, $email, $USER, $idtype );
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Base_Users_Authorise_Sessionid_Or_APIKey_For_Org;
DELIMITER //
CREATE PROCEDURE _Base_Users_Authorise_Sessionid_Or_APIKey_For_Org
(
$Sid CHAR(64),
$apikey CHAR(64),
OUT $ORG_ID INT,
OUT $email TEXT,
OUT $USER INT,
OUT $idtype TEXT,
OUT $org_user_role TEXT
)
SQL SECURITY INVOKER
BEGIN
SET $org_user_role = "";
CALL _Base_Users_Authorise_Sessionid_Or_Apikey( $Sid, $apikey, $email, $USER, $idtype );
IF "ADMIN" = $idtype THEN
SET $ORG_ID = 0;
SET $org_user_role = "DELETE.INSERT.REPLACE.SAVE.SELECT.UPDATE.OWNER.ADMIN";
ELSE
SELECT ORG_ID, org_user_role
INTO $ORG_ID, $org_user_role
FROM base_organisations_users
WHERE USER = $USER
LIMIT 1;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Base_Users_Authorise_APIKey;
DELIMITER //
CREATE PROCEDURE _Base_Users_Authorise_APIKey
(
$apikey_at_ip TEXT,
OUT $email TEXT,
OUT $idtype TEXT,
OUT $apikey_type TEXT,
OUT $USER INT,
OUT $ORG_ID INT,
OUT $PROJECT_ID INT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $apikey TEXT DEFAULT '';
DECLARE $ip_address TEXT DEFAULT '';
SET $email = "";
SET $idtype = "";
SET $apikey_type = "";
SET $USER = 0;
SET $ORG_ID = 0;
SET $PROJECT_ID = 0;
SET $apikey = SUBSTRING_INDEX( $apikey_at_ip, '@', 1 ); # Extract apikey
SET $ip_address = SUBSTRING_INDEX( $apikey_at_ip, '@', -1 ); # Extract ip address
IF EXISTS
(
SELECT apikey, COUNT(*)
FROM base_apikeys
WHERE apikey = $apikey
GROUP BY apikey
HAVING COUNT(*) > 1
)
THEN
CALL Error( '_Base_Users_Authorise_APIKey: DUPLICATE_APIKEY' );
ELSE
SELECT
email,
type,
apikey_type,
USER_OWNER,
ORG_ID,
PROJECT_ID
INTO
$email,
$idtype,
$apikey_type,
$USER,
$ORG_ID,
$PROJECT_ID
FROM base_apikeys
LEFT JOIN view_base_users ON (USER_OWNER=USER)
WHERE apikey=$apikey
AND (NOT IsNotEmpty(ip_address) OR ip_address = $ip_address);
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Base_Users_Authorise_APIKey_Type;
DELIMITER //
CREATE PROCEDURE _Base_Users_Authorise_APIKey_Type
(
$type TEXT,
$apikey_at_ip TEXT,
OUT $email TEXT,
OUT $idtype TEXT,
OUT $apikey_type TEXT,
OUT $USER INT,
OUT $ORG_ID INT,
OUT $PROJECT_ID INT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $apikey TEXT DEFAULT '';
DECLARE $ip_address TEXT DEFAULT '';
SET $email = "";
SET $idtype = "";
SET $apikey_type = "";
SET $USER = 0;
SET $ORG_ID = 0;
SET $PROJECT_ID = 0;
SET $apikey = SUBSTRING_INDEX( $apikey_at_ip, '@', 1 ); # Extract apikey
SET $ip_address = SUBSTRING_INDEX( $apikey_at_ip, '@', -1 ); # Extract ip address
IF EXISTS
(
SELECT apikey, COUNT(*)
FROM base_apikeys
WHERE apikey = $apikey
AND apikey_type = $type
GROUP BY apikey
HAVING COUNT(*) > 1
)
THEN
CALL Error( '_Base_Users_Authorise_APIKey_Type: DUPLICATE_APIKEY' );
ELSE
SELECT
email,
type,
apikey_type,
USER_OWNER,
ORG_ID,
PROJECT_ID
INTO
$email,
$idtype,
$apikey_type,
$USER,
$ORG_ID,
$PROJECT_ID
FROM base_apikeys
LEFT JOIN view_base_users ON (USER_OWNER=USER)
WHERE apikey = $apikey
AND apikey_type = $type
AND (NOT IsNotEmpty(ip_address) OR ip_address = $ip_address);
END IF;
END
//
DELIMITER ;