Solved

Password protecting Access Records using Groups and Users

Posted on 1998-07-30
15
162 Views
Last Modified: 2010-05-03
I have an Access database and I need to restrict record access to records based upon the user and supervisor.  I have a "Code" field that will identify the record user and also first and second level supervisors.(or group and subgroup)  For example:  The code XXX1A1 is a user and created the record.  Supervisors XXX1A and XXX1 would also need to access the record but no one else.

Code would certainly help.
0
Comment
Question by:clarwc
  • 7
  • 6
  • 2
15 Comments
 
LVL 14

Expert Comment

by:waty
ID: 1467222
I think you should add to your query a where clause.

Thes best solution should be creating a global function where you pass the query as parameter and you add the Where clause as following as you need.

0
 

Author Comment

by:clarwc
ID: 1467223
I am VERY new to VB and I don't understand your answer.
0
 
LVL 14

Expert Comment

by:waty
ID: 1467224
Add a clause in your queries like following (adapt for your code)

Select ***** From ***** Where *****
and ((Mid(Security, 4, 1) = '1') or (Mid(Security, 4, 2) = '1A')
or (Mid(Security, 4, 3) = '1A1'))

You should do something like that, of course.
0
 
LVL 1

Expert Comment

by:wford
ID: 1467225
what waty means is something like this in your SQL string used to get the records from access

where:
 Sqlstr = "SELECT * FROM YourTable Where Code Like '" & Trim$(Usercode) & "*'"
Set yourRS = yourDb.OpenRecordset(Sqlstr, dbOpenSnapshot)

this would get the files for all records where supervisor usercode = XXX1A and XXX1 and XXX
you need to be careful with this , a usercode of "" will have access to everything!

if this works better than watys , reject and i'll post as answer, he beat me by 1 min, spewing!!
0
 

Author Comment

by:clarwc
ID: 1467226
I am not using any SQL queries.  I have a login screen.  I would like to be able to have my program look at the username when logging in to determine what access will be granted.  For example the XXX1A1 user would have access to just his files.  The XXX1A user would have access to XXX1A1, XXX1A2, XXX1A3 etc...

My guess is that I need to set up a Group and user structure to do this.  Then when the user logs on the program could determine which groups he is a member of and grant access based upon that.
0
 
LVL 1

Expert Comment

by:wford
ID: 1467227
you could muck around with the LIke statment a bit to specify exactly the format that you need, have a look at the LIKE (microsoft jet sql)
0
 

Author Comment

by:clarwc
ID: 1467228
I guess what I was looking to try and do is build groups and users.  For example:

Group XXX1
   Sub Group XXX1A
       User XXX1A1
       User XXX1B

And then when the user logs on to the program the username determines what groups he is a member of and allows access to records accordingly.

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Expert Comment

by:wford
ID: 1467229
are you using the database control table property and then controls linked to this data control?


0
 
LVL 1

Expert Comment

by:wford
ID: 1467230
are you using the database control table property and then controls linked to this data control?


0
 

Author Comment

by:clarwc
ID: 1467231
Yes I have several forms with data controls and controls linked to the data control.
0
 
LVL 1

Accepted Solution

by:
wford earned 200 total points
ID: 1467232
ok, then what you need to do is restrict the recordset that the data control is using, for this you use an Sql to get that RS, then assign it to the datacontrols recordset property at logon, like:

in your logon sub, get the users access code based on there name , however you do it, then:

dim Sqlstr as string
dim yourRS as recordset  
 
Sqlstr = "SELECT * FROM YourTable Where Code Like '" & Trim$(Usercode) & "*'"
Set yourRS = data1.database.OpenRecordset(Sqlstr, dbOpenSnapshot)

'now set your data control to this rs then voila! all the linked records should see only the files that they have code access to

Set Data1.Recordset = yourRS

see what you think
0
 
LVL 1

Expert Comment

by:wford
ID: 1467233
woops, sorry I made some assumptions, you will have better luck with:

Dim Sqlstr As String, usercode As String
Dim yourRS As Recordset
Dim yourDB As Database
Dim ws As Workspace

Set ws = Workspaces(0)
Set yourDB = ws.OpenDatabase(Data1.DatabaseName)
'set usercode here
Sqlstr = "SELECT * FROM Functions Where AccessCode Like '" & Trim$(usercode) & "*'"
Set yourRS = yourDB.OpenRecordset(Sqlstr, dbOpenDynaset)
Set Data1.Recordset = yourRS

I didn't test it on its own before i sent it, bad huh!
0
 

Author Comment

by:clarwc
ID: 1467234
Where would I put this code.  In my login screen?  And how does it effect the other data controls on my other forms.  My login screen loks like this now:

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


Public OK As Boolean
Private Sub Form_Load()
    Dim sBuffer As String
    Dim lSize As Long


    sBuffer = Space$(255)
    lSize = Len(sBuffer)
    Call GetUserName(sBuffer, lSize)
    If lSize > 0 Then
        txtUserName.Text = Left$(sBuffer, lSize)
    Else
        txtUserName.Text = vbNullString
    End If
End Sub

Private Sub cmdCancel_Click()
    OK = False
    Me.Hide
End Sub


Private Sub cmdOK_Click()
    'To Do - create test for correct password
   
   
    'check for correct password
    If txtPassword.Text = "" Then
        OK = True
        Me.Hide
    Else
        MsgBox "Invalid Password, try again!", , "Login"
        txtPassword.SetFocus
        txtPassword.SelStart = 0
        txtPassword.SelLength = Len(txtPassword.Text)
    End If
End Sub


0
 
LVL 1

Expert Comment

by:wford
ID: 1467235
you could stick it in the cmdOK Click event, all you need to do is get the usercode in some way before sending the SQL. it will effect the other data type controls not at all, but all the other controls(textboxes, option buttons etc..) linked to this data control(ie. data1 in my code) will use only the subset of the records (with the conditions matching the where ..like clause)returned by the sql control and assigned to this data control.

I should note that at design time, your are esentialy doing the same thing as in my code, with the data controls propertys RecordSource et al, using an sql like this lets you filter and arrange the records before you use them.  

if you what the same rs to be used by all data controls then just put a list of them and set them all to yourRS like:

set Form1.data1.recordset = yourRS
set form2.data1.recordset = yourRS
...
however it would make more sense to declare yourRS as a global recordset and assign the data control to it in the form load event on each form.
hope this is what you need
0
 

Author Comment

by:clarwc
ID: 1467236
Thanks for all your help.


0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now