Nobody likes to be called a dummy by a dummy.

RANT:  Access security is crap

Well, I'm certainly not the first person to bitch about the MS Access security model, which was apparently designed by an insane person or persons.

I am the administrator of a database.  I have full rights to administer, modify, or do anything else to any object in the database. 

When I attempt to go into Design view for some queries, the system tells  me I don't have the appropriate permissions.  Yet I can go into the Security screens and give myself Administrator rights!

The guy who developed this database was a security freak and put so many passwords and other booby traps in this thing that I had to call him a couple of times after he left for the passwords that he forgot to give me, because there were so many of them.  He protected the app, the code, the ability to add References, even though this database can't be accessed except through Citrix (yet he set it up so that the users are running the mdb and not an mde executable where they can't get to the code anyway).

I'm thinking that somehow this database doesn't recognize me as the "owner", and somewhere this guy specified that only the owner had certain priviledges.  He obviously didn't care that he was a temporary consultant and someone else might take over the app one day.  Probably hoped we'd have to call him on a regular basis and throw him some business.
Permalink AMerrickanGirl 
July 25th, 2007 10:46am
Ok, weird.  I can't open the query in design mode, but I can write a VB routine that gives me the SQL text for the query.
Permalink AMerrickanGirl 
July 25th, 2007 11:10am
Well, the smart solution would have been to not develop it in Access at all.  Delphi, C++ and a lot of other languages come to mind as better choices.  So I wouldn't be surprised that the security model sucks.  You're using an amateur's tool and getting the expected amateur results.
Permalink Send private email Clay Dowling 
July 25th, 2007 11:11am
I saw some real wizards with Access, especially as you get into all the complex forms and reporting.

But on the security, lucky I never saw that part, I bet it was scary.
Permalink Bot Berlin 
July 25th, 2007 11:12am
"Well, the smart solution would have been to not develop it in Access at all."

No shit.  Not my call, though.

However, Access can perform on a decently professional (non-amateur) level if properly designed.

If it is simply used as a thin client front end with a SQL Server back end, you can use SQL user roles to control all aspects of data security and you can bypass the Access security model altogether.  That's what they should have done.
Permalink AMerrickanGirl 
July 25th, 2007 11:15am
>However, Access can perform on a decently professional >(non-amateur) level if properly designed.

Yes, if you use it to professionally organize your stamp collection.  I thought you work at a bank? They use Access for something where data security is a relevant concern (i.e. anything at a bank) !?

"Security freak" my ass, as if things are better protected if they're choke full of passwords. Can you blacklist people from not working for you again?
Permalink Send private email a2800276 
July 25th, 2007 11:22am
Don't worry, nobody here will hire this guy again.  The crazed bitch who hired him left at the same time he did.  No one misses either one of them.

Hey, it also runs really slow!  And the users hate it!
Permalink AMerrickanGirl 
July 25th, 2007 11:25am
Ok, so my workaround is, elicit the SQL statement in code, create a new query and save it with the same name as the old one.  Somehow it lets me do this.

Crazy.
Permalink AMerrickanGirl 
July 25th, 2007 11:27am
> Yes, if you use it to professionally organize your stamp collection.  I thought you work at a bank? They use Access for something where data security is a relevant concern (i.e. anything at a bank) !?

Augh shut the fuck up.  99.9% of what goes on inside any random business department is bullshit about as sophisticated as organizing stamp collections.  Even in banks.
Permalink Michael B 
July 25th, 2007 11:31am
In *nix, you can see who the owner of a file is just by typing 'ls -al filename', or 'ls -al' for the whole directory.

Windows gets way more hard to understand when permissions are involved, than *nix.  _Way_ more.  In fact, that's seems the hardest thing to learn to become an MCSE because it's so confusing and non-intuitive and overly sophisticated on Windows, and not readily apparent.
Permalink Send private email LinuxOrBust 
July 25th, 2007 12:36pm
Linux, how does this related to this topic?
Permalink AMerrickanGirl 
July 25th, 2007 12:37pm
>Even in banks.

Yeah, but compromised data or data integrity means alot more trouble at a bank. Even if it's just data about the stamp collection.
Permalink Send private email a2800276 
July 25th, 2007 1:25pm
> Linux, how does this related to this topic?

The topic is {technology,politics,copyright}.  Perfect opportunity to mention how Linux is superior.
Permalink Michael B 
July 25th, 2007 2:35pm
Linux has a way of completely going off on a tangent.  Which he did, above.
Permalink AMerrickanGirl 
July 25th, 2007 2:36pm
Funny, I thought the topic was "Using Access, Access Passwords, Why use Access at a Bank".

