Database security

[AccessFreak]

Thanks for all the help I got last week. You all gave me perfect help material. Thanks for that.

I have only a few questions. This application I build has to be secured in the source-code. Not only that, but Access itself sometimes changes the VBA code by itself.. I want to prevent that. How can I do that? Because when such problem accurs i need to get in VBA and change the weird character(s) which somehow are placed by Access. Next thing is how can I secure/protect it. I used a lot of macro's for some buttons and so on. Now it gives me that stupid security check at the beginning. How can I set it off. And next thing is. How can I disable the shift-key from preventing users to get into the source.

3 other questions:
* How can I connect the database with Active Directory?
* How can I build a button which can changes lables on the forms?
* Is there a possibility to build a button that can add a new field to a table and place it directly on the form?

Again thanks for all..

 

[HiTechCoach]

Thanks for all the help I got last week. You all gave me perfect help material. Thanks for that.

I have only a few questions. This application I build has to be secured in the source-code. Not only that, but Access itself sometimes changes the VBA code by itself.. I want to prevent that. How can I do that? Because when such problem accurs i need to get in VBA and change the weird character(s) which somehow are placed by Access. Next thing is how can I secure/protect it. I used a lot of macro's for some buttons and so on. Now it gives me that stupid security check at the beginning. How can I set it off. And next thing is. How can I disable the shift-key from preventing users to get into the source.

3 other questions:
* How can I connect the database with Active Directory?
* How can I build a button which can changes lables on the forms?
* Is there a possibility to build a button that can add a new field to a table and place it directly on the form?

Again thanks for all..

I have never has an version of Access change the VBA code or do anything like you have described.


The best was to protect the source code is to compile your database into an MDE/ACCDE. I would only do this with the front end. So this woudl require that your database be split. IMHO, "splitting" is a critical part of securing your database.

About macros, I would use NO macros! I would only use VBA code.


* How can I connect the database with Active Directory?

What are you needed?

* How can I build a button which can changes lables on the forms?
Button? I would this you woudl store the data in a table and it happen automatically.

* Is there a possibility to build a button that can add a new field to a table and place it directly on the form?
If you have a secured database using an MDE/ACCDE, it is not possible to modify the design. You should deploy a need front end. The back end should only be ydated after a backup has been made.

 

[AccessFreak]

I have never has an version of Access change the VBA code or do anything like you have described.


The best was to protect the source code is to compile your database into an MDE/ACCDE. I would only do this with the front end. So this woudl require that your database be split. IMHO, "splitting" is a critical part of securing your database.

About macros, I would use NO macros! I would only use VBA code.


* How can I connect the database with Active Directory?

What are you needed?

* How can I build a button which can changes lables on the forms?
Button? I would this you woudl store the data in a table and it happen automatically.

* Is there a possibility to build a button that can add a new field to a table and place it directly on the form?
If you have a secured database using an MDE/ACCDE, it is not possible to modify the design. You should deploy a need front end. The back end should only be ydated after a backup has been made.

I changed all yhe macros to vba code and deleted all the macros. But still it gives me the security popup at the beginning. How is ythat possible....

Next thing is how can i split the database in mde\AACDE format.i

 

[Banana]

I, too, have never ever seen Access change VBA code of its accord. If there's any change to VBA, I would first 1) blame myself for incompetence, 2) blame someone for messing with my codes, then blame myself for incompetence, and 3)... blame myself. Access's not even a suspect.

As for security popup, it sounds like you're using Access 2003 (or maybe 2000 or 2002)? If that is the case, this has to be fixed by using a digital certificate which is a whole bucket of worms in itself. It is doable, but you could also train the users to click the appropriate buttons in few seconds. Your decision.

As for splitting and compiling into MDE; we're talking about two steps.

First, you split the database, which is to move all tables out of your current MDB file and into a new blank MDB. On Menu: Tools -> Database Utilities -> Database Splitter.

If it's done correctly, all tables in your first MDB will be now linked to the tables in other mdb named <yourdatabasename>_be.mdb.

