Protecting and allowing access to specific records

[mussy]

I just created a huge database using access 2010. How do i setup security level to allow access by groups to specific records? Can someone please help?

[Rawb]

I suppose that depends on what you mean when you say "Specific groups of Records".

The most common (and easiest) way to set up Security is Form-based. Meaning, User A can access Form 1, but can't access Form 2. If you want to make it data-dependent (either Table- or Record-based), then you've got a lot more work to do.

Either way though, you'll need to basically write your own security setup and, if you really want it to be secure, it will be a lot of work: Group- and User-level permissions, encryption, etc.

If you're still interested, then you should check out the Security Forum here. It may already have the answers to some of your questions. For the rest, feel free to ask away!

[mussy]

Thanks Rawb for your prompt reply. Actually I was looking to set up the security level by user level permissions per state area so that New York groups could not see New Jersey rates and vice versa. Am i clear enough? Thanks for your help

[Rawb]

Gotcha.

The problem there is, if the NJ rates go into the same Table as the NY rates, it gets difficult to prevent access to one while allowing access to another. At least, in Access.

As long as there aren't users that need access to multiple areas, then you should be able to control it by assigning users to a specific area. If there are users that need access to multiple areas (or areas that overlap with other areas), then that gets a little tricky.

Either way though, you're still looking at a full-fledged security setup: Users will need to log in to the database with a unique username/password and that information will need to be encrypted (or at least obfuscated). And with the removal of support for UAC, there's no built-in method for handling this anymore.

So, while this is possible, it's not going to be just a simple little task. At a minimum, you're going to need the following:

• A User Table (with encrypted passwords)
• A Permissions Table that lists which users can access which areas. If the areas a user has access to needs to be secret, then you'll need to use encryption here too.
• A Form that controls logging into the database, including the ability to dynamically disconnect/reconnect to Linked Tables so the user can't just "cancel" the logon Form and still have access to the database.
• A VBA function (or functions) that confirms a user's access that will need to be run before each action the user takes.

[orange]

For what it may be worth, SydneyGeek has a tutorial on Permissions. It is a system he uses and he has documented and provided info on this approach. I haven't used it myself, but have suggested that others read it for info.
I know SydneyGeek is quite active in some forums.
see http://www.mrexcel.com/forum/microso...ns-system.html

[Rawb]

The only problem with the tutorial you linked is that there aren't any passwords. That means as long as you know another user's name, you can log in as them and have all of their permissions, making it trivial to "hack."

If you're working in an area where you don't think you need security and are simply trying to cut down on accidents though, that would work great.

[June7]

You can even make the login invisible to users if this is on a network. I just grab their username from network login and compare that to my users table. If they aren't in the table then they are presented with a login form that saves them in the table and they never see it again. This works because the frontend is installed only on user machines who need it and no one else in the office cares about this database.

[Estuardo]

G'd evening,
I think that we all agree that no matter what path you take won't be an easy job, also we all know that no matter how tight you security is access dbs are files, and that won't change.
Being said that, i don't think that your problem is security, but filters.
If is possible for you to add fields to your tables you can have rates by state then run a query based on the users location. The same approach for the other tables.
I would take point 1 and 4 from Rawb and then in every form perform the location verification and display rows according.
Any way you may try the Access Security http://office.microsoft.com/en-us/ac...005188226.aspx

G'd luck

[mussy]

Thanks Guys! Points well developed. My success in this project is all yours. While i am embracing every single point in here, i find Rawb's and Estuardo's more related to my problem. Cheers,

[mussy]

I was thinking though. Excel allows to have hidden rows protected by a password. Do we by any chance have that feature in access? Anyone knows?

[June7]

No, not in Access.

Isn't Excel password protection on the workbook, not individual elements?

[Rawb]

No, not in Access.

Isn't Excel password protection on the workbook, not individual elements?

I think June7's right. As far as I know, although rows and columns can be hidden in Excel, all it's really doing is setting the height (or width) to 0. There's no way to password protect those rows or columns.

In Access, you could do something similar to that using Filters, but even those are trivial to remove, even using the Runtime.

[mussy]

Sorry June7 and Rawb! In Excel hidden rows and/or columns can be password protected. i do it every single day. While a workbook can be protected, you can also use the protection functionality to protect rows, columns as well.

[June7]

That sounds interesting but I couldn't find it. Want to give clue on how it's done?

[JoeM]

That sounds interesting but I couldn't find it. Want to give clue on how it's done?

I then they just mean hiding the column/row and then protecting the sheet (under the REVIEW menu on the CHANGES ribbon).

I was thinking though. Excel allows to have hidden rows protected by a password. Do we by any chance have that feature in access? Anyone knows?

I hope you are not using that as a means of security, as in trying to hide information from users, because it is not very effective and there is a very easy way around that.

Let's say that you have hidden row 3, because you don't want the user to see what is in cell A3.
All they have to do is to go into any other cell, and type in =A3 and it will show them what is in cell A3.
Even if that whole sheet is protected, they could go to another sheet and use =Sheet1!A3 to see its value (if the protected sheet name was "Sheet1").