[GIS] WMS with Access Control (ACL) for different users? Or solution for thousands of points on web map

large datasetsopenlayers-2Securitywms

I have a set of sites in the thousands. Not all users see same the same Sites on the map – thus ACL. Data is stored in a non-GIS database (mssql, ora, etc) and right now is retrieved with a standard SQL query and returned to client (OpenLayers) with a JSP.

The current code is already optimized to do client-side clustering (so less of a mess on the client, but all the data is there), bounding-box retrieval (so a sub-set of data but have to go to server for every pan/zoom for new data) and even server-side clustering (to reduce data shipped to client).

What I would like to instead is generate images/tiles for all my data, similar to how Google Maps shows tons of little red dots for all the results – http://bit.ly/d73qrw [google maps search for "coffee"] and display on the client. And when clicked-on do a quick ajax call to a WMS service to get info. That's the idea at least.

But here's the problem – I can't just setup a standard WMS service in front of my data because not all users see same data. Is there a way to generate these tiles on the fly or make WMS ACL-aware?

edit – 9/22/2010 – So I found out how google generates the tiles, or rather the technology behind it. They are using Google Fusion Tables. Store N rows in their tables and then if the data is lat/lon aware, the Fusion Table product can generate the tiles on the fly (!). This is the kind of thing I'm looking for – performant map with tons of data. But of course I need it to be ACL controlled. Does it make sense to write a custom light-weight implementation of the WMS spec or modify an existing product? Though geoserver seems like a lot to "just" modify to support ACL.

edit – 9/27/2010 – Some more info since adding bounty. My data is in Oracle. Ora spatial is not enabled. Right now the data is extracted at the business level and converted to data, sent to client where the client puts the "dots" on the map. ACL is done at the business logic level, not DB or ActivDir or anything like that. Authentication is simple, but Authorization is not and so had to be captured in code. Would like to know how to best create a WMS service to serve up 1000's of "dots" on the map where each user will see a different subset of dots. Is the answer a CQL_FILTER? But then how are the parameters set? One idea that I have now is to do a 2-step process. First run in-house query to get list of ID's that user allowed to see, then build a WMS request string with those ID's in CQL_FILTER parameter. Is there anything that simplifies this process? And if I go with this, how can I add this layer as a "WMS" layer to an Open Layers client since to OL the end-point is my code to get IDs from DB not the actual WMS service on e.g. GeoServer?

Related Question