How to protect ONE db design on a network?

[webtest]

Esteemed Forum Participants and Lurkers:

I have an Access 97 data base on an enterprise network for about 100 users, and I want to generally protect the tables and query designs so that only a few administrators can maintain them. I do NOT want to burden the general users with passwords.

I have already experimented with splitting the tables/queries and forms/reports into separate files and linking the tables. I also totally locked myself out of access yesterday playing with a custom .mdw Workgroup file. That was a royal aggravation, and I've learned to hate the .mdw protection system.

My problem is that my data base is only one of many in the enterprise, and I don't want to affect or endanger any of the other data bases with the protection I set up for my application. There is absolutely no way I can control or maintain an .mdw workgroup file on all the user's PCs. What I need is protection specific to only my data base.

Does anyone have any suggestions as to how to go about minimally protecting all of the designs without requiring passwords from the general users and without any risk of fouling up any of the other Access applications on the network? Thank you for your gracious comments, suggestions, and assistance.

Blessings in abundance, all the best, and ENJOY!

Art Carlisle, PA USA
imadeptNOGI@NOGOpa.net
(Remove NOGI/NOGO - NO Garbage In, NO Garbage Out!)

 

[sfreeman]

Not much help, I am sure, but with
numerous db's on a corporate LAN
, and security a big issue
, and table linkage a consideration
, and not wanting to create another login

, have you given any thought to upsizing to SQL Server?

Integrated security and roles should give you want you want.

In Access, I am not aware of any way to protect the data tables without requiring a login.

One possiblilty would be to split the db into the standard front-end/back-end. Secure the back-end, and then provide the end users with access to the front-end only. If you hide the db window and do not allow them access to the design of the tables, then maybe you can effectively 'hide' the connect string, which includes the password to the back-end.

I still prefer the upsize, especially in a corporate environment.

Check this link, it is a little old, but may give you a bit more.

http://forums.aspfree.com/t26426/s.html

Sam_F
"90% of the problem is asking the right question.

 

[webtest]

Thanks Sam_F

Thanks for the gracious comments. Hey, I'm a tiny speck in the Corporate Coal Bin (I'm not even close to being in the IT department!), and this Access data base is about as insignificant as I am. Nothing is going to change around here just because of my wishes!

I have already split the tables/queries from the forms/reports. Fortunately, security is not a critically big issue on this one ... I just want to make it difficult for the average user to inadvertently foul up any designs, and to generally discourage a few knowledgeable users from getting directly into the main table.

Blessings in abundance, all the best, and ENJOY!

Art
Carlisle, PA

 

[ALRice]

I to am looking for an answer on this. I have gone one step further than spliting the database. The backend database is stored in a hidden folder, but anyone with any knowledge of how to unhide folders can get to it. Password protecting the folder is not an option because then legitimate access is denied, and the data is changed daily. I have also hidden the linked tables and the database window itself. but again, anyone that knows even a little bit about access can change the design and "back-door" the access. Sooo.. what i am looking for is to password the design of the tables/querys/forms/reports so that, tho the data changes, the way it is arranged does not. Any help is so very much appreciated.

Aim for the moon, for even if we miss, we are still among the stars.

 

[MoLaker]

DB security can be a pain. Basically, it boils down to either use Access user-level security (which will require login), move up to SQL Server or something like it (which will probably require login), or forget it and trust your users. The limits you seem to be operating under pretty much require you to use the last method.

About the best you can do is to make an MDE of your DB. At least, users cannot alter object designs except for tables and queries.

On the other hand, if you are willing to learn and use user-level security, you can accomplish most all your needs and protect your database from most users, except for the most persistent. So, my suggestion is to give Access security another try. But, start by first making a backup of your database. True, you will burden your users with a login, but if the data is important enough to protect, it's important enough to make users log in. You could create a generic user ID and and give that ID no password. At least users would only have to hit OK or the return key to get past the log in once that user ID was entered.

 

[gmacg]

How about "You toucha de database, you losa de hands!"?

Seriously, would it be possible to make up a modal form to start up on the backend? That might confound a few more users. Of course you would have to allow use of the shift key to by pass start up commands, or you wouldn't be able to get around it either.

Just a thought.....

 

[mrsbean]

If you want to experiment with SQL server, try MSDE which is SQL without the front end. You can use Access as the front end and administration tool and MSDE as the back end instead of the standard Access database option, and it's free. You might try installing and tinkering a bit to see if you like it. This option will give you the integrated security mentioned in sfreeman's post.

http://www.microsoft.com/sql/msde/default.asp

MrsBean

 

[sfreeman]

I agree with MRSBEAM, but would suggest using SQL Express instead... just a newer version.

Sam_F
"90% of the problem is asking the right question.

 

[gareth001]

Depending on how much access you wish for the user to have to your database tables, forms etc, you can give the users an access runtime shortcut to your database. This way they will only see the switchboard (or similar) and anything its menu links to.

"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "C:\Database\Database.mdb" /runtime

 

