topic Re: Cisco ISE and Oracle DB integration. Fetch Attribute Function in Network Access Control

Cisco ISE and Oracle DB integration. Fetch Attribute Function

murat001 — Fri, 12 May 2023 06:52:36 GMT

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 ?

Re: Cisco ISE and Oracle DB integration. Fetch Attribute Function

Mark Elsen — Fri, 12 May 2023 08:49:14 GMT

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;

Re: Cisco ISE and Oracle DB integration. Fetch Attribute Function

murat001 — Fri, 12 May 2023 09:13:46 GMT

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