General Discussion on Database security

[Jings00]

This is not a specific problem, I was just hoping to get a bit of discussion going on the topic of file and data control.

Maybe some background is in order. I work in the finance department, so we have a fairly large number of databases and spreadsheets that do everything from the basic and mundaine, to complex systems for setting budgets and forecast adjustments. Most of these systems are not "multi-user" in the typical sense. I create one, then hand it off to someone to use. That person does have full access to change the way the database works, but I never considered that a risk - and setting up user-level security is such a pain.

Recently, internal audit commented that our methods are "curious", in that these databases are not in scope of the financial audit (due to their complexity), but since they are created and administered in finance and not by IT, they fall outside the scope of an IT business system audit. Previous audits would confirm inputs and outputs, assuming that if both where ok, then the process between was fine as well.

I now feel this is about to change. In the near future, our databases will be subject to at least a low-level IT style systems risk audit of some form. They will address many things I am sure, but security of the data and the process will an issue for sure. I am hoping to avoid making too big a blip on their radar if you will by having some basic controls in place before they start this process.

Does anyone else have similar issues -and how did you deal with them?
And for the IT people in this forum, how can I address the access risk, without implimenting some painful form of user level security?

Jings00

 

[The_Doc_Man]

And for the IT people in this forum, how can I address the access risk, without implimenting some painful form of user level security?

security of the data and the process will an issue for sure.

In the strictest sense, you probably cannot address Access risks without implementing some form of user-level security.

If you move to put everything in a single database, or perhaps a split DB with front end and back end as separate files, you run into a basic concept that permeates use of Access. From the outside, an Access DB is a single (or at most, dual FE/BE) object. From the inside, it is a multiplicity of different objects. If you want detailed control, you must implement that control at a level that can "see" the details. Windows security will NEVER EVER provide object-level permissions to internal Access .MDB or .MDE objects. It cannot see them as individual objects.

A strict PROCEDURES audit will almost certainly reveal that you have multiple candidate users for a database and these multiple candidates will fall into various categories of use. We of the Access community simply say that your users will have various roles. Orient your security setup to these roles BEFORE your start. Retrofitting will be such a bear if you don't start out right.

Windows simply CANNOT provide separate role-based protection for these users. It can only make the dichotomy between users and non-users of the DB. Access, however, CAN see the objects and CAN distinguish among the possible roles that you might need different people to play. It is a basic security concept that you implement security at the closest possible place it can be implemented. I.e. don't ask Windows to do what Access can do far better.

I will strongly advise that you play with the SEARCH function of this forum to find articles/threads on "Workgroup Security" and "Database Security." Do not confuse "Workgroup Security" with "Windows Workgroups." At the network level, "Workgroups" has a different meaning, and is something that has been replaced by Domains (and this replacement is a GOOD thing, trust me on this one...)

In summary regarding just how complex you can get, I can offer a couple of thoughts in guiding your hand.

1. NEVER EVER contemplate individual rights for your users. ALWAYS define roles. Then assign people to one or more roles. The odds are that you will have far fewer roles than you will have users. Assign permissions to roles and assign roles to users. Let the users get their Access permissions from the roles assigned to them. INCLUDING the ADMINS Role.

2. NEVER EVER trust users to do the right thing. Their jobs don't usually include a description of security requirements. Clerks are clerks, not network security gurus. Therefore, be aware of the issues that Access presents to you via workgroups. Things such as default Workgroups and the fact that a user in a default workgroup is automatically the Admin account for any DB. This topic IS addressed in the forum's threads.

3. Set up a regular and highly predictable backup, repair, and compression schedule. Then adhere to that schedule like SuperGlue. Give your users adequate warning. Search this forum for topics in "Startup Forms" and "Forcing Users to Log Out" as relevant to the issue of scheduled maintenance. Be aware that a sloppy maintenance schedule will only hurt the DB Admin in the long run, because users won't know whether or not they have to log off at a given time.

4. Work with your network admins and sys admins regarding the sharing requirements for the DB. You should have the DB in a folder as high on the directory tree as possible. For instance, if you use S drive for sharing...

4.a. The path through the DB folder should be top-level if at all possible, or as short as possible if not. E.g. S:\FinDB\TheDB.MDB - though sub-folders can be created if you need to use these sub-folders for related files. The main DB should be as high as possible, though, because of side-effects in evaluating permissions for complex "trees" of folders.

