Security

Reply
Super Contributor I

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

We have this ties your open ssid with Mac auth. Are you doing Mac auth?
==========
Ryan Holland, ACDX #1 ACMX #1
The Ohio State University
Guru Elite

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

You will need to write custom SQL queries to do this.


Thanks,
Tim

| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
MVP Expert

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

Ryan, ok I understand that you have a different process for this than I have. While you have some sort of self-registration mactrac thing going on, I want to pre-register the device with some attributes linking the device to the user.

 

Tim, problem is that role_id is not available in the tips_user_db. My Aruba TAC contact wasn't able to figure out in which table it was either, but hopefully he will find a way. Perhaps you already know?


Regards
John Solberg

-ACMX #316 :: ACCX #902 :: ACSA
Aruba Partner Ambassador
Intelecom/NetNordic - Norway
----------------------------
Remember to Kudo if a post helped you! || Problem Solved? Click "Accept as Solution" in a post!
Guru Elite

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

This isn't exactly what you're looking for but it's a good example of how to join the informations from the different tables to get the role_id.

 

SELECT 'NOPASSWORD' AS User_Password,
CASE WHEN enabled = FALSE THEN 225
    WHEN ((expire_time is not null AND expire_time <= now())) THEN 226
    ELSE 0
END AS Account_Status, sponsor_name, t2.tag_value AS role_id
FROM tips_guest_users g
LEFT JOIN tips_guest_user_tag_mappings t1 ON (g.id = t1.instance_id)
LEFT JOIN tips_tag_values t2 ON (t1.tag_value_id = t2.id)
WHERE (g.guest_type = 'DEVICE')
  AND (g.user_id = UPPER('%{Connection:Client-Mac-Address-Hyphen}'))
  AND (t2.tag_id = (SELECT id FROM tips_tag_definitions WHERE name = 'Role ID' AND entity_id = (SELECT id FROM tips_dic_internal WHERE dic_prefix = 'GuestUser')));

| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
MVP Expert

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

Tim, that is awsome!

 

I manually created a copy of the [Guest Device Repository] and did some small adjustments to the SQL you provided and voila - I got the darn role_id attribute to authorize my devices!

I found just one reference on google to that SQL you provided so it's a **bleep** well kept secret you dug up there!

 

Thanks a bunch!

 


Regards
John Solberg

-ACMX #316 :: ACCX #902 :: ACSA
Aruba Partner Ambassador
Intelecom/NetNordic - Norway
----------------------------
Remember to Kudo if a post helped you! || Problem Solved? Click "Accept as Solution" in a post!
Guru Elite

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

Thanks to @SethFiermonti on this one as well.


| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
Highlighted
Frequent Contributor I

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

Hoping this is still relevant.

 

Came across this issue just this morning as I wanted to use the Guest Device Respository for authorisation, specifically tagerting the Role ID. I'm using 6.6 and it appears the tips_guest_user_tag_mappings structure has been replaced by utilising the "attributes" field in JSONB format in the tips_guest_users table.

 

After some work, I managed to pull back the Role_ID from the attribute field using the below by adding the device_role attribute to the existing filter:

 

SELECT user_credential(password) AS User_Password, CASE WHEN enabled = FALSE THEN 225 WHEN ((expire_time is not null AND expire_time <= now())) THEN 226 ELSE 0 END AS Account_Status, sponsor_name, CASE WHEN expire_time > now() THEN CAST(EXTRACT(epoch FROM (expire_time - NOW())) AS INTEGER) ELSE 0 END AS remaining_expiration, attributes->>'Role ID' as device_role FROM tips_guest_users WHERE ((guest_type = 'DEVICE') AND (user_id = UPPER('%{Connection:Client-Mac-Address-Hyphen}')))

 

Hope this helps!

Any amount of Kudos will be greatly appreciated!!!
Guru Elite

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

You shouldn't need any custom attributes to get the role ID. 

| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
Occasional Contributor II

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

We are having issues with the custom query when upgrading to CPPM 6.6.  As noted the tips_guest_user_tag_mappings/values are now mostly stored in the attributes blob for an entry in the tips_guest_users table.  I've updated the query with what I think is the appropriate SQL.

SELECT 'NOPASSWORD' AS User_Password, 
  CASE 
    WHEN enabled = FALSE THEN 225 
    WHEN ((expire_time is not null AND expire_time <= now())) THEN 226 
    ELSE 0 
    END AS Account_Status, 
    sponsor_name, 
    attributes->>'Role ID' AS role_id 
  FROM tips_guest_users g 
  WHERE (g.guest_type = 'DEVICE') 
    AND (g.user_id = UPPER('%{Connection:Client-Mac-Address-Hyphen}'));

I completely removed the second AND in the WHERE clause for two reasons:

  1. I couldn't find the tips_tag_values.tag_id column or its like anywhere in the updated database
  2. That second AND statement seemed like it was just trying to validate that the Role ID for a device was actually a GuestUser role.  Maybe I'm being ignorant but that seems a bit redundent given the datasource.

Here is the removed AND statement (same as Tim's post).  Any idea if I should be validating it some other way?

...
AND (t2.tag_id = 
        (SELECT id 
        FROM tips_tag_definitions 
        WHERE name = 'Role ID' 
          AND entity_id = 
              (SELECT id 
              FROM tips_dic_internal 
              WHERE dic_prefix = 'GuestUser')

  

Guru Elite

Re: Using ClearPass guest device registration for additional authorization of 802.1X devices

Aaron - is this for the Enterasys MAC-auth workaround?


| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: