Cisco ISE and Oracle DB integration. Fetch Attribute Function

[murat001]

Hi All

I try to make external identity ODBC integration with Oracle and ISE. and we have a table like following. According to this table i need to get framed-ip-address as attribute for return to the atuhenticated users. how it should be fetch attribute function or procedure in oracle db.  How should i change example function on the ISE according to example table ? 

table ; 

Examle Fetch Atrribute. 

create or replace function Retrieve-ATTRIBUTE

(

  ise_username IN VARCHAR2,

  ise_result OUT int

) return sys_refcursor as

BEGIN

  declare

    c integer;                      //     should the c variable be integer or different (like boolen etc.)                     

    resultSet SYS_REFCURSOR;

  begin

    select count(*) into c from USERS where USERS.USERNAME = ise_username;   // according the  screenshot of example table is this line correct?

    if c > 0 then

        ise_result := 0;

        open resultSet for select 'green' as 'eye_color', 1 as 'floor', 'true' as 'is_certified' from dual;   //  how can i change this line for i can get framed-ip-address column as attribute ? 

    ELSE

        ise_result := 3;

        open resultSet for select 0 from dual where 1=2;

    END IF;

    return resultSet;

  end;

END ;

thanks for your help ? 

[marce1000 · Accepted solution]

To retrieve the "framed-ip-address" attribute from the "USERS" table, you can modify the "SELECT" statement in the function as follows:
  SELECT FRAMED_IP_ADDRESS as "framed-ip-address" FROM USERS WHERE USERNAME = ise_username;
  
 This statement will select the "framed-ip-address" attribute from the "USERS" table where the "USERNAME" matches the "ise_username" parameter.

You can then modify the "OPEN" statement to return the "resultSet" cursor with the "framed-ip-address" attribute as follows:
   OPEN resultSet FOR SELECT FRAMED_IP_ADDRESS as "framed-ip-address" FROM USERS WHERE USERNAME = ise_username;

 The "c" variable can remain an integer as it is used to check if the username exists in the table.
             So the modified function would look something like this:
CREATE OR REPLACE FUNCTION Retrieve_ATTRIBUTE (
         ise_username IN VARCHAR2,
         ise_result OUT INT
 ) RETURN SYS_REFCURSOR AS
      resultSet SYS_REFCURSOR;
BEGIN
         DECLARE
             c INTEGER;
        BEGIN
      SELECT COUNT(*) INTO c FROM USERS WHERE USERNAME = ise_username;
      IF c > 0 THEN
              ise_result := 0;
              OPEN resultSet FOR SELECT FRAMED_IP_ADDRESS as "framed-ip-address" FROM USERS WHERE USERNAME =               ise_username;
       ELSE
             ise_result := 3;
             OPEN resultSet FOR SELECT 0 FROM dual WHERE 1 = 2;
     END IF;
     RETURN resultSet;
   END;
END;

M.

[murat001]

Thanks you so much marce. i will try it. I really appreciated for your quick response.