Personalized menu and functions

[Winla]

The program I am developing is going to be used by several group users with different level permission. Some of them are not allowed to access some functions(through menu and buttons) and only permitted to retrieve data related to that person from database. I have developed all-in-one program but now I am facing difficulty to customize the interface and functions for each user based on their login username.
My program is in VB6/NT/SQL7 environment and for 30+ users. I am looking for an efficient way to do it.
My question can be further divided into two parts.
1) in a form with menu and buttons. How to disable some of these buttons based on username? Where to store user group information(SQL7 or a text file)?
2)For SQL database, How to setup security to
allow only user-related information being retrieved from a single table ?(An alternative way may be just to set up restriction to the query in vb code?)

I would like to hear your experience and suggestions. Thank you.

[seaweed]

Well, I don't know a lot about this, but one way to do it would be to have a text file that contains username/password/permission information. When your program starts, the user enters their name/password, and your prog. reads the text file to get their permissions. Then you can enable/disable stuff based on that. You could also store queries in the text file.

If you do this, you may want to encrypt the file when saving it to disk (for security). Here is an example of how to encrypt/decrypt values. Create a form with 3 text boxes (Text1, Text2, Text3). Type info into Text1. The encoded data is displayed in Text2. The decoded data is displayed in Text3.

For added security, change the value of "ChangeAmt" periodically (from -120 to 65). Just make sure that you change it to the same value at the same time when decoding as you did when encoding!

Here's the code:

Private Sub Text1_KeyPress(KeyAscii As Integer)

    Dim ChangeAmt As Integer
    
    ChangeAmt = -120      'This can be any number from -120 to 65
    
    If KeyAscii >= 32 And KeyAscii <= 146 Then
        If KeyAscii - ChangeAmt < 32 Then
            Text2 = Text2 & Chr$(147 - (32 - (KeyAscii - ChangeAmt)))
        Else
            Text2 = Text2 & Chr$(KeyAscii - ChangeAmt)
        End If
        
        If Asc(Right$(Text2, 1)) > 146 - ChangeAmt Then
            Text3 = Text3 & Chr$(32 + ((147 - ChangeAmt) - (Asc(Right$(Text2, 1)))))
        Else
            Text3 = Text3 & Chr$(Asc(Right$(Text2, 1)) + ChangeAmt)
        End If
    Else
        Text2 = Text2 & KeyAscii
        Text3 = Text3 & Right$(Text2, 1)
    End If
    
End Sub

Hope that helps a little!

~seaweed

[netSurfer]

I would add a change to seaweeds comment. If you do want to change the modification value I would put it in a text file. Then have a ChangeEncrypt function that will modify it. DO something like:

current entry - day /2 + 4 or something wierd that would be dificult to guess. Make sure that you dump it into a integer so that the remainder will be removed. Then write that value into the file. Then you'd have to go through the password file and convert all the entries.

I'd store current value, then do the change. Then open the password file, read each entry, convert you old entry to read the real password. Then encrypt it with new value.

------------------
----------------------
I'm really easy to get along with once you people learn to
worship me.
----------------------

[Glenn]

I would suggest you use SQL7 for the security portion of your application. That way you can manage user security (add/delete/modify) without having to do work at the individual workstations.

If you users fit into to different categories (read only user, power user, administrator, etc..) then you can set up user groups in SQL7 and assign table permisions based on the groups. In the end your SQL statements in your code will control what each user can see. You can also use these groups in your code to disable and/or hide menu items. One good thing about VB is it easy to limit access to the database, since you control what you allow the user to do.

If you worried about your users getting access to the data through other means (acccess, etc) than use stored procedures and views in SQL7 and don't allow the users to have SELECT privileges on the tables themselves.

[Winla]

Thank you all for replay.

I have adopted glenn's suggestion using SQL7 security to handle my program security. but I haven't figured out how to Retrieve User Group information in order to use it to disable/add menu functions in VB code. The question is how to get user group name that the current user belongs to. Which system table has these information? If my program has access to system tables, is it secure from database point of view? Thanks