Report of database object permissions

[Guest]

Is there a way of printing or exporting the permissions assigned to database
objects for each user group? I am looking for an easier way to review and
confirm the permission settings. I have some databases with over 700
objects. Trying to use the Access Security Permissions window, which does
not sort objects alphabetically, is very tedious.

 

[Rick B]

Tools/Analyze/Documenter.

You would have to select all objects, then go into OPTIONS and set it to
display only the permissions.

In my experience, this takes a long long long time to run. Not even sure it
would be practical with 700 objects.

Yoiu might want to run it for four or five and see what you think.

Rick B

 

[Jeff Conrad]

The built in Documentor can do this for you.
Details can be found here:

http://www.access.qbuilt.com/html/security.html#DocSecPerms

Be warned, the Documentor is not "paper-friendly" and
will generate very long reports!

A better way is to use my free Access Security Add-In
which can print some nice security-related reports.
It's primary purpose is to transfer permissions from one
database to another, but the secondary purpose is to
make some nice reports of object permissions and
User/Group reports.

You can find the download file here at the top of this page:

"Sandra Daigle Permissions Wizard"
http://www.daiglenet.com/msaccess.htm

Read the Usage Guide before installing and using!

 

[Guest]

Your add-in is just what I needed. The detailed information report makes it
very easy to see which user group has which permissions.

Thanks.

 

[tw]

I know I'm not the original poster of this question, but I have been trying
to run the documentor for permissions. I log in as the administrator, run
the documentor having it set to display only permissions. I get an error
message "Record(s) cannot be read; No read permission on 'AccessLayout' What
can I do to give a user in the admin group permission to read the
'AccessLayout' which isn't even in the objects window of the security
permissions set up screen?

 

[Jeff Conrad]

in message:

Your add-in is just what I needed. The detailed information report makes it
very easy to see which user group has which permissions.

Thanks.

Excellent!
I'm glad you found my add-in useful.
Thanks for the feedback!

 

[Jeff Conrad]

in message:

I know I'm not the original poster of this question, but I have been trying
to run the documentor for permissions. I log in as the administrator, run
the documentor having it set to display only permissions. I get an error
message "Record(s) cannot be read; No read permission on 'AccessLayout' What
can I do to give a user in the admin group permission to read the
'AccessLayout' which isn't even in the objects window of the security
permissions set up screen?

Sounds like you are using the Access Documentor and not my add-in correct?
Just want to check.

Do you have a table (possibly hidden) or other object in the database
called AccessLayout? If you have no such object in your database now,
did you at some point? Try compacting the database before running the
Documentor to remove any residual temporary objects remaining in
memory.

 

[Joan Wild]

I'm glad you found my add-in useful.
Thanks for the feedback!

Hi Jeff, what was your approach to documenting the permissions? I cannot
fathom why you need an 'unknown' permission.

 

[Jeff Conrad]

in message:

Hi Jeff, what was your approach to documenting the permissions? I cannot
fathom why you need an 'unknown' permission.

Hi Joan,

That question is covered in the FAQ section of the Usage Guide.
Question #10 on page 12 discusses why that option is on the reports.

Are you asking how *exactly* I was able to get a list of them all?
Simple: hours and hours of work.
Serious.

I spent WAY more time on the reports than I did on the transfer areas!

In essence what I did was take an existing workgroup file and make a
new database file with only one object for each area (table, query, form, etc.).
This workgroup file had three custom groups (in addition to the Admins and
Users groups) and three custom users (in addition to my SuperUser and Admin).
I would then go to the Users and Groups Permissions screen on the Security
menu. In the Permissions area I would start with not checking any boxes
for the table (no Permissions) for all the Groups and Users and then run the
"Permission IDs Only" report for both Users and Groups. I made a chart
of the Permission checkboxes on a piece of paper and then would write down
the Permission number for each group and user across the page.

I would then continue this process by checking one box for that table for all
Users and Groups and then re-run the report. Again, I would write all this information
down. Now as you are aware, depending upon what checkboxes you tick in
the Permissions area, some other checkboxes with become selected/unselected.
So I had to VERY carefully make sure I was getting every conceivable checkbox
combination. Follow me? This was absolutely exhausting work let me tell you!

