UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

7 Pages V   1 2 3 > »   
Reply to this topicStart new topic
> Access 2007 security    
 
   
alancossey
post Aug 23 2006, 12:47 PM
Post #1

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Hello everyone.
Though I am very impressed with lots of stuff to do with Access 2007 (truly), I am concerned about the lack of security. User level security (aka ULS aka workgroup security) has been removed for the new Access 2007 file format, though encryption and the database password have been beefed up. Hopefully you will know that the old ULS has gradually lost all semblance of being actual security since tools have appeared on the internet that got through it. First these tools allowed the breaking of ULS if the tool user had access to the developer's MDW file and then, more recently, tools which did not even need the MDW file but broke into the MDB file directly. Thus in Microsoft's documentation MDB files using ULS changed from being "secure" to "security-enhanced" to Microsoft recommending that ULS only be used for navigational purposes. That leaves all the databases written some time ago with ULS most definitely in the lurch. Now with no direct replacement for ULS appearing in Access 2007
we may have a real problem.
Some have said that if you need to secure your data you need to move to SQL Server. Often that is not possible, e.g. if you just want to upgrade your many Access databases created in versions prior to 2007 to Access 2007 format to make use of the new whizzy features that require an Access 2007 format back end. I would be very interested in hearing people's thoughts on how secure Access 2007 can be made without resorting to SQL Server, but still sticking with linked tables (I know you could get some decent security if using unbound forms and the like). To kick off, I would like to point out that:

1) If a front end is password/encryption protected, the navigation pane, per se, does not appear to give any real protection. Though it can hide database objects from the user who opens the database, those database objects can be viewed (and altered) in at least a couple of ways, e.g. opening the database from another database using DAO (since the person writing that code has the first database's password) and secondly,it is possible to bypass the start up procedure that gets the navigation pane options to kick in (i.e. by using that old favourite the Shift key bypass).

2) If, as I have tried, you move your queries to an "mid tier" database and base your forms on queries in that mid tier, it sort of works, but you can get into problems with linked subforms and subdatasheets. The former get upset since the link fields don't appear to work properly (at least the way I tried it) and the latter since the Open event runs at unexpected times. I can give more info if anyone is interested (and I know that is a big IF!). There may be ways around this if not using linked tables, but linked tables are a major part of Access' RADability.

Where I have got up to now is as follows and I would be really, really interested to know if other people can find a way round my attempt as security (not counting network sniffers and stuff that catch passwords as they get transmitted across networks):

1) Set up a normal front end and back end databases. Let's call them FE.accdb and Data.accdb. Do an "Encrypt with Password" on both databases.

2) In the Access Options from the Office thingy up the top left corner in Access 2007, untick use of "Access Special Keys", untick "Allow Full Menus", untick "Allow Default Shortcut Menus".

3) Disable the Shift key bypass by using code similar to that at ttp://www.mvps.org/access/general/gen0040.htm (to get back in you would need to use this code to re-enable the Shift key bypass).

4) Lock the VBA project with a password and/or make the front end into an ACCDE file (the equivalent of the old MDE) if concerned that the VBA project might get hacked.

5) Write a VB exe to launch the front end (however I can't get this to keep the Access app open once the code has finished running)

Dim app As Access.Application
Set app = New Access.Application
app.Visible = True
app.OpenCurrentDatabase Command, , "TheFrontEndPassword" 'Replace TheFrontEndPassword by the correct password.

This last step opens the password-protected front end and ensures the start up options run. As far as I can tell, the only problem that might occur is if a fairly savvy user used Automation from another app (or another instance of Access) and get to data that way.

Comments much appreciated.
Go to the top of the page
 
+
alancossey
post Aug 23 2006, 01:51 PM
Post #2

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



VB6 code to open Access OK for a database with a database password such as the following seems to work OK.

Sub Main()
Dim app As Object ' Access.Application
Set app = CreateObject("Access.Application")
app.Visible = True
app.UserControl = True
app.AutomationSecurity = 1 ' = msoAutomationSecurityLow
app.OpenCurrentDatabase Command, , "TheFrontEndPassword"
Set app = Nothing
End Sub

You then need to set up a shortcut on your desktop with the path to the exe file followed by the path of the database, e.g.

"C:\Documents and Settings\Alan\Desktop\LaunchApp.exe" C:\Access2007\FE.mdb
Go to the top of the page
 
+
alancossey
post Aug 24 2006, 02:17 AM
Post #3

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



1) Code such as the following from a non-Access app, e.g. VB, Word, Excel gives you access to all sorts of stuff in the open Access app (assumes a reference has been set to Access and DAO):

