Security

Reply
Super Contributor I

oracel clearpass sql statement

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

 

Super Contributor I

Re: oracel clearpass sql statement

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

Aruba

Re: oracel clearpass sql statement

Thanks for letting us know. It's always nice when someone posts an update. I'm sure it will help out others. ;)
Thank You,
Troy

--Give Kudos: found something helpful, important, or cool? Click Kudos Star in a post.

--Problem Solved? Click "Accepted Solution" in a post.
Super Contributor I

Re: oracel clearpass sql statement

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 :-))

 

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