Developers

Omnis Technical Note TNSQ0003

Omnis ODBC Access Masks

For Omnis Studio 2.1 and Classic 7.3.7.1 and above
By Omnis Technical Support

Introduction
ODBC Access Control is a new feature of the Omnis data file ODBC driver. It is the ability to restrict ODBC access to particular tables and fields, to particular users. This provides significantly greater control than the current public data property implemented in Omnis 73 Version 7.0 and Studio 2.0. This feature is backwards compatible with the public data property.
There are three different ways to set these masks, which will be described below. Please note that if you choose to change the ODBC Access Masks with either notation or via the File class/format, then you should not attempt to use the SQL method and vice versa. The SQL method will NOT update the file class.

How to Set-Up your Users
A library has been provided with Omnis ODBCadmin.lbr - to enable you to set up your user groups.

How to Set ODBC Access Masks Using the File Class/Format

1)	Open your library and datafile
2)	Go to the file format/class that you want to set the masks for
3)	To set the file level masks :-
	a	For Omnis 7.3.7.1 you can set the file level access from the Modify Menu. Select ODBC Access Masks and you will see a window with 31 check boxes. Select the user groups who you want to have access to the file format.
	b	For Omnis Studio, select the file class in the browser and go to the property manager. From here, select the ODBC Access Mask drop down list and again, select the groups that you want to have access to that file class.
4)	To set the field level masks :-
	a	For Omnis 7.3.7.1, select the field in the file format and then click the ODBC Access mask button at the bottom of the screen, this will open a window where you can again select the user groups to whom you want to allow access to that particular field.
	b	For Omnis Studio 2.1, select the field in the file Class and then click the ODBC Access mask button at the bottom of the screen, this drop down a check box list where you can select the user groups to whom you want to allow access to that particular field.
5)	You will now need to update the data dictionary for the changes to be recognised by the data file:-
	a	Omnis 7.3.7.1, go to the Utilities menu (you will need to install this if it is not already present) and select Data File Tools ˆ Check Data File. Then select the file/files that you have just changed, click the Update Data Dictionary radio button and then Click the Start button.
	b	Omnis Studio 2.1, go to View ˆ Data File Browser, double click on the datafile, then double click on Data Slots and then select the Slot/Slots that you want to update. From the Slot menu (or right click for the context menu) select Update Data Dictionary. This option will be greyed out if the data dictionary does not need updating.

How to Set ODBC Access Masks Notationally

1)	To set up ODBC Access Masks for the file notationally, you need to do the following :-
	a	Omnis 7.3.7.1 the notation to change the Access Mask for the file is :-
		i	$root.$libs.LIBRARYNAME.$files.
				FILENAME.$head.$odbcaccessmask.$assign(500)
	b	Omnis Studio 2.1 the notation to change the Access Mask for the file is :-
		i	$root.$libs.LIBRARYNAME.$files.
				FILENAME.$odbcaccessmask.$assign(500)
2)	To set up ODBC Access Masks for the individual fields notationally, you need to use the following notation :-
	a	Omnis 7.3.7.1 and Omnis Studio 2.1 the notation to change the Access Mask for a field is :-
		i	$root.$libs.LIBRARYNAME.$files.
				FILENAME.$objs. FIELDNAME.$odbcaccessmask.$assign(500)
3)	Remember that you will have to update your data dictionary, as described above, once you have made these changes.

How to Set ODBC Access Masks using Omnis SQL

You can execute the following SQL with Omnis SQL to set ODBC access masks:
execute odbc.setaccess(Towns.Postcode,3)
sets the mask of column Town.Postcode to 3 (the numeric value corresponding to the mask group bits set). Similarly :
execute odbc.setaccess(Towns,3)
sets the mask for table Towns. Also,
execute odbc.getaccess(Towns) or
execute odbc.getaccess(Towns.Postcode)
gets the access mask. You can retrieve the result of a successful getaccess using sys(139). Note that sys(139) returns a negative value if bit 32 of the mask is set, since this is the sign bit of a 32 bit signed integer.

This only works when OmnisSQL is connected directly to a data file specified by the host name.