You would then compile the front end into a MDE by going Tools -> Database Utilities -> Compile into MDE.

Here is very important point: The operation is *irreversible*. You simply must keep a copy of MDB at all time if you want to do design change or updates. You can't change MDE back to MDB and if you don't have the original MDB, you're SOL.

Hope that helps!

 

[DCrake]

About one comment

* Is there a possibility to build a button that can add a new field to a table and place it directly on the form?

Lets work on the improbable and say you achieved this.

You created a new field by telling Access what type it was (date,Text,Boolean,etc) what its name was, which table it was added to, where on the form to place it, What lable is attached to it, what its tab index it was, which field was it bound to (if using a bound object). etc.

Now the user then enters the appropriate type of data in to this control, what are you going to do with it? There will be no code behind the control, No other code referencing this control. No validation inplace.

As you could not pre-emt any of the above you could not plan for it, could you?

So what is the point of the question, if not out of curiosity.

David

 

[AccessFreak]

So there is no possibility to use a button for adding new fields to tables and automatically to the selected form. I know it is not possible but still needed to ask.

This application is being installed on a sql-server. What do I need to do for the front-end and the back-end(tables). So every user of this application gets the Front-end and on the sql-server is the back-end. Is that how it works?

And how can I disable the SHIFT key at the beginning?

Next thing all my VBA codes work properly. But sometimes it changes a letter in the VBA code... I don't what that does.

 

[Banana]

I'm not sure if I'm following you, and if we're using same terms.

To clarify, it is possible to programatically add a new field and a new control to the form and bind the control to the new field, but this screams bad table design to me. It is not customary for developers, let alone end users, to go around changing table by adding new fields at a click of button. A correct designed database would make the need for adding new fields irrelevant.

But if you meant 'field' in sense of data being added, which we usually call 'record', it may be a different story... Would that be what you meant?

Shift key can be disabled by going to Database Startup in Tools menu and clearing the checkbox. There's also VBA to permanently disable it, but requires that you have a mean of getting in and enabling for your or other developers' use.

As for "change a letter", are you referring to capitalization? If so, that's normal behavior as VBA editor automatically format the syntax to use capitalization and so forth. It's for its benefit primarily.

 

[AccessFreak]

But if you meant 'field' in sense of data being added, which we usually call 'record', it may be a different story... Would that be what you meant?

As for "change a letter", are you referring to capitalization? If so, that's normal behavior as VBA editor automatically format the syntax to use capitalization and so forth. It's for its benefit primarily.

Yes, I mean a new field in a sense to add data.

And secondly, I don't mean "change a letter" like make a letter a capitale. NO I mean like this. I have a Qdef.Parameter for example. I called the parameter ("@Applicationcode1"). And then by sudden if I use that event, it gives me an error. If I check the VBA code it has been changed to ("@Applicationcode") WITHOUT THE NUMBER....

It works fine. I tested it many times. But it happens everyday 1 time at a minimum.

Also if I use a query. I have a critiria called Forms.Startmenu2.Subform_menu......... It somehow changes the query to Forms.Startmenu.Subform_Menu2 .... Veryy weird. So that is what I mean. How can I prevent that?

 

[Banana]

If you mean to add new data, it's 'adding a new record', not a 'adding a field', which is very different thing. We'd be glad to help if you could describe a bit more what exactly do you need to accomplish with the 'adding new record'.

As for the missing letter case, if that happened to me, I'd look at my code very carefully to see if I've forgotten to type in the '1' when I assigned/created the variable

You say it happens 'everyday 1 time at a minimum', so it's not consistent? It sometimes works, sometimes doesn't? If that is indeed the case, I think there's corruption going on. To rectify that, create a new blank database, turn off AutoCorrect (e.g. AutoCorrupt), then import in all objects from your old database. See if the behavior persist.

 

[AccessFreak]

I changed the autocorrect and now it is working perfect. Thanks for that.

About your question about adding a record.

