Database protection with modules

[Kobe2932]

Hi

I created a pretty simple databse, which is used by several users. The main functions are set up, however as it contains some macros and modules, I would like to protect the structure of the database.

I have an opening screen, and the database is set up to automaticcaly open that opening screen when someone opens it up.
I thought it would be easier if I could admin rights to 1-2 users. The main point is that the person who has admin rights, does not neccessary edits the database structure, mainly he is just using it and filling up with information.

So, I thought, the best solution could be if could add 2 buttons to this opening screen: Admin Mode ON and Admin Mode OFF. These buttons should be visible only to those users, who have admin rights.

When the database opens up, the ribbon, the properties, and every editing buttons should be disabled, but when the person clicks on the button, enables those features.

Simple users could not see these Admin mode buttons, neither turn it on or off.

I started to examine this solution:

Option Compare Database
Option Explicit
Global sLogon As String

Public Function StartUp()
Dim Designer As String
Dim Restart As Boolean
Dim stAppName As String

Designer = "yourlogonhere"
FIND_USER

If sLogon = Designer Then
Restart = UnlockStartup
Else
Restart = LockStartup
End If

If Restart Then
'Close database and re-open
stAppName = "MSAccess.exe " & CurrentDb.Name
Call Shell(stAppName, 1)
DoCmd.Quit
Else
If sLogon <> Designer Then DoCmd.OpenForm "TitleForm"
End If

End Function
-----------------------------------
Sub FIND_USER()

On Error GoTo ERR_FIND_USER

Dim UserParam$
Dim sChk As String
Dim CurrentAuditor As String

UserParam$ = Environ("S_USER")
If UserParam$ = "" Then UserParam$ = Environ("USERNAME")
sLogon = UCase$(UserParam$)

EXIT_FIND_USER:
Exit Sub

ERR_FIND_USER:
MsgBox Error$
Resume EXIT_FIND_USER
End Sub
---------------------------------------------
Function LockStartup() As Boolean
Dim Restart As Boolean

Restart = False
ChangeProperty "StartupShowDBWindow", dbBoolean, False, Restart
ChangeProperty "AllowBuiltinToolbars", dbBoolean, False, Restart
ChangeProperty "AllowFullMenus", dbBoolean, False, Restart
ChangeProperty "AllowToolbarChanges", dbBoolean, False, Restart
ChangeProperty "AllowBreakIntoCode", dbBoolean, False, Restart
ChangeProperty "AllowSpecialKeys", dbBoolean, False, Restart
ChangeProperty "AllowBypassKey", dbBoolean, False, Restart
Application.SetOption "Show Hidden Objects", False
LockStartup = Restart
End Function
-----------------------------------------------
Function UnlockStartup() As Boolean
Dim Restart As Boolean

Restart = False
ChangeProperty "StartupMenuBar", dbText, "(default)", Restart
ChangeProperty "StartupShowDBWindow", dbBoolean, True, Restart
ChangeProperty "StartupShowStatusBar", dbBoolean, True, Restart
ChangeProperty "AllowBuiltinToolbars", dbBoolean, True, Restart
ChangeProperty "AllowFullMenus", dbBoolean, True, Restart
ChangeProperty "AllowToolbarChanges", dbBoolean, True, Restart
ChangeProperty "AllowBreakIntoCode", dbBoolean, True, Restart
ChangeProperty "AllowSpecialKeys", dbBoolean, True, Restart
ChangeProperty "AllowBypassKey", dbBoolean, True, Restart
UnlockStartup = Restart
End Function
-------------------------------------------
Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant, Restart As Boolean) As Integer
Dim dbs As Database, prp As Property
Dim CurrentPropVal As Variant
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err

CurrentPropVal = dbs.Properties(strPropName)
If CurrentPropVal <> varPropValue Then
dbs.Properties(strPropName) = varPropValue
Restart = True 'need to restart database
End If
ChangeProperty = True
Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then 'Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

however it's not perfect, because it automatically turns on or off the ribbon etc if the userID matches, but admins does not neccessarily want to edit the database, just use it, so if I could give them some chance to select between modes, that would be perfect.

I tried to modify the above code, without success - tried to mix it with the below sub:

Private Sub Form_Open(Cancel As Integer)

If "Userid=Admin (more explicit code inserted here) = Then
bLock.Visible = True
bUnLock.Visible = True
End If

End Sub

can someone please help me how to create the above mentioned function?

The best solution would be if I could have a separate table listing the userIDs of all of the admins, and the Admin mode button would appear for everyone who are lsited in the table.

I guess updating the table would be much easier the updating the array of the users in the code.

thanks in advance for the help.

 

[mdlueck]

however it's not perfect, because it automatically turns on or off the ribbon etc if the userID matches, but admins does not neccessarily want to edit the database, just use it, so if I could give them some chance to select between modes, that would be perfect.

Consider... If the ID is on the Admin list, enable a button which would allow the user to enter edit mode.

Person A is an Admin, the "enable admin" button is enabled for them, just not clicked by default. They are able to click the button as they require is functionality.

Person B is not on the admin list, so for them the button stays disabled.

 

[Rx_]

