Security

last person joined: yesterday 

Forum to discuss Enterprise security using HPE Aruba Networking NAC solutions (ClearPass), Introspect, VIA, 360 Security Exchange, Extensions, and Policy Enforcement Firewall (PEF).
Back to discussions
Expand all | Collapse all

oracel clearpass sql statement

This thread has been viewed 1 times

  • 1.  oracel clearpass sql statement

    Posted Nov 01, 2014 12:40 AM

    Hi,

    I'm writing a simple auth source that queries an oracle database to see  if a mac address is present. If it is, I can assign a particular role to the connection.

     

    If I have

     

    select COUNT(*)   FROM sns.quarantined_macs WHERE mac = UPPER('%{Connection:Client-Mac-Address-Hyphen}')

     

    as my auth source attribute, then when i click on save, it seems to get saved correctly.

     

    If I have

     

    select COUNT(*) into quarantined_mac  FROM sns.quarantined_macs WHERE mac = UPPER('%{Connection:Client-Mac-Address-Hyphen}')

     

    where quarantined_mac is defined as an integer, I get

     

    The filter has been saved but has the following error:
    Invalid SQL syntax - ORA-00905: missing keyword

     

     

     

     

     

    So what;s wring with the sql ?

    Rgds

    Alex

     



  • 2.  RE: oracel clearpass sql statement

    Posted Nov 03, 2014 06:46 AM

    o.k. The following is accepted by clearpass when defining the Oracle SQL statement;-

     

    declare quarantined_mac number;begin select COUNT(*) into quarantined_mac FROM sns.quarantined_macs WHERE mac = UPPER('%{Connection:Client-Mac-Address-Hyphen}');end;

     

    Just need to check that clearpass picks up the result from the declared number

     

    Rgds

    A



  • 3.  RE: oracel clearpass sql statement

    EMPLOYEE
    Posted Nov 03, 2014 11:14 PM
    Thanks for letting us know. It's always nice when someone posts an update. I'm sure it will help out others. ;)


  • 4.  RE: oracel clearpass sql statement

    Posted Nov 04, 2014 10:52 AM

    o.k. next problem and for me its due to a lack of info in the clearpass online help.

     

    It seems that for Oracle I need to supply a service name somewhere but I don't know where I put it in the available clearpass fields and theres nothing in the online help other than "use these fields for a generic sql service"

     

    The error message I get is

     

    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
    Action:

    - Wait a moment and try to connect a second time.

    - Check which services are currently known by the listener by executing: lsnrctl services <listener name>

    - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

    - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

    - Check for an event in the listener.log file.

     

     

    Well at least its a step forward :-))