How does myDBR access the database?

myDBR was designed to be a reporting system that can be used without any modifications to the actual user databases. By default, myDBR installs into its own reporting database ('mydbr' by default). All myDBR database objects (including reports) are stored in this database. myDBR only needs read access to the database where the actual data is held.

You can also install myDBR directly to the user database, where your data is (instead of the default mydbr). myDBR uses its own naming scheme, so it will not conflict with your data. (myDBR objects have a prefix of: "mydbr_" for tables, "sp_MyDBR_" for myDBR internal procedures, "sp_DBR" for report procedures).

myDBR database access can be divided into two parts: the login process and the report execution. Here we'll go through both of them.

User login

In the login process, myDBR checks if the user has credentials to log in to the system. By default, myDBR uses its internal structure to hold usernames/passwords. The following authentication modules are available in myDBR preferences:

The user's username and password hash are checked against the myDBR's internal structure (mydbr_userlogin-table). The password field in mydbr_userlogin-table is a hash of the actual password.
The user's username and password are checked against external applications authentication. Please see more info at Single Sign-On
The user's username and password are checked against Microsoft Active Directory.
Suitable for environments where authentication is handled by external service, but Single Sign-On will not be applicable. In Custom authentication, the user enters a username/password in myDBR, and myDBR connects external service to authenticate and to report user/group info. Custom authentication provides a very easy integration of myDBR to an external server. The authentication implementation will be done in mydbr/user/custom_authentication.php where the protocol is documented.

Suppose you want to allow the use of myDBR without any login process (public intranet, extranet). In that case, you can set automatic login credentials in Admin Preferences. When automatic logins are set, admin access can be gained by pointing the browser to login.php instead of the usual index.php.

Report execution

Once the login process has been completed, all the operations in the database are done using the username/password stored in the admin preferences (section Database connection info: Username/Password). These are stored in the config.php (entries admin_username and admin_password). These are referred here as 'myDBR credentials'. myDBR credentials must have full access to the mydbr database and read access to the user database objects.

Report creation

The reports are created in the mydbr-database as stored procedures. myDBR credentials must have execution access to these stored procedures. When accessing the data in the actual user database, one refers to tables using the format 'mydatabase.mytable' in MySQL and 'mydatabase..mytable' in MS SQL Server, Sybase ASE.

SQL Anywhere does not support multiple databases so reports are created in the same database. Alternatively, you can install myDBR in a separate database and use SQL Anywhere proxy tables to link your data tables.

Installing myDBR into a read-only replica

To use myDBR database against a read-only replica, the most common approach is to install myDBR in the master database. The master database is used for report creation/user credentials. The read-only replica for the actual reporting. This will perform better as the queries are not done against the master. By default, myDBR collects statistics from the reports run into the database. Also, when using external authentication, mYDBR updates the user info and user's groups on login. To disable these writes to the database add the following line to the replica's :

  $mydbr_defaults['db_connection']['disable_writes_to_db'] = true;