Dim app As Access.Application 'Dim as object is no reference has been set to Access
Dim db As DAO.Database

Set app = GetObject(, "Access.Application")
Set db = app.CurrentDb

Debug.Print db.Name
Debug.Print db.TableDefs(0).Name
Set db = Nothing
Set app = Nothing

Using automation you can thus get at all the data and, presumably, recordsources of forms and reports and so on.

2) On a different tack, has anyone tried putting some sort of security on offline Sharepoint data? Presumably there is no restriction on what a user can do with such data.
Go to the top of the page
 
+
datAdrenaline
post Aug 24 2006, 05:18 PM
Post #4

UtterAccess Editor
Posts: 16,516
From: Northern Virginia, USA



I have a different approach, which seems a little less cumbersome ... plus does not require you to create an AppLauncher in VB or as a 3rd Access Application that just acts like a short cut. The key is this, once MS Access opens the BE using the password, and keeps that connection (not neccessarily an ADODB connection) to the BE alive, you do not need the password. Note: I do not have A2007, but I would bet that this technique will work for it as well ... actually, I bet, it could work for all versions of MSAccess with a DB password ... (I do use a class module ... but I don't have to so A97 are not out of the loop!)

Heres what you do... (all the names mentioned are the object names of the attached tables)

1. Create a backend datafile for your tables (BE.mdb). Along with your application tables, I suggest to create a dummy table with one record in it (tblDummyTable)... DO NOT PWD protect your BE ... yet. (My app table is tblUANames)

2. Create a frontend datafile for your forms, reports, and code (FE.mdb)

3. Link the BE tables to the FE .. NOTE: since there is no pwd on the BE, the Connect string does not contain the PWD ... which is a GOOD THING!!

4. Create some forms in the FE: A form that will be hidden (frmKeepAlive - UNBOUND, but designed while bound to tblDummyRecord to ease design -- read more later), A form used as the Main form for a user upon app start up (frmMyUARecords - Bound to tblUANames), A splash screen (frmSplash - Unbound), and a form used to initialize the application (frmStartUp - Unbound) utilizing the Form_Open event. The startup form is set to be the form to open on start up in the Tools->Start Up->Display Form/Page. I use a start form because it is possible to halt the autoexec macro with ctrl-brk. Check out this thread: How CRTL-Brk can be used to bypass Autoexec macro

4.1 When designing frmKeepAlive, initially I set this forms recordsource to tblDummyRecords, then create a couple of text boxes that are bound to the fields in the dummy table. Once the text controls are created, I set the forms recordsource to a ZLS (I blank it out). Then I save the form as an unbound form.

5. Close the FE, the Open the BE in exclusive mode in order to set the db password. Then close the BE and open up the FE again ... now when you try to open a table, you SHOULD get an error message that says you have the incorrect pwd.... which is what we want!!

6. In the FE, create a class module (clsBackEnd) ... You don't HAVE to use a Class module, but a class module allows you to create a 'Friend' procedure which *I THINK* means that external applications CAN NOT see it, but it acts like a PUBLIC procedure inside the host application ... so 'Internally Public' is the term I have coined to it!! In the class module create a procedure (BindBackend) that will bind frmKeepAlive to a recordsource. BindBackend opens a recordset using the pwd, then binds frmKeepAlive to a LINKED TABLE, which is now allowed since MS Access has a PWD granted connection to the BE. Here is the code (note the syntax of the SQL statement that is used to open the virtual recordset with the PWD):

CODE
Option Compare Database
Option Explicit
    
Friend Sub BindBackend()
    
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    On Error Resume Next
    strSQL = "SELECT *" & _
             "FROM tblDummyTable IN '' [MS Access;PWD=UA;DATABASE=C:\Temp\be.mdb]"
    
    'Open a recordset with the PWD, but don't bind this to the form because
    'if you do, a savvy user can open the VBA editor and use the immediate
    'window to get the recordsource property, which would then expose your
    'db pwd.
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    'Now that MS Access has a PWD granted connection to the BE, set the
    'recordsource of the form frmKeepAlive to your LINKED dummy table. So
    'now if the savvy user interogates the form properties, they will only
    'get the linked table, which DOES NOT have the PWD in the connect string.
    'and since the form is hidden PLUS does not have any code, most users will
    'not even know this form exists... but this is the life blood of how this
    'method works!
    Forms("frmKeepAlive").RecordSource = "tblDummyTable"
    
    'Clean up, now you can close the rst that was used to establish the PWD
    'granted connection to the BE, so now there are NO objects in your db
    'that expose the PWD and the BE is still opened and available to this
    'instance of MSAccess through frmKeepAlive.
    rst.Close
    Set rst = Nothing
    
End Sub


7. Next create the start up code that is attached to the open event of frmStartUp. The start up code will first test to see how the app was started, if it was started with Automation, then the app quits. If started by the user, the code then shows the splash screen, opens frmKeepAlive in hidden mode, executes BindBackend, waits for 5 seconds, then opens the main form of the application. Here is the code:

CODE
Private Sub Form_Open(Cancel As Integer)
    
    Dim clsBE As clsBackEnd
    Dim dtTimerExpired As Date
    
    'Quit the app if the app is opened by [OLE] Automation
    If UserControl = False Then
        Application.Quit
    End If
    
    'Create the class object
    Set clsBE = New clsBackEnd
    
    'Open the splash screen
    DoCmd.OpenForm "frmSplash"
    
    'Open the the form that will keep the PWD alive in the
    'current MS Access session .. in hidden mode of course
    DoCmd.OpenForm "frmKeepAlive", , , , , acHidden
    
    'Bind a recordset that is opened WITH the db pwd
    'to form frmKeepAlive, thus keeping the PWD alive.
    clsBE.BindBackend
    
    'Wait for 5 seconds ... just because I want the users to
    'see how long 5 seconds really is.
    dtTimerExpired = DateAdd("s", 5, Now())
    Do Until Now() > dtTimerExpired
        DoEvents
    Loop
    
    'Close the splash screen
    DoCmd.Close acForm, "frmSplash"
    
    'Open the main form of the application, which is bound
    'to a linked table, HOWEVER, the linked tabledefs DO NOT
    'have the PWD info in it, so the form "frmKeepAlive" MUST
    'be open, so the CurrentDB "knows" the PWD to open the table
    'that is bound to the form, if frmKeepAlive is not opened
    'and bound to the BE, then MS Access will error out when any
    'object bound to the linked tables is opened, saying
    'the pwd is incorrect
    DoCmd.OpenForm "frmMyUANames"
    
    'Close the startup form
    DoCmd.Close acForm, Me.Name
    
End Sub


8. Password protect the VBA project in the FE.

9. (optional). Make the FE into an MDE

Assumptions ...
Utilize the vast resource of UtterAccess to:
- Disable the shift bypass key
- Turn off the menus
- Change the start up properties appropriately (ie: Hide the db window, etc ...) (FYI: AllowBreakIntoCode is NOT on the Tools\StartUp dialog ... so it must be set programatically)

Here is a start, but there are SEVERAL threads on that stuff.

I have tried several things to get to the PWD, or otherwise, get to the good stuff, and was unsuccessful (with the use of a PWD cracker!). I do know how "weak" the current db pwd is, however, the purpose of this exercise was to simply present a method of protecting both the data and the application with ONLY the db password, since A2007 has dropped User Level Security ... So ... for this exercise, you will have to imagine that the db password is really strong (as it is reported to be in A2007).

I CAN import the tables/queries into a blank db, but that does no good because the .Connect string does NOT contain the PWD. Since the VBA project is PWD protected, you can not import any forms/reports with code behind then, however, you CAN import forms/reports that DO NOT have code, but again, thats really not too bad because the forms/reports do not have the BE PWD stored in them in anyway. If you want to prevent the ability to import your forms/reports you can just put some code behind them ... even if its just comments!! .. or you can covert the FE to an MDE. If the users are saavy enough to learn how to enable the "shift to bypass" after it has been disabled (which is NOT implemented in this example), its really not too bad ... the user MAY be able to change the form/report layouts, but again, the BE db password is NOT stored in an Access Object ... It is only stored in the VBA project, which is PWD protected... again, if you are worried about that kind of infiltration, you can convert to an MDE.

This concept SHOULD work for SQL Server backends as well ... when you link the tables to the FE, make sure you DO NOT save the PWD. Then you would have to modify the SQL Statement in BindBackend with a connect string that is appropriate.

The attached example contains 4 files:
----------------------------------------------
BE.mdb (A2000 format) - The backend tables
FE.mdb (A2000 format) - The application in A2000 format
FE_2003.mdb (A2003 format) - The application in A2003 format
FE_2003.mde (A2003 MDE format) - The application in A2003 format converted to an MDE

THEY MUST BE EXTRACTED TO THE FOLDER "C:\TEMP" otherwise the linked tables in the FE wont work, nor will the hardcoded SQL that references the BE file... SO ... PLEASE extract the files to C:\TEMP

Please let me know what you think ... PLUS .. please let me know if you can "get to the good stuff" .. keeping in mind that we are pretending that the db pwd is super strong and does not have 3rd party pwd crackers all over the place!!!

(note: If you didn't see it in the thread, the BE pwd is 'UA', that is the pwd for the VBA project as well)

Another note: If you want to me more "secure" you could add a code block in the start up code that looks at the current Windows user and performs actions accordingly!
Attached File(s)
Attached File  SecuredWithDbPwd.zip ( 69.29K ) Number of downloads: 265
 
Go to the top of the page
 
+
datAdrenaline
post Aug 24 2006, 06:26 PM
Post #5

UtterAccess Editor
Posts: 16,516
From: Northern Virginia, USA



While the FE was opened ... I was able to get to the Data utilizing the GetObject(,Access.Application) from MS Excel... But ... the backend pwd was no where to be found! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

... I'll have to work on that ... that is a tough one!!!

This is where it would be good to put some code in the startup module to analyze if the current windows user is a "trusted" user ... because, presumably, IF the user has access to the data through the front end, then, they have "permission" to the data ... even if its through an OLE automation object. With the solution I proposed, you can not start the app with Automation ... but the GetObject sortof blows by that!!
Go to the top of the page
 
+
alancossey
post Aug 25 2006, 09:54 AM
Post #6

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Hi Brent,
Despite the automation "hole", we might be getting somewhere. Previously I had quite a long play with the idea
of moving queries from the front end into a mid-tier database and creating a recordset based on this then
setting a form's recordset to the recordset. However, this caused problems in linking subforms and on
subdatasheets. However, if we use your idea of a dummy connection and then add a mid-tier database, things seem to be looking up. I have set up an example. If you want to try it out, extract the three files to C:\test. The
files are a front end of FE.mdb (with no database password), a mid-tier of MidTier.mdb (with a database
password of MidTier) and a data-containing back end database of Data.mdb (with a database password of Data).
Their structure is as follows:

Data.mdb - contains two tables; tblNames & tblDogs in a one to many relationship, i.e. each person in tblNames
can have several dogs.

MidTier.mdb - contains two linked tables linked to the two tables in Data.mdb. The connection string includes
the password to Data.mdb, so if a person opens these tables from in MidTier.mdb, they can manipulate the data.
This mdb file also contains two queries based on those two linked tables. I'll explain later why I have created
a couple of queries here.
It also contains a local table, tblDummy containing no data. This is the dummy table used for the persistent
connection in the front end.

FE.mdb - contains a form and subform based on the queries in MidTier.mdb. No database passwords are stored in
their recordsources.
This database also contains a query, qselDummyToSetUpConnection. It is based on the table, tblDummy, in
MidTier.mdb and it includes the database password for MidTier.mdb. In real life this query would not be used,
but rather your persistent recordset created in a class module would be used. However, in order to try and keep
the principle as simple as possible and the sample as simple as possible, I have just set up this query. Before
doing anything else in this FE.mdb, open this query manually to create a persistent connection.
The forms in this FE.mdb are frmNames and a subform, fsubDogs. They are currently in format of datasheet with
subdatasheet. I have tried this out as I was unable to get this to work when assigning recordsets to form and
subform in my previous endeavours. To turn them back to "normal" form and subform, you will know to just open
the main form in design mode and set the main form's Default View to Single and the subform's Default View to
Continuous. In these views, everything seems to work OK, again something I did not manage by using recordsets
(since the link fields got in a muddle).

In real life, the setting of all the start up properties would be needed, but the sample databases in their
simplicity seem to show that a persistent recordset works fine for form/subform combinations and datasheets/subdatasheets.

Back to having a mid-tier. I have set up a mid-tier as having the queries and linked tables there mean that
anyone opening the front end up (there is no database password) sees no tables or queries. As long as the
persistent connection has been set up prior to designing any forms or reports, it is fairly easy to still
design recordsources if, when the SQL Editor is first opened, you open the query property sheet and set the
Source Database to the mid-tier database. Once that has been done, you can add tables into the SQL Editor as
normal and design your form/report recordsource normally. I have found one irritation though. This is when you
open the Field List up, you can't drag fields onto the form to produce controls any more.

I put the queries in a mid-tier to hide them from the front end yet keep them separate from the back end so
that business logic is separate from the data, for updatability and so on, just as we have normally set up
front ends and back ends in the past.

On security, I would be interested in your thoughts. As long as some mechanism is added to check who the user
is, e.g. finding their Windows ID and comparing it to a list in the back end when each form or report is opened
(I've got this underway elsewhere), it looks like there may be sufficient security using this model. To sum up:

The user does not know the necessary database password (in my samples this would be the database password to MidTier.mdb or Data.mdb) as, in the real world, the persistent connection would be set up as you described in
your earlier post and in your samples.

If the user uses code to read the RecordSource of open forms or reports, he will see something like "Select *
From qselNames In 'C:\test\MidTier.mdb', i.e. no password is visible. This would mean, however, he could create
a new query in FE.mdb based on "qselNames In 'C:\test\MidTier.mdb', so it is not perfect, but it does seem an
improvement over just using unprotected linked tables in the front end.

There are bound to be some other problems, but I think we are getting somewhere, though the problem entioned
in the previous paragraph is still important and you may see other problems. Many thanks for your ideas and
help. I don't feel quite so despairing about Access 2007 security now!
Attached File(s)
Attached File  WithMidTier.zip ( 43.54K ) Number of downloads: 115
 
Go to the top of the page
 
+
alancossey
post Aug 25 2006, 12:24 PM
Post #7

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Hello again, Brent,
I’m afraid there was an error in one of the queries I put in MidTier.mdb and I left two unncessary tables in FE.mdb! I have now posted replacements. Also included in the replacements is some other work from my previous endeavours and modified to fit these new needs. This is because basing a form or a report on a query in another database can be a bit of a pain. In the latest samples, there is some code which can be called from a couple of keystrokes via an Autokeys macro. If you do Ctrl/Shift + I (i.e. capital “I”), it will import all the linked tables (except the dummy table) and all the queries from the mid-tier datbase. This enables you to modify the queries, if so desired, within the database where you have your forms and reports, which provides a more natural environment. If you do a Ctrl/Shift + E, it exports those tables and queries, including any modified queries, back to the mid-tier database.

Having the queries and tables in the front end database enables form and report wizards to work and for the Field List (in form and report design mode) to work again.

One remaining irritation is the need to alter the RecordSource of forms and reports from, say,

“qselMyQuery”

to

“Select * from qselMyQuery in ‘C:\test\MidTier.mdb’”

once the queries and linked tables have been re-exported to the mid-tier.

I've also added a couple of functions called "OpenForm" and "OpenReport" that run like their DoCmd equivalents with the addition of opening a recordset based on the mid-tier that persists while the function is running and which then closes. That way it reduces the risk of someone using the persistent connection. Thus with this new method there is no peristent connection if no forms or reports are open, e.g. when a user first opens the front end database and might be having a poke around. It doesn't totally remove the risk, but it does reduce it a bit.

To use, say, OpenForm just use

OpenForm "frmNames"

instead of

DoCmd.OpenForm "frmNames"

Alan

Attached File(s)
Attached File  WithMidTier2.zip ( 83.53K ) Number of downloads: 77
 
Go to the top of the page
 
+
alancossey
post Aug 25 2006, 12:27 PM
Post #8

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Me again. I knew I'd do something wrong in my rush to finish it before tea! I forgot to export the queries and tables from the front end database before posting the samples. When you open the front end for the first time, please do a Ctrl/Shift+E to move the queries and tables to the mid-tier.

Thanks,

Alan
Go to the top of the page
 
+
alancossey
post Aug 26 2006, 06:00 AM
Post #9

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Some good news. I tried running the following in a separate Access instance when a form was already open in the original Access instance with frmNames open. Though you can modify that form's recordset (may not be a problem because that is very similar to what you are doing through the original Access's UI anyway), it doesn't appear that you can use the query from the form's recordsource (get the SQL by finding app.forms(0).recordset.name) directly. I don't understand why that is the case, but if it is correct it is good news. Methinks it is even an improvement on the "good ole days" when the old User-Level Security had not yet been broken. At that time, I would think automation would have enabled you to get at the queries in the front end and run them since the hacking person would have had the necessary ULS permissions to do so. :-)

Public Sub HackQuery()
Dim app As Access.Application
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set app = GetObject("C:\test\fe.mdb")
Set db = app.CurrentDb
Set rst = db.OpenRecordset("Select qselNames.* from qselNames in 'C:\test\MidTier.mdb'") <-- complains about incorrect password at this point.

rst.Edit
rst(2) = "Wiseman"
rst.Update

rst.Close
Set rst = Nothing
Set db = Nothing
Set app = Nothing
End Sub
Go to the top of the page
 
+
datAdrenaline
post Aug 26 2006, 09:33 AM
Post #10

UtterAccess Editor
Posts: 16,516
From: Northern Virginia, USA



I am looking forward to analyzing this further ... I am rather swamped this weekend but I WILL look at it! ...

(just a post to let you know I have not dropped you like ULS!!!)
Go to the top of the page
 
+
alancossey
post Aug 26 2006, 02:43 PM
Post #11

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Hiya Brent,
No problem with a bit of a delay. It just means I get to have a bit more of a play.
I've done a bit more on this line (I do hope no show-stoppers come up now!) and so far so good. I have put in some more code into the front end, which can be called from key combinations as set up in an Autokeys macro.

Existing code with associated key combinations was:

Ctrl/Shift + I - imports all queries and linked tables from the mid-tier database into the front end, which is a more natural place to do mods to the queries, i.e. in the same database as the forms and reports.

Ctrl/Shift + E - exports those queries and linked tables to the mid-tier database, overwriting what is in the mid-tier database and deleting them from the front end.

New key combinations are:

Ctrl/Shift + D - puts the front end in "Design Mode". This sets up a persistent recordset based on the dummy table in the mid-tier. This allows forms and reports to be based on SQL such as "SELECT * FROM tblNames IN 'C:\test\MidTier.mdb"' without having to specify the mid-tier database password or open the form/report using the OpenForm or OpenReport functions, i.e. it makes editing the form or report a bit easier as you can open it from the database window directly (from the Navigation Pane in 2007).

Ctrl/Shift + S - disables "Design Mode" so the only way a form or report based on SQL such as "SELECT * FROM tblNames IN 'C:\test\MidTier.mdb'" can be opened now is using OpenForm or OpenReport. Design Mode is disabled by default when you open the front end database.

It all seems to work well as long as you don't get in a muddle with the SQL in the form's or report's RecordSource.

Remember stored queries are based on the tables in the same database so do NOT have"IN 'C:\test\MidTier.mdb'" in them, but forms and reports are based on SQL based on queries and tables in the mid-tier database so they DO have in the SQL in their RecordSource (ditto comboboxes and listboxes).

One downside to this putting all queries into the mid-tier database when running live is that if you want a criterion in the recordsource of a form or report, it doesn't seem possible to have that criterion in the query itself. Instead you have to put it into the SQL in the Recordsource (or rowsource of a combobox or listbox). I've done an example in frmNames where I have added a combobox which allows you to choose whose records you see. The recordsource of the form is SQL with the criterion that the record shown is based on the combobox. The AfterUpdate event of the combobox just requeries the form.

I've also renamed the dummy table in the mid-tier database to be USysLink as that sounds a bit more professional than tblDummy and using USys as the start of the name makes it a hidden table thereby reducing the chance of someone deleting it or otherwise mucking it up by mistake.

Future ideas (assuming no show-stoppers found when you have a look at this):
1) Make the code into a COM/Managed add-in, which would show whether the database was in Design Mode on a menubar in 2003, but presumably, this means learning how to handle the ribbon - deep joy!
2) Think of a better name than Design Mode.

