Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    galway, ireland

    Talking Unanswered: MS Access Security Permissions problems

    Hi folks, im trying to learn how to secure my databases and am practicing on a sample database. I'm learning from an on-line tutorial and everything is goin fine until i get to the User and Group permissions bit....but here the Open/Run option is disabled regardless of the user shown. Can someone tell me why this is?? Also, is it correct that the following tables are owned by the Engine? - MSysACEs, MSysObjects, MSysQueries, MSysRelationships.



  2. #2
    Join Date
    Dec 2004
    Madison, WI

    Some ways to set permission levels

    An easy way to create a permission type system without having to go through all of Access's permission stuff is:

    1. Have a table called dbo_Admin which has 2 fields: RecID = autonumber and another field called: LoginID = text (as a Data Type). Put the user's loginID you want to have admin permissions in this table for the LoginID field. Add as many LoginID records as you need that you want to have permissions.

    2. Create a module with the following function (uses ADO code):

    Function IsAdmin() As Boolean
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from dbo_Admin where LoginID = '" & GetUser() & "'"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    Set rs = Nothing
    IsAdmin = False
    Set rs = Nothing
    IsAdmin = True
    End If
    End Function

    3. Create another module with the following function:

    Function GetUser() As String
    Dim strSQL As String
    '** Procedure to Get the User's Name from the Windows Login
    Dim si As SystemInfo
    Set si = New SystemInfo
    Dim strOut As String
    'strGetUser = si.UserName
    'strOut = si.UserName & " is logged into " & si.ComputerName
    GetUser = si.UserName
    If GetUser = "" Then
    MsgBox ("There is a problem with your Network Login Name!! Please contact your Network Administrator.")
    End If
    End Function

    4. Now simply wherever you want to check on admin permissions use code like this:

    if isadmin() = true then
    do xxxxx
    do yyyyy
    end if

    For example, you could also have in your form on the On_Open or Form_Load event:
    if isadmin() = true then
    me.cmdallowclose.visible = true
    me.cmdallowclose.visible = false
    end if

    where cmdallowclose is a button on the form which has code in it to allow you to close the form (ie. docmd.close).

    Otherwise, you can work your way through the Access permission and group level settings for permissions.

    As a last note, you will want to lock down your forms by preventing the user from seeing them in design view, etc. You could use the isadmin function anywhere (ie. Form_Unload, etc.)

    They'll still be able to get into the forms by holding down the left shift key but most users don't know this but there are also ways to disable this.

    Create *.mde files if possible, especially if you're using linked tables. SQL Server with linked tables is a good alternative (as well as MySQL) which can also offer you another and better level of permissions on the server tables itself.

    Lastly, you can get fancy and have different dbo_Admin type tables (i.e. dbo_AdminPayments, dbo_AdminDataEntry, etc.) and then have different functions as 2 above (ie. Function isAdminPayment() as boolean).

    Hope that helps.

    Paul Kohn
    Database Administrator

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts