Controlling Data Access Permissions with a Connection

Goal

You want your application to set permissions on your data source.

This recipe applies to all the SAS providers. Sample code for ADO is included.

ADO Implementation

Values That Are Supported for the "Mode" Property

To control the available permissions for modifying data, you use the Connection object "Mode" property. The SAS providers support the following three values for the "Mode" property:

adModeRead
adModeReadWrite
adModeShareDenyNone

The default value for the "Mode" property is adModeReadWrite in conjunction with adModeShareDenyNone. (You cannot prevent other clients from connecting to the server; therefore adModeShareDenyNone is in effect regardless of your lock setting.)

How the Mode Value Affects the Lock Type

The "Mode" property value can affect which lock type is used on open recordsets. For example, the following Visual Basic code determines read-only access (adModeRead) before the connection is opened. Recordsets that you open with this connection will use the adLockReadOnly lock type, even if you request a different lock type.

Dim obConnection As New ADODB.Connection

obConnection.Provider = "sas.BaseSASProvider"
obConnection.Properties("Data Source") = "sdplserv"
obConnection.Properties("SAS Executable") = "C:\\Program Files\\SASHome\\SASFoundation\\9.3\\sas.exe"
obConnection.Properties("SAS Parameters") = "-initstmt %sasodbc(sdplserv) -icon -nologo -notutorialdlg"
obConnection.Properties("SAS Working Directory") = "C:\\Program Files\\SASHome\\SASFoundation\9.3\\"
obConnection.Mode = adModeRead
obConnection.Open

For an explanation of the properties used in the sample code, see Base SAS Provider Properties.

Note: For more information about lock types, see Implementing a Locking Strategyand Working with Cursor and Lock Type Combinations.

How the Mode Value Is Implemented

The following table provides additional information about how the SAS providers implement "Mode" property values.

CAUTION:

The value that you specify is not always the value that the provider uses.

How ADO Mode Property Values Are Implemented

Mode Specified	Mode Used	Comment
adModeShareDenyRead, adModeShareDenyWrite, or adModeShareDenyExclusive	adModeShareDenyNone	adModeShareDenyNone is always in effect because you cannot prevent other clients from connecting to a server.
adModeWrite	adModeReadWrite	You cannot open a connection that is limited only to write operations. Reading data is always supported on an open connection. This mode is used in conjunction with adModeShareDenyNone.
adModeRead	adModeRead	If read-only access is set before the connection is opened, recordsets that are opened by using the connection will use the adLockReadOnly lock type, regardless of any other lock type that you might have requested. This mode is used in conjunction with adModeShareDenyNone.

OLE DB Implementation

To get results that are identical to the ADO Connection object "Mode" property, you set a particular DBPROP_INIT_MODE bit mask. The following table lists the one-to-one correspondence between the values of the ADO Connection object ConnectModeEnum constants (the "Mode" property names) and the bit masks defined for the OLE DB DBPROP_INIT_MODE data source property.

ADO "Mode" Properties and Corresponding DBPROP_INIT_MODE Bit Masks

ConnectModeEnum Constant	DBPROP_INIT_MODE Bit Mask
adModeRead	DB_MODE_READ
adModeWrite	DB_MODE_WRITE
adModeReadWrite	DB_MODE_READWRITE
adModeShareDenyRead	DB_MODE_SHARE_DENY_READ
adModeShareDenyWrite	DB_MODE_SHARE_DENY_WRITE
adModeShareDenyExclusive	DB_MODE_SHARE_DENY_EXCLUSIVE
adModeShareDenyNone	DB_MODE_SHARE_DENY_NONE
adModeUnknown	0

When you set a particular DBPROP_INIT_MODE bit mask value, it has the same effect as when you the ADO Connection object "Mode" property to the corresponding ConnectModeEnum value, as shown in the following table:

How OLE DB ModeProperty Values Are Implemented

DBPROP_INIT_MODE Bit Mask Set	DBPROP_INIT_MODE Bit Mask Used	Comment
DB_MODE_SHARE_DENY_READ, DB_MODE_SHARE_DENY_WRITE, or DB_MODE_SHARE_DENY_EXCLUSIVE	DB_MODE_SHARE_DENY_NONE	A client cannot prevent other clients from connecting to a server.
DB_MODE_WRITE	DB_MODE_READWRITE	You cannot open a connection that is limited only to write operations. Reading data is always supported.
DB_MODE_READ	DB_MODE_READ	Use of DB_MODE_READ bit without the DB_MODE_WRITE bit restricts open rowsets to read-only access. Read-only rowsets do not implement the IRowsetChange and IRowsetUpdate interfaces.