Users only Work with Their Data

[robboll]

In MS Access 2003 What are some methods of restricting users to only
see their data in a shared database? Or if in SQL Server 2000 (or
2005) is this functionality available?

What I am doing now: I have a login form that has a dropdown for the
users. In the code behind the form is a case statement with hard-
coded passwords. If the password fails, it tells the user that the
authentication failed -- otherwise it opens up a main menu. The main
menu uses the user name from the login form to filter by form -- so
that the user only sees his data. Obviously all design options are
turned off and the form made into an MDE.

Okay -- shooting down my own method, I could by-pass the form and link
to the data with a new database. Even if I use a workgroup the user
links to the database with his workgroup password. He is able to
bypass the log-in form and can see everyone's data.

What I am looking for is row-level security and this Access method
would work if I could restrict the ability to link to the database --
password or no password. Any suggestions for this one.

Thanks,

RBolling

 

[Nikos Yannacopoulos]

I use a no-logon, no-password solution that is based on the user name by
which the user is logged on the network (Windows network, that is); this
can retrieved by means of function Environ("UserName").
You could use it to (a) auto-populate a UserID field in record creation,
and (b) filter your form recordsets.

The next requirement is to prevent the users from getting direct access
to the tables; I do that by (a) hiding the database window and disabling
special keys from the start-up options, and (b) linking to the BE tables
in code, only after I have checked that the database window is not
visible. So, if the user opens the .mde normally, they get connected to
the BE, but can't display the database window to get to the tables; if
they get smart and open the .mde holding down Shift, they get the
database window, but they are not linked to the BE; if they then try to
manually open the main form, whose Open event connects to the BE, the
code won't run because the db window is already visible.

HTH,
Nikos

 

[Jamie Collins]

What are some methods of restricting users to only
see their data in a shared database?

What I am looking for is row-level security

"Views can be created that provide a subset of rows, a subset of
columns, or a subset of both rows and columns from the base table."

A View Review
by Craig S. Mullins
http://www.dbazine.com/db2/db2-mfarticles/mullins-viewreview

The article is targeted at DB2 but the general principles may be
applied in Access/Jet. The VIEW syntax is available in Access/Jet SQL
syntax using ANSI-92 Query Mode (yes, Nikos, it's another of those Jet
4.0 'new' features <g>) but the Access analogue is a Query object
defined using a non-parameter SELECT query lacking an ORDER BY clause.

Jamie.

--