Security Question

[Mommio2]

Have just finished my first database for the elementary school where I
teach. We have set the permissions so that the teachers have read and
read/execute access, while the other administrators and I have full control.
On my menu page, I have a button for "Add Student". Is there a way to have
this button disabled if a teacher logs on and enabled for an administrator?
Thanks!

 

[Guest]

Why not give the teacher a frontend without the button?

 

[Jeff Boyce]

Do you currently have a way to tell who is logging on? Does your
application run over a LAN, with a front-end on the users' PCs and the
back-end on a server? Do users log in to that LAN/network?

Those would all be ways of determining what type of person logged on, then
using that to enable/disable the command button.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mommio2]

Thanks, but I don't know HOW to do that. Here is how it is set up. Database
is on the server. Each teacher computer on the network has a link to the
mdb file. We have the server permissions set up for teachers to have
read/execute and admins to have full control. How does Access know which is
logging on so it can "fade out" the button if it is a teacher? Is this the
correct way to set it up, or am I way off?

 

[Douglas J. Steele]

Assuming you haven't applied Access User-Level Security, you can use the
code from http://www.mvps.org/access/api/api0008.htm at "The Access Web" to
determine their network ID.

If you have applied ULS, you can use CurrentUser()

 

[Guest]

To get the user name of the currently logged in user add the following module
to your database:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends''''

Add a table Users to your database with two text columns UserName and
GroupName. For each user add a row with their user name and 'group' name
e.g. Teachers or Admins. Strictly speaking you should add another table
Groups with column GroupName, with two rows with values Teachers and Admins,
and relate the two tables, enforcing referential integrity. This prevents a
group not in the Groups table being entered in Users (by a simple typo for
instance).

In the form's Open event procedure put:

Dim strUser As String, strCriteria As String

strUser = GetUser()
strCriteria = """" & strUser & """ IN (SELECT UserName " & _
"FROM Users WHERE GroupName = ""Sheridan"")"

Me.[cmdAddStudent].Enabled = Not IsNull(DLookup("UserName", "Users",
strCriteria))

where cmdAddStudent is the name of the button. By using a subquery within
the DLookup function's criteria this caters for a user possibly being in both
the Teachers and Admins groups. It will work even if this isn't the case,
however.

From what you say it does sound like you are using a single .mdb file
installed in a shared folder on the server. This is not a good approach in a
multi-user environment. What you should have is a front end on each local
machine, containing the forms, queries, reports etc and a back end file on
the server containing just the tables. You can change your database to this
quite easily by using the built in database splitter wizard. This will
create a new back end and links to its tables in the front end file. You
should then move the back end file to a suitable folder on the server and by
using the built in Linked table manager in the front end refresh the links to
the back end in its new location. A separate copy of the front end should
then be installed on each local workstation along with Access itself. I have
actually seen one of my applications installed like this but with Access
itself on the server. Although not normally recommended it did seem to run
efficiently. Nevertheless it would be better to install Access on each
workstation.

The form of security you are using is very weak as far as the database is
concerned and could be easily subverted by a user. Access doe include its
own user and group security implementation, details of which you can find at:


http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp


Ken Sheridan
Stafford,

 

[Mommio2]

I'm not sure how to do that?? Thanks.

Why not give the teacher a frontend without the button?

 

[Mommio2]

Wow, thanks! Can't wait to try it!

Assuming you haven't applied Access User-Level Security, you can use the
code from http://www.mvps.org/access/api/api0008.htm at "The Access Web"
to determine their network ID.

If you have applied ULS, you can use CurrentUser()

 

[Mommio2]

Thanks a bunch! You are the first one to really explain all that to me.
I'll definitely be giving it a try. However, my brain cells are still fried
from spending the whole summer learning Access and building the database
project. It was fun, though! Really appreciate your help!

To get the user name of the currently logged in user add the following
module
to your database:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
(ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends''''

Add a table Users to your database with two text columns UserName and
GroupName. For each user add a row with their user name and 'group' name
e.g. Teachers or Admins. Strictly speaking you should add another table
Groups with column GroupName, with two rows with values Teachers and
Admins,
and relate the two tables, enforcing referential integrity. This prevents
a
group not in the Groups table being entered in Users (by a simple typo for
instance).

In the form's Open event procedure put:

Dim strUser As String, strCriteria As String

strUser = GetUser()
strCriteria = """" & strUser & """ IN (SELECT UserName " & _
"FROM Users WHERE GroupName = ""Sheridan"")"

Me.[cmdAddStudent].Enabled = Not IsNull(DLookup("UserName", "Users",
strCriteria))

where cmdAddStudent is the name of the button. By using a subquery within
the DLookup function's criteria this caters for a user possibly being in
both
the Teachers and Admins groups. It will work even if this isn't the case,
however.

From what you say it does sound like you are using a single .mdb file
installed in a shared folder on the server. This is not a good approach
in a
multi-user environment. What you should have is a front end on each local
machine, containing the forms, queries, reports etc and a back end file on
the server containing just the tables. You can change your database to
this
quite easily by using the built in database splitter wizard. This will
create a new back end and links to its tables in the front end file. You
should then move the back end file to a suitable folder on the server and
by
using the built in Linked table manager in the front end refresh the links
to
the back end in its new location. A separate copy of the front end should
then be installed on each local workstation along with Access itself. I
have
actually seen one of my applications installed like this but with Access
itself on the server. Although not normally recommended it did seem to
run
efficiently. Nevertheless it would be better to install Access on each
workstation.

The form of security you are using is very weak as far as the database is
concerned and could be easily subverted by a user. Access doe include its
own user and group security implementation, details of which you can find
at:


http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp


Ken Sheridan
Stafford,

Thanks, but I don't know HOW to do that. Here is how it is set up.
Database
is on the server. Each teacher computer on the network has a link to the
mdb file. We have the server permissions set up for teachers to have
read/execute and admins to have full control. How does Access know which
is
logging on so it can "fade out" the button if it is a teacher? Is this
the
correct way to set it up, or am I way off?

 

[Guest]

You won't have a group called Sheridan of course! That was just me testing
it on one of my own forms. I should have changed it to Admins. The way it
works is the DLookup function looks for a row in the Users table where the
user name is the same as the value returned by the GetUser function, i.e. the
currently logged in user, and where the group name is Admins. On looking at
it again, though I realise I've made it unnecessarily complex as it doesn't
actually need a subquery for this. I'd obviously lost sight of the wood for
the trees! Its much simpler just to build the criteria expression like this,
using a simple Boolean AND operation:

strCriteria = "UserName = """ & strUser & """ AND GroupName = ""Admins"""

In case you are wondering why there are so many quotes in this its because
to include a quotes character in a string already be delimited by quotes you
put two adjacent quotes characters. For a user named Ken the above would
evaluate to:

UserName = "Ken" And GroupName = "Admins"

The string values are thus wrapped in quotes as required for text data type.
If the DLookup function doesn't find a row which matches these criteria it
returns Null, so by testing for Not IsNull, if that is True the current user
is in the Admins group. QED. BTW don't ever be tempted to test for
something = Null. Nothing ever = Null, not even another Null; the result
will always be Null, not True or False. The nearest we can say as to what
Null 'means' is that it is 'unknown'. That's why we need the IsNull function
in VBA, and IS NULL in SQL.

Good luck.

Ken Sheridan
Stafford, England