Maintaining Database Permissions with ADO

The procedures in this section demonstrate techniques for granting permissions to users or groups (GrantPermissionsOnTableADO and GrantPermissionsToObjectADO) and revoking permissions from users or groups (RevokePermissionsFromTableADO and RevokePermissionsFromObjectADO).

Permissions can be granted to either user groups or individual users, which you specify through the strUserOrGroupName parameter. As mentioned earlier, the ideal scenario is to grant permissions to groups and then add users to groups so they can have the required permissions. Unlike the procedures that update user group and user information, the correct database must be specified in Data Source to grant permissions to the correct objects.

For these procedures, the strPermissions parameter can be one permission type or many permission types separated by commas, which is different than the method you used to combine permissions for DAO parameters.

While not generally recommended, using PUBLIC for the group/username (strUserNamej will set permissions for the default Users group account such that every user will be the assigned the specified permissions. The best use for this would be to grant CONNECT permission to the database because that would permit all users access to the database but not necessarily to all objects in the database.

Sub

GrantPermissionsOnTableADO( _

strUserOrGroupName As String, _

strTableName As String, _

strPermissions As String)

' Define variables

Dim cnn As ADODB.Connection

Dim strCommand As String

' Open a connection to the database

Set cnn = OpenConnectionADO(strDBPath, strMDWPath, strAdminUser, strAdminPass)

' Build the command to grant permissions

strCommand = "GRANT " & strPermissions & _

" ON TABLE " & strTableName & " TO " & strUserOrGroupName & ";"

' Execute the command

cnn.Execute strCommand

' Clean up

cnn.Close

Set cnn = Nothing

End

Sub

Permissions are revoked from the user or group depending on the value of strUserOrGroupName. Revoking permissions from a user does not affect the permissions of the group to which that user belongs.

Sub RevokePermissionsFromTableADO( _ strUserOrGroupName As String, _ strTableName As String, _ strPermissions As String)

' Define variables

Dim cnn As ADODB.Connection

Dim strCommand As String ' Open a connection to the database

Set cnn = OpenConnectionADO(strDBPath, strMDWPath, strAdminUser, strAdminPass) ' Build the command to revoke permissions strCommand = "REVOKE " & strPermissions & " ON TABLE " & _

strTableName & " FROM " & strUserOrGroupName & ";"

' Execute the command cnn.Execute strCommand

' Clean up cnn.Close

Set cnn = Nothing

End Sub

GrantPermissionsToObjectADO is a more generic form of the GrantPermissionsToTableADO procedure. It is used for object types other than tables and easily replaces the table-specific procedure. The GrantPermissionsToObjectADO employs the ObjectType enumeration globally defined previously to allow the caller of the subroutine to choose the database object type to modify.

Sub GrantPermissionsToObjectADO( _

strUserOrGroupName As String, _

otObjType As eObjectTypes, _

strObjectName As String, _

strPermissions As String)

' Define variables

Dim cnn As ADODB.Connection

Dim strCommand As String

' Open a connection to the database

Set cnn = OpenConnectionADO(strDBPath, strMDWPath, strAdminUser, strAdminPass)

' Build the command to grant permissions

strCommand = "GRANT " & strPermissions & " ON "

Select Case otObjType

Case eObjectTypes.Database:

strCommand = strCommand

Case eObjectTypes.Container:

strCommand = strCommand

& "CONTAINER"

Case eObjectTypes.Table:

strCommand = strCommand

& "TABLE"

Case eObjectTypes.Other:

strCommand = strCommand

& "OBJECT"

Case Else

MsgBox "Object Type Not

Recognized"

End Select

strCommand = strCommand & " " &

strObjectName & " TO " & i

strUserOrGroupName & ";"

' Execute the command cnn.Execute strCommand

' Clean up cnn.Close

Set cnn = Nothing

End Sub

RevokePermissionsFromObjectADO is a more generic form of the

RevokePermissionsFromTableADO procedure. It is used for object types other than tables and can easily replace the table-specific procedure.

Sub

RevokePermissionsFromObjectADO( _

strUserOrGroupName As String, otObjType As eObjectTypes, _

strObjectName As String, strPermissions As String)

' Define variables

Dim cnn As ADODB.Connection

Dim strCommand As String

' Open a connection to the database

Set cnn = OpenConnectionADO(strDBPath, strMDWPath, strAdminUser, strAdminPass)

' Build the command to revoke permissions

strCommand = "REVOKE " & strPermissions & " ON "

Select Case otObjType

Case eObjectTypes.Database:

strCommand = strCommand

Case eObjectTypes.Container:

strCommand = strCommand & "CONTAINER"

Case eObjectTypes.Table:

strCommand = strCommand & "TABLE"

Case eObjectTypes.Other:

strCommand = strCommand & "OBJECT"

Case Else

Debug.Print "Object type incorrect"

End Select

strCommand = strCommand & " " & strObjectName & " FROM " & i

strUserOrGroupName & ";"

' Execute the command

cnn.Execute strCommand

' Clean up

cnn.Close

Set cnn = Nothing

End

Sub

0 0

Post a comment