Access security explanation and explorer tool available

[Graham Wideman [Visio MVP]]

Folks:

Minor update to by recent posting.

1. I have mustered my version of "Access Security Explained". Does the world
need another one of these? Well... there are a few other references, which
are good on many points, and have some problems in other respects... hence
my effort.

http://www.grahamwideman.com/gw/tech/access/accesssec/index.htm

2. This effort is in conjunction with my PermExpl tool, which gives an
at-a-glance overview of users and groups membership, and permissions on
database objects. If there's another tool that does this I don't know of it,
and it has been highly useful in understanding how Access/Jet behaves in
this otherwise opaque area.

Description, screen shots and download here:

http://www.grahamwideman.com/gw/tech/access/permexpl/index.htm

Hope this helps others as much as it has me,

Graham

 

[Chris Mills]

Hi Graham,

Great utility on a quick try! Exe with no installation too!

It was a bit slow on 28,000 perms (18mins P450<g>) but never mind.
(Could changing the sort order not do a refresh?)

Question: it does give me an orphaned permissions count. Is there no way to
remove orphaned permissions? (I'd not have the foggiest now what the deleted
users were)
(does that waste space or is that being silly?)

Cheers
Chris

 

[Graham Wideman [Visio MVP]]

Chris:

Great utility on a quick try! Exe with no installation too!

Thanks for the feedback...

It was a bit slow on 28,000 perms (18mins P450<g>) but never mind.

P450 !?!? Was the spring fully wound up?

And I believe I do have a disclaimer for that

As I note in the docs, the primary goal of this tool was to shed light
on Access/Jet security behavior using test databases, rather than in
production databases.

If people really clamor for this to be used on 28,000 permissions, I will
have to investigate whether the delay is in the surveying of the
permissions, or the grid. (FWIW I'm using this particular grid because of
the vertical captions, and it has some sort and filter capability built in
.... with further effort could probably refine the speed issue... but, sigh,
low on time).

(Could changing the sort order not do a refresh?)

.... again, I'd have to investigate what it is that actually takes time. I'll
put that on the list.

Question: it does give me an orphaned permissions count. Is there no way
to
remove orphaned permissions? (I'd not have the foggiest now what the
deleted
users were)

Good question. Isn't there a REVOKE ALL type of statement in SQL? I wonder
if
Jet responds by deleting permissions even for users whose SIDs are not even
in
the current workgroup -- it would be nice if it did!

(does that waste space or is that being silly?)

I guess it depends how many orphans . Seriously I don't know... but it
wouldn't
seem much, since the properties like Permissions and AllPermissions are just
a single
long integer.

Regards,

Graham

 

[Chris Mills]

