Making Tables Secure Against Importing to other App....

[zevw]

I know there has been alot of talk about securing Databases.
Alot od arguments if Microsoft's Security is better or creating your own security is tougher.

I DON'T Like Microsoft's Security I feel it is to complicated and very not User friendly!!

I can secure my Databases that it cannot be imported or exported to another Access Database. However if I want to get the Data into Word, Excel I can import without a problem. Regardless if it is hidden and secured with all the permission security in Access as Read Design, Modify Design etc.

Is There a way that I can secure my tables that none of the data can be imported into any other application.

Any Help or Direction on this will be highly appreciated.

 

[RickSpr]

Look at it this way.

1. If the database is unencrypted, and users have read permissions on the .mdb file, anybody can open it with Notepad or WordPad, and thereby see the information in it. The information won't be organized, but one can usually glean some information out of it. So the database should definitely be encrypted.

2. If the database has only security implemented in VBA code within it, it is not secured against being opened in ways that don't run the VBA code. This includes opening the database while holding down the SHIFT key (note: even if you have AllowBypass=False set, there is supposed to be a way to defeat that), and opening it with Jet (instead of Access) using VB, C++, ODBC, etc. Jet doesn't even know what a form or a macro is, other than a generic object stored in its database, so it doesn't open the startup form or run the AutoExec macro. Besides that, Jet doesn't know how to execute VBA code. So you can't depend on VBA-implemented security when the database is opened with Jet.

3. Assuming you've encrypted your database, Jet is the only way to access, decrypt and make sense of its contents. So to protect your database, you need to have Jet do the security, which means authenticating the user and enforcing user permissions on the objects in the database. Guess what?! That's Access user-level security! Although Access provides the interface for manipulating it, it's really implemented by Jet itself. (You can actually have this same security implementation on a non-Access Jet database, though you'd have to write code to set up all the accounts and permissions.)

If you aren't relying on Jet security, which is to say if you haven't set up Access user-level security, it's always possible for another program to read your database, by slipping in "behind Access' back" so to speak. It's as simple as that.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

BTW, I forgot to mention: You said "I can secure my Databases that it cannot be imported or exported to another Access Database." If you mean you can do this without Access user-level security, I don't believe you. If you describe your technique, I'll bet I can describe a way around it.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[oharab]

I like it even more when people use VB as a front end for an application "cos it's more secure" then just leave the access files as they are! I've got at least 2 "Secure" systems on our network, written by IT, where I can just open the tables in Access, including the tblUsers which has 2 fields UserName & Password!! How often do you think someone uses the same password for that as their Lan logon!!
If I were an evil man.....


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[RickSpr]

Ben,

That's frightening! Especially since, combined with your signature, you've just exposed your company's back door to a world of hacking attempts!

If I were you, I'd waste no time explaining this to IT management. You've already gone on record as knowing how to crack the company's network passwords. What's it going to look like if somebody else in the company--an "evil man"--decides to read this and act on it?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[oharab]

No I don't think have! My sig is a personnal domain, not work related, I should be fairly ok.
I actually did mention this to a network guy, informally a while back, & his response was "The software is from another company, nothing to do with us!"
I also didn't mention the systems that work this way, so I think I've covered myself!

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[zevw]

Rick!

Thanks, for your response, I do appreciate it.

I use partial Microsoft Security and partially my security.

I set all the tables to dbHiddenObject, set security User/Permissions that only Read Design is enabled. So if you try export or import one of these tables into a new Database it will not allow you. Since the objects are hidden if you open another database and try to see all the objects you cannot see them. I then disable the Shift Key and have a hidden password built into one of the forms to re-enable the shift key again.

This all I think is good for not being able to view the code or modify the objects. What I found out last week is, that even though it is hidden, if you try to open in Excel by getting external data I can import all the data into an Excel sheet. That does not either bother me as much. BUT!! I had one table where I have some passwords for users that can administer the program. That gave me Goose Pimples because I saw their login names and passwords totally exposed.

You wrote about encrypting the Database. What do you mean? Do you mean putting a Microsoft Access Database Password on the the mdb file. If that is the case how can I make that the users don't know the password or with code I pass the password without have a dialog box pop for me to enter the password.

I have a third party software called winbatch which can detect if a dialog box pops, and will enter the password. I don't like that method, because someone that is somewhat hacky/savvy can create a file called Database password and the winbatch program will enter that password in the window of that text file.

So Rick give me somekind of direction what is my best bet for securing my Databases. Again thanks in advance for all your help.

William

 

[RickSpr]

First, let's talk about encryption. It's not a database password, but it is on the Tools|Security menu, as Encrypt/Decrypt Database. What it does is scramble the contents of the database, so that if you open the file with anything other than Jet, all you see is random nonsense.

Without encryption, you could open your database with Notepad, and find those passwords in it. It wouldn't be obvious that they were passwords, but if you were a hacker who knew the passwords were in there somewhere, you'd just look for any sequence of 6 to, say, 15 characters that can be typed, and try each one until you get a hit. If your passwords are actual words, as opposed to mixtures of words, numbers, and special characters, it would be even easier.

But I don't think encryption will solve your problem with Excel. The problem is that Excel uses Jet to access the database, and Jet does the decryption automatically. To protect your tables, you need to block access to the tables using user-level security.

It's not clear to me how you've set up your permissions. Do users have to log on, or have you given the permissions to the Admin user or the Users group?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[zevw]

Rick!

Thanks Again!

