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\\SAS\\SASFoundation\\9.2\\sas.exe"
obConnection.Properties("SAS Parameters") = "-initstmt %sasodbc(sdplserv) -icon -nosplash"
obConnection.Properties("SAS Working Directory") = "C:\\Program Files\\SAS\\SASFoundation\9.2\\"
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.