Security

Reply
Highlighted
All-Decade MVP 2020

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
Highlighted
Moderator

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


If this response is more than 1 year old, it may no longer be accurate. Please consult official Aruba documentation, TAC or your Aruba SE.

| Aruba Alumni | @timcappalli | timcappalli.me |

Highlighted
MVP

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!
Highlighted
Moderator

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')));


If this response is more than 1 year old, it may no longer be accurate. Please consult official Aruba documentation, TAC or your Aruba SE.

| Aruba Alumni | @timcappalli | timcappalli.me |

View solution in original post

Highlighted
MVP

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!
Highlighted
Moderator

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

Thanks to @SethFiermonti on this one as well.



If this response is more than 1 year old, it may no longer be accurate. Please consult official Aruba documentation, TAC or your Aruba SE.

| Aruba Alumni | @timcappalli | timcappalli.me |

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!!!
Highlighted
Moderator

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. 


If this response is more than 1 year old, it may no longer be accurate. Please consult official Aruba documentation, TAC or your Aruba SE.

| Aruba Alumni | @timcappalli | timcappalli.me |

Highlighted
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')

  

Highlighted
Moderator

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

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



If this response is more than 1 year old, it may no longer be accurate. Please consult official Aruba documentation, TAC or your Aruba SE.

| Aruba Alumni | @timcappalli | timcappalli.me |

Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: