hide SOME query results for unauthorized users

[Derek]

how can I restrict access to records marked confidential or sensitive? Some
of the data in my DB is to be viewed only by personnel with specific
permission, and not the general population. I think I know how I could "tag"
a record as being available to permission holders, but how can I keep the
data in the record from being viewed by users without that permission?

 

[Derek]

Thank you for the response. To answer your questions; my DB is not created
yet, I am still in the planning stage so I don't have anything constructed.
When I do have tables, one of the columns will be a checkbox for any user
(but typically only the users who are generating the sensitive data) to flag
the record as sensitive. Sure, you can hide from everyone all records in a
query that are flagged by splitting the database and excluding all but a few
from the back-end. But if I want everyone to be able to create their own
queries how do I allow some but not all users to see the flagged records.

The users table you mention sounds interesting - but I have no idea how to
create one.

 

[Derek]

Thanks.

Ok, I know how to make a table. I know how to assign a password to a
database to make it impossible to open without the password. What I'm
confused about is how to set-up a "login" procedure. Is it done via a
switchboard or something?

Building tables is the easy part. Use the table designer tool. Define fields
you desire (name, data type, etc.) Possible fields for Users table: LastName,
FirstName, Title, Department, SecurityLevel, PhExt.

The tricky part comes in controlling user access. You want them to be able to
create their own queries. If you let them use the toolbar tools then you
might have no control. I have a form in my project that has comboboxes from
which the users can select criteria (date range, document number, etc). Then
with VBA code I compile a SQL SELECT statement or set form Filter properties
(depending on the criteria selected) that will execute the users request. The
toolbars are not available to users. They are totally at my mercy -
bwahahahaha! The form RecordSource as well as these combobox lists of
choices can limited based on the user login ID.

Not having a specific issue to solve, can't offer any more than to say it can
be done. You will learn a lot by doing, so dive in and when you have specific
issue, post a question.

Thank you for the response. To answer your questions; my DB is not created
yet, I am still in the planning stage so I don't have anything constructed.
When I do have tables, one of the columns will be a checkbox for any user
(but typically only the users who are generating the sensitive data) to flag
the record as sensitive. Sure, you can hide from everyone all records in a
query that are flagged by splitting the database and excluding all but a few
from the back-end. But if I want everyone to be able to create their own
queries how do I allow some but not all users to see the flagged records.

The users table you mention sounds interesting - but I have no idea how to
create one.

I presume you have some kind of login procedure, perhaps even with password
protection, and you have a table of Users and Login info. Include the tag

[quoted text clipped - 8 lines]

a record as being available to permission holders, but how can I keep the
data in the record from being viewed by users without that permission?

 

[John W. Vinson]

how can I restrict access to records marked confidential or sensitive? Some
of the data in my DB is to be viewed only by personnel with specific
permission, and not the general population. I think I know how I could "tag"
a record as being available to permission holders, but how can I keep the
data in the record from being viewed by users without that permission?

If you have Access2003 or earlier you may want to consider Access Workgroup
Security (it was removed from 2007; secured databases can still be used in
2007 if they are in .mdb format, but not in .accdb, and I believe that there
are some security operations that require an older version). To read about
security download the Microsoft Access 2000 Security FAQ:

http://support.microsoft.com/kb/207793/en-us

It's complex. June's advice is good, but just be aware that security is VERY
difficult, especially if you're trying to defend against knowledgable and
determined computer experts! "Locks keep honest people out" applies here as
well as in the home. Any "home grown" security scheme will be either easy for
anyone skilled in Access to crack, or very difficult to implement - OR BOTH.
Microsoft decided to remove workgroup security from the 2007 version because
of this very issue - a quick Google search will find sites offering cracks for
secured Access databases, and not all of them are fraudulent.

If you need real, robust data security then you really need to keep the data
in SQL/Server or another client/server database where the security is
maintained by the operating system.