Migrate DB Connect from 1.1.0 to 2.4.0 - Security Enhancements - Allow user to only view SQL results

[saranya_fmr]

We are planning to migrate to the latest DB version. We are not planning to use the migrate scripts since we are in the lower version yet, instead we have planned to install the latest version in the cluster and then provide new DB Identities&Connections and then migrate.

I was analyzing the new security enhancements which allows role-based permissions to access specific DBs. I have noticed that in the intermediate versions it requires specific roles to be created for every DB Connection.

We have 3 use cases:
1) Allow users to access only connections(DB) specific to them. - User A
2)Allow certain users to access all DBs. - User B
3)Allowing non-dbx users to just view dashboards with search/report results generated from a SQL query i.e restricting their access to only those results and We don’t want them to be able to run any additional SQL queries; - User C

I followed steps outlined in this doc.
http://docs.splunk.com/Documentation/DBX/1.2.2/DeployDBX/Setupuserpermissions

to create two roles role_x(to access only DB -X) and role_root( to access all DBs)
Assumption: I have created two Connections X and Y . Here permissions refer to READ-ONLY.

1) I created role_x for the Connection_x and checked role_x in permissions tab for connection_x and unchecked role_x for connection_Y. By default , db_connect_user role has read access for all Identities and Connections. This allows user A and User B to query both DBs X and Y .

2) Thus unchecked db_connect_user role in Identities and connections of both X & Y . Now only role_x has permissions to Connection_x and role_root has permissions to all the connections. This works for user A to query only connection X and not Y, and user B can query BOTH.

Could someone please guide if this is the right approach. Also please let me know how I can achieve use case 3??

[earlhelms · Accepted solution]

sure, I would be happy to elaborate....

There are two scenarios that I mentioned:

1) Moving data to an index as referenced by SloshBurch
2) Changing the database side (this is outside of Splunk)

So, what do I mean by item #2? If you only want users to see a sub-set of the data stored in database x, create a new database user, create a view that can only see that specific data on the database, modify the user so they can only see that view. Create a Splunk user using those permissions and it's capability to see X will be tied to the subset exposed by the view. https://www.codeproject.com/Tips/639239/Creating-and-Usage-of-View-in-SQL - Note: this doesn't stop them from running dbquery but, it does lock down the scope of what they can search to the scope of the view.

[saranya_fmr]

Thankyou @earlhelms 🙂 got it..!!

[earlhelms]

I just did the migration myself. Regarding item #3, in my experience, permissions for a dashboard are tied to those for a database query. You can use a database input to move the data from the database into an index and give the index different permissions. Alternatively, you could provide a view to the query that you want on the database side and lock it down that way.

[saranya_fmr]

I didnt get this - you could provide a view to the query that you want on the database side and lock it down that way. Could you please elaborate?

[sloshburch]

I believe he is referring to the approach we discussed on the phone where the data itself is indexed and the permissions on the index are restricted in the traditional splunk fashion.

[sloshburch]

Some clarifications:

• "planning to migrate to the latest DB version." - I assume that by 'latest DB version' you mean DBConnect, not the database itself
• If you're setting this up on the new version of DBConnect, why are you following instructions from the old documentation: http://docs.splunk.com/Documentation/DBX/1.2.2/DeployDBX/Setupuserpermissions
• You refer to User A and B but then switched to describing connections X and Y without mapping which role each member is a user of. Is User A a member of role-x while User B is a member of role_root?

As you're probably aware, "Splunk DB Connect version 1.x reached its End of Life on July 28, 2016"

Also, feel free to edit your post to clarify or add screenshots to better describe what you've implemented.

[saranya_fmr]

• Yup, I meant latest DB Connect version 2.4.0.

• As per the new documentation , in the permissions tab if I provide permissions to roleS say sts_monitoring_curator and sts_monitoring_user for a DB Connection_X and a user who belongs to both these roles , queries for that DB Connection , he gets an error
  "Unknown search command dbxquery"

• Yup, user A belongs to role_x and user B belongs to role_root.

Its not allowing me add screenshots.

Sorry, my bad I followed the old version doc. Yup I was able to achieve user A to access DB X by assigning him to role db_connect_user in the new version 2.4.0.
But still quite confused with use case 2 and 3.