Hide passwords in table 2

[BSman]

I've gone back for the time being to using Access (not project) as a front end to SQL 7 database because of some display limitations in Access Project.

However, a serious problem I encounter with both systems is that I can't hide the contents of a password column in a user table. In Access I was able to set a column type to password so that the actual contents were always masked, even if someone opened the application database "on the sly" (holding the shift key down). However, SQL 7 does not seem to provide any data type that will automatically mask the contents of a column.

Any suggestions how to mask the contents of a column, so even if a user gets to the database window and opened the table he will not be able to read any passwords?

(It would also be nice to mask the column contents directly in SQL 7, but I'm not too concerned about that, since few users will have SQL Query Analyzer or SQL Enterprise Manager on their desktops.

 

[MarkWright]

Good morning BSman, you could change the "on the sly" combination. the standard is shift but that can be changed to any key combo you want. Am not sure how to change that but I do know it can be done.

Have a nice day
Mark

 

[BSman]

Mark,

What is the "on the sly" combination? I'm looking for a solution where the passwords are masked (with **** for example, like they are in Access). Ideally, this would even be true if you were in SQL Server and opened the table, but at minimum it should display that way when the table is opened in Access, even from the database window.

I definitely do not want to change the actual application, since that won't solve the fact that the password displays when the table is opened from the database window.

 

[BobJacksonNCI]

BSman (nice name, but may be fairly common ;-),

Mark's suggestion has to do with opening the database. The default to gain full access (pun not intended) is to hold down the shift key while hitting enter. He meant that the capability can be taken away from shift key and assigned to some other key or key combination. That would keep users from getting directly to the database window, assuming there is a menu system in place.

It will not do anything with the table. Since I'm an SQL novice, and also struggling to make Acces Project do what I need, I can't say for sure that there's a password mask for SQL field definitons. I'm guessing not at this point.

SQL provides a user-defined data type. For me that's definitely in the deep end and I'm still in the wading pool.

Good Luck!
Bob (M.E.A.N.)
Moderately Exasperated Access Novice ;-)

 

[MarkWright]

Hello BSman,
I have done some research and found out that in a SQL 7 Server you have the possibility to say who is allowed to see a Table or not by restrictions and password. all you have to do is go in under the admin mode, klick on a table and then you are give the type of things that people or groups are allowed to do. I added a table and changed the priority so that no one was alloed to see the table except myself and my password. It worked fine for me. I'ed tell you the side in the book if I could but I have a German book because I am working here in Germany. I can tell you that the book I have is --> Microsoft SQL Server 7.0 compendium. (its a red book) I looked under passwords for Tables and found the section. Its really easy if you know your way around the SQL Server. Its kinda like given a group user rights in a network. You just klick on who can and who can't.
Hope this helps you out
Mark

 

[BSman]

Hi Mark,

The problem I have is that the user needs to read the password because I use this to control logging in to the application (using Access or Access Project). So, although it is possible to limit access to a table through the log in to SQL Server (which I prefer to avoid because I want to use a login to the application), I'm still fighting the problem that the front end (application) needs to be able to read the table with the user list and their passwords.

With an Access table you can set the data type to "Password" and the text in the field (column) is masked with asterisks, so even though you can read the password in code and compare it to the way the user logged in (using a log in screen that is part of the application), no one can read the password by opening the table directly in Access (by starting with the Shift key, for example).

But thaks for the suggestion. Maybe someone will figure out a solution.
Bob

 

[cehepker]

Hello,

If you were still using and ADP, you could use SQL security and prevent users from seeing the table, then you could display the information needed in a view.

You may be able to do the same thing with the mdb.. I haven't worked with an mdb in a long time. But I do know that you can link to SQL views just as you can link to SQL tables, so you should be able to do the same there. In fact, you would not have to link to the table at all, just link to the view.

I don't know if that will work for you...
Carla

Documentation: A Shaft of light into a Coded world

 

[RiverGuy]

You can give them no permissions for that table, but the app has permissions using a different logon, if you do not make the VBA available.

If you use a SQL binary data type, it comes out looking like Asian characters if you link to Access.

 

[birgitd]

The way I handled this problem (which may be a bit of hack) is to use Windows Authentication for my SQL database, then I have a staff table that assigns each person to a user group and maps each user to a windows login (but no passwords are stored anywhere in the table).

