Solved

Preventing users from creating new queries or new tables

Posted on 2000-02-21
15
350 Views
Last Modified: 2008-03-10
Greetings from Belgium,

I have designed a heavy application in MS Access 97 in order to manage important date pertaining to a Help Desk. I have had no serious security problems up to last week.

Last week, I have had complaints from my hierarchy about errors in some of the reports and, after debugging the application, I found out that the application was not at fault but that data had been globally changed. The changes I found could not have been made through the main form because it would have implied to scan and modify 272 records, one at a time. I suspect someone to have, by accident, created a new Update Query and to have run it. It would only take a couple of minutes to do so. I also found out that whatever permissions you can give the users, they still can create new queries or new tables.

Therefore, in order to prevent any accidental or malicious manipulations in the future, I would like to preventing users to create new queries and new tables. After long researches and reading through the Web and the Newsgroups, I found out a document pertaining to MS Access Security FAQ in which they described the code of two functions (faq_NoNew(strUser as String) and faq_OKNew(strUser as String)). I tried to implement them into my application but it does not work!

Would someone know what I could do to reach my goal or how to correctly use those two functions?

Thank you so much for your help,

Jean-Philippe Guilbeau

PS: Here follows the code of two functions:
 
Function faq_NoNew(strUser As String)
Dim db As Database
Dim con As Container
Set db = DBEngine(0)(0)
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions And Not dbSecCreate
End Function
 
Function faq_OKNew(strUser As String)
Dim db As Database
Dim con As Container
Set db = DBEngine(0)(0)
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions Or dbSecCreate
End Function
0
Comment
Question by:guilbeauj
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 1

Expert Comment

by:fedsuns
Comment Utility
quilbeauj,
      There are couple of issues here and perhaps I can help by asking a few questions. Please excuse me if the questions are obvious or simple. I don't know where you are starting out from so I have to go slowly.First, I am assuming that you are using Access security and an mdw, but not the system mdw. I am also assuming that the user administrator has a password (if administrator doesn't have a password you won't get the logon screen)   Second, although you take away permissions for an individual user the code doesn't take away permissions for groups that that user may belong to, and since a user gets the permissions of a group he belongs to by inheritance if the group has permission to make queries so does the user even if he doesn't explicitly have those permissions. So for instance if the user JohnS doen't have permissions to modify design for new queries (this actually means he doesn't have the permissions to make new queries), but the group USERS (of which all users are members by default)does have that permission (which is the default setting for access) then the user JohnS will be able to create new queries. So to make sure that JohnS doen't have permissions to make queries you must remove that permission from the group USERS also. There are other steps you have to go through to make a totally secure access database and they do seem arcane so if this doesn't help let me know and I'll continue along this line.
   
0
 

Author Comment

by:guilbeauj
Comment Utility
Adjusted points to 300
0
 

Author Comment

by:guilbeauj
Comment Utility
Fedsuns,

a) Yes, I am using an .mdw but not the system.mdw because I want to keep the original file clean.
b) Yes, the user administrator has a password.
c) I am giving permissions to groups and not to individual accounts.  I assign individual user accounts to groups: it is way easier to manage that way.
d) I am using Windows 98 environment
e) I am using replication: how do I replicate the security features?

Thanks for your help!
0
 

Author Comment

by:guilbeauj
Comment Utility
Fedsuns,

I have been reading your comment over and over thinking that you gave me a good hint by saying "... if the user JohnS doen't have permissions to modify design for new queries (this actually means he doesn't have the permissions to make new queries)..."; therefore, I modified accordingly the permissions of the Users group.  I was really disappointed to see that the creation of new tables and of new queries was still enabled to any individual belonging to the Users group.

Friendly,

Jean-Philippe Guilbeau
0
 
LVL 4

Expert Comment

by:Jeremy_D
Comment Utility
What *exact* permissions does the user in question have, and what exact permission does the group have (s)he belongs to?
0
 
LVL 4

Expert Comment

by:Jeremy_D
Comment Utility
.. as far as queries are concerned, of course.
0
 
LVL 1

Expert Comment

