guilbeauj
asked on
Preventing users from creating new queries or new tables
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
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
ASKER
Adjusted points to 300
ASKER
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!
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!
ASKER
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
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
What *exact* permissions does the user in question have, and what exact permission does the group have (s)he belongs to?
.. as far as queries are concerned, of course.
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_C
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_
Exit Sub
Err_cmdRemovePermissions_C
MsgBox Err.Description
Resume Exit_cmdRemovePermissions_
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
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_C
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_
Exit Sub
Err_cmdRemovePermissions_C
MsgBox Err.Description
Resume Exit_cmdRemovePermissions_
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
ASKER
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
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
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).Ope
DoCmd.Hourglass True
strRetval = cbfChangeByPassKeyProperty
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).Ope
strRetval = cbfChangeByPassKeyProperty
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
''''''''''''''''''''''''''
' 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
cbfChangeByPassKeyProperty
'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("Al
Case Else
varPropValue = False
dbTurnOnOff.Properties("Al
End Select
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
'Create property
Set prp = dbTurnOnOff.CreateProperty
'Append property to DB
dbTurnOnOff.Properties.App
Resume Next
Else
' Unknown error.
cbfChangeByPassKeyProperty
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.
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).Ope
DoCmd.Hourglass True
strRetval = cbfChangeByPassKeyProperty
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).Ope
strRetval = cbfChangeByPassKeyProperty
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
''''''''''''''''''''''''''
' 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
cbfChangeByPassKeyProperty
'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("Al
Case Else
varPropValue = False
dbTurnOnOff.Properties("Al
End Select
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
'Create property
Set prp = dbTurnOnOff.CreateProperty
'Append property to DB
dbTurnOnOff.Properties.App
Resume Next
Else
' Unknown error.
cbfChangeByPassKeyProperty
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks John for all the help!
You do deserve the 300 points!
Jean-Philippe
You do deserve the 300 points!
Jean-Philippe
ASKER
Fedsuns,
Have you got your points?
I am lost in the procedure of attributing the points!
Thanks again,
Jean-Philippe
Have you got your points?
I am lost in the procedure of attributing the points!
Thanks again,
Jean-Philippe
Got the points and thank you very much. Good Luck
John Smith
John Smith
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
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
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.