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?

 

[June7 via AccessMonster.com]

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
info (organizational position?) in the Users table. Include join of these
tables in the query and return only those matching User Login ID. Is your
project set up to open to particular form and has menus and shortcuts (right
click) disabled?

 

[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.

 

[June7 via AccessMonster.com]

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]

 

[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?

 

[June7 via AccessMonster.com]

What I have done is build a form with one textbox, into which users type
initials. Project set to open this form when user opens project. Code behind
the form sets a globally declared string variable to the value of the
initials. Then those initials are saved to the table for the record user is
editing at various stages of document processing. Don't use it for security,
just work flow tracking. In your case, don't see need to save to table, but
the global variable could be good. Code would check that the login info
matches data in the Users table, if okay then proceeds to open next form
(probably a menu). Use global variable in form RecordSource and in VBA code
queries to filter records by this criteria. Haven't used password protection
so not sure how this would play into process.

My project is split. Copy of front end installed to user's C drive on their
system, data on central server. This allows each user to run an independent
instance of the project to reduce conflicts between concurrent users and make
code run faster.

I don't use any of the object design wizards, always build from scratch and I
don't use macros, all VBA. Don't like the 'switchboard' template, uses macros.

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,

[quoted text clipped - 31 lines]

 

[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.