Have uploaded latest files, having done the above work and noticed the SQL in the one subreport was wrong in previous samples.


Attached File(s)
Attached File  WithMidTier3.zip ( 152.57K ) Number of downloads: 73
 
Go to the top of the page
 
+
datAdrenaline
post Aug 26 2006, 02:57 PM
Post #12

UtterAccess Editor
Posts: 16,516
From: Northern Virginia, USA



... Ok ... still no time to D/L ... but ... I just can't wrap my brain around why a mid-tier db is needed. In the method I proposed there are no objects that reveal the db pwd. Is is just to create one more barrier for a hacker to get through? ... or ... ??? ... Maybe the D/L's will help explain the need. The concept sounds cool ... I just want to understand its benefits, because at this point, my user base, which is all internal to my place of employment, will be plenty "scared off" by just the FE/BE approach I had posted. Can you shed some more light on the benefits of the MidTier ... If I do not implement, I STILL wish to be informed about the benifits of the methodology and the barriers that it can create.

Edited by: datAdrenaline on Sat Aug 26 16:05:10 EDT 2006.
Go to the top of the page
 
+
alancossey
post Aug 26 2006, 03:40 PM
Post #13

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Hiya Brent,
In your post of 08/24/06 07:26 PM you said that you could get at the data (via automation) which I took to mean that the queries and/or linked tables in your front end were capable of being got at. It may well be that there is little risk of someone using such a means to get at your data in which case you can ignore that problem. However, if you wish to avoid this risk, moving the queries to either the back end (i.e. mix in with the tables) or to a separate "mid-tier" database and using "IN 'C:\.....'" to show their location seems to overcome this problem since the queries and linked tables are no longer in the front end database at all.

