SQL-Retriever ODBC

Security Manager

A common concern amongst system administrators is that once ODBC software is installed on the Windows PC with the application using ODBC (e.g. an in-house developed VB application), there is a potential security hole. It is theoretically possible for a user to install another application such as MS-Access on the same PC and have broader access to the database than otherwise intended.

Most common RDBMSs have extensive security systems in terms of SQL such as grant and revoke for example. These should cater for most unwanted attention and in most cases this should be sufficient for the careful application developer. However, there is no restriction or concept of the calling application (e.g. MS Access) within the RDBMS engine, and organisations may care to implement additional security on the client.

The ODBC specification itself does not include any indication on security of this nature. Therefore, some vendors have gone above and beyond the ODBC standard to implement security. SQL-Retriever provides a "Security Manager". The Security Manager is a component of the host module of SQL-Retriever that allows a System Administrator to control access by:

There is also the facility to create groups of users and databases and restrict access by these groups.

Access can be granted or revoked on the above components. Any restrictions imposed by Security Manager are in addition to those imposed by the RDBMS which take precedence. A sample security file (sqlrsec.conf) containing example 'rules' is shipped with SQL-Retriever.

The Security Browser allows administrators to troubleshoot the Security Manager component of SQL-Retriever. You can use Security Browser to examine the Security Manager configuration file sqlrsec.conf. Sqlrsec.conf entries are displayed graphically enabling users' privileges to be determined quickly and errors in the restriction logic more easily removed.

The Security Manager is further documented in the SQL-Retriever on-line help.

On the SCO labelled Vision2K 2.1 CD, there is a directory \MISC\PC\SECBROWS which contains the Security Browser which is an application which may assist with developing and debugging sqlrsec.conf.

Read-only access

A quick solution to making access to the database with SQL-Retriever read-only would be:

in the Security Manager configuration file: $VISION_DIR/etc/sqlrsec.conf add the following line:

GRANT ALL ALL:ALL:ALL:ALL:ALL = SELECT

By default, all privileges are revoked; therefore you need to grant the ones that are required.

To enable the Security Manager, start the SQL-Retriever ODBC server/demon with the '-r' option.

Note:

  1. Another method to restrict the access within the database on the UNIX host, is by using the database's own rules of security.
  2. A further way is to set the ODBC Data Source to read-only (done in the Advanced tab of the Data Source configuration). However, this may not offer the security that the administrator requires, as it may be possible for each user to unset this option.

There is more information on the Security Manager in a white paper in the library section.