Linux, and linux users, and linux user passwords, seems awfully far from the conversation.
Permalink SaveTheHubble 
July 25th, 2007 2:37pm
Nonsense, SaveTheHubble!

The bank ought to throw out their existing workgroup investment, install Linux over all the Windows desktops, set up a Linux server running Apache+MySQL, and re-implement all of their business processes in PHP.

This way they'll avoid that permissions problem in that one Access application because they'll be using UNIX-style permissions.
Permalink Michael B 
July 25th, 2007 2:48pm
"In *nix, you can see who the owner of a file is just by typing 'ls -al filename', or 'ls -al' for the whole directory."

This doesn't apply to Access, because the problem isn't owning the whole file, it's the individual queries within the Access app that have messed up security settings.

Still haven't found anything on the web that explains what happened.  I checked every setting I could think of.  Thank god for workarounds.
Permalink AMerrickanGirl 
July 25th, 2007 2:49pm
Access user-level security can be tricky but if done right it is just fine. Sounds like this "security freak" knew just enough to be dangerous.

The permissions to objects are stored in the application mdb

The accounts are stored in the security workgroup mdw file

If you open the app mdb without the right workgroup
You should always create a new mdw file for each app and always start each app with the the /wrkgrp switch.

Otherwise Access loads the current default workgroup, usually  system.mdw - it also uses a special account called "admin"

That is probably allowing you to do things becuase it is a "special" account that is the same across workgroup - like a backdoor - I guess MS did that for compatibility.

One of the first steps of securing a database is removing all rights form the "admin" account.

Anyways I could keep going on and on about it...
Permalink Cheers 
July 25th, 2007 3:11pm
The Admin role doesn't have any rights.  And the app has been using the same MDW file all along.

The problem with Access security is that the interface to it is incomplete.  If you're ever bored one day and want to drive yourself insane, try it and you'll see.
Permalink AMerrickanGirl 
July 25th, 2007 3:20pm
++

The security model definitely does suck.

All atrocious Access avalanches amounting to avarice are actually articulations of the lowered barrier to entry coupled with what is truly a great deal of flexibility and raw power.

I'm sure I wrote a few turds in the years I was using it heavily, but I know I turned out some masterpieces too.

There are tools out there that will strip Access of its security entirely if you want to go the route of fixing Frankenstein...
Permalink Send private email JoC 
July 25th, 2007 3:30pm
Hopefully they'll dump this turkey soon and either develop something new or find a product that does what it is supposed to do.  Meanwhile, I'm not supposed to change any of the code.

This guy was a piece of work.  Here are a few of his signature moves:

He didn't set up referential integrity.  Orphan records abound.

In some cases, when users enter data, the daily data feed for the following day replaces the data they entered.  Doesn't update it, replaces it, leaving no trace of the previous work. 

Every error message has lots and lots of exclamation points.  "ERROR!!!!  The download cannot be run!!!  Inform your applications support person immediately!!"

He likes to pack as many functions calling functions calling functions into one statement as he can.  Some of them aren't even necessary from what I can see.

The opening form SLOWLY loads the entire 60K record table (every single open case) before the user even has a chance to specify what kind of records they want to see.  Then when they finally do, it requeries the entire table all over again, as well as the embedded subform that is also based on the entire table but just shows summary counts. Loading the screen can take up to a minute, which gets annoying when you have to switch between record types frequently.