I would prefer to have the queries in a separate database to the back end for ease of updating, i.e. the same rationale as for the classical front end/back end split of Access databases. Mind you, using the export/import routine I've mentioned, maybe that would suffice, i.e. have the stored queries and local tables just in the back end, thereby reducing the number of files back to two with no linked tables whatever.

Hmmm... I'll have to think about that.
Go to the top of the page
 
+
alancossey
post Aug 26 2006, 04:09 PM
Post #14

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



I've also had a check to see whether using OpenForm and OpenReport with their "inline" setting up of a temporary persistent recordset (!) would enable the queries to be left in the front end. However, if you leave them there, it seems that the recordset created by the open form or open report enables all the queries and linked tables in the front end "log in" to the data so that the data can be modified even after the persistent recordset created by OpenForm or OpenReport has been closed. It seems that the queries need to be moved out of the front end into either a mid-tier or into the back end itself. It is a pity as it makes it more complicated, but is not surprising I suppose.
Go to the top of the page
 
+
alancossey
post Aug 27 2006, 06:16 AM
Post #15

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Brent,
If, as appears it is the case, it is necessary to have the stored queries in a database other than the database in which the forms and reports reside, it doesn't matter at one level whether they are in a mid-tier database or the back end database. However, I would expect that in some situations at least it will be necessary to have them in a mid-tier database for the following reason. The recordsource for forms and reports consists of hard-coded SQL, i.e. it contains the location of the database containing the query or table (perhaps a linked table) from which the data is extracted. This means that, when developing the forms and reports, we would be altering tables and/or queries fairly frequently in that database. If that is the back end, it may well be that we mess things up for other users. If, however, we are basing our forms on a mid-tier database that resides, say, on our own PC, then we won't cause problems for other users. Mind you, it means an extra file to distribute, but that ought to be OK.

