csqlsecurity.cls

SQL Security with Visual Basic 6.0

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "CSQLSecurity"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Attribute VB_Ext_KEY = "RVB_UniqueId" ,"3AA821B1004C"
Option Explicit

Private Rolls As CRolls

'This function logs into the provided server, using the sLogon and sPW parms and fills out a class data model based
'on all the non-system defined rolls associated to the provided database, and all the users assigned to each roll.
'This way you can see if the user has rights to a certain action before actually running the query or stored procedure.
'You could wrap large functions with this check instead of going through half the function's code before you get to the
'actual SQL call, just to find out that the user does not have rights to that query or table.  For example, say you had
'an app that had two modes.  Read and Read / Write.  The app 2 forms for displaying ready only data and 4 forms for editing data,
'each form accessed through a button on a tool bar.  with integrated SQL server security, the app wouldn't know that
'the user didn't have privs to edit the data on those 4 forms until the app tried to run a query against the SQL server.
'with this dll, you can load all rolls and users of those rolls for a database when the app starts.  then check what
'roll rights that user has and enable or disable the buttons on the toolbar as necessary.  this way the user never even
'has the choice to try and edit the data because they can't access the forms.

'Another benifit of doing this is that with integrated security, the app assumes that the person logged onto
'the PC is the person running the app.  To tighten that security model, have the user log onto the app when
'it first starts and after all rolls and users are loaded into the data model check to see if that user
'is even listed.  If not the app can give a warning that the user has no privs to the app and close itself.

'One draw back to this dll is that it only works, as is, if you associate NT Users to SQL Server rolls.  If you assigned
'NT Groups, you would need to use ADSI in order to pull out all users of that group and add the groups users
'to the roll
Public Function LoadSecurity(sSQLServer As String, sLogon As String, sPW As String, sDatabase As String) As Boolean
  Dim x As Integer
  Dim y As Integer
  Dim sRollName As String
  Dim sUserName As String
  Dim oDMO As SQLDMO.SQLServer
  Dim oDB As SQLDMO.Database
  Dim oRole As SQLDMO.DatabaseRole
  Dim oQuery As SQLDMO.QueryResults
  
  On Error GoTo SQLError
  LoadSecurity = False
  Set Rolls = New CRolls
  Set oDMO = New SQLDMO.SQLServer
  Set oDB = New SQLDMO.Database
  Set oRole = New SQLDMO.DatabaseRole
  
  oDMO.DisConnect
  'connect to the SQL Server with admin logon and pw
  oDMO.Connect sSQLServer, sLogon, sPW
  Set oDB = oDMO.Databases(sDatabase)
  
  For x = 1 To oDB.DatabaseRoles.Count
    'The IsFixedRole method returns True when the database role referenced is system defined.
    'We want non-system defined rolls
    If oDB.DatabaseRoles.Item(x).IsFixedRole = False Then
      'add the roll name to the data model
      Rolls.Add oDB.DatabaseRoles.Item(x).Name
      Set oQuery = oDB.DatabaseRoles.Item(x).EnumDatabaseRoleMember
      For y = 1 To oQuery.Rows
        'add each user name to each roll in the data model
        Rolls.Item(oDB.DatabaseRoles.Item(x).Name).Users.Add oQuery.GetColumnString(y, 1)
      Next
    End If
  Next
  LoadSecurity = True
  
  'used in testing.
  'displays all rolls and users for those rolls
  'For x = 1 To Rolls.Count
  '  For y = 1 To Rolls.Item(x).Users.Count
  '    Debug.Print Rolls.Item(x).Name & "  |  " & Rolls.Item(x).Users.Item(y).Name & vbcrlf
  '  Next
  'Next
  
  Exit Function
SQLError:
  Set oDMO = Nothing
  Set oDB = Nothing
  Set oRole = Nothing
  Set oQuery = Nothing
End Function

'this function takes the username and rollname and checks to see if that
'user is associated to that roll
Public Function CheckUserRightsToRoll(UserName As String, RollName As String) As Boolean
  On Error Resume Next
  If Len(Rolls.Item(RollName).Users.Item(UserName).Name) > 0 Then
    CheckUserRightsToRoll = True
  Else
    CheckUserRightsToRoll = False
  End If
  On Error GoTo 0
End Function

'this function checks all rolls for the loaded database and sees if he is listed
'as a user for any of the rolls.  This can be used to see if the user has rights to
'run the app
Public Function CheckUserRightsToDatabase(UserName As String) As Boolean
  Dim x As Integer
  Dim y As Integer
  CheckUserRightsToDatabase = False
  For x = 1 To Rolls.Count
    For y = 1 To Rolls.Item(x).Users.Count
      If Rolls.Item(x).Users.Item(y).Name = UserName Then
        CheckUserRightsToDatabase = True
        Exit Function
      End If
    Next
  Next
End Function