by:fedsuns
Comment Utility
guilbeauj,
    My apologies for  not being clearer and not getting back to you sooner.
 In order for a group or user to not have permissions to create and save a query the permissions must be revoked in code. That is to say if yo wish to remove permissions from the users group to create and save new tables and queries then you must execute code similair to this once:
Private Sub cmdRemovePermissions_Click()
On Error GoTo Err_cmdRemovePermissions_Click
Dim db As Database
Dim con As Container
Dim strUser As String
Set db = DBEngine(0)(0)
Set con = db.Containers("Tables")
strUser = "Tom"
con.UserName = strUser
con.Permissions = con.Permissions And Not dbSecCreate
con.UserName = "users"
''''Revoking permissions to create ''''queries and new tables from users group
con.Permissions = con.Permissions And Not dbSecCreate

Exit_cmdRemovePermissions_Click:
    Exit Sub

Err_cmdRemovePermissions_Click:
    MsgBox Err.Description
    Resume Exit_cmdRemovePermissions_Click
   
End Sub
However, this will only revoke permission to actually create and save a query or table. If the user can get to the query design window through the database window and has the rights to modify the table then he will be able to construct a query there and run it but not save it. I know of no direct way of using access security to prevent a user from seeing the query design window. However there are simple ways to make sure that the user cannot see the database window and therefore doesn't have access to the query design window. There is also a simple way to disable the shift f11 key from showing the database window and a simple way of restoring that capacity in case you as the database designer need to get back to it. Do you wish me to show you how to do that?
John Smith
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:guilbeauj
Comment Utility
Fedsuns,
John,

Would you, please, show me the "simple way to disable the shift f11 key from showing the database window and a simple way of restoring that capacity in case the database designer need to get back to it.

Thanks,


Jean-Philippe Guilbeau
0
 
LVL 1

Expert Comment

by:fedsuns
Comment Utility
Here's come code:
Private Sub cmdTurnOff_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'     The purpose of this routine is to disable the effect of the shift key when starting  '
'     up access                                                                            '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      Dim strPROCEDURE        As String         'Procedure name for error trapping
      Dim strRetval           As String         'Return value for functions
      Dim strDbTurnOnOff      As String         'Complete filename and pathe to datbase we
      Dim dbTurnOnOff         As Database       'want to change the shift key settings on
      strPROCEDURE = "cmdTurnOff_Click"
      On Error GoTo err_cmdTurnOff_Click
      strDbTurnOnOff = bas_UtilitiesGetFileName
      If strDbTurnOnOff <> "Cancel" Then
            Set dbTurnOnOff = DBEngine.Workspaces(0).OpenDatabase(strDbTurnOnOff)
            DoCmd.Hourglass True
            strRetval = cbfChangeByPassKeyProperty("Off", dbTurnOnOff) 'Adds the turn off property to this database
            DoCmd.Hourglass False
      End If
      Exit Sub
err_cmdTurnOff_Click:
    MsgBox Err.Description
      Resume Next
End Sub

Private Sub cmdTurnOnShift_Click()
      Dim strPROCEDURE        As String         'Procedure name for error trapping
      Dim strRetval           As String         'Return value for functions
      Dim strDbTurnOnOff      As String         'Complete filename and pathe to datbase we
      Dim dbTurnOnOff         As Database       'want to change the shift key settings on
      strPROCEDURE = "cmdTurnOnShift_Click"
      On Error GoTo Err_cmdTurnOnShift_Click
      strDbTurnOnOff = bas_UtilitiesGetFileName
      If strDbTurnOnOff <> "Cancel" Then
            DoCmd.Hourglass True
            Set dbTurnOnOff = DBEngine.Workspaces(0).OpenDatabase(strDbTurnOnOff)
            strRetval = cbfChangeByPassKeyProperty("On", dbTurnOnOff) 'Adds the turn off property to this database
            DoCmd.Hourglass False
      End If
      Exit Sub
Exit_cmdTurnOnShift_Click:
    Exit Sub

Err_cmdTurnOnShift_Click:
    MsgBox Err.Description
    Resume Exit_cmdTurnOnShift_Click
   
End Sub

