Creating a Read-only connection to the DP4 ODBC driver - T4000006 - 19 Apr 2004

Frequently you may want to allow end-users of systems built around the DP4 database to be able to access data via ODBC, so that they can query the database using Office programs, or reporting tools such as Crystal Reports. However, you may want to ensure that users cannot inadvertently (or maliciously) damage the database by use of insert/update statements.

There is no facility to do this via configuration settings (Previously, the DP4 ODBC documentation implied that a "read only" checkbox was avialble in the ocnection dialog, but this was an error). However, you can easily achieve the desired result by judicious use of the security facilities built into DP4. You should proceed as follows:

1. First, ensure that the global userdata.sys file with the "default" security settings has an access level that is sufficient for running any programs that are needed that are run from outside the context of a DP4 login.

   • A write access level of 255 will give unrestricted access to the database (this even allows users to "hack" the dictionary tables using programs such as browser, so really should not be used in end user installations).
   • A level of 99 is sufficient for access to all application tables on a database, and to read but not update the base dictionary using BROWSER.(Note that security levels on dictionary tables are only checked in programs such as Browser and Report Writer, so that setting a 99 or below write security level does not prevent other programs, typically DP4 maintanence utilities, which update the dictionary tables, from running normally.)
   • Lower levels of write access may not be sufficient to access particular tables, depending on whether you used the relevant options of MAKEDB when creating the database.

   See Setting Database Security Levels for the various ways you can set database security.

   If you have not used DP4 security levels before you should probably set the global "userdata.sys" file to have read and write access levels of 99.

2. Use the MENUUSER or MENUEDIT programs to create a restricted user, "readonly", with a read access level of 99, but a write access level of 0. See Maintaining the User List for a description of this process. You may choose to give this user a blank password. You should probably modify the passwords for the DP4 and DATAFIT users if you have not already done so, and make them hard to guess.
3. Create a new Data Source for the DP4 databases you want the user to have read-only access to. Instead of using the DP4 user as the default user, use the "readonly" user you created in the previous step. Assuming you do not want the logon dialog to appear, then clear the "prompt" checkbox, and enter any password you specified for the user.
4. At this stage, the data source is not read only, but you can protect individual tables on databases by modifying the security levels set using MAKEDB. To make the connection completely read-only you need a minor enhancement to the DP4 SQL library, and to set a global write security level on the whole database. See Setting the Database Version Information for a description of this option. Set the Write security field to a non zero value. 1 is probably a good choice, if you have not used DP4 security levels before.
5. Now, when you connect to DP4, or logon to DP4 using the "readonly" user, any programs that do not open the database read only will fall over with system error 174 "You do not have sufficient write authority to access this database". If you do not update the DP4 SQL library you will also get this error when you try to use the data source you set up above. With the enhanced driver contained in the download, the DP4 SQL interface detects the system error 174, and instead of reporting it and giving up, it opens the database with the DP4 READ_ONLY flag. The SQL connection is also explicitly set to read only, and and INSERT,UPDATE and DELETE statements will be rejected.

Download