Lastly, though we've used a dummy table for setting up our persistent recordset, I would think we could use one of the system tables (MSys...) for this and not need a special table.
Go to the top of the page
 
+
robcooper
post Aug 27 2006, 01:51 PM
Post #16

Microsoft Corp UtterAccess VIP
Posts: 1,839
From: Bellevue, WA



There is certainly some interesting stuff being discussed on this thread. I find Brent's technique of binding to a dummy recordset that contains the password then opening forms that are bound to linked tables that do not contain the password to be very creative. This goes without saying, but to protect the integrity of the system you should use an MDE file in this case.

There has been a lot of discussion regarding the removal of ULS, but I want to make it very clear that ULS has not been removed for MDB files - only for ACCDB files. You can continue to edit users, groups, and permissions for an MDB file. If you have an MDB solution that used ULS it should continue to run as expected in Access 2007. If it doesn't, please send me a PM and I will investigate.

You can also create new workgroup files, but we removed the entry point for the Workgroup Administrator from the UI. You can launch it using code:

CODE
DoCmd.RunCommand acCmdWorkgroupAdministrator


Hope this helps. I'll have more comments coming soon...

Thanks!
Go to the top of the page
 
+
alancossey
post Aug 27 2006, 02:13 PM
Post #17

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Thanks, Rob. I agree with your comment about Brent's idea. It is one of those ideas that make you sit up and go, "Cor, that's clever!"
Go to the top of the page
 