Look it's like this.
The users of that company want to have the possibility to add a new record from a table. This record doesnt even exist is the tables yet. So that button needs to add a new record to a table and automatically set the field into the form.

The meaning of this is that the users want full control off adding fields to tables which are connected to the record.

Example:

I have a table called "Applications". In this table are 3 fieldnames called Applicationname, ApplicationDescription, ApplicationVersion. Let's say in a half year they want a new field from the table Applications. This field needs the name "Vendorname". But there are just three.. The button on the form needs to generate a new field to the table Applications and needs to set it automatically beneath the other formfields.

 

[Banana]

Glad to hear that importing solved the weird behavior.


Okay, so you want to add a new field to the table? You want to give your users a functionality of doing so?

Well, to be blunt, this is a case of putting the cart before horse.

A correctly designed database application should have its tables, fields, and relationship already defined early in the planning stages, so you would have to invest significant time and effort in gathering the data requirement of your users and what they need to go through the process. Furthermore, with proper normalization, it should make any design change unnecessary and relatively stable.

"But the users' requirement could change!"

Yes, business model do change, but not on regular basis and surely not daily basis. Furthermore, when the model does change, the correctly designed application should make it a trivial task for the developer to add the new changes and distribute it to the users. Even better, the developer can play it safe and consider what is possibly in future and model accordingly. The users are not the right people to change and alter the data model for that matter.

Furthermore think about what will happen if users were free to edit willy-nilly.

I can envision some well-meaning worker adding "Phone1", "Phone2", "Phone3" fields. Now we've broken the first normal form. Who gets to clean up the mess? You'd end up with *more* work than if you just added the needed fields and updated the forms yourself.

 

[AccessFreak]

Glad to hear that importing solved the weird behavior.


Okay, so you want to add a new field to the table? You want to give your users a functionality of doing so?

Well, to be blunt, this is a case of putting the cart before horse.

A correctly designed database application should have its tables, fields, and relationship already defined early in the planning stages, so you would have to invest significant time and effort in gathering the data requirement of your users and what they need to go through the process. Furthermore, with proper normalization, it should make any design change unnecessary and relatively stable.

"But the users' requirement could change!"

Yes, business model do change, but not on regular basis and surely not daily basis. Furthermore, when the model does change, the correctly designed application should make it a trivial task for the developer to add the new changes and distribute it to the users. Even better, the developer can play it safe and consider what is possibly in future and model accordingly. The users are not the right people to change and alter the data model for that matter.

Furthermore think about what will happen if users were free to edit willy-nilly.

I can envision some well-meaning worker adding "Phone1", "Phone2", "Phone3" fields. Now we've broken the first normal form. Who gets to clean up the mess? You'd end up with *more* work than if you just added the needed fields and updated the forms yourself.

Yes precisly, I know what happens if users get full control. One of the problems is (if I was a user of that application), when I symbolic say I click on that button. The button needs to do all the following steps which are not even possible.

The users use an MDE-file

*a new field has to be added to the tabledesign on the SQL-server. The back-end needs the new field. This field needs to be connected to the developers MDB.
*Then the MDB must update the selected form and sets the new field.
*Then the MDB updates the MDE.
*All the users need to close thear application.
*And get the new MDE which is automatically shoppable for all the applicationowners.

This is just impossible because only the developer can do those changes. There is just no option to do this... I think I know enough.

 

[sphynx]

I run the following code to disable the Shift Bypass function & also allow me to re-enable it when required

The following code is placed in a module

Public Function SetProperties(strPropName As String, _
varPropType As Variant, varPropValue As Variant) As Integer

On Error GoTo Err_SetProperties
Dim db As DAO.Database, prp As DAO.Property
Set db = CurrentDb
db.Properties(strPropName) = varPropValue
SetProperties = True
Set db = Nothing

Exit_SetProperties:
Exit Function

Err_SetProperties:
If Err = 3270 Then 'Property not found
Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
db.Properties.Append prp
Resume Next
Else
SetProperties = False
MsgBox "SetProperties", Err.Number, Err.DESCRIPTION
Resume Exit_SetProperties
End If
End Function