P450 !?!? Was the spring fully wound up?Nah. Run for years and never been cleaned-up. Also, never mind the memory I
had 1/2doz internet sessions open at the same time (which is clearly your

And I believe I do have a disclaimer for that

Yes you do, and I read that in your docs. I wondered whether to mention it. I
was just giving feedback and not complaining. I realise, as a developer, that
you appear to be retrieving "everything from the outset". For those "18
minutes" my PC appeared to near-lock-up, you might like to consider some
equivalent of Access "DoEvents" or perhaps a progress meter. Here I am
complaining already and you provided it for free...

As I note in the docs, the primary goal of this tool was to shed light
on Access/Jet security behavior using test databases, rather than in
production databases.

Yep, you did. But it's so good it will quickly be used so, IMHO. What did you
expect me to do but see what I could discern wrong with my production
databases?

If people really clamor for this to be used on 28,000 permissions, I will
have to investigate whether the delay is in the surveying of the
permissions, or the grid. (FWIW I'm using this particular grid because of
the vertical captions, and it has some sort and filter capability built in
... with further effort could probably refine the speed issue... but, sigh,
low on time).

I haven't worked out all the cryptic meanings of your utility.

28,000 perms may be a lot (it's also a 33mB FE).
The size is primarily due to stored "form images".
There are 500 or so "forms" at a rough count.(so "objects" is of that order)
There are 60 Users/Groups. That's reasonable given that that is NOT the same
as 60 simultaneous users.

That is probably at the high-end of what a reasonable FE should be, I imagine.
In fact I've cut it down by half by removing "obsolete govt forms" to a
separate FE (but still must be available).

... again, I'd have to investigate what it is that actually takes time. I'll
put that on the list.

Well, please don't think I'm complaining. Just, after retrieving all the data,
naturally I thought "is this view better than that one?" Maybe 18minutes was
an unfair test mdb.

Good question. Isn't there a REVOKE ALL type of statement in SQL? I wonder
if
Jet responds by deleting permissions even for users whose SIDs are not even
in
the current workgroup -- it would be nice if it did!

Well, I was asking you because I know when I'm out of my depth. Your utility
is the first time I have seen "orphaned permissions". It makes sense. The
question (perhaps) is, should one revoke all the perms from a User or Group
before deleting them? (it would seem the answer is YES)

I guess it depends how many orphans .

Another "silly" question would be if it wastes time rather than space (looking
through longer system tables). I guess the likely answer is "yes, but not much
relative to other things". Still, I like to keep a distributable db as clean
as possible. And your utility points out it is not necessarily as clean as I
think. You know, 28,000 must be a lot of records held in some mdb system
table...which have to be checked every time some object is accessed...

But now is the question I really wanted to ask you. Have you ever seen
"Nessie"? <guffaw>

(It really is a good utility for a different GUI and aspect. Mainly because
things hidden or forgotten stand out)
Regards
Chris

 

[Graham Wideman [Visio MVP]]

Chris:

You raise a bunch of good points, and no I was not at all taking
this as complaining. I'm exactly the same way when I have a new
toy

you appear to be retrieving "everything from the outset". For those "18
minutes" my PC appeared to near-lock-up, you might like to consider some
equivalent of Access "DoEvents" or perhaps a progress meter.

It's true that when PermExpl refreshes, it refreshes everything.
That's primarily because it doesn't know what might have changed since
last time. Even when editing a permission from PermExpl, it doesn't
know what implications there may be -- for example you delete a permission
on a group and a bunch of users are affected. (Part of the point was to
*find out* what gets affected.)

As for the "DoEvent" equivalent -- since I never have to wait for it, that
wasn't
a high priority .

It might be useful to be able to kill it if a long operation is in progress,
that's true. That potentially leaves its data in an incomplete state,
so at that point I think I'd have to clear it all. Hmmm, that's starting
to sound like a plan.

Anyhow, as I say, if there's a clamor, I'll be motivated/embarrassed into
making it speedier. You can consider yourself in the vanguard of the clamor.

I would however be curious to hear your report on performance with this FE,
on a machine built some time this millenium, however

Graham

 

[Chris Mills]

Ah, I just point out some "progress meter" might be usefull, as a compensation
for speed, I really don't like aborting things. Everyone knows the purpose is
just to keep the operator amused...

I once played a joke on a colleague. On an e-mail system prior to the one now
well-known, it was known to be flakey to abort it. So I intercepted the
"administrator's" command-line with an exact replica program of my own making.
Except my one just had a never-ending incoming count of false messages. I
think it was nearly an hour before he took the courage to abort it! So, you
can see why I don't like to abort things!

Anyhow, as I say, if there's a clamor,

Let others comment. I appreciate your effort.

I would however be curious to hear your report on performance with this FE,
on a machine built some time this millenium, however

That's just a test PC of mine. The actual FE is implemented on over 500 sites
on god-knows-what. More than likely many of the PC's of similar vintage (for
all I know-ever tried upgrading a customer?).

But the FE performance is of no relevance here of course. Your utility will
only be run by a developer in a one-time exclusive local test environment. In
that respect, I also can have no complaint about your Ute!

(The FE runs lickety-splick. It's very simple really. There may be 500 forms,
but of course most of them are just sitting idle (just taking up minor
disk-space) at any given time)

Cheers
Chris