[jgoodman00]

How about (I presume users have individual logins):

1. Create linked 'application' front end dbs & maintain the data in a network location.
2. Create a Users table in the backend db. This will contain a list of the Environ("UserName") logins that you want to grant access to the backend DB.
3. Create a simple startup form in the backend db which executes whenever the db is opened. This procedure needs to compare the Environ("Username") against the list of valid users. If a match is not found, display a message box (such as 'Not authorised') & boot them using DoCmd.Quit.

This should give you the protection you require & I have used this with great effect. You could even goto the extent of removing the ability to use the shift key in order to bypass processing of the startup form. However, if people are trying to bypass this they should probably face official disciplinery action anyway, as their motives could be questionable.


You can protect the application objects (in an MDB this will include queries), by hiding the DB window & disabling the shift key etc. Converting to MDE will also increase security.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[jgoodman00]

My description was a little wordy so I created a sample:

http://www.j.goodman.dsl.pipex.com/Access.htm

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[mordja]

webtest,

I was trying to implement the same thing and could not at first get access to work well protecting one MDB with a workgroup file. The mistake I made was to change access from the System.mdw to the one I created which caused access to ask for a password for any database. Using a short cut with the open with property set to

"Path Of Secured Access DB" /WRKGRP "Path of .mdw"

I had no more problems. Access works as normal using the System.mdw except when I want to access my protected db which I do using a the above shortcut, DONT CHANGE ACCESS FROM THE System.mdw

Mordja

 

[ReluctantDataGuy]

For protecting sensitive data from prying eyes, I have a technique that is ( I think ) simple yet fool-proof. I've had some very smart people try to hack it and they couldn't find a way.

It does require a workgroup, but the workgroup is only for database design changes. Users don't have to even have access to the workgroup file (!!)

Here's the gist of it:

1) Create a workgroup and in the workgroup, create a "super user" account (named whatever you want).

2) Give the super user account ALL priviledges on ALL objects.

3) Remove (almost) ALL priviledges on ALL tables for the Admin account. Note that you'll have to take care of both users and groups, but its been a while since I've done this so forgive the vagueness). The one permission you must allow the Admin user to retain for tables is the "Read Design" permission. Remove all other permissions.

4) Using the super user account, create a query for each table. (yeah, I know, a query for each table is a pain, but it's required for this method to work) In the query, you can just use the * (as in SELECT * FROM...)

5) For each query that is related to a table, set it to open "With Owner Permissions" which is a property of the query.

6) Base the recordsource of ALL forms, reports, and other queries on the queries described in 4 and 5 above.

7) When distributing the application, don't do anything special. Allow the users to open the database as the default Admin user that is active when simply double clicking a .mdb file. Because the default user (Admin) has no permissions on the tables (except Read Design), they can't look at the data even if they can see and attempt to open the tables in design mode. They just get a security error.

Of course, you'll want to do other things like turn off the ability to use the shift key to get into design mode, make it an mde instead of an mdb file, or whatever is appropriate for your situation.

Using this method, no one will be able to get to the data. Even if they create an empty database and import the tables from your secure database, they'll just get an access denied message when attempting to open the tables.

It sounds like a lot of work, but once you get it working correctly, you can use the same workgroup (.mdw) file to create any and all databases - and the security schema is simply inherited by the new database. For this reason, its best to name your .mdw file with a generic name rather than say, the same name as your .mdb. (ie: don't give the .mdw file the same name as a specific database, like: MySpecificDatabaseName.mdb and MySpecificDatabaseName.mdw)

To create a new database using the generic workgroup, simply open Access (by itself, without a specific database) using the command line switch "/wrkgrp <.mdw file path and name>" (or whatever it is - can't remember off hand). Note that to do this you'll need to create an icon shortcut to Access with the command line switch specified in the shortcut.

Okay, once you have a "secure" copy of Access up and running on your machine, simply create a new database. That database will inherit the security from the .mdw file. Then whenever you need to make design changes to your database, simply open the database using the command line switches for the .mdb file and for "/user <your super user account>". When the database opens, you'll be asked for your super user password. Once logged in as the super user, you can make design change, see the data, and create new databases.

If you've limited the permissions on the Admin user correctly, no one but the super user will be able to view your secure data.

Oh, the real beauty of this solution is that you don't have to do SQUAT as far as managing individual users in the Access Security schema. It frees you up to manage user access and priviledges via your own user and permissions tables. As far as Access Security is concerned, all users are the default "Admin" user. Even if someone logs into a database as an account other than "Admin", it doesn't matter, only the super user can access the table data.

Even though this method does require you to deal with the built in Access (Jet, actually) Security system, this is about the simplest useage of it possible - yet it is secure and flexible.

Oh and a final note, this works for linked tables or local tables and for passthrough queries. The queries per table don't care if the tables they point to are linked, passthrough eries or local tables.

With appologies to those who have difficulty with Access Security, that I can't provide more detail on creating the mdw security... I hope this helps.

ReluctantDataGuy