Next, I put a listbox on each form with the row source:

select groupname from stafftable where windows_login = SYSTEM_USER

Because I'm using SQL Server to tell me who the user is based on their windows login to the database, I can use that listbox on each form to handle what the user can see and do.

It's not as nice as friendly as the database (not project) permissions handling (which is conveniently built in), but it does the job.

Hope this helped!

Birgit

 

[leiphasw]

I do this by encrypting the user's password before it is placed in the table. When the user enters their password the next time, I encrypt the entered value and compare it to the encrypted version stored in the db. If they are equal, the user entered the right password. I use IE's 128bit encryption so that every password, no matter how many characters looks the same length and looks similar to these actual examples:

9C204E6EEAC562DA8F87113B72FB480A
3B51C79A7F111D2BE9D347AC9BFE3ABD
74186ADF7F381C816FBB8DAB121A6973

So, who cares if you can see them in plain text?

 

[chickey]

OK, quick and dirty solution to encrypt and decrypt password
text strings in access. Its not secure encryption by
any means and would probably not stand up to any
hack attempt, but it will put off the general user
when (s)he opens the table and sees strange characters
in the password field.

Put these into a module and call EncryptAuth when inserting
into the database and DecryptAuth when displaying (- oh, and
replace the "Call ut_Bug" lines with your own error
handler):

Hope this helps

Regards...

--

Function DecryptAuth(ByVal UserAuth As String)
On Error GoTo DecryptAuth_Err

Dim i As Integer

For i = 1 To Len(UserAuth)
If Asc(Mid$(UserAuth, i, 1)) - 77 >= 0 Then
Mid$(UserAuth, i, 1) = Chr$(Asc(Mid$(UserAuth, i, 1)) - 77)
Else
Mid$(UserAuth, i, 1) = Chr$((Asc(Mid$(UserAuth, i, 1)) + 255) - 77)
End If
Next i

'Send back the decrypted password
DecryptAuth = UserAuth

DecryptAuth_Exit:
On Error Resume Next
Exit Function

DecryptAuth_Err:
Call ut_Bug(Err.Description, Err.Number, Erl(), Application.CurrentObjectName, "DecryptAuth&quot
Resume DecryptAuth_Exit
End Function

Function EncryptAuth(ByVal UserAuth As String)
On Error GoTo EncryptAuth_Err

Dim i As Integer

For i = 1 To Len(UserAuth)
If Asc(Mid$(UserAuth, i, 1)) + 77 > 255 Then
Mid$(UserAuth, i, 1) = Chr$((Asc(Mid$(UserAuth, i, 1)) + 77) - 255)
Else
Mid$(UserAuth, i, 1) = Chr$(Asc(Mid$(UserAuth, i, 1)) + 77)
End If
Next i

'Send back the encrypted password to check against
'what is entered in the database
EncryptAuth = UserAuth

EncryptAuth_Exit:
On Error Resume Next
Exit Function

EncryptAuth_Err:
Call ut_Bug(Err.Description, Err.Number, Erl(), Application.CurrentObjectName, "EncryptAuth&quot
Resume EncryptAuth_Exit
End Function

 

[Fubear]

I have this exact same problem, which I was conveniently about to tackle just before reading this thread.

The way I was planning on doing this was to deny access to that field to everyone using the SQL server security features. Then write a stored procedure which accepts a login/password as paramenters, validates them against the table, and returns a 0 (failure) or 1 (success).

as far as I am aware (I havn't tried this yet), the stored procedure will be able to see the field even if the user cannot. I'm sure I read somewhere that you can deny access to the talbes and just let the user access the stored procedures/views that are required. I suppose you would also have to deny the user permissions to create/edit views and SP's, but the average user shouldnt need to do this anyway.

I am not exactly clear on how I could recieve the return result. I know to call the procedure I would need to write something along the lines of:

sql="EXEC SP_CheckPassword(username,password)
docmd.runsql(sql)

But I dont know how to set the SP to return a value, or how to pick it up in the ADP forontend.
If anyone could help with this it ould be great.

 

[Fubear]

As an update, its a LOT easier to just use the encryption functions above than play around with security settings.

I have implemented the encryption into my system and everything seems to be working fine.