So lost with Security

[teel73]

I'm requesting in advance to please be patient with me. I have searched through and read up on user level security but I'm still having a major problem. How do I keep users from accessing the database through the directory or through their "MyDocuments"? One database that I created using the workgroup created by the security wizard works fine. I've tried the same with the workgroup I created but they are able to access the database. I need some serious "walk me through it help". Please anybody. What can I do to prevent this?

 

[odin1701]

I really don't like the workgroup method....what I do is more involved and requires quite a bit of coding, but then you have complete control over who can and cannot access your database, what forms/functions etc. they can or cannot access, no matter where the file resides.

 

[qdogfball]

If you want to prevent someone from opening the DB from the network or My documents or Recently used files, you will not succeed!

FYI, Access is NOT the best at total security.

But you can if you use the workgroup security, prevent people from opening the DB. (If they are not to hacker savvy )

Just set the DB up to use a password to gain entry.

Do a search on passwords to gain more understanding.

 

[teel73]

thanks guys. FYI, gdogfball, you can prevent users from accessing the database from the directory or MyDocuments. I have a database now that has that capablity. If you try to open from the directory or MyDocuments it gives you an error that reads: "You don not have the neccessary permissions to use FileName.mdb. Have your system administrator .........".

The thing is, the workgroup used with this database was created with the Security Wizard and I don't know how this is accomplished any other way. This is my problem. I want that level of security with the workgroups I've created. I'm getting close but not close enough. I believe it has something to do with the Admin user account.

I just hoped someone had an answer for me. Thanks anyway. I'll keep searching and posting.

 

[odin1701]

You could use VBA that runs on startup to check the path of where the file is and if it is somewhere local then have it pop up an error and then close the DB.

 

[odin1701]

Make a Macro called AutoExec, and in this macro as the first item pick RunCode and in the Function Name area put: Startup()

Then add this code to a module in your database:

Function Startup()
Dim strDBPath As String
Dim strDBFile As String

strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)

strDBPath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))

If strDBPath Like "C:\Documents and Settings\" & Environ("UserName") & "\My Documents*" Then

DoCmd.Quit

Else

End If

End Function

You can add OR clauses to the IF statement to compare to multiple locations. I would also add a message box before the Quit command saying they don't have access to run from the current location, so they don't just think that it's opening and closing and crashing or something.

 

[Exodus]

There is one big hole in Access security and its the MDW. If anyone is clever enough all they have to do is join that mdw as the system default and then create a new db and import all the objects from your db. I don't know if they addressed this issue in 2007 version but it is there in the 2002.

 

[odin1701]

There is one big hole in Access security and its the MDW. If anyone is clever enough all they have to do is join that mdw as the system default and then create a new db and import all the objects from your db. I don't know if they addressed this issue in 2007 version but it is there in the 2002.

Another reason I don't like workgroup security.

I prefer to deploy MDE files. At least even if they can break a password and re enable special keys to get into the file, you can't get to the code as far as I know if you are putting it in modules.

 

[Exodus]

Another reason I don't like workgroup security.

I prefer to deploy MDE files. At least even if they can break a password and re enable special keys to get into the file, you can't get to the code as far as I know if you are putting it in modules.

True about all they can do is get at your data. Which I feel is more of a threat than the code.

 

[odin1701]

True about all they can do is get at your data. Which I feel is more of a threat than the code.

Nah, if you got my MDE all you could do is get the forms/reports. Data is either on a secured source or SQL server. You would have to have a valid local login, plus have that login in my MDE in order to gain access to the linked/SQL tables with data.

There is of course probably ways around everything, but it's about as secure as you can get an Access DB.

 

[Exodus]

Unfortunately our SQL does not require a secure login. So I didn't take that into account.

 

[odin1701]

Yeah the only thing I'd have to worry about is an employee that has access taking the data. But there really isn't anything I can do about that.

 

[teel73]

But there is only one database file and it's located on the server. That's the database that should be accessing. They just should be using their desktop shortcut that points to that file in that location. When they use their shortcut the workgroup knows who the current user is but if they go thru the directory there isn't any current user information captured.

Make a Macro called AutoExec, and in this macro as the first item pick RunCode and in the Function Name area put: Startup()

Then add this code to a module in your database:

Function Startup()
Dim strDBPath As String
Dim strDBFile As String

strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)

strDBPath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))

If strDBPath Like "C:\Documents and Settings\" & Environ("UserName") & "\My Documents*" Then

DoCmd.Quit

Else

End If

End Function

You can add OR clauses to the IF statement to compare to multiple locations. I would also add a message box before the Quit command saying they don't have access to run from the current location, so they don't just think that it's opening and closing and crashing or something.

 

[odin1701]

Oh...well in that case I'm not sure how that could be coded.

I would just educate everyone on the proper procedure for opening the database.

This is why I don't like workgroup security much.


Well what you could do is make a table that contains all of the valid usernames that can access the database, and then compare that list against the results of the Environ("UserName") function and if they are not in that list, quit the database.

 

[Exodus]

I see another possible issue here. If you have multiple users on the same network db your best option is to go Front End and Back End databases.

 

[Len Boorman]

Send me a PM with an email address and I will send a couple of documents that explain security

If you just ran the security wizard that was the first mistake.

Okay it is not totally bombproof but you can control matters to quite a high level

Cannot post the documents here as they are too big

L

 

[odin1701]

I see another possible issue here. If you have multiple users on the same network db your best option is to go Front End and Back End databases.

I always recommend going to a Front End/Back End solution. Not only does this help protect data and prevent data corruption in databases that have a lot of code, it's easier to control I think. Your access DB can become more of an application - you can control and push out updates and so much more.

 

[teel73]

Yes. Exodus, this is what I have. I have a backend where my tables live and that file is in a seperate location. My frontend links up to the backend. That isn't my issue. I want to simply keep them from accessing the database file from the directory. And if they try to open it from the directory, I want the "no permissions" message to pop up. For example ... I can send you my database and you wouldn't be able to open it if the mdw file isn't in the same location as the mdb. You would get that message of having no permissions to open the file.

And the reason why this is so important to me is because I need to be able to track the current user. And again, if the user goes thru the directory and their system mdw isn't joined to the mdw asssociated with the database file, the current user is captured as admin. I can tell them all day long .."DO NOT ACCESS THE DATABASE FROM THE DIRECTORY" , but there will be and are some a-holes that disregard procedure so this is why I need that capability so badly.

 

[Exodus]

Sound like you need the docs from Len. You can restrict the admin account in the mdw. Its one of the biggest holes in access security. It is kinda of tricky Back up your db first.
I can't recall the exact way to do it but I do know that You have to build the the db while joined to your mdw.

 

[Len Boorman]

In truth what happens with every Access database is that you open it as a secured database with full admin rights by default.

So every Access Database is actually secured.... but everyone by default is an Admin User

This is controlled via the System.mdw file. You never use the System.mdw file with a Database that you want to properly secure. If you do you screw all other databases basically.

The Admin User (default) becomes the problem. You cannot delete it... You create a new Administrator account and give them permissions and remove all permissions from the Admin account.

This in itself needs to be done very carefully in a controlled manner other wise you shut yourself out of your own database... and everybody else sometimes as well.

Access Security can be very effective when done correctly... but it is a dog to set up correctly

L