Everyone hates this system.  Management hates it.  The IT department hates it.  The users hate it.  I hate it (although reading the code can be somewhat entertaining, as I search for the perfect entry to send to the DailyWTF.

And the saddest thing?  It's a brand new system.  Went live six months ago.  They paid this loser well over $100 an hour to write it.

A senior VP, the one who left, steamrolled this into production over the objections of the IT department.  So there's bad blood all the way around.
Permalink AMerrickanGirl 
July 25th, 2007 3:45pm
Sounds like a case where you could earn some major brownie points if you felt like making the alternative on your own time.
Permalink Send private email JoC 
July 25th, 2007 3:48pm
JoC, I would ... but if you had any idea of the amount of red tape involved in doing anything around here, you'd see why it isn't gonna happen.

From a technical standpoint, it would be pretty easy to redo this, sure.  In a smaller company, no problem.
Permalink AMerrickanGirl 
July 25th, 2007 3:57pm
I gotcha.

I was on easy street here for awhile. I'm separate from our 'development group' by several hundred miles. But I am starting to get a taste of the red tape now. I don't much care for it.

So if you can't change code, what do they expect you to do with/about it?
Permalink Send private email JoC 
July 25th, 2007 4:08pm
What am I supposed to do with it?

Add new queries and reports.  Provide the IT department with remediation plans, disaster recovery plans, and other thrilling activities.

Good thing this dog is only one part of my job.
Permalink AMerrickanGirl 
July 25th, 2007 4:22pm
"Provide the IT department with remediation plans, disaster recovery plans, and other thrilling activities."

Seems like reasonable activities, and your IT departement looks competent from your description.

Not that of a bad environment.
Permalink Rick Zeng 
July 25th, 2007 4:26pm
Our IT department is fine, pretty much.

This product was written outside of the IT department, and against their better judgement.
Permalink AMerrickanGirl 
July 25th, 2007 4:28pm
Oy nebbech. I feel for you. Even Denman doesn't deserve to manage an Access app.

Or, maybe he does.
Permalink LeftWingPharisee 
July 25th, 2007 8:21pm
Access apps get a bad rap.  Within certain strictures, they have a place.  The college I used to work at had an Access front end, SQL Server back end, and it did most of what they needed it to do for several years.  They finally dumped it because it didn't have a web interface and they wanted a package that had a financial aid interface to the federal award system.

This current one is simply a piece of shit, but this programmer could have written it in any language and it still would have sucked, because he's a crappy programmer.  It's not Access's fault.

It's a fairly simple CRUD app, and properly written would have served its purpose.
Permalink AMerrickanGirl 
July 25th, 2007 8:27pm
Passwording source sucks. MDEs are appropriate for new clients who get MDAs on system approval (and payment). 

Ever had the satisfaction of explaining this to a deadbeat asking for the password 6 months down the track to a system they never intended paying for and were happily using until they needed to make some mods?

I take it you paid this klutz.
Permalink trollop 
July 25th, 2007 9:05pm
Oh, they paid him.

We've pretty much got all the passwords.  It was like pulling teeth, though.
Permalink AMerrickanGirl 
July 25th, 2007 10:10pm
It's a decent front end to a real relational database, a pretty good reporting tool, but a shitty database itself.

Can you believe that Diebold runs its voting machines on Access?
Permalink LeftWingPharisee 
July 25th, 2007 10:16pm
It would be ironic if the Diebold voting machine fiasco was indeed due to crappy Access databases instead of political corruption.
Permalink AMerrickanGirl 
July 25th, 2007 10:22pm
Right.  You take Access, whack off it's head, and suddenly you have a cheap (in developer time) front-end for SQL Server.

"Went live six months ago.  They paid this loser well over $100 an hour to write it."

You see it from your point of view.  Or..maybe the co. did realize that it would be cheaper to go the quick and crappy way than to hire an entire development team, over a period of months to get it done, and keep them full-time. 

A co. may think "Oh, we need an Access guru!"  When in reality, they just need a data-application programmer "guru".
Permalink LinuxOrBust 
July 26th, 2007 2:34am
++ for improving it, naturally.  But you notice how it served it's purpose.  You are not a full-time programmer, like I am, I take it.  Access "frees you up" to dump the trashcans. harhar.

That's what I didn't like about being an Access programmer (okay, I didn't really have to dump the trash cans, but I did stuff envelopes and stuff like that).  And of course I know the politics that can come with a co. that settles on something like Access.
Permalink LinuxOrBust 
July 26th, 2007 2:37am
BTW, if I'm so wrong and off-topic, then tell me that that co. doesn't balk when it comes to paying for SQL Server licenses.

Now, getting back to LAMP and sh*t.  lol.
Permalink LinuxOrBust 
July 26th, 2007 2:50am
>The opening form SLOWLY loads the entire 60K record table (every single open case) before the user even has a chance to specify what kind of records they want to see.

This sounds like the idiot was using bound forms. Which would have got you fired at one place I worked at. Some of the tables had about a million rows added per month, so a bound form would have taken about 2 months to load (if you had made one touching that table).
Permalink Peter 
July 26th, 2007 8:59am
Yes, they're using bound forms.  Of course!
Permalink AMerrickanGirl 
July 26th, 2007 10:21am
I'm feeling like a Virginia Slims ad right now.  Hard to believe the highly touted Access programmer that wrote the program at that one job, pulled the whole file (db) across the network every time you ran a query, even if you just ran it right before.  Took like 3 1/2 minutes to run the "query" because no sql was used.

Like someone else here tried to point out, that's the problem with making easy tools, it seems like it makes it easier to dumb down the devs to whatever extent, over traditional programming.
Permalink LinuxOrBust 
July 26th, 2007 1:20pm
Or if there was a query, it was like select *, and then the column returns were calculated in VBA programming!
Permalink LinuxOrBust 
July 26th, 2007 1:21pm

This topic is archived. No further replies will be accepted.

Other topics: July, 2007 Other topics: July, 2007 Recent topics Recent topics