Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

User setup, limited to only their department infomation.

I have an MS Access 2010 database that I would like users to access, the problem is I would like each user to see only their department information.  Can someone point me to a code that I can use for this.  I am desperate.  

Thanks
0
Kdankwah
Asked:
Kdankwah
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Bill RossCommented:
Hi,

Try these steps:

1. Create a table (tUsers) with users names, userids and departments.
2. Get the users network userid with the following code:

new module...

Option Compare Database
Option Explicit
Declare FunctionAPI_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetUserName() As String
   
    Dim lpBuf As String
    Dim ret As Long
   
    lpBuf = String(255, 0)
    ret = API_GetUserName(lpBuf, Len(lpBuf))
    GetUserName = UCase(Left(lpBuf, InStr(lpBuf, Chr(0)) - 1))
   
End Function

... end of module


3.  On your forms/reports join the user table tUser with the department selected by the userid = GetUserName()

Regards,

Bill
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Can someone point me to a code that I can use for this.

It takes a lot more that just code to do this. Every query must be updates to filter the records. If form's or report are based on tables then they will have to be updates to use a query that filters the records.

Implement this type of security in Access is something that should be done at the very beginning. It needs to be integrated into everything. It is possible to bolt in onto an existing Access Application but it is a lot of work and possible redesign.

Do you already have user tracking created?
Does your table design and relationships allow you to easily filter everything by department?
0
 
KdankwahAuthor Commented:
Thanks TheHiTechCoach, the database will have only one table and will not be updatable by the users.  Its going to be read only and for informational purposes.  

I will update the database at the backend.  Its only for users to see their reports.  

I really understand your concern if users were going to update the database.  


Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
clarkscottCommented:
Create a public variable

public glbDept as string '--- or Long depending on what type of field the dept is.

Now, create a funcion to return this value so you can refer to it ANYWHERE...

FUNCION get_glbDept () as string '---- or long depending on what type....
get_glbDept = glbDept
End Function

Create a logon routine that looks up your users and determines their dept (might be a homemade routine).
Assign the User's department to the public variable:    glbDept = {Users Department}



Now, if you are binding your forms straight to tables... make queries instead.  In the query design, in the DEPT column....     = get_glbDept()   for criteria.

Scott C
0
 
clarkscottCommented:
Further.... Except for VBA code, you cannot reference a variable value straight up.  But.... you can reference a FUNCTION that returns the value.   Using this technique allows you to actually use public variables to control entire systems.

I do it all the time....

Scott C
0
 
KdankwahAuthor Commented:
What if a user (a Director) is allowed to see two or three departments.  How would you create the code for that?.  

Should I have the Director in the users table multiple times based on what department they are allowed to see or have separate fields for each department, like department 1, department 2 etc.  
Another option I am thinking if doing is to have one department field and add the department numbers separated by comma.

Thanks
0
 
clarkscottCommented:
Create a "child" table to maintain each user's departments (each record is a department number and userid.   Put these in a single query and link this query to your other queries... including all the departments per user.

Instead of public variable for the dept.... do the same thing for the UserID.  Put the "=get_userid()" function in the query that marries both the userid and table of departments.

Scott C
0
 
Bill RossCommented:
Hi,

As Scott says it's not too difficult but you will need to set up the process.

Here is an article (from a different Scott) that might give you some insight.

http://www.accesssecurityblog.com/author/Scott-Diamond.aspx

Regards,

Bill
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Kdankwah,

It really does not matter it you have one table or many table. Or if the data is read-only or updatable. The security model will be the same.

I assume that your main table has a field to identify the department. Like DeptID.

Based on what you need you should use security groups.  Similar to the was Windows handles security.

You will have there tables
1) Groups
2) Group Departments
4) Group Users
4) Users
 
For a specific user, to find what departs they can see you would use the following table relationships  to get a list if there available departs.
 
Users -> Group Users - > Group Department

This would be used to filter the records in your main table by Department ID.

It is possible to get the Windows User name and then automatically filter the records.

If you are in a domain and using Active Directory, it may be possible to use the security group in AD to filter the records. Assuming you already has the security model set up in AD.
0
 
KdankwahAuthor Commented:
I like the answer given by TheHitechcoach
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now