Mercury stores meta-data -- report definitions, user/report permissions, etc -- in its own SQL Server database in Microsoft’s secure Azure environment. Each Mercury installation gets its own Mercury meta-db – it’s not commingled with any other customer’s data, and of course it’s also completely separate from any of your production databases. The meta-db keeps no production data.
All communication is encrypted:
The Mercury client talks to your Mercury Azure meta-database over a secure channel.
If your organization uses database-specific credentials to access production data (i.e. a database login-name and password rather than implicit Windows user credentials), those credentials are also encrypted.
Mercury relies on queries (stored procedures, views, SQL statements, OData services, etc) to retrieve data.
If you choose to use Stored Procedures and/or Views to retrieve data for Mercury reports, these types of queries reside in one of your own databases.
We suggest creating a brand-new database on one of your servers to house them. (For convenience we’ll call this a Mercury “query database” or “on-prem” database. Even if it’s in a remote data center or in your cloud, “on-prem” emphasizes that it’s part of your network world – not Mercury’s.)
For speed and efficiency, this query database should be on the same network as your production databases.
Your Windows users would need permission to run stored procedures and views in this on-prem/query database. And those procedures and views in turn need access to your production databases. The details depend on your particular database platform(s) and how you wish to control user access. Please contact us if you’re not sure how to proceed.
In most cases Mercury would then need to use only your query/on-prem database as the source of the views and stored procedures that retrieve data. Those objects would in turn access the production data for a report, and all the security and access control resides with you. The Mercury client application doesn’t have to know anything about the underlying servers and databases or tables. It simply uses a view or stored procedure in your query/on-prem database.
Depending on your database platform, one way to go is to create a database “role” that has permission to the views and stored procedures, and make your users members (or if you use a database login credential, make that a role member).
There’s one more use for the on-prem/query database: using custom data to support your operation. For example, if you have several companies with different accounting structures, you could create a custom account-number translation table to harmonize these structures and present a consistent set of reports to your audience. This custom table could live in the query/on-prem database.
Mercury is intended to be a read-only application. (Of course it updates the metadata in its own database, but production databases are intended to be read-only.)
Although Mercury has some ability to manipulate report data – for example, by creating custom calculated fields – no values are kept and nothing is cached. Mercury would keep only meta-data items such as calculation expressions and formatting.
However: if your organization uses stored procedures for data retrieval, Mercury has no knowledge of or control over the internal workings of those procedures. Your IT department is responsible for ensuring that stored procedures used for Mercury data-retrieval don’t modify data.