I had to continue this process for every different object in the database. Now I would
like to *believe* I accounted for every conceivable Permission possibility, but it
is quite probable I missed something. So the "Unknown" field was to account for
that and be a "Red Flag" that a different Permission ID was found. As stated in
the Usage Guide, people can report any Unknown ones here and I will make
the changes necessary for subsequent releases.

Once I had all the numbers themselves, I was amazed at the sheer volume. I
even posted a question about it here for Graham Seach. I was trying to make
sense of the numbers and was confused by looking at a table listing in his book.
The real *fun* came trying to code all these possibilities!! You would not believe
the HUGE Select Case and If/Else/End If coding involved in generating that report!!
It is MASSIVE!!! From memory I think I found 57 different Permission ID values
for just the tables alone! And remember I had to record these values for each
user and groups as well. That part of the code was absolutely mind-numbing for
me sometimes and I just had to step away.

Does that answer your question?
If you are really, really, really interested in the code I can send you the MDB
source code file.

 

[Joan Wild]

That question is covered in the FAQ section of the Usage Guide.
Question #10 on page 12 discusses why that option is on the reports.

Hey, I did RTFM ;-), which is what prompted my question. I gathered that
you were doing something 'unusual'.

Are you asking how *exactly* I was able to get a list of them all?
Simple: hours and hours of work.
Serious.

I spent WAY more time on the reports than I did on the transfer areas!
snip

So I had to VERY carefully make sure I was getting every conceivable
checkbox combination. Follow me? This was absolutely exhausting work
let me tell you!
snip

The real *fun* came trying to code all these possibilities!! You
would not believe the HUGE Select Case and If/Else/End If coding
involved in generating that report!! It is MASSIVE!!! From memory I
think I found 57 different Permission ID values
for just the tables alone!

Good grief! I don't think you realize how easy it is to get the permissions
in 'human sensible' terms.

You're basically interested in whether the user or group has a certain
permission:

For example on tables/queries
dbSecRetrieveData means they can read data.

All you need to do to determine if this is true is to do is a bitwise
comparison on the permissions value with dbSecRetrieveData to determine if
it is set or not.

If SomeTable.Permissions And dbSecRetrieveData = dbSecRetrieveData
then they can read data

So all you need to do is loop through the documents, loop through the
accounts and check for each of the constants of interest.

I'm pretty sure you have the ADH. There is sample code in the security
chapter.

Does that answer your question?
If you are really, really, really interested in the code I can send
you the MDB source code file.

Sure, I'll have a look if you like.

 

[Jeff Conrad]

in message:

Hey, I did RTFM ;-), which is what prompted my question. I gathered that
you were doing something 'unusual'.

I was just checking.


I'll assume that the F is for "Fantastic."
;-)

Good grief! I don't think you realize how easy it is to get the permissions
in 'human sensible' terms.

You're basically interested in whether the user or group has a certain
permission:

For example on tables/queries
dbSecRetrieveData means they can read data.

All you need to do to determine if this is true is to do is a bitwise
comparison on the permissions value with dbSecRetrieveData to determine if
it is set or not.

If SomeTable.Permissions And dbSecRetrieveData = dbSecRetrieveData
then they can read data

So all you need to do is loop through the documents, loop through the
accounts and check for each of the constants of interest.

Ohh trust me, I am well aware of all of that. Serious.

If you are wondering why I did it my way, it was really just for fun. Well, Ok,
it *started* out as fun. <g>

Using the code Sandra and Michka have as a base I really was just curious
about these numbers and their interactions with the permissions. So I started
to play with them for fun and see if I could fill an existing table and report with
this information. The table and report I had already created for another project
a long time ago so I was really pumped when I could see that through some
code I could get this to work just as I envisioned. So naturally being "me" I
thought, "Well this won't take long to do." Yeah, sure. <sg>

So when all is said and done, could it have been done differently and maybe
even more efficiently? Oh sure. But lessons learned. I learned a lot from the
project, expanded my knowledge, provided a useful tool that can help others,
and I am quite pleased by the end result. So, "it's all good."

I'm pretty sure you have the ADH. There is sample code in the security
chapter.

Of course I have the ADH; I sleep with it under my pillow.
There is also code in Graham's book as well which I extensively studied.

Sure, I'll have a look if you like.

Ok, I'll send along a copy today.

 

