Enable auditing of user activity in Graphana shared Vertica connection

[scherepanov]

Hi,

I am looking at user audit from Graphana in Vertica.

As expected, Graphana is completely anonymise user activity. Vertica connection reports OS version, OS username, account used for connection. It is not known what user actually run query.

That most likely is same for any database connection, not only Vertica.

That is a hard problem to address, as same Vertica connection is being used to run queries for many users.

One approach that can work is to inject final user info into SQL text. Graphana can add to SQL text comment like this:

select / user=traceable_user / from ....

Vertica strip comments outside SQL, i.e. before first and after last SQL keyword. Comment should be injected inside SQL.

That probably would take some efforts to implement...

If you have other ideas, how to enable auditing of user activity through Graphana. Please post it here. Each SQL issued by Graphana should be traceable back to user who originated it.

It is actually a serious problem, security and audit has very high priority.

In my Vertica environment, this is a very big reason to use per-user Vertica connection #30

Thanks for reading!

[verticacrossman]

Hi,

We have been reviewing this alongside your issue 30 as there is some overlap. A couple quick notes: Our sql go driver actually prevents SQL injection on purpose and that in itself is a huge security risk.

It appears that Grafana doesn't expose the logged in user name (based on review of internal env vars shown with Volker Labs Env plugin). They also limit the env vars that Grafana service will allow for security reasons. So if we did allow sql injection we most likely could not programmatically get the logged in user name to pass in.

Vertica supports labels on queries. It's not clear if the username could be found whether it could be dynamically added as a dashboard variable. At first glance it doesn't look like it as the variables are hard coded and there's no internal env var to pick it up from.

The search continues.

[scherepanov]

Thanks for looking into issue!

labels are hard to use. I implemented adding label to generated SQL text on in-house system. I can see only around 30% of SQL get label recognized and processed by Vertica.

Problem is that label is required to follow main SELECT keyword.

First I added label after first SELECT keyword. It work properly in a very few cases, because users used WITH SELECT a lot.

Now I am adding label to last SELECT. Works properly only with 30% of SQL. Users typically doing complicated joins…

To add LABEL properly, you need to fully parse SQL… which is not possible and not practical.

Sooooo…..

Adding label to SQL is same as adding plain comment to SQL. It works fine, I can search in SQL text, but not understandable by Vertica.

I do not think adding comment qualify for SQL injection.

Yes, adding comment with keyword and username will provide reasonable audit capabilities. You can do comment in LABEL format, just do not expect it to be working properly every time.