I am trying to add security level to my database. I want customers to see only data that applies to them. All customers data is stored in the same area. My problem is I need to setup accounts that user need to login to. These account will need to have differewnt level of security. Any idea's would be appreciated. Thanks
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
MS Access Security User Levels 6
- Thread starter assets
- Start date
-
2
- #2
You will have to use your database engine to lock your users out of reading data directly from the tables. Then you will have to have your users have permission to get the data they need from queries/views that restrict the records they are allowed to see. In any case, you will need to store the user information in each record. You may also want to set up some sort of user hierarchy or something in tables. Then you can use a query (or function if multiple levels) to create a recordsource that you will in turn use to restrict users to the appropriate records).
In access, you may use the currentuser function or it may be better to use an API call to get the domain user see below.
Declare Function WNetGetUser& Lib "Mpr" Alias "WNetGetUserA" (lpName As Any, ByVal lpUserName$ _
,lpnLength&)
Function NetworkUser() As String
'Hacked up code from ACC: How to Retrieve Workgroup Information Under Win32
'PSS ID Number: Q148835
Dim cbusername As Long, UserName As String
Dim ret As Long
UserName = ""
' Windows 95 or NT - call WNetGetUser to get the name of the user.
UserName = Space(256)
cbusername = Len(UserName)
ret = WNetGetUser(ByVal 0&, UserName, cbusername)
If ret = 0 Then
' Success - strip off the null.
UserName = Left(UserName, InStr(UserName, Chr(0)) - 1)
Else
UserName = ""
End If
NetworkUser = UserName
End Function
In access the type of query you want is called RWOP (Run with Owners Permsissions). A RWOP query is created by setting the Run Permission's property of the query to Owner's instead of the default User's.
I hope this is enough to get you started. It is definitely a must to understand the security system of your database engine to make something like this work. If using Access, be sure to lock down the table permissions in the backend. You can define permissions in the frontend but if someone goes in the backend directly, they would have full permission.
In access, you may use the currentuser function or it may be better to use an API call to get the domain user see below.
Declare Function WNetGetUser& Lib "Mpr" Alias "WNetGetUserA" (lpName As Any, ByVal lpUserName$ _
,lpnLength&)
Function NetworkUser() As String
'Hacked up code from ACC: How to Retrieve Workgroup Information Under Win32
'PSS ID Number: Q148835
Dim cbusername As Long, UserName As String
Dim ret As Long
UserName = ""
' Windows 95 or NT - call WNetGetUser to get the name of the user.
UserName = Space(256)
cbusername = Len(UserName)
ret = WNetGetUser(ByVal 0&, UserName, cbusername)
If ret = 0 Then
' Success - strip off the null.
UserName = Left(UserName, InStr(UserName, Chr(0)) - 1)
Else
UserName = ""
End If
NetworkUser = UserName
End Function
In access the type of query you want is called RWOP (Run with Owners Permsissions). A RWOP query is created by setting the Run Permission's property of the query to Owner's instead of the default User's.
I hope this is enough to get you started. It is definitely a must to understand the security system of your database engine to make something like this work. If using Access, be sure to lock down the table permissions in the backend. You can define permissions in the frontend but if someone goes in the backend directly, they would have full permission.
bddewell408
Technical User
I am definitely way out of my league judging by this website and the contents. I tried the security wizard and I messed up big time. I cannot open any database on my computer. I tried uninstalling the entire Microsoft Office XP and then reinstalling, needless to say it did not work. Are there any files that can be deleted through the windows explorer that will wipe out the security all together? I did print a snapshot when trying the security wizard, but I cannot seem to figure out how to get back into access. Please help
- Thread starter
- #4
bdell408, donot use the security wizard on a live database. Always work with a copy.
Iameid, Thanks it has given my some ideas. I am not that cocerned with end. The database is on a network drive that most people on have read only access. What I am trying to do in access(not having a copy of VB6 that can compile)is to have list of users to appear when the database starts. The user then enter there password. This will link to a entity in a table, only showing records for that entity.
Iameid, Thanks it has given my some ideas. I am not that cocerned with end. The database is on a network drive that most people on have read only access. What I am trying to do in access(not having a copy of VB6 that can compile)is to have list of users to appear when the database starts. The user then enter there password. This will link to a entity in a table, only showing records for that entity.
assets,
I'd set use the password input mask for the password field in the table so that it displays only asterisks. Then I'd have a listbox or combobox to select the user that is logging in. Next the user would need to enter the password which you would use to put the value in a hidden form that your RWOP query uses as criteria. For this to work users should be locked out of the database window (startup properties) and you need to disable the shift key for startup (I think there is a FAQ about that).
bddewell408,
Can't say I know what the security wizard does because I've not used it. However, I do understand Access security. Access uses workgroup files (.mdw) to authenticate and unlock secured databases. You must have the appropriate key (pid) in a workgroup file in order to access a database file. Therefore, once secure you cannot just strip security by deleting a file. In order to strip security off a database, you need read design and read data permissions to all objects. Then you can import everything into a fresh file to strip the security in the new file (the user that imports the objects is the owner in the new file). If you did not secure a database but managed to set a password that you can't remember, the default workgroup file is system.mdw and is buried somehere in the windows system folder (Usually Windows Or WinNT). I'd rename this file and reinstall office. You may need it if you did secure your databases. If you hosed yourself and can't get into a database, I think officerecovery.com may be able to help. That's not an endorsement, I've only seen their ads. I can't say anything good or bad about them.
If you have the PID's used to create the file and the user/groups but lost the password, you can use the workgroup administrator to create a new workgroup file using the same PID's to gain access.
I hope this has been helpful. Also bddewell408, in the future please start your own thread instead of replying to someone else's.
I'd set use the password input mask for the password field in the table so that it displays only asterisks. Then I'd have a listbox or combobox to select the user that is logging in. Next the user would need to enter the password which you would use to put the value in a hidden form that your RWOP query uses as criteria. For this to work users should be locked out of the database window (startup properties) and you need to disable the shift key for startup (I think there is a FAQ about that).
bddewell408,
Can't say I know what the security wizard does because I've not used it. However, I do understand Access security. Access uses workgroup files (.mdw) to authenticate and unlock secured databases. You must have the appropriate key (pid) in a workgroup file in order to access a database file. Therefore, once secure you cannot just strip security by deleting a file. In order to strip security off a database, you need read design and read data permissions to all objects. Then you can import everything into a fresh file to strip the security in the new file (the user that imports the objects is the owner in the new file). If you did not secure a database but managed to set a password that you can't remember, the default workgroup file is system.mdw and is buried somehere in the windows system folder (Usually Windows Or WinNT). I'd rename this file and reinstall office. You may need it if you did secure your databases. If you hosed yourself and can't get into a database, I think officerecovery.com may be able to help. That's not an endorsement, I've only seen their ads. I can't say anything good or bad about them.
If you have the PID's used to create the file and the user/groups but lost the password, you can use the workgroup administrator to create a new workgroup file using the same PID's to gain access.
I hope this has been helpful. Also bddewell408, in the future please start your own thread instead of replying to someone else's.
-
1
- #6
If you're going to secure your database, use the security built in to Access. If you're going to use the security built in to Access, read the Security FAQ. Several times. Then back up your database. Then Secure it. Manually.
You can get teh FAQ from MS or from my website.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Professional Development for Clients Large and Small
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
You can get teh FAQ from MS or from my website.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Professional Development for Clients Large and Small
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Thread starter
- #7
Lameid, Thanks again, this is the way I have been trying to go. I have been working on this project for 2 years now. In my last reply I said most people had read only access, which stopped them altering information (in most cases when data was altered it was people not understanding the database). Things are changing where more department will be able alter data and see other departments information. This has not forced the security issue. I had a look at FAQ, have not found the one on shift key yet but will keep looking it a good suggestion. I take on you point about password in tables. I know you and Jeremy have answered hundred of questions and I think your help is appreciated by all.
You use the AllowBypassKey property of the database object to prevent the shift key from bypassing startup. I'm still using Access 97 for most stuff (a lot of DAO code to convert) so I'm not sure if the below code will work seemlessly in later versions. NoByPass sets the property to false using the second function. I think I liberated the second function from somewhere but can't remember where... Must have been an MSKB article. It also uses DAO. I don't know how many more versions will let us keep DAO but until then, this DAO code isn't exactly the performance dog that keeps me from jumping to 2002 
Function NoByPass()
Dim varResult As Variant
varResult = AllowBypassKeyFalse
If varResult = True Then
MsgBox "Bypass startup key will now be disregarded."
Else
MsgBox "Unable to disable bypass startup key."
End If
End Function
Function AllowBypassKeyFalse() As Integer
Dim dbs As Database, prp As Property
Dim strPropName As String
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
strPropName = "AllowBypassKey"
On Error GoTo AllowBypassKeyFalse_Err
dbs.Properties(strPropName) = False
AllowBypassKeyFalse = True
AllowBypassKeyFalse_Exit:
Exit Function
AllowBypassKeyFalse_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, dbBoolean, False)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
AllowBypassKeyFalse = False
Resume AllowBypassKeyFalse_Exit
End If
End Function
Function NoByPass()
Dim varResult As Variant
varResult = AllowBypassKeyFalse
If varResult = True Then
MsgBox "Bypass startup key will now be disregarded."
Else
MsgBox "Unable to disable bypass startup key."
End If
End Function
Function AllowBypassKeyFalse() As Integer
Dim dbs As Database, prp As Property
Dim strPropName As String
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
strPropName = "AllowBypassKey"
On Error GoTo AllowBypassKeyFalse_Err
dbs.Properties(strPropName) = False
AllowBypassKeyFalse = True
AllowBypassKeyFalse_Exit:
Exit Function
AllowBypassKeyFalse_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, dbBoolean, False)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
AllowBypassKeyFalse = False
Resume AllowBypassKeyFalse_Exit
End If
End Function
-
1
- #9
Something that may be helpful as well is capturing the network Logon and using that (WinNT and higher)
I keep a table of all network logons that should be using the database. So you could have a table of Logons, names and Department. Then in your code form code have something like
[tt]
If permissions="pre-sales" then sfrm_sales.visible=false
[/tt]
And then the function is...
[tt]
Function Permissions()
Dim Dept As String
Dim UserName As String
UserName = Chr(34) & Environ("username"
& Chr(34)
Dept = DLookup("[employee status]", "[tlkp_Staff]", "[tlkp_Staff]![logon] = " & UserName)
Permissions=Dept
[/tt]
You may not be able to use this exactly, but its another avenue to explore. I went this avenue because I don't always have physical access to user's computers to play with their security window. Perhaps its of ignorance, but his works for me.
-Alan
I keep a table of all network logons that should be using the database. So you could have a table of Logons, names and Department. Then in your code form code have something like
[tt]
If permissions="pre-sales" then sfrm_sales.visible=false
[/tt]
And then the function is...
[tt]
Function Permissions()
Dim Dept As String
Dim UserName As String
UserName = Chr(34) & Environ("username"
& Chr(34)Dept = DLookup("[employee status]", "[tlkp_Staff]", "[tlkp_Staff]![logon] = " & UserName)
Permissions=Dept
[/tt]
You may not be able to use this exactly, but its another avenue to explore. I went this avenue because I don't always have physical access to user's computers to play with their security window. Perhaps its of ignorance, but his works for me.
-Alan
- Thread starter
- #10
Thanks AlanKW, It a bit hard for me as we are Running XP on Netware network. The users do not have admin rights in XP. It would have been GREAT if I could have used the netware login.
Lameid, Thank you for the code, yes I stuck with access 2002. I think office 97 was a better product. IT had examples of hyper menu, this was tahen out in 2000 & 2002.
I entered the code into a module but do not know how to have the module running when the shift key is pressed & data base selected to run. any help would be appreciated.
Lameid, Thank you for the code, yes I stuck with access 2002. I think office 97 was a better product. IT had examples of hyper menu, this was tahen out in 2000 & 2002.
I entered the code into a module but do not know how to have the module running when the shift key is pressed & data base selected to run. any help would be appreciated.
Be careful with this. Once you run the NoByPass function it will prevent you from bypassing the startup group using the shift key. I run NoByPass, use an autoexec macro to load startup procedures and use the startup options to remove permission to the database window and possibly the default toolbars. I usually do this in a copy of the frontend that is distributed to the clients. It is possible to get back into a database designed that you have locked down in this way. You just have to open the database programatically from another database as a user that has administer permissions to the database object of the locked down database and set the AllowByPassKey property to true. This is obnoxious but doable.
Its been a loonnnggg time since I've touched Netware. Not sure what local admin rights have to do with this. I'm using this on an NT server where users don't have admin rights.
For S&Gs, set up a test form that just has a textbox with [tt]Environ("username"[/tt] as the control source.
For S&Gs, set up a test form that just has a textbox with [tt]Environ("username"
[/tt] as the control source.- Thread starter
- #13
You can run the NoByPass function directly in the module by selecting it and hitting f5 or from the debug window (ctrl+G) by typing in "nobypass()" (do not include quotes) and pressing enter. This only has to be done once, then the property is set and the shift key can't bypass the database startup procedure. The only other thing I have to add is that nobypass recquires the other function. Are you getting an error? I just don't understand where you are having problems.
- Thread starter
- #15
Lameid, Thanks . I was having problems with the Set prp = dbs.createProperty(strPropName, dbBoolean, False),it was producing a error of type mismatch. I was look around Tek-Tips and found a forum started in 2002. This lead me to a web site . This site had a file called lock&unlock, that had a database that can lock and unlock the shift key (it is in 97 format but can convert to 2002). This is good a I can now spend some time on the login problem I started with in this forum. I think I will have to add a field to main assets table that will tell which user can view that record? Have combobox as per your first idea, for users.
hi,
i am a (relative) newbie to all this and still haven't added user level security to my (under development) access 2000 database but that is definitely something that's going to be done down the pike and my users are clamoring for record level security. basically there've been comotions among users claiming 'the other guy' did this or did that to a record in a table in a database. i suppose the 'easy' way is to create two copies of the same database and prevent the other user from accessing the other's database then merge the corresponding tables in a third database. but if there were something slicker to allow more than one user to access the same table but prevent his/her counterparts from ever modifying a record in the table that'd be kind of cool.
any thoughts?
i am a (relative) newbie to all this and still haven't added user level security to my (under development) access 2000 database but that is definitely something that's going to be done down the pike and my users are clamoring for record level security. basically there've been comotions among users claiming 'the other guy' did this or did that to a record in a table in a database. i suppose the 'easy' way is to create two copies of the same database and prevent the other user from accessing the other's database then merge the corresponding tables in a third database. but if there were something slicker to allow more than one user to access the same table but prevent his/her counterparts from ever modifying a record in the table that'd be kind of cool.
any thoughts?
Start your own thread. I just gave something of an answer in another thread you sort of hijacked. It's best to start a new thread. If you want, you can reference a different thread by including something like thread702-640708 (from just under the subject of the thread). Check out the thread in my tag for some other suggestions about how to get good answers.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Thread starter
- #18
uscitizen having two databases does not work for me.
Jeremy,
Can you help? I have added fields to the employee table, login name and password. This as part of the security for loging in of the users that I was trying to achieve.
I have done a lookup of password and login name and this works fine. The problem is I need the divisionID as well.
Then I need to filter on the divisionId of the user, to only see records for that division. I have tried to Dim /Public loginfilter as an integer.
then logininfilter="[divisinID]" (have also used the table as well)
filter=loginfilter
But it only freezes.
thanks
Jeremy,
Can you help? I have added fields to the employee table, login name and password. This as part of the security for loging in of the users that I was trying to achieve.
I have done a lookup of password and login name and this works fine. The problem is I need the divisionID as well.
Then I need to filter on the divisionId of the user, to only see records for that division. I have tried to Dim /Public loginfilter as an integer.
then logininfilter="[divisinID]" (have also used the table as well)
filter=loginfilter
But it only freezes.
thanksAssets,
Use a function in all your queries to return the divisionId('s) for the user. If the user keys in the username, set it to a global variable and have a function that returns that global variable. Otherwise you can use the same function you use to determine the username.
So you would have something like this for your where clause...
Where DivisionID IN (Select DivisionID from DivisionUserXref where Username = FunctionReturningUsername)
You just have to be certain to lock users out of changing queries, changing data in tables and use RWOP queries to be total secure.
Use a function in all your queries to return the divisionId('s) for the user. If the user keys in the username, set it to a global variable and have a function that returns that global variable. Otherwise you can use the same function you use to determine the username.
So you would have something like this for your where clause...
Where DivisionID IN (Select DivisionID from DivisionUserXref where Username = FunctionReturningUsername)
You just have to be certain to lock users out of changing queries, changing data in tables and use RWOP queries to be total secure.
What he said.
Except that you said you're storing the passwords in a table. That's wrongwrongwrongwrong. Are you using Access Security? Should be. And if you are, there's no need to store the passwords anywhere but where Access puts them, in the mdw.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
Except that you said you're storing the passwords in a table. That's wrongwrongwrongwrong. Are you using Access Security? Should be. And if you are, there's no need to store the passwords anywhere but where Access puts them, in the mdw.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Status
Similar threads
- Locked
- Question
- Locked
- Question