I then create a form which is displayed on start up that requires the correct password to be entered to allow users to display the switchboard and gain access to the system.

Within this form I have a hidden button which runs the following code allowing me to enter a password and enable/disable the Shift Bypass key

Private Sub Command19_Click() 'HIDDEN PASSWORD UNLOCK DIALOG BOX

On Error GoTo Err_bDisableBypassKey_Click

'This ensures the user is the programmer needing to disable the Bypass Key

Dim strInput As String
Dim strMsg As String

strMsg = "Do you want to enable the Bypass Key?" & vbCrLf & vbLf & _
"Please key the programmer's password to enable the Bypass Key."
strInput = InputBox(Prompt:=strMsg, title:="Disable Bypass Key Password")
If strInput = "Your Password" Then

SetProperties "AllowBypassKey", dbBoolean, True
Beep
MsgBox "The Bypass Key has been enabled." & vbCrLf & vbLf & "The Designated key will allow the users to bypass the startup & options the next time the database is opened.", vbInformation, "Set Startup Properties"
Else

Beep
SetProperties "AllowBypassKey", dbBoolean, False
MsgBox "Incorrect ''AllowBypassKey'' Password!" & vbCrLf & vbLf & "The Bypass Key was disabled." & vbCrLf & vbLf & "The Designated key will NOT allow the users to bypass the startup options the next time the database is opened.", vbCritical, "Invalid Password"

Exit Sub
End If

Exit_bDisableBypassKey_Click:
Exit Sub
Err_bDisableBypassKey_Click:
MsgBox "bDisableBypassKey_Click", Err.Number, Err.DESCRIPTION
Resume Exit_bDisableBypassKey_Click

End Sub
This code works well for me as the majority of users have no idea about the shift bypass key, it does however rely on you remembering to deactivate it after use.

Hope this is of some use to you

 

[DCrake]

Quite a while ago I was out at a clients business and they had an inhouse programmer, and I vaugly remembering that he was able to define an alternative key other than the shift key to act as a bypass key. Was I just dreaming this or can you actually define say the "A" to act as a bypass key.

David

 

[sphynx]

I would love to say yes, but in truth I dont know!

I am self taught and have only been working with access for the last 2 years and vb for approx 4. Therefore there are some rather large gaps in my knowledge!!!

 

[BillBee]

I have been told to ask the dumb questions. I am referring to post 4 from Banana. When the database is split and the front end is "converted" to an MDE file are all the queries, forms and reports secure from people being able access to the design areas of these objects if they are still MDB files.

 

[Banana]

There's no such thing as dumb questions, just nonobvious questions.

If my memory serves, only tables and queries can be edited in MDE. Even so, the process is less straightforward because you no longer have user interface available for editing the tables and queries, though you could import it out, edit it, and re-import it back in, or change it via code. Form, Reports and code behind access object and modules are no longer editable.

 

[AccessFreak]

I have been told to ask the dumb questions. I am referring to post 4 from Banana. When the database is split and the front end is "converted" to an MDE file are all the queries, forms and reports secure from people being able access to the design areas of these objects if they are still MDB files.

When you convert an MDB to an MDE, the MDB still excist because its for the developer. The MDE is for the users. They cant access the forms. They can't change the forms. But they can delete queries or tables. That means that the MDE of that user doesnt work anymore. The MDB can be used to change forms and update them to the users. Thats a developers issue.

 

[BillBee]

I think I understand. You are saying that no access to the design of a form or a report. Another question then. Would it be as practical to switch your database to Access runtime or is that more complicated.

 

[Banana]

Well, it's good idea for where you have computers that doesn't have Access installed. But it is in no way a protection as anyone with a full license of Access still can modify the same file that they couldn't modify under the runtime.

Some developers do force the file to be run in runtime mode, regardless of whtether a full installation is present or not, which can help simplify the development and keep the honest users out. But this is not in any sense a real protection against those who will want to modify the file when they shouldn't be.