[Joan Wild]

So when all is said and done, could it have been done differently and
maybe
even more efficiently? Oh sure. But lessons learned. I learned a lot
from the project, expanded my knowledge, provided a useful tool that
can help others,
and I am quite pleased by the end result. So, "it's all good."

Well OK, but I ran it once on one database and got a lot of 'unknowns' back,
so I don't think it's all good . Also there were some that listed a
permission but no check, even on unknown.

 

[Guest]

Late yesterday and this morning I started using your add-in for its stated
purpose, to transfer permissions to a new database.

In my testing so far, the add-in works OK except that, for the the Database
and New object selections (New table, New form, etc.), it gives full
administer permissions to the Admins and Users usergroups. That gives me
only a few things that I have to change, but I am wondering if I am doing
something wrong.

Bruce

 

[Jeff Conrad]

in message:

Well OK, but I ran it once on one database and got a lot of 'unknowns' back,
so I don't think it's all good . Also there were some that listed a
permission but no check, even on unknown.

See, I told you I would miss something.

I would be very interested to hear what the numbers are that it found.
Was it a user or group? What checkboxes were ticked in the Permissions
dialog box for that object?

When you say, "Also there were some that listed a permission but no
check, even on unknown." do you mean there is a number listed, but
nothing checked? That is to be expected actually in some cases through
my testing.

I have no problems making additions/changes to the add-in Joan.
If you think I can make it better by doing "this" or "that", I'm all
for it. Since releasing it, I have even thought of some enhancements.
One in particular involves running a report against the default Admin
User and Users Group. In most cases of course we want both of
those entities to have zero permissions. So I would run this report
to check to see if just those two entities have permissions to anything
in the database. New users (and even experienced ones) could run
this report to "check" if they have missed anything. Follow me?
I think that would be a cool addition.

 

[Jeff Conrad]

in message:

Late yesterday and this morning I started using your add-in for its stated
purpose, to transfer permissions to a new database.

In my testing so far, the add-in works OK except that, for the the Database
and New object selections (New table, New form, etc.), it gives full
administer permissions to the Admins and Users usergroups. That gives me
only a few things that I have to change, but I am wondering if I am doing
something wrong.

You are not doing anything wrong Bruce; I have not incorporated that functionality
into the add-in at this time. If you look on Page 7 in the walk-through section I
mention this on Step 14. I did not, however, mention the new object selections and
I probably should have. I will consider adding this feature.

 

[Joan Wild]

See, I told you I would miss something.

I guess that's what I'm trying to get through. Why wouldn't you use proven
methods to get the correct information, rather than going the route you
have - it's highly likely that you'll never get all of it or all of it
necessarily correct.

The results I got was running it on one database with just 1 table/2 queries
(email me if you want the details). I think the tranferring of permissions
from one database to another works fine, but I don't have a lot of
confidence in the documenting in the way you have, but it's your add-in, so
have fun.

If you think I can make it better by doing "this" or "that", I'm all
for it. Since releasing it, I have even thought of some enhancements.
One in particular involves running a report against the default Admin
User and Users Group. In most cases of course we want both of
those entities to have zero permissions.

Good idea, but not all cases, so make it optional.

 

[Guest]

Now that I know about it, this is not a big deal. The add-in is a whole lot
easier than trying to manually reproduce the permissions. Making those few
corrections is now just one more item on my deployment checklist.

Thanks again.

Bruce

 

[Jeff Conrad]

in message:

The results I got was running it on one database with just 1 table/2 queries
(email me if you want the details).

Already did.

I think the tranferring of permissions from one database to another works fine,
but I don't have a lot of confidence in the documenting in the way you have,
but it's your add-in, so have fun.

I sense you're holding back Joan, come on tell me how you really feel.
;-)

As I said, if you think I can improve it, I do not have a problem making
changes. However, I may require your assistance. Are you willing to do
that?

Good idea, but not all cases, so make it optional.

It would not "do" anything with the permissions for those two entities,
it would just simply display a report listing any permissions for the
Admin User and Users Groups. Like a "Hey, you missed these things,
just thought you might like to know."

 

[Joan Wild]

in message:


Already did.

I tried to answer but am getting bounces back.

 

[Jeff Conrad]

I tried to answer but am getting bounces back.

I got it.