Permissions on running Queries 1

[MichelleButler]

I have received some really helpful advice on tek tips and I thought I would try this one. I have a database in Access, currently being updated to SQL but in the meantime, I have loads of people running queries on the database. I would like to try and limit the permissions on the queries, so they can only run select quieries, rather than run any updates, inserts, deletes. I have people logging into the database but I pefer not to use system.mdw. When people log into the database, the permissions are checked in the table and then are granted the correct forms. I would like to try and do something similar to how they run quieries. I thought the only way I can do it, is to create a custom menu and only allow quieries to run. Any suggestions would be grateful!!!!!!!!!!!!!!!!!

Michelle

 

[SiJP]

Heya again Michelle..

Create a user in SQL Enterprise Manager that has the permission of SELECT only.. then give that username and password to your users to use in their DSN so when they link in their tables they are forced to use this password resticted connection (or create some code to do the linking for them without using a DSN.. or ask me for the code and I'll pass to you .. either way )

This way all they can literally do is select data ...

Neat huh?

Let me know if you need any help in any part of the above blurb.

 

[MichelleButler]

Hi SiJP,

It will be several months before I upsize Access to SQL, as all my users are still on Access 97 and Windows NT. As soon as I get the go ahead to upgrade everybody to Windows 2000 and Office 2000 I will then upsize the database. I work in the MOD, so we are still way behind. In the meantime, I have to find a good way to limit people to run create and run their own queries (only as read only and not allow any udates or select). I have created a custom menu, but this does not seem to be an elegant way to do this. You suggestions are valuable.

Michelle

 

[SiJP]

Ok, gotcha.

1)

Firstly, by the sounds of your scenario, you mightn't want to start implementing access's built in security as this *can* be somewhat a lengthy process to understand, set up, implement and maintain. PLus you've already stated you dont want to use it, as you have a process in place already.

Which is a shame because implementing access's own security messaures you can literally give read/write permissions to tables and queries. If you do decide this would be the right route for you, it is well worth playing around with - there is loads of support on tek-tips for this particular topic as well.

2)

The second method I can suggest, is to 'lock down' the database by splitting it client/server (creating an MDE for the client side), hide the database window (so objects cannot be seen), and setting the bypasskey to null.. whilst this method is not un-crakable to the enthusiast, you have to know a moderate amount of coding to break in, and when you do.. would it really be worth it?

Once you've split the database, and locked it down, you can create a form that allows users to create and run custom queries, export them etc etc.. there is a form already designed for this purpose by the boys at mvps.com .. it comes either as acc97 or 2000 as can be downloaded from:

- Find Record 97 (

http://www.mvps.org/access/downloadsfindrecord.zip

)
- Find Record 2000 (

http://www.mvps.org/access/downloadsfindrecord2k.zip

)

Large amouts of Kudos to Dev Ashish and Terry Kreft for these.


I've a feeling this second method would be easier for you to implement, as it takes no time at all to create a 'secure' mde that has a custom form in it.

(With the MDE, use linked tables into your backend 'master' db...)

I have some code for locking down the by pass key if you would like it:

New Module: "SetByPass"

Option Compare Database
'Following functions are to set the bypass to
'either true or false.  This allows the designer
'to say who gets to see the tables or not :)
'(asuming that the database window is hidden)

'disallow letting the shift key be pushed when opening
'the database to access the db window
Sub SetBypassPropertyFalse()
Const DB_Boolean As Long = 1
    ChangePropertyFalse "AllowBypassKey", DB_Boolean, False
End Sub

Function ChangePropertyFalse(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
    Dim dbs As Object, prp As Variant
    Const conPropNotFoundError = 3270

    Set dbs = CurrentDb
    On Error GoTo Change_Err
    dbs.Properties(strPropName) = varPropValue
    ChangeProperty = True

Change_Bye:
    Exit Function

Change_Err:
    If Err = conPropNotFoundError Then    ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, _
            varPropType, varPropValue)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        ChangePropertyFalse = False
        Resume Change_Bye
    End If
End Function

Sub SetBypassPropertyTrue()
Const DB_Boolean As Long = 1
    ChangePropertyTrue "AllowBypassKey", DB_Boolean, True
End Sub

Function ChangePropertyTrue(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
    Dim dbs As Object, prp As Variant
    Const conPropNotFoundError = 3270

    Set dbs = CurrentDb
    On Error GoTo Change_Err
    dbs.Properties(strPropName) = varPropValue
    ChangePropertyTrue = False

Change_Bye:
    Exit Function

Change_Err:
    If Err = conPropNotFoundError Then    ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, _
            varPropType, varPropValue)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        ChangePropertyTrue = True
        Resume Change_Bye
    End If
End Function

You can then make a call to one of two functions

Call SetBypassPropertyFalse ' secures the database.. shift key can't bypass the startup code, and cannot view the database window.

Call SetBypassPropertyTrue ' does the reverse.. allows users to use the shift key to bypass the start up.

(Once again, code adapted from an outside source.. can't remember where, so speak up if you want to take credit.)


Hope the above is of *some* help.. obviously would be happy to make suggestions on anything else you need... just ask

Si



------------------------
Hit any User to continue

 

[SiJP]

Sorry.. links should be:

- Find Record 97 (

http://www.mvps.org/access/downloads/findrecord.zip

)
- Find Record 2000 (

http://www.mvps.org/access/downloads/findrecord2k.zip

)

------------------------
Hit any User to continue