Public Function cbfChangeByPassKeyProperty(strOnOrOff As String, dbTurnOnOff As Database) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'     The purpose if this routine is to toggle the bypass key property NB once toggled the '
'     new state of the switch only takes place the next time the app is opened             '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      'Dim strOnOrOff         as string         ' = on then turn bypass key on otherwise off
      'Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
      cbfChangeByPassKeyProperty = "OK"
      'Dim dbTurnOnOff         As Database       'database we are turning on or off
      Dim prp                 As Property       'AllowBypassKey property
      Dim varPropValue        As Variant        'Value of AllowByPassKey
      Dim strPROCEDURE        As String         'Name of procedure for error trapping
      Const conPropNotFoundError = 3270

      On Error GoTo Change_Err
      Select Case strOnOrOff
            Case "On" 'turn on the shift key
                  varPropValue = True
                  dbTurnOnOff.Properties("AllowBypassKey") = varPropValue
            Case Else
                  varPropValue = False
                  dbTurnOnOff.Properties("AllowBypassKey") = varPropValue
      End Select
Change_Bye:
      Exit Function

Change_Err:
      If Err = conPropNotFoundError Then  ' Property not found.
            'Create property
            Set prp = dbTurnOnOff.CreateProperty("AllowByPassKey", dbBoolean, varPropValue)
            'Append property to DB
            dbTurnOnOff.Properties.Append prp
            Resume Next
      Else
            ' Unknown error.
            cbfChangeByPassKeyProperty = "Error"
            MsgBox Err.Description
            Resume Change_Bye
      End If

End Function
Basically I put this code in a general database outside the one I am disabling. The code sets a property that disables the shift f11 key. Note if the property doesn't all ready exist the code adds it. The code I have sent you goes to two buttons on a form. The function bas_UtilitiesGetFileName, which I have not sent you, presents a file selection form and then returns the complete path and file name of application that you want to lock up. I took the cod for bas_UtilitiesGetFileName from the Access Developers Handbook. If you wnat that code also I can send it. It is a bit long though. Note to run this program you must be logged into the mdw that the file you want to work with is associated with.
0
 
LVL 1

Expert Comment

by:fedsuns
Comment Utility
guilbeauj,
     What the code I just sent you does is disable (or reenable) the shift key from stopping whatever code you have on startup. In Access 97 (the procedure is similair for access 2000 I believe) to disable the f11 key for the database you want to use, open up the database and when the database window shows select tools, and then startup from the main menu. On the screen that appears click advanced. Then deselect the use special access key select box. If you don't want the database window to show then deselect the display database window box. These boxes can be toggled on and off again from the database window, but once you have toggled them off the database window won't show it and it appears as if you can't reset them. Howeve,r this is where the shift key comes in. If the shift key is held down while a database is being opened then even if these options are deselected the database window displays. A sophisitcated user would know this  and this is where the code I sent you in the previous message comes into play. By running that code you disable the bypass function of the shift key and thereby prevent the user from using the shift key to get at the database window. In addition you can then use the code to reenable the shift key so that you can hold down the shift key and get at the database window. It works pretty neatly I think. The only real drawback is that it doesn't take any special permissions to set the enable or disable the shift key property. So if the user is really sophisticated he can guess at what you have done, write his own code and he's in.
0
 
LVL 1

Accepted Solution

by:
fedsuns earned 300 total points
Comment Utility
Jean-Phillipe,
     Sorry for the multi part message but work called. Anyway I hope that will fix you up. If any of this is not clear or a bit muddled (as it is bound to be if only because of the typos) let me know and I'll get back to you. Let me know how it worked out please.
Good Luck
John
0
 

Author Comment

by:guilbeauj
Comment Utility
Thanks John for all the help!

You do deserve the 300 points!

Jean-Philippe
0
 

Author Comment

by:guilbeauj
Comment Utility
Fedsuns,

Have you got your points?
I am lost in the procedure of attributing the points!

Thanks again,

Jean-Philippe
0
 
LVL 1

Expert Comment

by:fedsuns
Comment Utility
Got the points and thank you very much. Good Luck
John Smith
0
 

Expert Comment

by:bsnare
Comment Utility
fedsuns:

guilbeauj commented:
e) I am using replication: how do I replicate the security features?

I didn't notice a reference to replication. I thought security wouldn't work on replicas unless the password was turned off (Access97 Dev Handbook).

Does that mean that the replica was not discarded or not used?

Thank you -
Bob Snare
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now