Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Apr 2014
    Posts
    89

    Unanswered: Password for part of a database

    Hello eveyrone great experience on this forum so far..

    Here is what I have going on.
    I have a alarm database that has a end user switchboard and an Admin Switchboard.. The admin section has alot of functianlty that the end user does not need. I have the Admin Section password protected but it does not put **** when typing the password and i would like it to..

    For the Password, Access is run thru a network in a state agency and so you can not use any thing that would tied to that such as when somone logs in it recognizes there authority.. That was the answer on another forum..Cant do that.

    Also I wanted to know if I have the navigation section turned off for the end user is there any way that when I log in as the admin to get that nativation section to open again?
    its a pain to have to open it using the shift key.

    Thanks I am also just learning VBA so please be patient

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The easiest and more secure way of identifying a user consists in interrogating the operation system (i.e. Windows) as every user must have a login accout defined in the system.

    There are 2 usual way of doing that:
    1. You can use:
    Code:
    Dim strUserName As String
    strUserName = Environ$("USERNAME")
    2. A more secure way (temper proof) uses a Windows API call (i.e. a call to an internal function of the operating system):
    Code:
    ' In the declaration section of a module:
    '
    Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    '
    Public Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUserName As String
        Dim lngUserNameSize As Long
        
        strUserName = String$(255, 0)
        lngUserNameSize = Len(strUserName)
        GetUserName strUserName, lngUserNameSize
        strUserName = Left$(strUserName, lngUserNameSize - 1)
        GetNTUser = strUserName
        
    End Function
    You can then use:
    Code:
    Dim strUserName As String
    strUserName = GetNTUser
    You can then create a table that will list the users allowed to open the application. A set of attributes (or privileges, or authorisations) is associated with each user. This set of attributes defines what the user is allowed to do within the application. How you implement the attributes can vary: it can be a numeric (Byte, Integer or Long) column with each bit of the number corresponding to a privilege, it can be a set of binary columns, each column defining an authorization, your imagination is the limit here...
    Here's the SQL DDL definition of such a table (i.e. the query that can be used to create it):
    Code:
    CREATE TABLE [Tbl_Users]
        ( [User_ID] INTEGER NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [User_Name] TEXT(128) NULL,
          [User_Privileges] TINYINT NULL,
          [User_Inactive] BIT NOT NULL
        );
    Note: If you're not familiar with the DDL SQL syntax, a numeric column of type Byte is used to store the attributes in the table: [User_Privileges] TINYINT NULL

    From there, you can have a function that is executed when the database is open. This function:
    - Retrieve the current user name.
    - Try to find a match in the users table Tbl_Users
    - If a match is found, the attributes for that user as retrieved.
    - Depending of the attributes one switchboard form (End User switchboard) is open for an end user, while another switchboard (Admin Switchboard) is open for an administrator.

    Let's create a new standard module: Mod_Security
    Code:
    Option Compare Database
    Option Explicit
    
    Public Const c_Priv_Admin As Byte = 128
    
    Public Function Main()
    
        Dim strUserName As String
        Dim varUserPrivileges As Variant
        
        strUserName = Environ$("USERNAME")
        varUserPrivileges = DLookup("User_Privileges", "Tbl_Users", "User_Name = '" & strUserName & "'")
        If IsNull(varUserPrivileges) Then
            MsgBox "You are not allowed to use this program.", vbExclamation, "Access denied"
            Application.Quit
        ElseIf (varUserPrivileges And c_Priv_Admin) = c_Priv_Admin Then
            DoCmd.OpenForm "Admin Switchboard"
        Else
            DoCmd.OpenForm "End User Switchboard"
        End If
        
    End Function
    We can call the function Main() from the AutoExec Macro:
    Action: RunCode
    Function Name: Main()

    If you prefer to use a startup form (NOT one of the switchboard forms!), a splashform for instance, the function Main() can be called from the Form_Open event handler of this form:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Open(Cancel As Integer)
    
        Main
        DoEvents
    
    End Sub
    If you need a more precise control on what a user can do or not, you can build a function in Mod_Security, such as:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Const c_Priv_1 As Byte = 1
    Public Const c_Priv_2 As Byte = 2
    Public Const c_Priv_3 As Byte = 4
    Public Const c_Priv_4 As Byte = 8
    '
    ' etc...
    '
    Public Const c_Priv_Admin As Byte = 128
    
    Public Function GetUserPrivilege(ByVal Privilege As Byte) As Boolean
    
        Dim strUserName As String
        Dim varUserPrivileges As Variant
        
        strUserName = Environ$("USERNAME")
        varUserPrivileges = DLookup("User_Privileges", "Tbl_Users", "User_Name = '" & strUserName & "'")
        If (varUserPrivileges And Privilege) > 0 Then GetUserPrivilege = True
        
    End Function
    Now you have three ways to control what a user can do:

    1. On a form, you can control whether a user can open it or not
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        If GetUserPrivilege(c_Priv_Admin) = False Then Cancel = True
    
    End Function
    or concisely:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Cancel = Not GetUserPrivilege(c_Priv_Admin)
    
    End Function
    Note: In such a case don't forget to implement an error handler in the procedure that opens the form because setting Cancel to True in a Form_Open event handler will raise error 2501.

    2. On a form, you can enable/disable or hide/unhide a command button (or any other control)
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        '
        ' Only user with admin rights can print the report.
        '
        Me.Command_PrintReport.Visible = GetUserPrivilege(c_Priv_1)
    
    End Sub
    or:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        '
        ' Only user with admin rights can print the report.
        '
        Me.Command_PrintReport.Enabled = GetUserPrivilege(c_Priv_1)
    
    End Sub
    3. You can check if the current user has sufficient privileges to execute a procedure
    Code:
        If GetUserPrivilege(c_Priv_2) = True Then ComputeTotal
    Note: The column User_Inactive in the table Tbl_Users can be used to temporarily activate/deactivate a user: user is active if User_Inactive = 0 (False) and user is active if User_Inactive = 1 (True).

    To cope with that, you can either change the lookup function to:
    Code:
        varUserPrivileges = DLookup("User_Privileges", "Tbl_Users", "User_Name = '" & strUserName & "' AND User_Inactive = 0")
    or you can use a query (qry_Users):
    Code:
    SELECT Tbl_Users.User_ID, 
           Tbl_Users.User_Name, 
           Tbl_Users.User_Privileges
      FROM Tbl_Users
     WHERE Tbl_Users.User_Inactive=0;
    then base the lookup function on the query:
    Code:
        varUserPrivileges = DLookup("User_Privileges", "qry_Users", "User_Name = '" & strUserName & "'")
    If a user can have 2 sets of privilege, a "normal" set that is allocated to him/her when the application starts and an "admin" set he can reclaim by for instance pressing a command button then entering a password, the system becomes more complex but is still easily manageable:

    1. The table Tbl_Users gets 2 extra columns:
    Code:
    CREATE TABLE [Tbl_Users]
        ( [User_ID] INTEGER NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [User_Name] TEXT(128) NULL,
          [User_Privileges] TINYINT NULL,
          [User_PrivilegesExtra] TINYINT NULL,
          [User_Password_Extra] TEXT(50) NOT NULL,
          [User_Inactive] BIT NOT NULL
        );
    2. In Mod_Security, you define an attribute that will control whether a user can reclaim extra privileges or not:
    Code:
    Public Const c_Priv_Extra As Byte = 64
    This privilege will be used to hide/unhide or show/unshow the command button that control the access to extra privileges, as described above.

    3. We declare a Private member variable in the declaration section of Mod_Security: m_bytUserPrivileges:
    Code:
    Private m_bytUserPrivileges As Byte
    The variable is declared private so that it can only be accessed by a function or a sub in Mod_Security: it is not Global (can be accessed from anywhere), nor Public (can be accessed from any module in the application.

    When the application starts, the Main() functions now sets the value of m_bytUserPrivileges:
    Code:
    Public Function GetUserPrivilege(ByVal Privilege As Byte) As Boolean
    
        Dim strUserName As String
        Dim varUserPrivileges As Variant
        
        strUserName = Environ$("USERNAME")
        varUserPrivileges = DLookup("User_Privileges", "qry_Users", "User_Name = '" & strUserName & "'")
        If Not IsNull(varUserPrivileges) Then m_bytUserPrivileges = varUserPrivileges
        If (varUserPrivileges And Privilege) > 0 Then GetUserPrivilege = True
        
    End Function
    If the function GetUserPrivilege is used, it now becomes:
    Code:
    Public Function GetUserPrivilege(ByVal Privilege As Byte) As Boolean
    
        If (m_bytUserPrivileges And Privilege) > 0 Then GetUserPrivilege = True
        
    End Function
    and the Main() function must be used to initialize m_bytUserPrivileges when the program starts.
    We now add a function in Mod_Security that can change the value of m_bytUserPrivileges:
    Code:
    Public Sub GetExtraPrivilege()
    
        Dim strUserName As String
        Dim varUserPrivileges As Variant
        Dim strPwd As String
        
        strUserName = Environ$("USERNAME")
        varUserPrivileges = DLookup("User_PrivilegesExtra", "Tbl_Users", "User_Name = '" & strUserName & "'")
        If Not IsNull(varUserPrivileges) Then
            strPwd = DLookup("User_Password", "Tbl_Users", "User_Name = '" & strUserName & "'")
            If strPwd = InputBox("Enter your password for extra privileges:", "GetExtraPrivilege") Then
                m_bytUserPrivileges = varUserPrivileges
            End
                MsgBox "Incorrect password", vbInformation, "Access denied"
            End If
        End If
    
    End Sub
    This procedure is called when a user press the command button to reclaim extra privileges. If it succeeds, the value of m_bytUserPrivileges is now the one stored in the column User_Password_Extra of the table Tbl_Users.

    Note: A secure system would of course cipher the users passwords.
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Posts
    89

    Password for part of a database

    WOW ok thanks for all the info looks a bit overwhelming so i will have to take some time to play around with it.

    1 question do any of these alow the database to open with the navigation pane open while it is closed for others?

    I also have an easier post out there about If statement if you would not mind chekcing that out.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    aaargh not the effin environ.... I have seen situations where the environ is compromised. if it can be compromised then it has no place in any applciation in my books

    use the network API call, each and every time. google dev ashish API call

    depending in what version of Access you are using (more recent versions A2007 on) don't use the workgroup file/don't implement workgroup security. but if you do use a workgroup file, what yuou cna do is allocate the network logon to user groups, then request the users workgroup via the ADOX security extensions

    Functionally divide the application ito security groups then request if that user has a specific group membership as part of eachj and every form / report on load event. Then assuming that you deploy your applciation as a MDE then users cannot get access to anything unless you permit it, or unless you allow the workgroup file to be compromised.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2014
    Posts
    89

    Password for part of a database

    ok thanks gonna have to try and figure it all out.. Seems very complex

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Hiding/unhiding the navigation pane is easy. You can do it in the same Main function I talked about in my last post. The priciple consists in selecting an object you know exists (this is for setting the navigation pane as the active window), then issue a command to hide the active window:
    Code:
    '
    ' Hide the database window (Acc 2003) or the navigation pane (Acc 2007-2010).
    '
        DoCmd.SelectObject acForm, "Frm_About", True ' "Frm_About" is the name of an object that we know exists in the database.
                                                     ' Note: The name of any object can be used provided it exists and is not hidden.
        DoCmd.RunCommand acCmdWindowHide
    For unhiding the navigation pane, it's easier: we simply select an object we know exists into it:
    Code:
    '
    ' Unhide the database window (Acc 2003) or the navigation pane (Acc 2007-2010).
    '  
        DoCmd.SelectObject acForm, "Frm_About", True ' "Frm_About" is the name of an object that we know exists in the database.
                                                     ' Note: The name of any object can be used provided it exists and is not hidden.
    If, when the navigation pane is hidden, you want to prevent the use of the F11 key (hitting F11 shows the nav. pane), you need to add a property (AllowSpecialKeys) to the Currentdb object or change the value of this property if it already exists:
    Code:
    Sub DisableSpecialKeys()
    
        Dim dbs As dao.Database
        Dim pty As dao.Property
        
        Set dbs = CurrentDb
        For Each pty In dbs.Properties
            If pty.Name = "AllowSpecialKeys" Then
                pty.Value = False
                Exit For
            End If
        Next pty
        If pty Is Nothing Then ' Property AllowSpecialKeys was not found --> Create it.
            Set pty = dbs.CreateProperty("AllowSpecialKeys", dbBoolean, False)
            dbs.Properties.Append pty
        End If
        Set pty = Nothing
        Set dbs = Nothing
        
    End Sub
    To allow the use of the F11 key, you can use the same code, simply replacing False with True for the property value.
    You can also write a function with a parameter that will set/reset the value of the property:
    Code:
    Sub AllowSpecialKeys(ByVal State As Boolean)
    
        Dim dbs As dao.Database
        Dim pty As dao.Property
        
        Set dbs = CurrentDb
        For Each pty In dbs.Properties
            If pty.Name = "AllowSpecialKeys" Then
                pty.Value = State
                Exit For
            End If
        Next pty
        If pty Is Nothing Then ' Property AllowSpecialKeys was not found --> Create it.
            Set pty = dbs.CreateProperty("AllowSpecialKeys", dbBoolean, State)
            dbs.Properties.Append pty
        End If
        Set pty = Nothing
        Set dbs = Nothing
        
    End Sub
    On the same path, you can hide/unhide the ribbon, using:
    Code:
    ' Hide ribbon in Access 2007-2010
    '
        DoCmd.ShowToolbar "Ribbon", acToolbarNo
    And:
    Code:
    ' Unhide ribbon in Access 2007-2010
    '
        DoCmd.ShowToolbar "Ribbon", acToolbarYes
    Have a nice day!

  7. #7
    Join Date
    Apr 2014
    Posts
    89

    Password for part of a database

    u would need to create buttons then to activate these things?
    or would it go in the main code of the database and then a button on the admin form to open the navigation pane?

    I think basically you gave me an even easier answer of using F11 I have never heard that i always did the shift.. That F11 was really easy..

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You've been given excellent advice on the matter, but as to the question
    Quote Originally Posted by dlafko View Post

    ...I have the Admin Section password protected but it does not put **** when typing the password and i would like it to...
    you'd have to select the Control in question, then go to Properties - Data and set the Input Mask to Password. Sadly this is not available when using the InputBox function, as suggested by Sinndho's code. I've seen a number of threads, over the years, concerning this, but the only workaround to date is to replace the InputBox with a small custom popup Form, using the 'Password' InputMask

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Apr 2014
    Posts
    89

    Password for part of a database

    Ok my Basic question for Sinndho were in the VB Sheet does all this go or do you need to create Unhide button to unhide the navigation pane?

    With this language can you prevent someone from going into the design views of the forms they are opening and such to lock it all down? with the expection of the Admin or is that getting into even more complex code?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Hiding/unhiding the nav.pane and the ribbon, allow/forbid the use of the F11 key, dispkaying a switchboard form or another, all these can be done in the Main function I posted, based on the privileges of the current user. It can also be done when a user reclaims extended privileges (if you use a variation of what I proposed on that subject). I provided some building blocks that can help you to customize the application according to rights granted to the users however its up to you to decide which of the blocks you want to use and how (you need to provide the cement to tie the blocks together, if I may say so).

    For instance, in the applications I build (I'm a professional developer), I have a set of standard modules that are present in every application. One is Mod_Credentials that contains all the functions needed to manage and control the users privileges, one is Mod_Startup wich contains the Main() function called by an AutoExec macro to run when the application starts. In the Main() function the sequence of events is the following:

    - Exit the Main() function if the command line that launched Access contains "NOSTART:xxxx" where xxxx is a password.
    - Prevent from launching a second instance of the application.
    - Hide the ribbon in Access 2007-2010.
    - Retrieve the name of the application.
    - Log application start with a time stamp in a log file.
    - Hide the database window (Acc. 2003) or the nav. pane (Acc. 2007-2010).
    - Show the SplashForm if any.
    - Change the name of the main Access window.
    - Instanciate the SQL Connector (a class used to connect to SQL Server).
    - Check the application version against the server and auto update if necessary.
    - Change menus to application specific ones (if defined).
    - Call an application specific Startup() function.

    Many of these functionalities use either Window API calls encapsulated in function (such as GetNTUser() in Mod_Credentials) all in several dedicated modules and/or custom (user defined) classes that are also part of the "standard" set of modules present in any of my projects.

    As I wrote in my first post, your imagination is the limit.
    Have a nice day!

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dlafko View Post
    Ok my Basic question for Sinndho were in the VB Sheet does all this go or do you need to create Unhide button to unhide the navigation pane?

    With this language can you prevent someone from going into the design views of the forms they are opening and such to lock it all down? with the expection of the Admin or is that getting into even more complex code?
    Only to certain extend. A skilled user with a good knowledge of Access won't be prevented from tempering with this kind of security features.

    If you need a higher level of security, you can convert your project to a compiled (mde or accde) version, where users cannot have access to any development features (design view, Modules, etc.). If you chose this path, keep a copy of the original project (mdb or accdb) in a safe place: you'll need it to make any changes later on, because there is no easy way to revert from a compiled to an uncompiled Access project: it's a one way, one shot feature.
    Have a nice day!

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Sinndho View Post

    ...Only to certain extend. A skilled user with a good knowledge of Access won't be prevented from tempering with this kind of security features...
    Pretty much all security, from protecting a bicycle to guarding Fort Knox, has to be based on what you're trying to prevent, i.e. data theft, accidental damage or intentional sabotage, and who you're trying to protect it from, your average end-user, an experienced Access user or a malicious programmer.

    If you decide to go with the mde or accde approach, be very, very sure to heed Sinndho's advice! Once the file is 'compiled' the only way you can use the mde or accde file to make changes is to rewrite the whole database, looking at how it currently works. Their are companies that offer this service, but they charge megabucks to do it! Of course, with Access' proclivity for becoming corrupt, you should back up your file before making any real change(s) to it.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Apr 2014
    Posts
    89

    Password for part of a database

    Thanks I appreciate your knowledge here just alot to learn.. couple questions.

    1. Were or how do you suggest someone learn VBA basics to understand how to build this stuff out in Access

    2. With in the database you have I wanted to make some type of update query but not sure how to make it work.. Here is the senerio
    The end user enters the basic info, In the admin section, admin form it then asks for other information for the business. This info is added when a company has 3 alarms. SO I wanted to know can a something be created that you type in the business name and the address and it automattically updates all those records based on the business name?

    3. have a query called Warning Letter Needed qry
    It looks for business names in the Fault table and is looking for 3 of them, it also looks to see if they have not gotten a warning letter and if the alarm was fault.

    So ok all good.

    Example
    Perry golf course has 2 fault alarms with warning letter, they get a 3rd one obviously no letter sent for that one so I run the query and I get the single record.. But I need to pull all 3 records so when the letter goes out it shows them all three alarms we are referencing.

    So I am thinking I need some type of if statement

    If Business name/ residence name is greater than 2 and if there is no warning letter and if the alarm is fault then return all business name/residence name

    So this would show all perry golf course alarms of which there are three

    and i would want this to work for the citation report as well.. But if you explain this if u know who to work it i can change the code as needed.. I uploaded the most current database as well with some of the changes.


    Again thanks for your help.. Never new this could be so complex.
    Attached Files Attached Files

  14. #14
    Join Date
    Apr 2014
    Posts
    89
    BASCCALLY I WANT TO MAKE SURE AN END USER DOES NOT GET INTO A DESIGN SIDE OF THE PROGRAM AND ACCIDENTALLY MESS IT UP.. mIGHT BE GOOD ENOUGH TO HIDE THE RIBBON/NAVIGATION BUT THEY CAN STILL RIGHT CLICK TO CHANGE A FORM... MIGHT BE OVER THINKING IT TOO CONSIDERING THE PEOPLE WORKING WITH IT ARE LESS TECH SAVY THEN I AM AND I AM A NOVICE

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dlafko View Post
    1. Were or how do you suggest someone learn VBA basics to understand how to build this stuff out in Access
    In the good old times (1990's) Microsoft "offered" some good books in the documentation package of its products. I wrote "offered" in quotes because it was comprised in the price when you bought an MS product such as Access. And yes: there was printed documentation in the package at that time.

    Access online help is valuable but not very handy to be used as a learning book. There are several good books sold by various editors: Sybex, Microsoft Press, Addison Wesley, Mc GrawHill, to cite a few of the most reknown in that domain.

    Its been a long time since I last learned something about Access from a book (when a need a precise piece of information I rely on online help and Google) so i'm not in the best place to provide advice on which book to buy and to learn. I'll just make an exception for a book that's not directly related to Access but is, for me, one of the most valuable. It's an old book (1997) named "Hitchhiker's Guide to Visual Basic & SQL Server", written by William R. Vaughn and published by Microsoft Press (ISBN 1-57231-567-9).

    I found it so valuable because it provides an in-depth explanation on how DAO works (as well as Databases in general), in a clear, no-nonsense manner. Why does DAO remains so important for Access? Simply because that's the internal database library Access is built on. Tables and queries are DAO objects in Access. Although MS introduced ADO/ADOX later on, DAO often remains the fastest easiest way to communicate with the data objects inside Access.

    Quote Originally Posted by dlafko View Post
    2. With in the database you have I wanted to make some type of update query but not sure how to make it work.. Here is the senerio
    The end user enters the basic info, In the admin section, admin form it then asks for other information for the business. This info is added when a company has 3 alarms. SO I wanted to know can a something be created that you type in the business name and the address and it automattically updates all those records based on the business name?

    3. have a query called Warning Letter Needed qry
    It looks for business names in the Fault table and is looking for 3 of them, it also looks to see if they have not gotten a warning letter and if the alarm was fault.
    I'll have a look at the database you posted, but it will be later: it's late here where I live and some duties are requiring my attention. Perhaps someone else will pick up your demand and provide answers sooner than I can.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •