Tuesday, June 13, 2006

Is Hiding Databases A Good Idea?

In case you missed my previous discussions on Access 2007 security, Workgroup security (otherwise knows as User Level Security) will not be supported in the new ACCDB file format. This led me to think about other credible alternatives and one that springs to mind is Windows File Security. One idea that I discussed a while ago with a friendly chap called Bill was hiding files in Windows Folders. i.e.

Right click on a File in Windows Explorer and select the Hidden Attribute check box

What I would like to find out from experienced Access DBA's is "Is the hidden file attribute a good way to protect your database? "

To help me out on this, read the rest of the conversation that I had on this topic and then post your ideas and knowledge on this topic below.

Garry said "Bill, I would be really interested on the positives and negatives of hiding a database using Windows Explorer. If you are interested I will post your answers in my newsletter and ask the users for their thoughts."

Bill said "No problems that I have been aware of in hiding databases. In our state wide domain, users do not have the ability to see hidden objects or to turn that ability on. Administrators do the backups. Generally we store our back-ends on servers with nightly backup and additional off site storage or mirror to another site."

Bill "Our system default is that standard users cannot see hidden objects and cannot turn that on, hiding adds another level of security and can prevent malicious deletion of files potentially, although I have never run into this, accidental, yes. "

Bill "Other times, we may make a front end visible but hide the back-end to make it harder for a user to copy, just another level of security."

Please post your thoughts below so that readers can ascertain if this is a good idea or not.


Garry Robinson said...

Bill also said "I have code in my front ends to automatically set the hidden attribute on a backend automatically.

We have implemented the script file for several database systems that copies the front end to the temp directory of the local user and opens it there. Options are to do the local copy, to reference and mdw file, use specific script file. Called from a batch file that is in directory that users can run the shortcut but cannot view or edit the batch file or the paths set within.

Another method of implementing minimal security where folks were resistant to it was to use a batch file with a script file and grant admin to be a member of a specific user group with no password for admin. The user would not know that security was being used. If the backend were ever copied out, it could not be opened. "

Garry Robinson said...

-- Related Notes --

More on notes on Windows Security

There is also more on the topic in Chapter 12 of my book with supplementary notes on Windows XP here

Bill said...

I have avoided workgroup (user level) security in access for several years already in favor of my own internal security. No tears shed here seeing it go. Hidding files can be effective for your avarage users as well as changing extensions to something other than "mdb". This way your database is not easily recognizable even if it is found.

She Through Whom All Data Flows said...

I struggled with user level security for years and found it to be such a hassle that stopped using it.

My db's are all on a server which is backed up at least a couple of times per day. Nearly all of my users have run-time Access only, so it's hard to see how they could do much damage.

Each user has their own private copy of the db frontend, so if they do manage to screw up something there, I just send them out a fresh copy.

Maybe I'm too optimistic, but this seems secure enough in my world, and in about 10 years of running this way, I have never had any security issues.

Garry Robinson said...

In the world described by Miss Data Flow, hiding the backend database would be a nice simple solution that would stop most curious users. That is why I am interested in the approach of hiding the database.

Alan Cossey said...

Hiding a database can be a help, depending on how interested your users may be in wanting to hack around with files (or how bored they may be on a night shift and looking for something to do). However, doesn't this cause a problem if you want to analyse the data using Access, Excel or whatever. I realise you can take copies and use those, but if you want up to date data, then you need your users to have access to the back end.

Garry Robinson said...

In this case you are implying that your users have uncontrolled access to the back-end database. Why not get them to use a linked database with the necessary tables and queries. Security always has its penalties. Good point though

Alan Cossey said...

Having a linked database with the necessary tables and queries is a good solution in many cases if you are using workgroup security (and if you think your users are not going to get round that). However, with Access 2007 having no such security (.accdb format), I would be loathe to use it. All a user needs to do is open a linked table in design mode then right-click to get its properties, which include the location of the back end.
We would need the queries in the "front end" to be read only, which could be done by putting a one record table in the back end, creating a query in a midtier database based on a cartesian product of said one record table with the table you want your data from and then basing the query in the linked "front end" database to that midtier query. That way there would be no need for any tables in the front end and no need to fiddle around with Navigation Panel settings.

datAdrenaline said...

Hello Garry,

My name is Brent Spaulding (datAdrenaline on UtterAccess.com). Myself and Alan Cossey have had a discussion concerning this topic and we have created a concept that we (I) have coined vPPC. This thread:
logs our whole discussion. It is a good read, and both Alan and I feel the vPPC method is a solid way to implement protection strategies that are analogous to the database pwd level (vPPC/Standard) as well as a level of security that is analogous to the old ULS (vPPC/Enhanced). Please read and comment at your leisure ... that goes for all other who read this as well. Alan has some slides on a link that is refenced in the thread. Also, the thread has a bunch of uploads with samples of each implementation of vPPC.

Arni Laugdal said...

I am an MS Access programmer in Iceland and have used the User level security in some cases. It is complicated but works fine – very fine. Changing attributes and file security can never help us to protect the data. Hiding things is no security.
For example how can we limit access to individual forms and reports per user with file security? Creating front end databases per user is complex when users must have different access; and also limited security.
I expect that Microsoft will ask us to use the SQL 2005 Express edition for complex security projects. Hopefully will Access 2007 have a perfect upsize wizard to SQL 2005. Which will upsize the User level Security as well.
Thank you Gary, for a fine web.

Anonymous said...

Your article is very informative and helped me further.

Thanks, David

Anonymous said...
This comment has been removed by a blog administrator.
sink sink socks said...
This comment has been removed by a blog administrator.
Anonymous said...

Could make absolutely no sense of the Access security setup, so gave that a very far miss and wrote my own.
The problem with back-end Access databases is that even making your tables system objects, hiding files etc., using SQLServer or PostgreSQL, retrieving the data is no problem.
My feeling is that it is the Network administrators responsibility to setup server access for those who need it, and deny access to the folder to those who dont.