4.b. The permissions on the DB folder should allow full access to the folder for the users of the DB and no access to anyone else. However, do NOT implement this by denying permissions to the "EVERYONE" or "Authenticaed Users" dynamic groups in Windows. Instead, revoke all group permissions except SYSTEM and Adminstrators plus one group your Domain admin defines for you. These three Group IDs should have Full Control (SYSTEM, Administrators) or MODIFY (your special group) permissions. Then, when you add a new user, ask the domain admin to place that person in the special DB users group. If you need sub-folders, allow permission inheritance from the parent folder.

4.c. Access by itself does not provide auditing. If you want to do this "right" then you have to force all actions to occur through forms. I'll suggest one more search topic - Audit Logs or Audit Logging - as a way to implement reasonable accountability for what is done to your DB

5. Your "mindset" needs to be adjusted. If you are working in financial groups, you have to face up to issues in fiduciary responsibility. You are not handling your own money. Would YOU want someone to take a relaxed attitude towards YOUR money? Would you like it if a bank didn't protect your account from hackers, thieves, and looters? Similarly, if you are going to implement a serious, combined DB for financial work, don't take such a lackadaisical attitude with you. Leave it at home. (Sorry if this sounds harsh, but you asked for discussion and I don't hold back on such requests.)

 

[Shane_1]

I have a similar problem:

I am in control of the access database. I am NOT a systems administrator.

My boss wants me to set up security on the database.

The database is a Access 2000 database. I am working on a machine with access 2003. ALL of the other database users are using Access 2000.

I run the security wizard on the database, and on my machine it asks me for a password. BUT when someone opens it from another machine, they can get in without having to enter a password....

What am I doing wrong? How can I secure my database..........

Thanks for any help in advance...

 

[The_Doc_Man]

I run the security wizard on the database, and on my machine it asks me for a password. BUT when someone opens it from another machine, they can get in without having to enter a password....

Your problem, at first guess, is that you did not adequately protect your system against "default workgroup files" that your users are still using. Search this forum for topics with my name and "Workgroup Security" or "Database Security" or just plain "Security". I have addressed this exact problem many times.

Please get in the habit of searching these forums, folks. By now we have built up an admirable list of common topics that you can resolve without having to ask anyone. And the SEARCH function works fine!

 

[Pat Hartman]

Also while you're searching, make sure you find the reference to the Security FAQ published by Microsoft. It gives step-by-step instructions. I believe that the security wizard in some versions doesn't handle the admin account properly and that leaves your db unsecured.

 

[Jings00]

I will absolutely accept all forms of criticism and comments on my meathods (or lack of). My main purpose for starting this thread was to discuss the security choices available, not the mechanics of how to implement each choice.

.
Your "mindset" needs to be adjusted. If you are working in financial groups, you have to face up to issues in fiduciary responsibility. You are not handling your own money. Would YOU want someone to take a relaxed attitude towards YOUR money? Would you like it if a bank didn't protect your account from hackers, thieves, and looters.

I am aware of access security, but I believe the magnitude and cost the control should match the risk, and I think you have overstated the risk.
Couple of points to clarify this issue:

- I am NOT dealing with systems that handle disposition/control of assets - in any form. There is 0% risk of fraudulent behaviours through the databases I hand out. What I create are systems for information purposes only - the absolute highest risk would be information risk that would potential cause some form financial misstatement and decision error - and even that is a strain to believe.
- These systems are not really shared, current security involves creating a database/spreadsheet, placing it in a specific folder, and then handing folder rights to myself and 1 or 2 other people. Groups, if I had to create them would be myself with full rights and the other person(s) with slightly limited rights.
- The users (and myself for that matter) would have no reason to hack or otherwise change the data in a malevolent way - we would nothing at all to gain from it.
- The real risk here is process errors due to accidental changes, etc.
- I am already using forms for all these systems - in order to minimize the error potential - although the user has full rights in the database and could potentially do anything.
- Again, I how no idea what this IT auditor will think, he/she may see the low risk and walk away. I just don't know what to expect from this process since this will be the fist time. I hate going into these things blind.

As a financial guy, I am looking at this control issue from a cost vs. benefit perspective. Can I truly justify the resources to put all these databases into a fully functional controled environment like you suggest, when the risk is marginal - or is there a security 'Lite' option available for systems like these.

 

[ghudson]

Very well stated "Doc Man".

I am glad that somebody else has finally stated the need for assigning permission's to roles and not the individual users!

Access security takes only a few minutes to assign all the needed permission's to your db objects for each user group.

 

[Pat Hartman]

For security "lite" you can deliver .mde's. Just make certain to keep the original .mdb safe in case you need to make any modifications. In an .mde, there is no code so you can't open anything that can have code in design view. If your users create objects in the db's you make for them, the .mde won't work because they wouldn't be able to create any forms/reports/modules. In an .mde, the user still has access to tables, queries, and macros unless you have implemented Access security.

 

[The_Doc_Man]

First, some of my comments will seem a bit harsh, but something you said (and something you repeat in your reply) makes me want to address you from a system administrator or security administrator viewpoint. "I just don't know what to expect from this process since this will be the fist time." Well, I can do that for you. So pardon me if I take the role of Devil's Advocate.

I'll add that one of my jobs (and I wear more than one hat at work, which is why the plural usage) is as a teacher of Windows System Security for System Administrators. I happen to also know a tad about Access. So if you are going to deal with your IT folks for the first time, I'll give you the response you might expect from a conservative system administrator.

I am aware of access security, but I believe the magnitude and cost the control should match the risk, and I think you have overstated the risk.

I accept that as a possibility. However, you asked for discussion. Further, another question/comment below addresses the risk factors more directly.

There is 0% risk of fraudulent behaviours through the databases I hand out.

In other words, these data resources you hand out to others have nothing to do with the operation of your company at any time. You are just wasting the company's time by handing out meaningless, useless things. You hand them out to give the appearance that you are a necessary part of your company's operation. (Yeah, that is VERY harsh - but from a procedures audit standpoint, open your mind to the possibility that you will hear that exact comment, or at least a similar one.)

These systems are not really shared, current security involves creating a database/spreadsheet, placing it in a specific folder, and then handing folder rights to myself and 1 or 2 other people.

And how is this NOT a form of sharing? Sharing means "more than one person can use it." Trust me on this one, under Windows security terms, this IS EXACTLY a case of sharing.

The users (and myself for that matter) would have no reason to hack or otherwise change the data in a malevolent way - we would nothing at all to gain from it.

See earlier comments about doing something with no value. Also, it is my experience that the moment you begin to organize data, someone will want access to it. They will suddenly think this database is the greatest thing since sliced bread. It will GAIN value. As it gains in value, its potential for abuse increases.

I am already using forms for all these systems - in order to minimize the error potential - although the user has full rights in the database and could potentially do anything.

Nothing wrong here. I fully agree with using forms to minimize errors.

Again, I how no idea what this IT auditor will think, he/she may see the low risk and walk away. I just don't know what to expect from this process since this will be the fist time. I hate going into these things blind.

You are no longer blind. I just gave you a somewhat conservative, highly skeptical system administrator's viewpoint on IT processing.

As a financial guy, I am looking at this control issue from a cost vs. benefit perspective.

What is the cost if someone tampers with information you use to run the company? Even if only internal information?

What is the cost if some mercenary sells this internal information to a third party? Like a competitor...

What is the cost if you have a disgruntled employee who suddenly gets an urge to do damage? And this info is used to make decisions within your company. What is the value of time lost recovering from a bad decision made 'cause you got hacked? How many employees take marching orders based on decisions made from your data set?

Can I truly justify the resources to put all these databases into a fully functional controled environment like you suggest, when the risk is marginal

With distributed, individualized resources, if you need security at all then your cost of managing security is INCREASED. (More places to touch. More files to touch.) Ditto when addressing BACKUP of this critical data grouping.

With distributed, individualized copies of resources, if someone makes a wrongful change and sends back the updated copy, you have to consider ways to reconcile data differences. If this relates to your business model, even in a small way, you are inviting severe tests of the validity of your model. By consolidating, you can CONTROL the ability of your users to make changes that involve consolidation issues.

If you were trying to consolidate these data resources, you obviously saw some value to doing so. What is the value that you saw? Can you quantify it? Can you identify a cost savings by such consolidation? If not, you are premature in asking this security question anyway.

Now, here is a question I cannot ever answer 'cause it is site-specific. You are using some information-management tools to manage information that is used to run your company. Does your company have a security policy for such data? If so, the cost of not implementing security is that you are in violation of corporate policy. How much does THAT cost?

is there a security 'Lite' option available for systems like these.

Not really. The way to do "security lite" is to first and foremost limit the number of different roles you recognize. If you only have a couple, then it is not that big a deal to implement Workgroup Security.

 

[The_Doc_Man]

I'll also add that Pat has beaucoup experience in this arena. If she makes a comment, it is not usually wrong. We have different backgrounds and come from different directions, which is why we sometimes appear to disagree. But in truth, long-term DP professionals are often like that.

Pat's comment about .MDE files IS a way to do security lite, but the way I read your original question, I didn't think it would apply. She obviously did, and there's nothing wrong with that.

 

[Jings00]

Doc Man:

Your 'harsh' comments are fine (as long as its not you that steps through the door when the audit arrives) . I really need to see this issue from a system's perspective before this audit process even begins.

I won't bother quoting, but you mentioned issues of how these systems fit into the context of our corporate IT policies, and I think that is exactly the issue at stake here. Previously, IT policies applied to IT owned processes -end of story. I guess the question I struggle with is - who is responsible for the integrity of this process - ie - when does a user system cross the line and be subject to the more intense requirements of IT data procedures. I know that previously, ownership/creator of a process was the determining factor into what policies applied, but that rule is being questioned. But where does the line get drawn? We don't expect to trouble the salesguy who runs a userquery on his/her orders do we? but if he/she sets up a userquery with a parameter so other salespeople can use it then IT policies apply?
I think our whole company - including IT and even the audit group are struggling with this issue. I don't think we have rules (yet) to clearly define where to draw the line. This is new ground here - at least from the non-IT department perspective.

I have had real life discussions with others on this issue and it seems ever since the SOX 404 rules have come into effect, alot of companies are really shoring up their control policies - on all fronts. 404 has a heavy hand, so I must believe that there are many others struggling with these issues or worse.

I would be very interested into hearing from others that have walked this path already - they are out there somewhere - maybe getting net access from the public library after the pink slip {joking}

Pat: I like the idea of the MDE file, and i think it will work wonderfully for some systems I have.

Another idea that came forward: My current control process for some critical spreadsheets is to run a littel excel vba routine that compares the formulae in the working copy to a read-only master copy and then prints report of where the formulae have been altered. Is this type of thing possible in access - to compare individual objects in the working copy to a controlled master and give a list of objects that have changed?

 

[The_Doc_Man]

Is this type of thing possible in access - to compare individual objects in the working copy to a controlled master and give a list of objects that have changed?

Yes, it is possible. My oblique references to reconciliation from earlier are exactly that: the process of finding apparent changes in one DB that might conflict with changes in another DB.

Typically, you would want the "master" to be the .mdb file from which the comparison process is run. You want the master to control the updates it accepts rather than having a copy externally impose such changes.

You would have to somehow make the copied .mdb file known to the master (perhaps by using a FileOpen function, and Pat Hartman supplied a perfectly good thread for this, so a Search should work. Or look in the sample DB files associated with this forum.) The master would then open the copy .mdb as a second DB and could then run some code (perhaps) to find differences. You would probably need a secondary or working table to hold a list of the differences. Another way might be to import the copied DB's tables to temp tables in the master and run some intelligent queries. We can explore that later if need be.

I see from your last comment that you are basically struggling with new standards, rules, concepts, etc. I don't know this SOX thing, but that doesn't surprise me. I'm in a USA Government (actually, Dept of Defense) environment, so the standards I face every day never have only three letters in their abbreviations. Or if we DO have only three letters, they mean something so obscure that it doesn't help to know the letters. When you start getting into government-class "process management" rules, you have no clue as to which hoop you will have to jump through next.

I understand that grasping new rules and trying to figure out how they apply to you can be a mind-altering (mind-blowing?) experience. It is in some aspects almost like Dorothy stepping out of the farmhouse into Oz in that old Judy Garland movie. And sometimes the changes you face make about as much sense as did Oz. There, my friend, I can truly sympathize with you based on my long experience with such situations.

 

[Jings00]

Thanks Doc Man, I am definitely interested in some of these ideas. I have some options to consider for trying to tighten things up here. I do some forum serching on the concept of doing database object comparisons, and then start another thread if I have any questions on that subject.

One specific question about creating the MDE file: would it make any sense to create a FE/BE type set-up, then make an MDE of the FE? or would the creation of the MDE remove the need for moving to a FE/BE altogether?

PS: sometimes we all get a little carried away with acronyms. SOX is Sarbains Oxley - a new US regulation applying to publicly listed US corporations that addresses corporate governance issues - fallout from the Enron fiasco. It has different implications for different people, but for finance and IT, it means way more rules and restrictions.

 

[The_Doc_Man]

Still not familiar with SOX but well aware of Enron. My nephew by marriage worked for Enron, had a lot of employee-option stock, and now turns beet-red if you even MENTION Enron in his presence. We worry 'bout his blood pressure a lot. We talk (USA-style) football when he is around, or maybe just play some poker. Though he's not good at THAT, either.

OK, here's a different way of stating my viewpoint. Pardon me if I put on my "old professor" hat for a moment.

Your department is going to face issues derived from an environment where trust is harder to find. This is usually handled procedurally by having many checkpoints along the way. Perhaps you can think of checkpoints as having "points of review by a third party" or "automated ways to verify actions." You will perhaps need to show some verifiability in what you do. Some questions will surely be asked.

First, what does your process contribute to the operation of the company? This quantification is both NECESSARY and SUFFICIENT to answer the next question, which is the key to this whole exercise.

Second, can the company totally do without it? If the answer is YES then you have all the answers you need. Presuming that the answer is NO (either qualified or unqualified)...

Third, can your process or a similar alternative be implemented in a way that assures verifiability at selected steps of the process?

Fourth, can this process be FORMALIZED in some way that includes a written, documented "Standard Operations Procedure" to define actions at every step of the way? This is where you would include a discussion of how you reconcile differences among shared/distributed elements. Not how you would solve it in Access, but how you would solve it on paper. This is because there is an "old programmer's rule" that says "if you can't do it on paper, you can't do it in Access."

Fifth (and here is where Access comes in), can this process be AUTOMATED in such a way as to PRECLUDE variations from S.O.P.? I.e. don't LET a person make a mistake in what to do next?

Sixth (and again, Access might help here), can this process be AUTOMATED in such a way as to accurately record actions related to this facet of your company's operations? And also to guarantee proper math is done at every step of the way? This is when you talk about auditing, intermediate reports for verification, getting signatures or other forms of approval, etc.

Seventh (Access can help here too), can this process be PROTECTED in such a way as to guarantee that whatever value it provides has not been compromised? This is where security issues come into play.

Security wonks think along lines such as this: C I A A... Confidentiality (of data), Integrity (of data), Availability (of data), Authentication (of data input or changes). You want to avoid D A D D... Disclosure (of data to unauthorized viewers), Alteration (of data), Denial (of service), Denial (of authority for input or changes). CIAA is GOOD. DADD is BADD. (Sorry...)

The Enron scandal addresses a basic question. In essence, how can we make our procedures trustworthy in an atmosphere of reduced trust? The facets of that trust (components of trust?) include the issues of the previous paragraph.

 

[Pat Hartman]

I don't disagree with Doc at all about the importance of security. In fact I intend to bookmark this discussion so I can refer people to it. I only added the .mde option as a "lite" solution to be used with the network security that is already in place. Access (Jet really) security secures OBJECTS rather than data. I frequently find that instead of it or in addition to it, I need to secure data. The only way to do that with Jet security is to create separate forms/reports/queries/etc. for each set of data. This becomes onerous in a very short while so I implement my own internal security that controls what data each person has access to.

I think that there is a time and a place for everything. As long as your database is always placed on a share with limited access, that will be your first line of protection. If you want to just keep people from viewing/modifying your objects, you can use the .mde (make sure you have a saved copy of the .mdb because you can't go back) and the disable the bypasskey method to keep people from looking under the covers. If you need to segregate objects so that some users have access to some objects and others have access to other objects, you have no alternative but to use Jet security.

All databases should be split and for further security, the be can be placed in a separate directory. This will make it harder although not impossible for someone to simply download the .mdb or .mde and make off with it. The first time they do it, they won't get the data. They will then have to find the data. If you have the .mdb/.mde fairly well locked down with the disable bypass key or Jet security, it will be quite difficult for the thief to link the two separate databases back together to regain access.

Access needs to be treated, security wize, more like a word document or spreadsheet than a typical business application. The reason is that the db's data is either embedded (in a 1 file application) or easily isolated (in a 2 file application).

 

[Jings00]

Thanks Doc Man and Pat for all the input and advice. Two things have happened in the past few days:
1) I am yet to hear a date on the IT audit, but they have asked for a proceess map, with detailed manual and automated control points.
2) I have also been contacted by our site IT manager, who has offered her services in helping us prepare.

I will repost updates as I get them. Thanks again.