Fine-grained security in Access (record-level)

[Vic]

Dear All,

I have been developing a small access database, but I am new to
security concepts with access. This is a multiuser database, I have a
table which will be written by various users through a form as an
interface. At the moment, there is an authorId field on the form
(other details of authors are in a separate table connected to this
table with the authorID) , and every user select their initials from a
combo for the record they input. Questions:

1. How could I "automatically" detect which user is logged on to the
network and accessing the database and store its login name in the
table (I know about the current user() function, but I would prefer to
detect who is logged on to the win2k environment (rather than setting
up separate access users which makes users log on twice, once to the
network, then to access)before accessing the database)

2. How can I implement that a user could only see/write those records
from a table which belong to him/her (aka, records containing his
authorID in the the Authors field) and of course could add a new
record?

Appreciate any help, and bear with me, I am a newbie as far as acces
security is concerned...

Viktor

[MacDermott]

Dear Vic,

I'm afraid you won't get far in implementing Access security if you're not
willing to have your users log into Access separately. AFAIK, Windows
networking only allows setting of permissions down to the file level, and of
course all of the distinctions you want to make are going on inside the one
MDB file.
And while Access can return the current network username (see
http://www.mvps.org/access/api/api0008.htm), I know of no way to have it use
that name within its own security model.

Part of the issue is that you log into Access before you execute any code,
which makes it difficult to customize Access's behavior at that point.
(You'd have to be already logged into Access before you could use the code
at the above URL to even retrieve the username.)

HTH
- Turtle

"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Dear All,

I have been developing a small access database, but I am new to
security concepts with access. This is a multiuser database, I have a
table which will be written by various users through a form as an
interface. At the moment, there is an authorId field on the form
(other details of authors are in a separate table connected to this
table with the authorID) , and every user select their initials from a
combo for the record they input. Questions:

1. How could I "automatically" detect which user is logged on to the
network and accessing the database and store its login name in the
table (I know about the current user() function, but I would prefer to
detect who is logged on to the win2k environment (rather than setting
up separate access users which makes users log on twice, once to the
network, then to access)before accessing the database)

2. How can I implement that a user could only see/write those records
from a table which belong to him/her (aka, records containing his
authorID in the the Authors field) and of course could add a new
record?

Appreciate any help, and bear with me, I am a newbie as far as acces
security is concerned...

Viktor

[Dan Morgan]

You get the w2k user name by this:

dim wsh as object
dim strUser as string

set wsh = createobject("wscript.network")

strUser = wsh.username

set wsh = nothing

'and then set the default of the control on the open event of the form

Me!ctl.DefaultValue = "=" & Chr(34) & strUser & Chr(34)

'lock the control in the form so the user can't change the default

Me!ctl.enabled = false
Me!ctl.locked = true

Depending on how you open the form you could set the filter of the form
to show the user only his own work.


la*****@ntlworld.com (Vic) wrote in message news:<52**************************@posting.google. com>...

Dear All,

I have been developing a small access database, but I am new to
security concepts with access. This is a multiuser database, I have a
table which will be written by various users through a form as an
interface. At the moment, there is an authorId field on the form
(other details of authors are in a separate table connected to this
table with the authorID) , and every user select their initials from a
combo for the record they input. Questions:

1. How could I "automatically" detect which user is logged on to the
network and accessing the database and store its login name in the
table (I know about the current user() function, but I would prefer to
detect who is logged on to the win2k environment (rather than setting
up separate access users which makes users log on twice, once to the
network, then to access)before accessing the database)

2. How can I implement that a user could only see/write those records
from a table which belong to him/her (aka, records containing his
authorID in the the Authors field) and of course could add a new
record?

Appreciate any help, and bear with me, I am a newbie as far as acces
security is concerned...

Viktor

[Galina]

Viktor
You can define network user name by 2 ways:
1. If environment file on your network contins user name, which is
usually the case, you can use a function Environ("USERNAME") - see
Help.
2. You can use Windows API function (please excuse me, the code is
very humble):
Option Compare Database
Option Explicit
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long
Public Function Registr()
Dim lpBuffer As String, nSize As Long, A As String, B As String, x, i
as Integer

'Set up buffer to get the string from the function
nSize = 50
lpBuffer = " "
'API function call
x = GetUserName(lpBuffer, nSize)
'Select from the buffer string without gups
A = lpBuffer
For i = Len(A) To 1 Step -1
If Mid(A, i, 1) <> " " Then Exit For
Next i
User Name= Left(A, i - 1)

To show records only created by a user is very simple. Store AuthorID
defined by UserName somewhere in an invisible field on a start up
form, which doesn't show any data. Base your form, which shows rows of
the data, on a query rather then the table. Make this query criteria
AuthorID=Forms![My start up form]!AuthorID.
Hope it will be of some help.
Galina
la*****@ntlworld.com (Vic) wrote in message news:<52**************************@posting.google. com>...

Dear All,

I have been developing a small access database, but I am new to
security concepts with access. This is a multiuser database, I have a
table which will be written by various users through a form as an
interface. At the moment, there is an authorId field on the form
(other details of authors are in a separate table connected to this
table with the authorID) , and every user select their initials from a
combo for the record they input. Questions:

1. How could I "automatically" detect which user is logged on to the
network and accessing the database and store its login name in the
table (I know about the current user() function, but I would prefer to
detect who is logged on to the win2k environment (rather than setting
up separate access users which makes users log on twice, once to the
network, then to access)before accessing the database)

2. How can I implement that a user could only see/write those records
from a table which belong to him/her (aka, records containing his
authorID in the the Authors field) and of course could add a new
record?

Appreciate any help, and bear with me, I am a newbie as far as acces
security is concerned...

Viktor