DB stored procedure returns incorrect results to Cisco ISE

[cisco-garyoppel]

DB stored procedure returns incorrect results to Cisco ISE

iPSK Manager's stored procedures play a key role in how ISE handles the requests for access.

Affected Stored Procedures

• iPSK_MACLookup
• iPSK_MACLookupNonExpired

Procedures execute the following SQL Statements and return the results to ISE when an Endpoint is Disabled, causing ISE to continue to the next Identity Store within the Identity Sequence

SELECT 3, 0, 'odbc','ODBC Authen Error';

This causes the DB to always return an incorrect response when an endpoint is either disabled or expired.

Expected Results The affected stored procedures should return the proper response according to the status of the endpoint within the iPSK Manager Database.

Update the Stored Procedures as per the Credential Check for ODBC Database section located at Cisco ISE's ODBC Identity Source Documentation

[cisco-garyoppel]

Updated Stored Procedures

• iPSK_MACLookup
• iPSK_MACLookupNonExpired

Database values Updated

• Updated 'db-schema' value to '2' within the settings table

Other Required Updates

• Updated Installation files (install.php & install.inc.php) to include the updated stored procedures for new installations

Please review the DB_CHANGELOG @ https://github.com/CiscoSE/iPSK-Manager/blob/master/DB_CHANGELOG.md

---

Below are the updated stored procedures for the database:

iPSK_MACLookup

SELECT UCASE(REPLACE(REPLACE(username,':',''),'-','')) INTO @strippedMAC;

SELECT CONCAT_WS(':',SUBSTRING(@strippedMAC,1,2),SUBSTRING(@strippedMAC,3,2),SUBSTRING(@strippedMAC,5,2),SUBSTRING(@strippedMAC,7,2),SUBSTRING(@strippedMAC,9,2),SUBSTRING(@strippedMAC,11,2)) INTO @formattedMAC;

IF EXISTS (SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC) THEN
    IF NOT (SELECT expirationDate FROM endpoints WHERE endpoints.macAddress = @formattedMAC) = 0 THEN
        IF NOT (SELECT accountExpired FROM endpoints WHERE endpoints.macAddress = @formattedMAC) = 'True' THEN
            IF (SELECT expirationDate FROM endpoints WHERE endpoints.macAddress = @formattedMAC) < UNIX_TIMESTAMP(NOW()) THEN
                UPDATE `endpoints` SET `endpoints`.`accountExpired` = 'True' WHERE `endpoints`.`macAddress` = @formattedMAC;
            END IF;
        END IF;
    END IF;
END IF;

IF EXISTS (SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC) THEN
    UPDATE `endpoints` SET `endpoints`.`lastAccessed` = CURRENT_TIMESTAMP WHERE `endpoints`.`macAddress` = @formattedMAC;

    IF EXISTS(SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC AND accountEnabled = 1) THEN
        SELECT 0,11,'This is a very good user, give him all access','no error';
    ELSE
        IF EXISTS(SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC AND accountEnabled = 0) THEN
            SELECT 10001, 0, 'Account Disabled','ODBC Authen Error';
        ELSE
            SELECT 4, 0, 'odbc','ODBC Authen Error';
        END IF;
    END IF;
ELSE
    IF EXISTS(SELECT * FROM `unknownEndpoints` WHERE `unknownEndpoints`.`macAddress` = @formattedMAC) THEN
        UPDATE `unknownEndpoints` SET `unknownEndpoints`.`lastSeen` = CURRENT_TIMESTAMP WHERE `unknownEndpoints`.`macAddress` = @formattedMAC;
    ELSE
        INSERT INTO `unknownEndpoints` (`macAddress`,`createdBy`) VALUES(@formattedMAC ,'SYSTEM-ODBC');
    END IF;

    SELECT 1, 0, 'odbc','ODBC Authen Error';

END IF;

iPSK_MACLookupNonExpired

SELECT UCASE(REPLACE(REPLACE(username,':',''),'-','')) INTO @strippedMAC;

SELECT CONCAT_WS(':',SUBSTRING(@strippedMAC,1,2),SUBSTRING(@strippedMAC,3,2),SUBSTRING(@strippedMAC,5,2),SUBSTRING(@strippedMAC,7,2),SUBSTRING(@strippedMAC,9,2),SUBSTRING(@strippedMAC,11,2)) INTO @formattedMAC;

IF EXISTS (SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC) THEN
    IF NOT (SELECT expirationDate FROM endpoints WHERE endpoints.macAddress = @formattedMAC) = 0 THEN
        IF NOT (SELECT accountExpired FROM endpoints WHERE endpoints.macAddress = @formattedMAC) = 'True' THEN
            IF (SELECT expirationDate FROM endpoints WHERE endpoints.macAddress = @formattedMAC) < UNIX_TIMESTAMP(NOW()) THEN
                UPDATE `endpoints` SET `endpoints`.`accountExpired` = 'True' WHERE `endpoints`.`macAddress` = @formattedMAC;
            END IF;
        END IF;
    END IF;
END IF;

IF EXISTS (SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC) THEN
    UPDATE `endpoints` SET `endpoints`.`lastAccessed` = CURRENT_TIMESTAMP WHERE `endpoints`.`macAddress` = @formattedMAC;

    IF EXISTS(SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC AND accountEnabled = 1 AND expirationDate = 0) THEN
        SELECT 0,11,'This is a very good user, give him all access','no error';
    ELSE
        IF EXISTS(SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC AND accountEnabled = 1 AND expirationDate > UNIX_TIMESTAMP(NOW())) THEN
            SELECT 0,11,'This is a very good user, give him all access','no error';
        ELSE
            IF EXISTS(SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC AND accountEnabled = 0) THEN
                SELECT 10001, 0, 'Account Disabled','ODBC Authen Error';
            ELSE
                IF EXISTS(SELECT * FROM endpoints WHERE endpoints.macAddress = @formattedMAC AND expirationDate < UNIX_TIMESTAMP(NOW())) THEN
                    SELECT 10002, 0, 'Account Expired','ODBC Authen Error';
                ELSE
                    SELECT 4, 0, 'odbc','ODBC Authen Error';
                END IF;
            END IF;
        END IF;
    END IF;
ELSE
    IF EXISTS(SELECT * FROM `unknownEndpoints` WHERE `unknownEndpoints`.`macAddress` = @formattedMAC) THEN
        UPDATE `unknownEndpoints` SET `unknownEndpoints`.`lastSeen` = CURRENT_TIMESTAMP WHERE `unknownEndpoints`.`macAddress` = @formattedMAC;
    ELSE
        INSERT INTO `unknownEndpoints` (`macAddress`,`createdBy`) VALUES(@formattedMAC ,'SYSTEM-ODBC');
    END IF;

    SELECT 1, 0, 'odbc','ODBC Authen Error';

END IF;

This issue has been resolved, closing with Commit f7f5b7e0db8179586b3bbbd29797610099787c38