The way I Secure (UM!! I Don't know if they are secure), is going to Tools/Security/User and Group Permissions.

For Forms, Reports and Macros I only give Open/Run Permission all other options are taken away, and I set the same permissions for User and Groups. For Tables and Queries (Usually my queries are built into the code so it is not an issue), I leave all permissions open, except for Modify Design and Administer. The reason I leave all the permissions open is because I need to read and enter new data in the tables. Maybe there is another around this issue, but I don't know of another way to read and write to tables. As I said above, since I disable the Bypass key (Shift Key) No one can modify these permissions (Well! maybe I'm wrong).

So I am willing to hear what you suggest about my method.

In regards to using the User Level Security Wizard, I got to tell you I really think it will screw me up royally. Maybe I will give it a shot with a copy of my Database.

Any other suggestions, please let me know.

Thanks, William

 

[RickSpr]

You didn't answer some of my questions, but I assume your users don't log on, but rather open the database with the default Admin user account. You have given this account permission to do almost anything with the tables except Modify Design and Administer. You are hoping that you can protect your tables by hiding them, so people won't know their names.

This means you're making your security dependent on the ignorance of the people trying to circumvent it. In security circles, this is considered very weak protection. You never know when your table name will be revealed by some process you didn't anticipate.

For example, a savvy user can write code that opens your database in code, and loops through the TableDefs collection displaying all the table names. Your table names will be exposed.

The Database Documentor also lists hidden tables, though if you attempt to print the design information it just prints a blank page.

A user can also import or link your tables into a new Access database, if he knows the names, by using the TransferDatabase action or method.

Apparently Excel ignores the dbHiddenObject attribute all together, which is why you can easily import the table into Excel.

To protect your table against all these exposures, you need to deny permission to open it to the security account(s) used by your users. You should have a separate account that requires a logon password. You need to assign ownership of the table to this account. You will have to use this account to access the table yourself. (Make a copy of the workgroup file and join it, then open up a database and assign a password to the Admin user. You'll need to use this workgroup file to get the logon prompt when you want to sign on as the owner of the table.)

Of course, your forms, reports, and modules need to be able to open and read/update the tables. To do so, they must use OWNERACCESS queries. In Access stored queries, these have the "Run Permissions" query property set to "Owner's". In SQL statements, they include the WITH OWNERACCESS OPTION clause. OWNERACCESS queries are executed using the permissions of the table owner, not the user's permissions. Since the owner always has full permissions on the table, an OWNERACCESS query can do anything; it's up to you to limit what it does (via the query type) and which columns it selects.

Of course, that wouldn't be very secure either, if the user can create his own OWNERACCESS queries, so to complete the picture, you need to block the user from getting into the design view of any object other than a table design grid. (Queries, forms, reports, macros, and modules can all run SQL statements, so you need to block access to design view for all of them.) To do that, you basically need to uncheck all the startup properties--Allow Full Menus, Allow Built-in Toolbars, Allow Default Shortcut Menus, Allow Toolbar/Menu Changes, etc. Since that prevents you from using the built-in menus and toolbars, you have to provide your own, custom menus and toolbars (which can include safe built-in menu items).

I think the only remaining exposure to block is the possibility that users can change their own permissions. You must make sure the following are all true:
1. The users are not members of the Admins group in the workgroup file you were using when you created the database (or this copy of the database).
2. The users are not owners of any objects in the database. If any objects have a group account as their owner, the users must not be members of that group.
3. The users must not have Administer permission on any of the objects in the database.

That about covers it. Let me point out, though, that I've made the assumption that you set up your Access workgroup file correctly. Getting that wrong is the most common mistake made by people trying to implement Access user-level security. There are other security setup details you haven't given me, so I'm assuming those are ok, too. If you've got any of these setup details wrong, all bets are off. But if you can't open the database using an unmodified workgroup file from a fresh Access installation, you're probably ok.

Let me also point out that this is almost a complete implementation of Access user-level security. The only difference, I think, is that your users all use the Admin account and don't have to log on.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[daneharnett]

RickSpr,
It seems to me that you are very experienced with access user-level security.
I started on my current project a few months ago and implemented my own custom security because i didnt know access had built-in security.
My question is...
Am i able to query the permissions table to find out the current user's permissions... i would like to have a switchboard like menu but only show buttons that the user has permission to.. most permissions will be set 'by-group'
Also is it possible to provide a more user-friendly interface for the account management section... i would like users to be able to change thier password, but when you do this in access security you must go into users and accounts... then click on the change logon password tab... users should not have to see the other tab if you understand what i mean...
Or//
Would you recommend I use a more powerful front end like VB/C++/C#.NET etc etc etc to secure the database...
????

 

[RickSpr]

Dane,

It's really better if you start a new thread rather than tack additional questions onto an existing one. However, I'll give you a short answer here, this time.

For each object in the database, there is a companion object called a Document object. The Document objects have an AllPermissions property (a Long value) that consists of the sum of all the permissions the current user has on the object. (You'll probably know what I mean if I tell you it's a bit string.)

Look up AllPermissions in the help file to find some example code you can adapt. If you get stuck with it, start a new thread on Tek-Tips.

For a more user-friendly password dialog, simply create your own form. Use the Password setting of the Input Mask property to make the password text boxes appear as "*"s. Set the new password using the NewPassword method; again, see the help file for an example.

Access user-level security is actually implemented in Jet. If you push the security outward, into the front end, then another program can interface directly with Jet and circumvent your security. You can create front ends in other languages, but you'd still want to use Jet security to protect it. If your front end isn't Access, you'll just wind up having to create your own security administration tool, and why do that if Access already provides it? (This is not to say there aren't some good reasons to use a different front end language, but security is not a good reason.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[daneharnett]

RickSpr,
Thanks alot for your quick response...
I figured there was a way to do it, but thought about the reasons you have given me about why it probably shouldnt be done...
I suppose all you can do is do your best and settle for the pros and cons of your choice...
Why cant development be easy and also provide the best solution for everyone!!!!!! LOL