+
datAdrenaline
post Aug 28 2006, 12:29 AM
Post #18

UtterAccess Editor
Posts: 16,516
From: Northern Virginia, USA



Rob and Alan,

Thank you both for the complimentary words regarding the concept of this technique! My intent was to ultimately create a "white paper" of sorts and post in the FAQ's or Code Archive ... but since I have not tested with A2007 ... and since Alan has bombarded my brain with more stuff!!! (FE/ME/BE) ... I have delayed a little bit. I have just D/L'd A2007 beta tonight and hope to test all this stuff out this week.

Rob:
>>> but to protect the integrity of the system you should use an MDE file in this case. <<<

I 100% agree ... (see step #9 in my first post ... I labelled it as optional, figuring it was like a developers choice thing ... basicially depending on the barriers you want to put in place!) ... Also, I eluded to MDE's with a comments later on the the post, but that was buried pretty deep!! ... so thank you for bubbling it up to the top! ... moving to MDE is, by far, one of the best steps towards protecting your application!

>>> I'll have more comments coming soon... <<<

I always look forward to gleaning info from your posts!!!
Go to the top of the page
 
+
alancossey
post Aug 28 2006, 11:21 AM
Post #19

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Have done a fair bit of tidying up.
Attached File(s)
Attached File  WithMidTier4.zip ( 81.67K ) Number of downloads: 110
 
Go to the top of the page
 
+
alancossey
post Aug 29 2006, 10:31 AM
Post #20

UtterAccess Veteran
Posts: 428
From: Norfolk, UK



Just checking to see whether this posts OK as I did a post this morning about different Automation characteristics in Access 2007 which seems to mean that the Automation problem we saw early on in this thread may not be a problem after all. Said post has not yet appeared.

Testing, testing, 1, 2, 3.
Go to the top of the page
 
+

7 Pages V   1 2 3 > » 
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 24th April 2014 - 10:18 AM