One of the great features of this forum is the Sample Databases. Let me suggest taking a little time to reivew some submissions. This is a short list of a quick search on the subject:
http://www.access-programmers.co.uk/forums/showthread.php?t=226927&highlight=user+security
http://www.access-programmers.co.uk/forums/showthread.php?t=99777&highlight=user+security&page=2
http://www.access-programmers.co.uk/forums/showthread.php?t=193131&highlight=user+security
http://www.access-programmers.co.uk/forums/showthread.php?t=170781&highlight=user+security
http://www.access-programmers.co.uk/forums/showthread.php?t=109462&highlight=user+security

There might be some new ideas to explore

 

[Kobe2932]

One of the great features of this forum is the Sample Databases. Let me suggest taking a little time to reivew some submissions. This is a short list of a quick search on the subject:


There might be some new ideas to explore

Hi

thanks for the links, most of them were already familiar to me, however these are not exactly what I'm looking for, that1s why I opened up this thread.

My database has a lot of potential users, so the base principle is that everyone who is not an admin, is a user. I think, it would be easier to define only the admins, and their rights, all the others should be able to see the database in a "use only, do not modify" mode. So I'm trying to avoid this logon-thing, as it creates only more workload to keep, update the passwords for approx. 20-30 people. Dealing with 2-3 admins is easier, I guess.

Probably it wasn1t fully clear, what do I want to achieve, so I1m trying to give a more prompt description, so if someone already met with this solution, might help.

As a default option, I would like to disable all the editing buttons in access, and I would like to add 2 buttons to the opening form, lets call them "Admin mode ON" and Admin mode off". These buttons should be visible only to admins, and they could switch between the "use-only" or design view.

 

[khodr]

Why don't you add extra field in you table and call it user level then identify each user as admin or user then you can create a button which holds the code to populate a form with password and then on that form you can have more buttons with on click event coded to show or hide the access ribbon and enable the editing of the forms

 

[Rx_]

I like to keep things simple (because it fits my mentality).
Trying to put too much in one application might increase the maintenance over the product life-cycle.

So, I have one application for the users. No tool bar at all. Everything is coded.

I have another database (Maintenance DB) located in a very protected network folder for the Admin.
The Maintenance DB has some basic instructions for admin levels (not programmers) to add or remove items to a listbox, combo-box, and other things. It is not a bullet-proof because the Admin are trusted.
All tables are designed for the admin DB.
For example: the States table:
Primary autocounter key, Full State name, Abbr State, and "Activity"
the Activity is "I" for inactive and "A" for active.
The combobox filter only shows "A"
Once something is added, it can never be deleted. It just gets Inactivated with an Activey setting of "I".
For current reports, only fields with an "A" are shown and reported.
For historical / audit reports, both A and I are shown.
If we deleted a record, we couldn't look up the related reference.

The Maintenance DB also has tables with multiple columns.
Multiple columns allow for Rule Based programming.
Lets say you have a list of Offices and Offices must complete reports for the FED, STATE, COUNTY, LOCAL, BLM, TRIBE, .... agencies
The Admin (a non programmer) can open up this table and put "A" in the agencies this office applies to. Make a printout and easily understand the Rule Based requirements that drive the forms, controls, reports, and regulatory logic.

Instead of trying to cram everything into one application, think about an independent application that adjust the table values. Then program according to a table value.
With a 1 page of documentation for each table, the non-tech managers are able to take care of 95% of the maintenance.

Just another idea, I have a lot of crazy ideas.

 

[Kobe2932]

I like to keep things simple (because it fits my mentality).
Trying to put too much in one application might increase the maintenance over the product life-cycle.

So, I have one application for the users. No tool bar at all. Everything is coded.

I have another database (Maintenance DB) located in a very protected network folder for the Admin.
The Maintenance DB has some basic instructions for admin levels (not programmers) to add or remove items to a listbox, combo-box, and other things. It is not a bullet-proof because the Admin are trusted.
All tables are designed for the admin DB.
For example: the States table:
Primary autocounter key, Full State name, Abbr State, and "Activity"
the Activity is "I" for inactive and "A" for active.
The combobox filter only shows "A"
Once something is added, it can never be deleted. It just gets Inactivated with an Activey setting of "I".
For current reports, only fields with an "A" are shown and reported.
For historical / audit reports, both A and I are shown.
If we deleted a record, we couldn't look up the related reference.

The Maintenance DB also has tables with multiple columns.
Multiple columns allow for Rule Based programming.
Lets say you have a list of Offices and Offices must complete reports for the FED, STATE, COUNTY, LOCAL, BLM, TRIBE, .... agencies
The Admin (a non programmer) can open up this table and put "A" in the agencies this office applies to. Make a printout and easily understand the Rule Based requirements that drive the forms, controls, reports, and regulatory logic.

Instead of trying to cram everything into one application, think about an independent application that adjust the table values. Then program according to a table value.
With a 1 page of documentation for each table, the non-tech managers are able to take care of 95% of the maintenance.

Just another idea, I have a lot of crazy ideas.

I want to put everything in 1 application because the main function of the database is very simple - collecting and storing, updating data in 1 table, and creating a performance report on a regular basis, that's all. 1-2 main table, nad 2 modules with 2 macros. The structure is not so sophysticated, so neither do I want to make protection / user rights so complex. That's why I did not want to split it inot master copy / maintenace database, etc - as there is already a function in the database that automatically creates a backup copy, which is enough I guess.

I just want to make sure, that not every user, but only the trusted admins have right to browse the modules.

I think the above mentioned solution - adding an extra column to the users table and defining the rights based on it is the method I'm looking because it is much easier to update that 1 table, if you have a functional button / code for that.