Specify schema-name for usage stats

evilolive · January 24, 2025, 4:48pm

I am using a MS SQL Database for logging and have specified the url, username and password in the application.yml:

proxy:
  usage-stats:
    - url: jdbc:sqlserver://<server-address>\<instance>;databaseName=<database>;encrypt=true;trustServerCertificate=true;
      username: <username>
      password: <password>
  usage-stats-table-name: event

However, this writes to the standard schema dbo. In compliance with our Data Governance policies, I need to write to a different schema. How can I specify the name of the schema? It is not listed as one of the url-connection properties: Setting the connection properties - JDBC Driver for SQL Server | Microsoft Learn

It seems specifying the schema would need to be implemented somewhere here:

github.com

openanalytics/containerproxy/blob/master/src/main/java/eu/openanalytics/containerproxy/stat/impl/JDBCCollector.java#L55


      
          *
          *
          * # MS SQL Server usage-stats-url:
          * jdbc:sqlserver://localhost;databaseName=shinyproxy
          *
          * Assumed table layout:
          *
          * create table event( event_time datetime, username varchar(128), type
          * varchar(128), data text );
          */
          public class JDBCCollector extends AbstractDbCollector {
          
             private final String url;
             private final String username;
             private final String password;
             private final String tableName;
             private HikariDataSource ds;
             @Inject
             private Environment environment;
          
             public JDBCCollector(String url, String username, String password, String tableNam) {

tdekoninck · February 13, 2025, 7:45am

Hi, it’s currently not possible to specify a different schema using ShinyProxy.
I created a ticket to implement this, hopefully we can integrate it in the next version.

There might be a workaround described here: https://stackoverflow.com/a/5512274/1393103

evilolive · February 13, 2025, 8:25am

Thanks for reaching out and for picking the topic up!

I actually found the same stackoverflow post you linked and implemented it this way.

For ease of use for other people: We created a new local user on the database and set their default schema to the schema we wanted to use for logging. That way we did not need to specify the schema in the ShinyProxy config.