Sharing database connections across teams

Akshay Davis
Akshay Davis Experian Super Contributor
edited December 2023 in General

What's the recommended approach to sharing tables/views and connections to databases across a wider team? Should we create new connections per user, share tables with specific users or create credentials for other users on the same connection?

I'm interested in the security aspect but also the performance impact of each of these.

Best Answers

  • Clinton Jones
    Clinton Jones Experian Elite
    Answer ✓

    @Akshay Davis just a further comment on this topic.

    Users should be actively DISCOURAGED from creating their own personal JDBC connections, and instead should always use a common JDBC connection but their own credentials. The reason for this, is that Data Studio has no way of 'knowing' that the JDBC connection created by User A is not the same as the JDBC connection created by User B. This is also one of the reason why we encourage the administrator to be the ONLY person creating JDBC or database connections. This approach means fewer JDBC connections and more efficient data caching.

    If every user has their own JDBC connection and own credentials (or even a share credential) - they will all be continuously filling the cache with their own different but essentially duplicated view of data coming from the source(s). Thats inefficient and will slow things down unnecessarily.

Answers

  • Clinton Jones
    Clinton Jones Experian Elite

    Using data that comes from files is problematic. The main reason being that the data is stored in the user's IMPORT storage area on the server.


    The administrator is user #5 and anything visible as MyFiles as the Administrator is contained in that folder structure

    To share common data sets as files there is a hack, that has been documented internally but which is not really recommended.

    A better approach in my opinion is to stage all data that needs to be used across multiple users, via a data connection, ie in a database table or on Azure or AWS folders.

    You have the ability to set up seperate credentials for each user.


  • Clinton Jones
    Clinton Jones Experian Elite

    here is a link to the support documentation on this topic


  • Akshay Davis
    Akshay Davis Experian Super Contributor

    Thanks @Clinton Jones to be clear, my question was really only referring to database connections and the performance impact of having users creating separate connections to the same database vs. having credentials added to one connection.

    Is there any difference in performance or resource utilisation?

  • Clinton Jones
    Clinton Jones Experian Elite

    @Akshay Davis by having more granular credentials the dba or owner of the database has more control over the who and the what.

    In terms of performance, the credntials are a passthrough when the credntials are a single set being used by multiple users, that might not pass audit-muster for a given customer situation and so i also don't recommend a shared credential BUT there are valid reasons where a share credntial or system account may be quite fine.

    There should be no difference from a performance standpoint.