complex security

[Guest]

Hi All...I am desining an application where I have several forms..say about
15. From the Main Menu some of the forms can be accessed. I have buttons to
access those forms on click events. Some of the forms also contain subforms
and tab controls. Now, on each forms including the main form I want the
system to display certain things based on user access level. I know how to do
this by having detect the user and the access level on each form. To cut the
redundancy, I want to create a module and on each form I want to call the
function that will return me the access level. Now, this I am not sure how to
do it from module level and I am looking from you to assist me. Any help you
can provide me would be greatly appreciated. Please see below the code I
tried to use in a module. Just an FYI, I am not using access security. I am
detecting user name from Library and I have table that contains the same
username and accesslevel. This is to ensure end user doesn't have to remember
one more userID and password, something more towards single sign on kind of
thing.

Option Compare Database

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


Public txtUserName As String

Public Function getUser() As String
Dim strBuffer As String
Dim lngLen As Long, lngReturn As Long

strBuffer = String$(15, " ")
lngLen = 15

lngReturn = getUserName(strBuffer, lngLen)

If lngReturn Then
getUser = Left(strBuffer, lngLen - 1)
Else
getUser = ""
End If
End Function

Public Function getAccess(access) As String
Dim strSql
Dim mydb
Dim myrecset
Dim access

strSql = "select tblUserAccessLevel from tblUser where tblUserName = " &
"'" & getUser & "'"

Set mydb = CurrentDb
Set myrecset = mydb.openrecordset(strSql)

access = myrecset.tblUserAccessLevel

End Function

 

[John Nurick]

Do you realise that this system is not actually going to provide any
security? You will be able to adjust the user interface according to the
user's access level, but the data itself will not be protected (unless
you're implementing some other scheme you haven't mentioned). For
example, a power user would be able to create a new database and then
write queries to change or retrieve your data, bypassing your user
interface.

What you're proposing will help create a convenient user interface but
it won't secure the data. More comments inline.

Hi All...I am desining an application where I have several forms..say about
15. From the Main Menu some of the forms can be accessed. I have buttons to
access those forms on click events. Some of the forms also contain subforms
and tab controls. Now, on each forms including the main form I want the
system to display certain things based on user access level. I know how to do
this by having detect the user and the access level on each form. To cut the
redundancy, I want to create a module and on each form I want to call the
function that will return me the access level. Now, this I am not sure how to
do it from module level and I am looking from you to assist me. Any help you
can provide me would be greatly appreciated. Please see below the code I
tried to use in a module. Just an FYI, I am not using access security. I am
detecting user name from Library and I have table that contains the same
username and accesslevel. This is to ensure end user doesn't have to remember
one more userID and password, something more towards single sign on kind of
thing.

Option Compare Database

Always use this too. It will reveal some errors in your code:
Option Explicit

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

This variable isn't used:
Public txtUserName As String

Public Function getUser() As String
Dim strBuffer As String
Dim lngLen As Long, lngReturn As Long

strBuffer = String$(15, " ")
lngLen = 15

lngReturn = getUserName(strBuffer, lngLen)

If lngReturn Then
getUser = Left(strBuffer, lngLen - 1)
Else
getUser = ""
End If
End Function

Public Function getAccess(access) As String

I think you mean
Public Function GetUserAccessLevel() As Long
or is the user access level a string??

It's usually better to declare variables with the right data types and
not to use Variants for everything

Dim strSql AS STRING
Dim mydb AS DAO.DATABASE
Dim myrecset AS DAO.RECORDSET

This variable isn't actually used:

Dim access

strSql = "select tblUserAccessLevel from tblUser where tblUserName = " &
"'" & getUser & "'"

Set mydb = CurrentDb
Set myrecset = mydb.openrecordset(strSql)

Because getUser() returns the Windows username and you're querying your
own tblUserAccessLevel, there's no guarantee that the user name will be
found. So you need to return a default user access level in that case.

access = myrecset.tblUserAccessLevel

Instead,

If myrecset.RecordCount = 0 Then
'Return default access level
GetUserAccessLevel = 0
Else
GetUserAccessLevel = myrecset.tblUserAccessLevel
End If

End Function

Once this is working right, you can get the current user's access level
from code anywhere in the application by just doing something like
Dim lngUserAccessLevel

lngUserAccessLevel = GetUserAccessLevel()

For instance, in a Form's Open or Load event, you can do something like
this (there are many other ways)
Select Case GetUserAccessLevel()
Case 5 'top level
'Do nothing: has access to all controls
Case 4 'shouldn't see salaries
Me.txtSalary.Visible = False
Case Else
'Levels 3 downwards have read-only access
Me.AllowEdits = False
End Select