From 48202ee2f738f46e4d7a59b3a87ac547e71790e3 Mon Sep 17 00:00:00 2001 From: kambizzandi Date: Sun, 3 Apr 2022 16:54:22 +0430 Subject: [PATCH 1/2] some changes to session for storing last renew --- App/Server/QJWT.cpp | 108 ++++-------------- App/Server/QJWT.h | 12 +- App/Server/clsRequestHandler.cpp | 27 +++-- Interfaces/AAA/Authentication.cpp | 52 +++++++++ Interfaces/AAA/Authentication.h | 34 +++--- Interfaces/AAA/PrivHelpers.cpp | 15 +-- Interfaces/AAA/clsJWT.hpp | 13 ++- Interfaces/Common/GenericTypes.h | 8 +- Interfaces/Helpers/RESTClientHelper.cpp | 35 +++--- Interfaces/Helpers/RESTClientHelper.h | 18 +-- Interfaces/Interfaces.pro | 9 ++ Interfaces/Test/Test.pri | 9 +- ...20220401_144613_AAA_add_jwt_to_session.sql | 4 - Modules/Account/moduleSrc/Account.cpp | 96 ++++------------ .../Account/moduleSrc/ORM/ActiveSessions.cpp | 32 +++--- .../Account/moduleSrc/ORM/ActiveSessions.h | 2 +- Modules/Advert/moduleSrc/Advert.cpp | 8 ++ Modules/Ticketing/moduleSrc/Ticketing.cpp | 8 +- Modules/Ticketing/moduleSrc/Ticketing.h | 4 +- conf/api.conf | 8 +- 20 files changed, 231 insertions(+), 271 deletions(-) delete mode 100644 Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql diff --git a/App/Server/QJWT.cpp b/App/Server/QJWT.cpp index 255fd547..40b2d1de 100644 --- a/App/Server/QJWT.cpp +++ b/App/Server/QJWT.cpp @@ -81,7 +81,7 @@ tmplConfigurable QJWT::TTL( tmplConfigurable QJWT::NormalLoginTTL( QJWT::makeConfig("NormalLoginTTL"), "Time to live for the login token", - static_cast(24*60*60), + static_cast(24*60*60), ReturnTrueCrossValidator(), "", "", @@ -91,7 +91,7 @@ tmplConfigurable QJWT::NormalLoginTTL( tmplConfigurable QJWT::RememberLoginTTL( QJWT::makeConfig("RememberLoginTTL"), "Time to live for the login token when remembered", - static_cast(7*24*60*60), + static_cast(7*24*60*60), ReturnTrueCrossValidator(), "", "TTL", @@ -121,20 +121,27 @@ QString QJWT::createSigned( { const QString Header = QString("{\"typ\":\"JWT\",\"alg\":\"%1\"}").arg(enuJWTHashAlgs::toStr(QJWT::HashAlgorithm.value())); - _payload["iat"] = static_cast(QDateTime::currentDateTime().toTime_t()); + if (_payload.contains("iat") == false) + _payload["iat"] = static_cast(QDateTime::currentDateTime().toTime_t()); if (_expiry >= 0) _payload["exp"] = _payload["iat"].toInt() + _expiry; else _payload.remove("exp"); + bool ssnRemember = true; + if (_payload.contains("ssnexp") == false) + _payload["ssnexp"] = _payload["iat"].toInt() + + (qint32)(ssnRemember ? Server::QJWT::RememberLoginTTL.value() : Server::QJWT::NormalLoginTTL.value()); + if (_sessionID.size()) _payload["jti"] = _sessionID; else _payload.remove("jti"); if (_remoteIP.isEmpty() == false) - _privatePayload.insert("cip", _remoteIP); + _privatePayload["cip"] = _remoteIP; +// _privatePayload.insert("cip", _remoteIP); if (_privatePayload.isEmpty() == false) _payload["prv"] = simpleCryptInstance()->encryptToString(QJsonDocument(_privatePayload).toJson()); @@ -146,10 +153,9 @@ QString QJWT::createSigned( return Data + "." + QJWT::hash(Data).toBase64(); } -QJsonObject QJWT::verifyReturnPayload( - QString &_jwt, - const QString &_remoteIP, - bool _renewIfExpired +TAPI::JWT_t QJWT::verifyJWT( + const QString &_jwt, + const QString &_remoteIP ) { QStringList JWTParts = _jwt.split('.'); @@ -166,7 +172,7 @@ QJsonObject QJWT::verifyReturnPayload( if (Payload.isNull()) throw exHTTPForbidden("Invalid JWT payload: " + Error.errorString()); - QJsonObject JWTPayload = Payload.object(); + TAPI::JWT_t JWTPayload = Payload.object(); if (JWTPayload.empty()) throw exHTTPForbidden("Invalid JWT payload: empty object"); @@ -185,7 +191,6 @@ QJsonObject QJWT::verifyReturnPayload( throw exHTTPExpectationFailed("Invalid private JWT payload: " + Error.errorString()); QJsonObject PrivateObject = Private.object(); - JWTPayload["prv"] = PrivateObject; // check client ip --------------- @@ -196,82 +201,15 @@ QJsonObject QJWT::verifyReturnPayload( } } - if (JWTPayload.contains("exp") - && static_cast(JWTPayload.value("exp").toInt()) <= QDateTime::currentDateTime().toTime_t() - ) - { - if (_renewIfExpired == false) - throw exHTTPUnauthorized("JWT expired"); - - QString SessionKey = JWTPayload["jti"].toString(); - - DBManager::clsDAC DAC("AAA"); //master db dac -> AAA - - //check session - QString Qry = R"( - SELECT TIME_TO_SEC((TIMEDIFF(NOW(), ssnCreationDateTime))) AS LifeSeconds - , tblActiveSessions.* - FROM tblActiveSessions - WHERE ssnKey=? - AND ssnStatus='A' -)"; - QJsonDocument Result = DAC.execQuery({}, Qry, { SessionKey }) - .toJson(true); - - if (Result.object().isEmpty()) - throw exHTTPUnauthorized("Active session not found"); - - QVariantMap SessionInfo = Result.toVariant().toMap(); + //check large expiration + if (JWTPayload.contains("ssnexp") == false) + exHTTPForbidden("Invalid ssnexp in JWT"); + if (static_cast(JWTPayload.value("ssnexp").toInt()) <= QDateTime::currentDateTime().toTime_t()) + throw exHTTPUnauthorized("Session expired"); - //check old JWT - QString ssnJWT = SessionInfo["ssnJWT"].toString(); - if ((ssnJWT.isEmpty() == false) && (_jwt != ssnJWT)) - { - _jwt = ssnJWT; //this will add response header X-AUTH-NEW-TOKEN - throw exHTTPForbidden("JWT not replaced by client"); - } - - //check large expiration - quint64 LifeSeconds = SessionInfo["LifeSeconds"].toUInt(); - bool ssnRemember = (SessionInfo["ssnRemember"].toInt() == 1); - - if (LifeSeconds >= (ssnRemember ? QJWT::RememberLoginTTL.value() : QJWT::NormalLoginTTL.value())) - { - Qry = R"( - UPDATE tblActiveSessions - SET ssnStatus='E' - WHERE ssnKey=? -)"; - DAC.execQuery({}, Qry, { SessionKey }); - - throw exHTTPUnauthorized("Session expired"); - } - - // - if (SessionInfo["ssnIPReadable"] != _remoteIP) - throw exHTTPForbidden("Invalid IP"); - - //ssn_usrID - //ssnFingerPrint - - //TODO: check user ban or large expiration - - //---------------------------------------- - _jwt = QJWT::createSigned( - JWTPayload, - JWTPayload.contains("prv") ? JWTPayload["prv"].toObject() : QJsonObject(), - JWTPayload["exp"].toInt() - JWTPayload["iat"].toInt(), - JWTPayload["jti"].toString(), - _remoteIP - ); - - Qry = R"( - UPDATE tblActiveSessions - SET ssnJWT=? - WHERE ssnKey=? -)"; - DAC.execQuery({}, Qry, { _jwt, SessionKey }); - } + if (JWTPayload.contains("exp") + && static_cast(JWTPayload.value("exp").toInt()) <= QDateTime::currentDateTime().toTime_t()) + throw exJWTExpired("JWT expired"); return JWTPayload; } diff --git a/App/Server/QJWT.h b/App/Server/QJWT.h index 2c95cfb0..792af9f8 100644 --- a/App/Server/QJWT.h +++ b/App/Server/QJWT.h @@ -25,6 +25,8 @@ #define TARGOMAN_API_SERVER_CLSJWT_H #include "libTargomanCommon/Configuration/tmplConfigurable.h" +#include "Interfaces/Common/GenericTypes.h" +#include "ServerConfigs.h" namespace Targoman::API::AAA { class clsJWT; @@ -32,6 +34,9 @@ class clsJWT; namespace Targoman::API::Server { +TARGOMAN_ADD_EXCEPTION_HANDLER(exJWT, exTargomanAPI); +TARGOMAN_ADD_EXCEPTION_HANDLER(exJWTExpired, exJWT); + TARGOMAN_DEFINE_ENHANCED_ENUM(enuJWTHashAlgs, HS256, HS384, @@ -55,10 +60,9 @@ class QJWT const QString &_remoteIP = {} ); - static QJsonObject verifyReturnPayload( - /*INOUT*/ QString &_jwt, - const QString &_remoteIP, - bool _renewIfExpired = false + static TAPI::JWT_t verifyJWT( + const QString &_jwt, + const QString &_remoteIP ); private: diff --git a/App/Server/clsRequestHandler.cpp b/App/Server/clsRequestHandler.cpp index 202914f4..a86d4288 100644 --- a/App/Server/clsRequestHandler.cpp +++ b/App/Server/clsRequestHandler.cpp @@ -39,6 +39,9 @@ #include "APICache.hpp" #include "OpenAPIGenerator.h" +#include "Interfaces/AAA/Authentication.h" +using namespace Targoman::API::AAA; + namespace Targoman::API::Server { using namespace qhttp::server; @@ -315,7 +318,7 @@ clsRequestHandler::stuResult clsRequestHandler::run(clsAPIObject* _apiObject, QS qhttp::THeaderHash Headers = this->Request->headers(); qhttp::THeaderHash Cookies; - QJsonObject JWT; + TAPI::JWT_t JWT; if (_apiObject->requiresJWT()) { @@ -325,26 +328,26 @@ clsRequestHandler::stuResult clsRequestHandler::run(clsAPIObject* _apiObject, QS QString BearerToken = Auth.mid(sizeof("Bearer")); Headers.remove("authorization"); - QString OldBearerToken = BearerToken; + QString RemoteIP = this->toIPv4(this->Request->remoteAddress()); try { - JWT = QJWT::verifyReturnPayload( + JWT = QJWT::verifyJWT( BearerToken, - this->toIPv4(this->Request->remoteAddress()), - true + RemoteIP ); - - if (BearerToken != OldBearerToken) - ResponseHeaders.insert("X-AUTH-NEW-TOKEN", BearerToken); } - catch (...) + catch (exJWTExpired &exp) { - if (BearerToken != OldBearerToken) - ResponseHeaders.insert("X-AUTH-NEW-TOKEN", BearerToken); + QString NewToken = Authentication::renewJWT( + BearerToken, + RemoteIP + ); - throw; + BearerToken = NewToken; + ResponseHeaders.insert("X-AUTH-NEW-TOKEN", BearerToken); } + JWT["encodedJWT"] = BearerToken; } else throw exHTTPForbidden("No valid authentication header is present"); diff --git a/Interfaces/AAA/Authentication.cpp b/Interfaces/AAA/Authentication.cpp index d23009c1..2786fe81 100644 --- a/Interfaces/AAA/Authentication.cpp +++ b/Interfaces/AAA/Authentication.cpp @@ -23,6 +23,8 @@ #include "Authentication.h" #include "PrivHelpers.h" +#include "Interfaces/AAA/clsJWT.hpp" +#include "App/Server/QJWT.h" namespace Targoman::API::AAA::Authentication { @@ -56,16 +58,66 @@ stuActiveAccount login( return PrivHelpers::processUserObject(UserInfo, {}, _requiredServices); } +/* stuActiveAccount updatePrivs(const QString& _ip, const QString& _ssid, const QString& _requiredServices) { makeAAADAC(DAC); + QJsonObject UserInfo = DAC.callSP({}, "spSession_UpdateActivity", { {"iIP", _ip}, {"iSSID", _ssid}, }).toJson(true).object(); + return PrivHelpers::processUserObject(UserInfo, {}, _requiredServices.split(',', QString::SkipEmptyParts)); } +*/ + +QString renewJWT( + const QString &_jwt, + const QString &_ip + ) +{ + QStringList JWTParts = _jwt.split('.'); + + if (JWTParts.length() != 3) + throw exHTTPForbidden("Invalid JWT Token"); + + QJsonParseError Error; + QJsonDocument Payload = QJsonDocument::fromJson(QByteArray::fromBase64(JWTParts.at(1).toLatin1()), &Error); + + if (Payload.isNull()) + throw exHTTPForbidden("Invalid JWT payload: " + Error.errorString()); + + TAPI::JWT_t JWTPayload = Payload.object(); + + clsJWT JWT(JWTPayload); + QStringList Services = JWT.privatePart().value("svc").toString().split(',', QString::SkipEmptyParts); + + makeAAADAC(DAC); + + quint32 Duration = JWTPayload["exp"].toInt() - JWTPayload["iat"].toInt(); + QJsonObject UserInfo = DAC.callSP({}, + "spSessionRetrieveInfo", { + { "iSSID", JWT.session() }, + { "iIP", _ip }, + { "iIssuance", JWTPayload["iat"].toInt() }, + }).toJson(true).object(); + + + stuActiveAccount ActiveAccount = PrivHelpers::processUserObject(UserInfo, {}, Services); + + JWTPayload["iat"] = ActiveAccount.Privs["Issuance"]; + JWTPayload["privs"] = ActiveAccount.Privs["privs"]; + + return Server::QJWT::createSigned( + JWTPayload, + JWTPayload.contains("prv") ? JWTPayload["prv"].toObject() : QJsonObject(), + Duration, + JWTPayload["jti"].toString(), + _ip + ); +} QString retrievePhoto(const QString _url) { diff --git a/Interfaces/AAA/Authentication.h b/Interfaces/AAA/Authentication.h index a3f1dc8b..28a0370c 100644 --- a/Interfaces/AAA/Authentication.h +++ b/Interfaces/AAA/Authentication.h @@ -24,6 +24,7 @@ #ifndef TARGOMAN_API_AAA_AUTHENTICATION_H #define TARGOMAN_API_AAA_AUTHENTICATION_H +#include "Interfaces/Common/GenericTypes.h" #include "Interfaces/AAA/AAADefs.hpp" #include "Interfaces/AAA/PrivHelpers.h" @@ -48,21 +49,26 @@ struct stuOAuthInfo { }; extern Targoman::API::AAA::stuActiveAccount login( - const QString& _ip, - const QString& _login, - const QString& _pass, - const QString& _salt, - const QStringList& _requiredServices, - bool _rememberMe, - const QJsonObject& _info, - const QString& _fingerPrint - ); + const QString &_ip, + const QString &_login, + const QString &_pass, + const QString &_salt, + const QStringList &_requiredServices, + bool _rememberMe, + const QJsonObject &_info, + const QString &_fingerPrint +); -extern Targoman::API::AAA::stuActiveAccount updatePrivs(const QString& _ip, const QString& _ssid, const QString& _requiredServices); -extern stuOAuthInfo retrieveGoogleUserInfo(const QString& _authToken); -extern stuOAuthInfo retrieveLinkedinUserInfo(const QString& _authToken); -extern stuOAuthInfo retrieveYahooUserInfo(const QString& _authToken); -extern stuOAuthInfo retrieveGitHubUserInfo(const QString& _authToken); +//extern Targoman::API::AAA::stuActiveAccount updatePrivs(const QString &_ip, const QString &_ssid, const QString &_requiredServices); +extern QString renewJWT( + const QString &_jwt, + const QString &_ip +); + +extern stuOAuthInfo retrieveGoogleUserInfo(const QString &_authToken); +extern stuOAuthInfo retrieveLinkedinUserInfo(const QString &_authToken); +extern stuOAuthInfo retrieveYahooUserInfo(const QString &_authToken); +extern stuOAuthInfo retrieveGitHubUserInfo(const QString &_authToken); } //namespace Targoman::API::AAA::Authentication diff --git a/Interfaces/AAA/PrivHelpers.cpp b/Interfaces/AAA/PrivHelpers.cpp index c3c9443a..043901ac 100644 --- a/Interfaces/AAA/PrivHelpers.cpp +++ b/Interfaces/AAA/PrivHelpers.cpp @@ -138,7 +138,8 @@ QVariant PrivHelpers::getPrivValue(const QJsonObject& _privs, const QString& _se } stuActiveAccount PrivHelpers::processUserObject(QJsonObject& _userObj, const QStringList& _requiredAccess, const QStringList& _services) { - _userObj = _userObj[DBM_SPRESULT_ROWS].toArray().at(0).toObject(); + if (_userObj.contains(DBM_SPRESULT_ROWS)) + _userObj = _userObj[DBM_SPRESULT_ROWS].toArray().at(0).toObject(); if(_userObj.size()){ stuActiveAccount ActiveAccount = @@ -181,11 +182,11 @@ TAPI::EncodedJWT_t clsJWT::createSigned(QJsonObject _payload, QJsonObject _priva _sessionID); } -TAPI::EncodedJWT_t clsJWT::createSignedLogin(bool _remember, QJsonObject _payload, QJsonObject _privatePayload, const QString& _sessionID) -{ - return Server::QJWT::createSigned(_payload, _privatePayload, - _remember ? Server::QJWT::RememberLoginTTL.value() : Server::QJWT::NormalLoginTTL.value(), - _sessionID); -} +//TAPI::EncodedJWT_t clsJWT::createSignedLogin(bool _remember, QJsonObject _payload, QJsonObject _privatePayload, const QString& _sessionID) +//{ +// return Server::QJWT::createSigned(_payload, _privatePayload, +// _remember ? Server::QJWT::RememberLoginTTL.value() : Server::QJWT::NormalLoginTTL.value(), +// _sessionID); +//} } //namespace Targoman::API::AAA diff --git a/Interfaces/AAA/clsJWT.hpp b/Interfaces/AAA/clsJWT.hpp index 3652802e..ec686896 100644 --- a/Interfaces/AAA/clsJWT.hpp +++ b/Interfaces/AAA/clsJWT.hpp @@ -32,18 +32,19 @@ namespace Targoman::API::AAA { namespace JWTItems{ +TARGOMAN_CREATE_CONSTEXPR(iat); +TARGOMAN_CREATE_CONSTEXPR(jti); +TARGOMAN_CREATE_CONSTEXPR(priv); +TARGOMAN_CREATE_CONSTEXPR(privs); TARGOMAN_CREATE_CONSTEXPR(usrLogin); TARGOMAN_CREATE_CONSTEXPR(usrName); TARGOMAN_CREATE_CONSTEXPR(usrFamily); -TARGOMAN_CREATE_CONSTEXPR(rolName); -TARGOMAN_CREATE_CONSTEXPR(rolID); -TARGOMAN_CREATE_CONSTEXPR(privs); TARGOMAN_CREATE_CONSTEXPR(usrID); TARGOMAN_CREATE_CONSTEXPR(usrApproval); TARGOMAN_CREATE_CONSTEXPR(usrStatus); -TARGOMAN_CREATE_CONSTEXPR(jti); -TARGOMAN_CREATE_CONSTEXPR(priv); TARGOMAN_CREATE_CONSTEXPR(canChangePass); +TARGOMAN_CREATE_CONSTEXPR(rolID); +TARGOMAN_CREATE_CONSTEXPR(rolName); } class clsJWT{ @@ -91,7 +92,7 @@ class clsJWT{ * @param _sessionID optinally a session key for each user to be stored in `jti` * @return a base64 encoded string in form of HEADER.PAYLOAD.SIGNATURE */ - static TAPI::EncodedJWT_t createSignedLogin(bool _remember, QJsonObject _payload, QJsonObject _privatePayload, const QString& _sessionID = {}); +// static TAPI::EncodedJWT_t createSignedLogin(bool _remember, QJsonObject _payload, QJsonObject _privatePayload, const QString& _sessionID = {}); private: const QJsonObject& Token; }; diff --git a/Interfaces/Common/GenericTypes.h b/Interfaces/Common/GenericTypes.h index c180c73d..d74a4463 100644 --- a/Interfaces/Common/GenericTypes.h +++ b/Interfaces/Common/GenericTypes.h @@ -34,16 +34,12 @@ #include "Interfaces/Common/tmplAPIArg.h" #include "Interfaces/Common/HTTPExceptions.hpp" -namespace Targoman { -namespace API { -namespace Server { +namespace Targoman::API::Server { extern QList gOrderedMetaTypeInfo; extern QList gUserDefinedTypesInfo; -} -} -} +} //namespace Targoman::API::Server //I used TAPI as namespace in order to make Targoman::API shorter namespace TAPI { diff --git a/Interfaces/Helpers/RESTClientHelper.cpp b/Interfaces/Helpers/RESTClientHelper.cpp index eb1fa970..c590921f 100644 --- a/Interfaces/Helpers/RESTClientHelper.cpp +++ b/Interfaces/Helpers/RESTClientHelper.cpp @@ -27,7 +27,7 @@ #include #include "Interfaces/AAA/PrivHelpers.h" //#include "Interfaces/AAA/clsJWT.hpp" -#include "App/Server/QJWT.h" +//#include "App/Server/QJWT.h" using namespace Targoman::Common::Configuration; using namespace Targoman::API::AAA; @@ -57,26 +57,21 @@ tmplConfigurable ClientConfigs::RESTServerAddress( QVariant RESTClientHelper::callAPI( TAPI::JWT_t _JWT, RESTClientHelper::enuHTTPMethod _method, - const QString& _api, - const QVariantMap& _urlArgs, - const QVariantMap& _postOrFormFields, - const QVariantMap& _formFiles, + const QString &_api, + const QVariantMap &_urlArgs, + const QVariantMap &_postOrFormFields, + const QVariantMap &_formFiles, QString _aPIURL ) { -// clsJWT JWT(_JWT); -// QString EncodedJWT = JWT.session(); - -// QString EncodedJWT = clsJWT::createSigned(_JWT); - - QString EncodedJWT = Targoman::API::Server::QJWT::createSigned(_JWT, - {}, - 300, //Targoman::API::Server::QJWT::TTL.value(), - {} - ); +// QString EncodedJWT = Targoman::API::Server::QJWT::createSigned(_JWT, +// {}, +// 300, //Targoman::API::Server::QJWT::TTL.value(), +// {} +// ); return RESTClientHelper::callAPI( - EncodedJWT, + _JWT["encodedJWT"].toString(), _method, _api, _urlArgs, @@ -89,10 +84,10 @@ QVariant RESTClientHelper::callAPI( QVariant RESTClientHelper::callAPI( QString _encodedJWT, RESTClientHelper::enuHTTPMethod _method, - const QString& _api, - const QVariantMap& _urlArgs, - const QVariantMap& _postOrFormFields, - const QVariantMap& _formFiles, + const QString &_api, + const QVariantMap &_urlArgs, + const QVariantMap &_postOrFormFields, + const QVariantMap &_formFiles, QString _aPIURL ) { diff --git a/Interfaces/Helpers/RESTClientHelper.h b/Interfaces/Helpers/RESTClientHelper.h index 5ba24193..9c8a8c4a 100644 --- a/Interfaces/Helpers/RESTClientHelper.h +++ b/Interfaces/Helpers/RESTClientHelper.h @@ -34,7 +34,7 @@ namespace Targoman::API::Helpers { struct ClientConfigs { - static inline QString makeConfig(const QString& _name) { return "/Client/" + _name; } + static inline QString makeConfig(const QString &_name) { return "/Client/" + _name; } static Targoman::Common::Configuration::tmplConfigurable RESTServerAddress; }; @@ -52,20 +52,20 @@ class RESTClientHelper static QVariant callAPI( TAPI::JWT_t _JWT, RESTClientHelper::enuHTTPMethod _method, - const QString& _api, - const QVariantMap& _urlArgs = {}, - const QVariantMap& _postOrFormFields = {}, - const QVariantMap& _formFiles = {}, + const QString &_api, + const QVariantMap &_urlArgs = {}, + const QVariantMap &_postOrFormFields = {}, + const QVariantMap &_formFiles = {}, QString _aPIURL = {} ); static QVariant callAPI( QString _encodedJWT, RESTClientHelper::enuHTTPMethod _method, - const QString& _api, - const QVariantMap& _urlArgs = {}, - const QVariantMap& _postOrFormFields = {}, - const QVariantMap& _formFiles = {}, + const QString &_api, + const QVariantMap &_urlArgs = {}, + const QVariantMap &_postOrFormFields = {}, + const QVariantMap &_formFiles = {}, QString _aPIURL = {} ); diff --git a/Interfaces/Interfaces.pro b/Interfaces/Interfaces.pro index 98b5f49d..72a00ec1 100644 --- a/Interfaces/Interfaces.pro +++ b/Interfaces/Interfaces.pro @@ -48,6 +48,15 @@ HEADERS += \ Helpers/SecurityHelper.h \ Helpers/URLHelper.h \ Helpers/PhoneHelper.h \ + AAA/AAA.hpp \ + AAA/AAADefs.hpp \ + AAA/Authentication.h \ + AAA/Authorization.h \ + AAA/clsJWT.hpp \ + AAA/PrivHelpers.h \ + AAA/Accounting_Interfaces.h \ + AAA/Accounting_Defs.hpp \ + AAA/intfAccountingBasedModule.h \ DBM/Defs.hpp \ DBM/clsORMField.h \ DBM/clsTable.h \ diff --git a/Interfaces/Test/Test.pri b/Interfaces/Test/Test.pri index 70ee74ab..5327b9cb 100644 --- a/Interfaces/Test/Test.pri +++ b/Interfaces/Test/Test.pri @@ -11,16 +11,19 @@ HEADERS += \ $$BASE_PROJECT_PATH/Interfaces/Test/testBase.hpp \ $$BASE_PROJECT_PATH/Interfaces/Test/testCommon.hpp \ $$BASE_PROJECT_PATH/App/Server/clsSimpleCrypt.h \ - $$BASE_PROJECT_PATH/App/Server/QJWT.h \ -# $$BASE_PROJECT_PATH/Interfaces/AAA/clsJWT.hpp \ # $$BASE_PROJECT_PATH/Interfaces/AAA/PrivHelpers.h \ +# $$BASE_PROJECT_PATH/Interfaces/AAA/Authentication.h \ +# $$BASE_PROJECT_PATH/Interfaces/AAA/intfAccountingBasedModule.h \ +# $$BASE_PROJECT_PATH/App/Server/QJWT.h \ $$BASE_PROJECT_PATH/Interfaces/Helpers/RESTClientHelper.h \ $$BASE_PROJECT_PATH/Interfaces/Helpers/SecurityHelper.h \ SOURCES += \ $$BASE_PROJECT_PATH/App/Server/clsSimpleCrypt.cpp \ - $$BASE_PROJECT_PATH/App/Server/QJWT.cpp \ # $$BASE_PROJECT_PATH/Interfaces/AAA/PrivHelpers.cpp \ +# $$BASE_PROJECT_PATH/Interfaces/AAA/Authentication.cpp \ +# $$BASE_PROJECT_PATH/Interfaces/AAA/intfAccountingBasedModule.cpp \ +# $$BASE_PROJECT_PATH/App/Server/QJWT.cpp \ $$BASE_PROJECT_PATH/Interfaces/Helpers/RESTClientHelper.cpp \ $$BASE_PROJECT_PATH/Interfaces/Helpers/SecurityHelper.cpp \ diff --git a/Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql b/Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql deleted file mode 100644 index 9824b85b..00000000 --- a/Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql +++ /dev/null @@ -1,4 +0,0 @@ -/* Migration File: m20220401_144613_AAA_add_jwt_to_session.sql */ - -ALTER TABLE `tblActiveSessions` - ADD COLUMN `ssnJWT` TEXT NULL AFTER `ssnRemember`; diff --git a/Modules/Account/moduleSrc/Account.cpp b/Modules/Account/moduleSrc/Account.cpp index 0a29a642..835f4c0b 100644 --- a/Modules/Account/moduleSrc/Account.cpp +++ b/Modules/Account/moduleSrc/Account.cpp @@ -195,16 +195,17 @@ Account::Account() : TAPI::EncodedJWT_t Account::createJWT(const QString _login, const stuActiveAccount& _activeAccount, const QString& _services) { return clsJWT::createSigned({ - { JWTItems::usrLogin, _login }, - { JWTItems::usrID, _activeAccount.Privs["usrID"] }, - { JWTItems::usrName, _activeAccount.Privs["usrName"] }, - { JWTItems::usrFamily, _activeAccount.Privs["usrFamily"] }, - { JWTItems::rolID, _activeAccount.Privs["usr_rolID"] }, - { JWTItems::rolName, _activeAccount.Privs["rolName"] }, - { JWTItems::privs, _activeAccount.Privs["privs"] }, - { JWTItems::usrApproval, TAPI::enuUserApproval::toStr(_activeAccount.Privs["usrApprovalState"].toString()) }, - { JWTItems::usrStatus, TAPI::enuUserStatus::toStr(_activeAccount.Privs["usrStatus"].toString()) }, - { JWTItems::canChangePass, _activeAccount.Privs["hasPass"] }, + { JWTItems::usrLogin, _login }, + { JWTItems::usrID, _activeAccount.Privs["usrID"] }, + { JWTItems::usrName, _activeAccount.Privs["usrName"] }, + { JWTItems::usrFamily, _activeAccount.Privs["usrFamily"] }, + { JWTItems::rolID, _activeAccount.Privs["usr_rolID"] }, + { JWTItems::rolName, _activeAccount.Privs["rolName"] }, + { JWTItems::privs, _activeAccount.Privs["privs"] }, + { JWTItems::usrApproval, TAPI::enuUserApproval::toStr(_activeAccount.Privs["usrApprovalState"].toString()) }, + { JWTItems::usrStatus, TAPI::enuUserStatus::toStr(_activeAccount.Privs["usrStatus"].toString()) }, + { JWTItems::canChangePass, _activeAccount.Privs["hasPass"] }, + { JWTItems::iat, _activeAccount.Privs["Issuance"] }, }, QJsonObject({ { "svc", _services } }), _activeAccount.TTL, @@ -425,16 +426,17 @@ TAPI::EncodedJWT_t Account::apilogin( QFV.asciiAlNum().maxLenght(20).validate(_salt, "salt"); - auto LoginInfo = Authentication::login(_REMOTE_IP, - _emailOrMobile, - _pass, - _salt, - _services.split(",", QString::SkipEmptyParts), - _rememberMe, - _sessionInfo.object(), - _fingerprint); - - return this->createJWT(_emailOrMobile, LoginInfo, _services); + stuActiveAccount LoginInfo = Authentication::login(_REMOTE_IP, + _emailOrMobile, + _pass, + _salt, + _services.split(",", QString::SkipEmptyParts), + _rememberMe, + _sessionInfo.object(), + _fingerprint + ); + + return this->createJWT(_emailOrMobile, LoginInfo, _services); // return Targoman::API::AccountModule::stuMultiJWT({ // this->createLoginJWT(_rememberMe, _emailOrMobile, LoginInfo.Privs["ssnKey"].toString(), _services), // }); @@ -501,60 +503,6 @@ bool Account::apiresendApprovalCode( return true; } -//bool Account::apiPUTrequestMobileVerifyCode( -// TAPI::RemoteIP_t _REMOTE_IP, -// TAPI::Mobile_t _mobile -// ) -//{ -// Authorization::validateIPAddress(_REMOTE_IP); - -// _mobile = PhoneHelper::NormalizePhoneNumber(_mobile); - -// quint64 aprID = this->callSP("spMobileVerifyCode_Request", { -// { "iMobile", _mobile }, -// }) -// .spDirectOutputs() -// .value("oAprID") -// .toDouble(); - -// return (aprID > 0); -//} - -/* -TAPI::EncodedJWT_t Account::apiPUTverifyLoginByMobileCode( - TAPI::RemoteIP_t _REMOTE_IP, - TAPI::Mobile_t _mobile, - quint32 _code, - TAPI::CommaSeparatedStringList_t _services, - bool _rememberMe, - TAPI::JSON_t _sessionInfo, - TAPI::MD5_t _fingerprint - ) -{ - Authorization::validateIPAddress(_REMOTE_IP); - - _mobile = PhoneHelper::NormalizePhoneNumber(_mobile); - - QJsonObject UserInfo = this->callSP("spLogin_VerifyByMobileCode", { - { "iMobile", _mobile }, - { "iCode", _code }, - { "iIP", _REMOTE_IP }, - { "iInfo", _sessionInfo.object() }, - { "iRemember", _rememberMe ? "1" : "0" }, - { "iFingerPrint", _fingerprint.isEmpty() ? QVariant() : _fingerprint }, - }) - .toJson(true) - .object(); - - auto LoginInfo = PrivHelpers::processUserObject(UserInfo, {}, _services.split(",", QString::SkipEmptyParts)); - - return this->createJWT(_mobile, LoginInfo, _services); -// return Targoman::API::AccountModule::stuMultiJWT({ -// this->createLoginJWT(_rememberMe, _mobile, LoginInfo.Privs["ssnKey"].toString(), _services), -// }); -} -*/ - ///TODO: cache to ban users for every service ///TODO: update cache for each module ///TODO: JWT lifetime dynamic based on current hour diff --git a/Modules/Account/moduleSrc/ORM/ActiveSessions.cpp b/Modules/Account/moduleSrc/ORM/ActiveSessions.cpp index 5dfd9592..6b841129 100644 --- a/Modules/Account/moduleSrc/ORM/ActiveSessions.cpp +++ b/Modules/Account/moduleSrc/ORM/ActiveSessions.cpp @@ -34,23 +34,23 @@ ActiveSessions::ActiveSessions() : intfSQLBasedModule( AAASchema, tblActiveSessions::Name, - {///< ColName Type Validation Default UpBy Sort Filter Self Virt PK - { tblActiveSessions::ssnKey, S(TAPI::MD5_t), QFV, ORM_PRIMARY_KEY }, - { tblActiveSessions::ssn_usrID, S(quint64), QFV.integer().minValue(1), QRequired, UPNone }, - { tblActiveSessions::ssnIP, S(quint32), QFV.integer().minValue(1), QRequired, UPNone }, - { tblActiveSessions::ssnIPReadable, S(QString), QFV.allwaysInvalid(), QInvalid, UPNone, false, false }, - { tblActiveSessions::ssnInfo, S(TAPI::JSON_t), QFV, QNull, UPNone, false, false }, - { tblActiveSessions::ssnFingerPrint, S(TAPI::MD5_t), QFV.allwaysInvalid(), QNull, UPNone, false, false }, - { tblActiveSessions::ssnLastActivity, S(TAPI::DateTime_t), QFV, QNull, UPNone }, - { tblActiveSessions::ssnRemember, S(bool), QFV, false, UPNone }, - { tblActiveSessions::ssnJWT, S(QString), QFV, QNull, UPAdmin, false, false }, - { tblActiveSessions::ssnStatus, ORM_STATUS_FIELD(Targoman::API::AccountModule::enuSessionStatus, Targoman::API::AccountModule::enuSessionStatus::Active) }, - { tblActiveSessions::ssnCreationDateTime, ORM_CREATED_ON }, - { tblActiveSessions::ssnUpdatedBy_usrID, ORM_UPDATED_BY }, + {///< ColName Type Validation Default UpBy Sort Filter Self Virt PK + { tblActiveSessions::ssnKey, S(TAPI::MD5_t), QFV, ORM_PRIMARY_KEY }, + { tblActiveSessions::ssn_usrID, S(quint64), QFV.integer().minValue(1), QRequired, UPNone }, + { tblActiveSessions::ssnIP, S(quint32), QFV.integer().minValue(1), QRequired, UPNone }, + { tblActiveSessions::ssnIPReadable, S(QString), QFV.allwaysInvalid(), QInvalid, UPNone, false, false }, + { tblActiveSessions::ssnInfo, S(TAPI::JSON_t), QFV, QNull, UPNone, false, false }, + { tblActiveSessions::ssnFingerPrint, S(TAPI::MD5_t), QFV.allwaysInvalid(), QNull, UPNone, false, false }, + { tblActiveSessions::ssnLastActivity, S(TAPI::DateTime_t), QFV, QNull, UPNone }, + { tblActiveSessions::ssnLastRenew, S(TAPI::DateTime_t), QFV, QNull, UPNone }, + { tblActiveSessions::ssnRemember, S(bool), QFV, false, UPNone }, + { tblActiveSessions::ssnStatus, ORM_STATUS_FIELD(Targoman::API::AccountModule::enuSessionStatus, Targoman::API::AccountModule::enuSessionStatus::Active) }, + { tblActiveSessions::ssnCreationDateTime, ORM_CREATED_ON }, + { tblActiveSessions::ssnUpdatedBy_usrID, ORM_UPDATED_BY }, }, - {///< Col Reference Table ForeignCol Rename LeftJoin - { tblActiveSessions::ssn_usrID, R(AAASchema, tblUser::Name), tblUser::usrID, "Owner_" }, - { tblActiveSessions::ssnUpdatedBy_usrID, R(AAASchema, tblUser::Name), tblUser::usrID, "Updater_", true } + {///< Col Reference Table ForeignCol Rename LeftJoin + { tblActiveSessions::ssn_usrID, R(AAASchema, tblUser::Name), tblUser::usrID, "Owner_" }, + { tblActiveSessions::ssnUpdatedBy_usrID, R(AAASchema, tblUser::Name), tblUser::usrID, "Updater_", true } } ) {} diff --git a/Modules/Account/moduleSrc/ORM/ActiveSessions.h b/Modules/Account/moduleSrc/ORM/ActiveSessions.h index 7945319f..178fd47e 100644 --- a/Modules/Account/moduleSrc/ORM/ActiveSessions.h +++ b/Modules/Account/moduleSrc/ORM/ActiveSessions.h @@ -54,8 +54,8 @@ TARGOMAN_CREATE_CONSTEXPR(ssnCreationDateTime); TARGOMAN_CREATE_CONSTEXPR(ssnInfo); TARGOMAN_CREATE_CONSTEXPR(ssnFingerPrint); TARGOMAN_CREATE_CONSTEXPR(ssnLastActivity); +TARGOMAN_CREATE_CONSTEXPR(ssnLastRenew); TARGOMAN_CREATE_CONSTEXPR(ssnRemember); -TARGOMAN_CREATE_CONSTEXPR(ssnJWT); TARGOMAN_CREATE_CONSTEXPR(ssnStatus); TARGOMAN_CREATE_CONSTEXPR(ssnUpdatedBy_usrID); } diff --git a/Modules/Advert/moduleSrc/Advert.cpp b/Modules/Advert/moduleSrc/Advert.cpp index 01e96420..9d77aa71 100644 --- a/Modules/Advert/moduleSrc/Advert.cpp +++ b/Modules/Advert/moduleSrc/Advert.cpp @@ -103,6 +103,14 @@ Advert::Advert() : stuServiceCreditsInfo Advert::retrieveServiceCreditsInfo(quint64 _usrID) { + //TODO: complete this + return stuServiceCreditsInfo( + {}, + NULLABLE_NULL_VALUE, + NULLABLE_NULL_VALUE, + {}, + {} + ); } void Advert::breakCredit(quint64 _slbID) diff --git a/Modules/Ticketing/moduleSrc/Ticketing.cpp b/Modules/Ticketing/moduleSrc/Ticketing.cpp index cfd19be3..17cec301 100644 --- a/Modules/Ticketing/moduleSrc/Ticketing.cpp +++ b/Modules/Ticketing/moduleSrc/Ticketing.cpp @@ -65,7 +65,7 @@ quint64 Ticketing::insertTicket( const QString &_title, const QString &_body, const TAPI::Files_t &_files, - NULLABLE_TYPE(quint32) _unitID + quint32 _unitID ) { TAPI::ORMFields_t CreateFields({ @@ -83,8 +83,8 @@ quint64 Ticketing::insertTicket( if (_inReplyTicketID > 0) CreateFields.insert(tblTickets::tktInReply_tktID, _inReplyTicketID); - if (NULLABLE_HAS_VALUE(_unitID)) - CreateFields.insert(tblTickets::tkt_untID, NULLABLE_GET(_unitID)); + if (_unitID > 0) + CreateFields.insert(tblTickets::tkt_untID, _unitID); quint64 TicketID = this->Create(Tickets::instance(), _createdBy, CreateFields); @@ -130,7 +130,7 @@ QVariantMap Ticketing::apiPUTnewMessage( const QString &_body, quint32 _serviceID, quint64 _targetUserID, - NULLABLE_TYPE(quint32) _unitID, + quint32 _unitID, const TAPI::stuFileInfo &_file ) { diff --git a/Modules/Ticketing/moduleSrc/Ticketing.h b/Modules/Ticketing/moduleSrc/Ticketing.h index 2f59307a..d48deeab 100644 --- a/Modules/Ticketing/moduleSrc/Ticketing.h +++ b/Modules/Ticketing/moduleSrc/Ticketing.h @@ -56,7 +56,7 @@ class Ticketing : public intfSQLBasedWithActionLogsModule const QString &_title, const QString &_body, const TAPI::Files_t &_files = {}, - NULLABLE_TYPE(quint32) _unitID = NULLABLE_NULL_VALUE + quint32 _unitID = 0 ); private slots: @@ -68,7 +68,7 @@ private slots: const QString &_body, quint32 _serviceID, quint64 _targetUserID = 0, - NULLABLE_TYPE(quint32) _unitID = NULLABLE_NULL_VALUE, + quint32 _unitID = 0, const TAPI::stuFileInfo &_file = {} ), "create new message targeting a user or all users (if target user is 0)" diff --git a/conf/api.conf b/conf/api.conf index 9e14fe84..c54e6a9d 100644 --- a/conf/api.conf +++ b/conf/api.conf @@ -178,15 +178,15 @@ WarningLevel = 9 # Time to live for the login token # Valid values: 0 to 4294967295 -# Default value: 20864 +# Default value: 84600 #--------------------------------------- -;NormalLoginTTL = 20864 +;NormalLoginTTL = 84600 # Time to live for the login token when remembered # Valid values: 0 to 4294967295 -# Default value: 14976 +# Default value: 604800 #--------------------------------------- -;RememberLoginTTL = 14976 +;RememberLoginTTL = 604800 # Secret to be used for encrypting private JWT objects # Valid values: 0 to 18446744073709551615 From c37bdb26632bcfb0834657749bf155e869937f11 Mon Sep 17 00:00:00 2001 From: kambizzandi Date: Sun, 3 Apr 2022 16:57:51 +0430 Subject: [PATCH 2/2] migration files for session last renew --- ...20220401_144613_AAA_add_jwt_to_session.sql | 4 + ...403_164811_AAA_some_changes_to_session.sql | 509 ++++++++++++++++++ 2 files changed, 513 insertions(+) create mode 100644 Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql create mode 100644 Modules/Account/migrations/db/m20220403_164811_AAA_some_changes_to_session.sql diff --git a/Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql b/Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql new file mode 100644 index 00000000..9824b85b --- /dev/null +++ b/Modules/Account/migrations/db/m20220401_144613_AAA_add_jwt_to_session.sql @@ -0,0 +1,4 @@ +/* Migration File: m20220401_144613_AAA_add_jwt_to_session.sql */ + +ALTER TABLE `tblActiveSessions` + ADD COLUMN `ssnJWT` TEXT NULL AFTER `ssnRemember`; diff --git a/Modules/Account/migrations/db/m20220403_164811_AAA_some_changes_to_session.sql b/Modules/Account/migrations/db/m20220403_164811_AAA_some_changes_to_session.sql new file mode 100644 index 00000000..26593bff --- /dev/null +++ b/Modules/Account/migrations/db/m20220403_164811_AAA_some_changes_to_session.sql @@ -0,0 +1,509 @@ +/* Migration File: m20220403_164811_AAA_some_changes_to_session.sql */ + +DROP PROCEDURE IF EXISTS `spSession_UpdateActivity`; + +ALTER TABLE `tblActiveSessions` + DROP INDEX `ssnLastActivity`; + +ALTER TABLE `tblActiveSessions` + ADD COLUMN `ssnLastRenew` DATETIME NULL DEFAULT NULL AFTER `ssnLastActivity`; + +ALTER TABLE `tblActiveSessions` + DROP COLUMN `ssnJWT`; + +DROP PROCEDURE IF EXISTS `spSessionRetrieveInfo`; +DELIMITER ;; +CREATE PROCEDURE `spSessionRetrieveInfo`( + IN `iSSID` CHAR(32), + IN `iIP` VARCHAR(50), + IN `iIssuance` BIGINT UNSIGNED +) +LANGUAGE SQL +NOT DETERMINISTIC +CONTAINS SQL +SQL SECURITY DEFINER +COMMENT '' +BEGIN + DECLARE vSessionStatus CHAR(1); + DECLARE vUserStatus CHAR(1); + DECLARE vUserID BIGINT UNSIGNED; + DECLARE vErr VARCHAR(500); + DECLARE vLastRenew BIGINT UNSIGNED; + + DECLARE EXIT HANDLER FOR SQLEXCEPTION + BEGIN + GET DIAGNOSTICS CONDITION 1 vErr = MESSAGE_TEXT; + INSERT INTO tblActionLogs + SET tblActionLogs.atlBy_usrID = vUserID, + tblActionLogs.atlType = 'Session.act', + tblActionLogs.atlDescription = JSON_OBJECT( + "err", vErr, + "iSSID", iSSID, + "iIP", iIP, + "iIssuance", iIssuance + ); + ROLLBACK; + RESIGNAL; + END; + + SELECT tblActiveSessions.ssnStatus, + tblActiveSessions.ssn_usrID, + tblActiveSessions.ssnLastRenew, + tblUser.usrStatus + INTO vSessionStatus, + vUserID, + vLastRenew, + vUserStatus + FROM tblActiveSessions + JOIN tblUser + ON tblUser.usrID = tblActiveSessions.ssn_usrID + WHERE tblActiveSessions.ssnKey = iSSID; + + IF (NOT ISNULL(vLastRenew) AND (vLastRenew != iIssuance)) THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '501:JWT not replaced by client'; + END IF; + + IF ISNULL(vSessionStatus) THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Invalid Session'; + ELSEIF vSessionStatus = 'E' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Session expired'; + ELSEIF vSessionStatus = 'F' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:You were fired out. contact admin'; + ELSEIF vSessionStatus = 'G' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:You were logged out'; + ELSEIF vUserStatus = 'B' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '405:User Blocked. Ask administrator'; + ELSEIF vUserStatus = 'R' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '405:User Removed. Ask administrator'; + ELSEIF vUserStatus != 'A' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '501:Invalid Session State'; + END IF; + + -- CHECK for same IP was discarded + SET vLastRenew = UNIX_TIMESTAMP(); + + UPDATE tblActiveSessions + SET tblActiveSessions.ssnLastActivity = NOW(), + tblActiveSessions.ssnLastRenew = vLastRenew + WHERE tblActiveSessions.ssnKey = iSSID; + + SELECT tblUser.usrID, + tblUser.usrName, + tblUser.usrFamily, + tblUser.usrEmail, + tblUser.usr_rolID, + tblUser.usrApprovalState, + tblRoles.rolName, + fnGetAllPrivs(tblUser.usr_rolID, tblUser.usrSpecialPrivs) AS privs, + NOT ISNULL(tblUser.usrPass) AS hasPass, + tblUser.usrStatus, + iSSID AS ssnKey, + vLastRenew AS Issuance + FROM tblUser + JOIN tblRoles + ON tblRoles.rolID = tblUser.usr_rolID + WHERE tblUser.usrID = vUserID; +END;; +DELIMITER ; + +DROP PROCEDURE IF EXISTS `spApproval_Accept`; +DELIMITER ;; +CREATE PROCEDURE `spApproval_Accept`( + IN `iBy` CHAR(1), + IN `iKey` VARCHAR(128), + IN `iCode` VARCHAR(50), + IN `iLogin` TINYINT, + IN `iLoginIP` VARCHAR(50), + IN `iLoginInfo` JSON, + IN `iLoginRemember` TINYINT, + IN `iTTL` INT +) +LANGUAGE SQL +NOT DETERMINISTIC +CONTAINS SQL +SQL SECURITY DEFINER +COMMENT '' +BEGIN + DECLARE vAprID BIGINT UNSIGNED; + DECLARE vUserID BIGINT UNSIGNED; + DECLARE vNewKey VARCHAR(128); + DECLARE vByType CHAR(1); + DECLARE vAprStatus CHAR(1); + DECLARE vIsExpired BOOL; + DECLARE vSessionGUID VARCHAR(32); + + DECLARE EXIT HANDLER FOR SQLEXCEPTION + BEGIN + ROLLBACK; + RESIGNAL; + END; + + IF ISNULL(iKey) THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Invalid key'; + END IF; + + -- 24*60*60 + -- never expire? + IF iTTL = 0 THEN + SET iTTL = NULL; + END IF; + + SELECT tblApprovalRequest.aprID + , tblApprovalRequest.apr_usrID + , tblApprovalRequest.aprApprovalKey + , tblApprovalRequest.aprRequestedFor + , tblApprovalRequest.aprStatus + , (tblApprovalRequest.aprStatus = 'E' + OR (iTTL IS NOT NULL + AND tblApprovalRequest.aprSentDate IS NOT NULL + AND TIME_TO_SEC(TIMEDIFF(NOW(), tblApprovalRequest.aprSentDate)) > iTTL) + ) + INTO vAprID + , vUserID + , vNewKey + , vByType + , vAprStatus + , vIsExpired + FROM tblApprovalRequest + LEFT JOIN tblUser + ON tblUser.usrID = tblApprovalRequest.apr_usrID + WHERE tblApprovalRequest.aprApprovalKey = iKey +-- AND tblApprovalRequest.aprIsForLogin = 0 + AND tblApprovalRequest.aprApprovalCode = iCode + AND tblApprovalRequest.aprStatus IN ('N', 'S', 'A', '1', '2', 'E') +-- N: New, S: Sent, A: Applied, R: Removed, 1: FirstTry, 2:SecondTry, E: Expired + ORDER BY aprRequestDate DESC + LIMIT 1 + ; + + IF vAprStatus = 'N' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Code not sent to the client'; + END IF; + + IF vAprStatus = 'A' THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Already applied before'; + END IF; + + IF ISNULL(vUserID) THEN + IF (iBy = 'M') THEN + UPDATE tblApprovalRequest + SET aprStatus = IF(aprStatus = 'S', '1', + IF(aprStatus = '1', '2', 'E')) + WHERE aprApprovalKey = iKey +-- AND aprIsForLogin = 0 + AND aprStatus IN ('S', '1', '2') + ; + END IF; + + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Invalid user or code'; + END IF; + + IF vIsExpired THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Code expired'; + END IF; + + START TRANSACTION; + + UPDATE tblApprovalRequest + SET aprApplyDate = NOW(), + aprStatus = 'A' + WHERE aprID = vAprID; + + IF vByType = 'E' THEN + UPDATE tblUser + SET usrEmail = vNewKey, + usrApprovalState = IF(usrApprovalState IN ('N','E'), 'E', 'A'), + usrStatus = IF(usrStatus IN('A','V'), 'A', usrStatus), + usrUpdatedBy_usrID = vUserID + WHERE usrID = vUserID + ; + ELSE + UPDATE tblUser + SET usrMobile = vNewKey, + usrApprovalState = IF(usrApprovalState IN ('N','M'), 'M', 'A'), + usrStatus = IF(usrStatus IN('A','V'), 'A', usrStatus), + usrUpdatedBy_usrID = vUserID + WHERE usrID = vUserID + ; + END IF; + + IF iLogin = 1 THEN + SET vSessionGUID = SUBSTRING({{dbprefix}}CommonFuncs.guid(NULL), 1, 32); + + INSERT + INTO tblActiveSessions + SET tblActiveSessions.ssnKey = vSessionGUID, + tblActiveSessions.ssn_usrID = vUserID, + tblActiveSessions.ssnIP = INET_ATON(iLoginIP), + tblActiveSessions.ssnRemember = iLoginRemember, + tblActiveSessions.ssnLastActivity = NOW(), + tblActiveSessions.ssnInfo = iLoginInfo; + + UPDATE tblUser + SET tblUser.usrLastLogin = NOW(), + tblUser.usrActiveSessions = tblUser.usrActiveSessions + 1 + WHERE tblUser.usrID = vUserID + ; + + INSERT + INTO tblActionLogs + SET tblActionLogs.atlBy_usrID = vUserID, + tblActionLogs.atlType = 'UserLoggedIn' + ; + END IF; + + COMMIT; + + SELECT tblUser.usrID, + tblUser.usrName, + tblUser.usrFamily, + tblUser.usrEmail, + tblUser.usr_rolID, + tblUser.usrApprovalState, + tblRoles.rolName, + fnGetAllPrivs(tblUser.usr_rolID, tblUser.usrSpecialPrivs) AS privs, + NOT ISNULL(tblUser.usrPass) AS hasPass, + tblUser.usrStatus, + vSessionGUID AS ssnKey, + UNIX_TIMESTAMP() AS Issuance + FROM tblUser + JOIN tblRoles + ON tblRoles.rolID = tblUser.usr_rolID + WHERE tblUser.usrID = vUserID + ; +END;; +DELIMITER ; + +DROP PROCEDURE IF EXISTS `spLogin`; +DELIMITER ;; +CREATE PROCEDURE `spLogin`( + IN `iLogin` VARCHAR(100), + IN `iIP` VARCHAR(50), + IN `iPass` CHAR(32), + IN `iSalt` VARCHAR(50), + IN `iInfo` JSON, + IN `iRemember` TINYINT, + IN `iOAuthInfo` VARCHAR(10000) +) +LANGUAGE SQL +NOT DETERMINISTIC +CONTAINS SQL +SQL SECURITY DEFINER +COMMENT '' +BEGIN + DECLARE vLoginStatus CHAR(1); + DECLARE vUserID BIGINT UNSIGNED; +-- DECLARE InnerRolID BIGINT; + DECLARE vSessionGUID CHAR(32); + DECLARE vLastOAuthInfo JSON; + DECLARE vUserApprovalState CHAR(1); + DECLARE vUserEmail CHAR(50); + DECLARE vMessage CHAR(128); + + DECLARE EXIT HANDLER FOR SQLEXCEPTION + BEGIN + ROLLBACK; + RESIGNAL; + END; + + START TRANSACTION; + + SELECT IF (tblUser.usrMaxSessions > 0 AND tblUser.usrMaxSessions - tblUser.usrActiveSessions <= 0, + 'O', + IF (fnPasswordsAreEqual(iPass, iSalt, tblUser.usrPass), + tblUser.usrStatus, + 'I' + ) + ), + tblUser.usrID, + tblUserExtraInfo.ueiOAuthAccounts, + usrApprovalState, + usrEmail + INTO vLoginStatus, + vUserID, + vLastOAuthInfo, + vUserApprovalState, + vUserEmail + FROM tblUser + LEFT JOIN tblUserExtraInfo + ON tblUserExtraInfo.uei_usrID = tblUser.usrID + WHERE ( + tblUser.usrEmail = iLogin + OR tblUser.usrMobile = iLogin + OR ( + NOT ISNULL(iOAuthInfo) + AND JSON_EXTRACT(iOAuthInfo, "$.type") = 'Linkedin' + AND tblUserExtraInfo.ueiOAuthAccounts->"$.Linkedin" = JSON_EXTRACT(iOAuthInfo, "$.id") + ) + ) + AND tblUser.usrStatus IN ('A','V'); + + IF NOT ISNULL(vUserApprovalState) AND vUserApprovalState NOT IN ('A', IF(vUserEmail = iLogin, 'E', 'M')) THEN + SET vMessage = CONCAT('428:', IF(vUserEmail = iLogin, 'Email', 'Mobile'), ' not approved'); + + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = vMessage; + END IF; + + IF ISNULL(vLoginStatus) THEN + IF ISNULL(iOAuthInfo) THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Invalid User or Password'; + ELSE +-- CALL spSignup(iBy, iLogin, iPass, iRole, iIP, iName, iFamily, iSpecialPrivs, iMaxSessions, oUserID); +-- TODO create wallet + INSERT + INTO tblUser + SET tblUser.usrName = JSON_EXTRACT(iOAuthInfo, "$.name"), + tblUser.usrFamily = JSON_EXTRACT(iOAuthInfo, "$.family"), + tblUser.usrEmail = JSON_EXTRACT(iOAuthInfo, "$.email"), + tblUser.usrApprovalState = 'E'; + + SET vUserID = LAST_INSERT_ID(); + + INSERT + INTO tblUserExtraInfo + SET tblUserExtraInfo.uei_usrID = vUserID, + tblUserExtraInfo.ueiPhoto = JSON_EXTRACT(iOAuthInfo, "$.photo"), + tblUserExtraInfo.ueiOAuthAccounts = JSON_OBJECT(JSON_EXTRACT(iOAuthInfo, "$.type"), JSON_EXTRACT(iOAuthInfo, "$.id")); + + SET vLoginStatus = 'H'; + END IF; + END IF; + + CASE vLoginStatus + WHEN 'O' THEN + INSERT + INTO tblActionLogs + SET tblActionLogs.atlType = 'OverSession', + tblActionLogs.atlBy_usrID = vUserID; + COMMIT; + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '409:Max sessions used close old sessions'; + + WHEN 'I' THEN + INSERT + INTO tblActionLogs + SET tblActionLogs.atlType = 'InvalidPass', + tblActionLogs.atlBy_usrID = vUserID; + COMMIT; + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Invalid user or Password'; + + WHEN 'R' THEN + INSERT + INTO tblActionLogs + SET tblActionLogs.atlType = 'UserRemoved', + tblActionLogs.atlBy_usrID = vUserID; + COMMIT; + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '403:User Removed. Ask administrator'; + + WHEN 'B' THEN + INSERT + INTO tblActionLogs + SET tblActionLogs.atlType = 'UserBlocked', + tblActionLogs.atlBy_usrID = vUserID; + COMMIT; + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '403:User Blocked. Ask administrator'; + + WHEN 'V' THEN + IF ISNULL(iOAuthInfo) THEN + INSERT + INTO tblActionLogs + SET tblActionLogs.atlType = 'UserNotApprovedYet', + tblActionLogs.atlBy_usrID = vUserID; + COMMIT; + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '428:You must approve either email or mobile'; + END IF; + + WHEN 'A' THEN + SET @a = 1; + END CASE; + + IF NOT ISNULL(iOAuthInfo) AND vLoginStatus != 'H' THEN + IF ISNULL(vLastOAuthInfo) THEN + INSERT + INTO tblUserExtraInfo + SET tblUserExtraInfo.uei_usrID = vUserID, + tblUserExtraInfo.ueiPhoto = JSON_EXTRACT(iOAuthInfo, "$.photo"), + tblUserExtraInfo.ueiOAuthAccounts = JSON_OBJECT(JSON_EXTRACT(iOAuthInfo, "$.type"), JSON_EXTRACT(iOAuthInfo, "$.id")) + ON DUPLICATE KEY UPDATE + tblUserExtraInfo.uei_usrID = vUserID, + tblUserExtraInfo.ueiPhoto = JSON_EXTRACT(iOAuthInfo, "$.photo"), + tblUserExtraInfo.ueiOAuthAccounts = JSON_OBJECT(JSON_EXTRACT(iOAuthInfo, "$.type"), JSON_EXTRACT(iOAuthInfo, "$.id")); + ELSE + UPDATE tblUserExtraInfo + SET tblUserExtraInfo.ueiPhoto = JSON_EXTRACT(iOAuthInfo, "$.photo"), + tblUserExtraInfo.ueiOAuthAccounts = JSON_MERGE( + astOAuthInfo, + JSON_OBJECT(JSON_EXTRACT(iOAuthInfo, "$.type"), JSON_EXTRACT(iOAuthInfo, "$.id")) + ), + tblUserExtraInfo.ueiUpdatedBy_usrID = vUserID + WHERE tblUserExtraInfo.uei_usrID = vUserID; + + INSERT + INTO tblActionLogs + SET tblActionLogs.atlBy_usrID = vUserID, + tblActionLogs.atlType = 'UserOAuthUpdated'; + END IF; + END IF; + + SET vSessionGUID = SUBSTRING({{dbprefix}}CommonFuncs.guid(NULL), 1, 32); + + INSERT + INTO tblActiveSessions + SET tblActiveSessions.ssnKey = vSessionGUID, + tblActiveSessions.ssn_usrID = vUserID, + tblActiveSessions.ssnIP = INET_ATON(iIP), + tblActiveSessions.ssnRemember = iRemember, + tblActiveSessions.ssnLastActivity = NOW(), + tblActiveSessions.ssnInfo = iInfo; + + UPDATE tblUser + SET tblUser.usrLastLogin = NOW(), + tblUser.usrActiveSessions = tblUser.usrActiveSessions + 1 + WHERE tblUser.usrID = vUserID; + + INSERT + INTO tblActionLogs + SET tblActionLogs.atlBy_usrID = vUserID, + tblActionLogs.atlType = 'UserLoggedIn'; + + COMMIT; + + SELECT tblUser.usrID, + tblUser.usrName, + tblUser.usrFamily, + tblUser.usrEmail, + tblUser.usr_rolID, + tblUser.usrApprovalState, + tblRoles.rolName, + fnGetAllPrivs(tblUser.usr_rolID, tblUser.usrSpecialPrivs) AS privs, + NOT ISNULL(tblUser.usrPass) AS hasPass, + tblUser.usrStatus, + vSessionGUID AS ssnKey, + UNIX_TIMESTAMP() AS Issuance + FROM tblUser + JOIN tblRoles + ON tblRoles.rolID = tblUser.usr_rolID + WHERE tblUser.usrID = vUserID; +END;; +DELIMITER ; + +DROP PROCEDURE IF EXISTS `spLogin_VerifyByMobileCode`;