Permissions Report Pecipitates Predicaments

In a previous post a user made the following comment:

talking on the topic of permission how I can generate a report per screen that will show only the user that have at least one permission and not showing all users wit no permission at all!!

I’ve always been annoyed by the report aspect of M2M user permissions. The report is just awful. One of the primary tasks I’m asked to handle at larger clients is to audit their permissions, make recommendations regarding them, and the report is woefully inadequate for this task. In fact, at my current employer, even after removing deleted companies from my Util database using the method I wrote about, the report still requires over 30 minutes for detailed user permissions on the database. This ties up my M2M client during that time, taxes the server, and generally irritates me. In the past, I have exported it to CSV, then imported it into Access, and wrote Crystal Reports on it. A CSV has to be used or you risk exporting too many rows for Excel to handle.

Anyway, in answer to his question I tried adding an advanced filter to the report, filtering out all the people who had no permissions to the particular screen. It would be nice to be able to do so because as it is now, if I run the report for an individual screen I still get 16 pages of user names, 90% of which have no permissions to that screen anyway. The process is tedious and involves lots of time with a highlighter.

The filter should look like this:

Don’t bother using that however, it doesn’t work. In fact, no additional filters work on that report. I have always wanted to have easy access to permissions, but the user names are encrypted in some tables. Furthermore, if you look in that table it is obvious that detailed user permissions aren’t kept there anyway. Take a second to look at your user permissions table. Please read my standard disclaimer before using any code from this site.

Open FoxPro and type the following:

OPEN DATABASE X:\m2mdata\util\util.dbc
USE utacc
BROWSE FOR fccompid = "01" 

Remember to replace X with your database drive letter, and substitute the company number you wish to view for fccompid. This will show you the assigned user permissions in M2M for that company, but these are different from the detailed permissions required for every screen/report/etc.

For example, if a new company is created only one user who has rights to the system and by default has root access. Only one record should show up in the table instead of a record for every individual screen or report for which they had access. However, when a new company is created, the creator is restricted to View rights to the Form Customizer (FCUST) screen. I have no idea why M2M restricts that screen, instead of several screens which are more important/dangerous, but it does.

Therefore, M2M has a very complicated algorithm to calculate a user’s detailed permissions based upon what is in that table. In this case, it determines that I should have all rights, minus the FCUST screen. Below are the results when I remove rights (all but View) to the Sales Module from myself.

Now, I’ll grant myself Add, Edit, and Delete rights to the Sales Order Screen (SO).

From those records M2M determines that I should have full rights to everything, minus the FCUST rights, minus any rights to the Sales Module, except add rights for the SO screen. I think you’ll realize just how complex this algorithm must be.

Now look what happens when I simply add a new user. Remember that all new users have root access by default, which is a terrible security flaw in my opinion. However, something very unusual occurs:

It explicitly removes rights from VBA Editing. I thought perhaps that setting resulted from a new employee not having "VB Developer" checked on the User Management Screen, but it doesn't. I have no idea why this happens either. However, when you add a new Administrator who will work with VBA, you must manually give them permissions to the screen.

Since we're experimenting, let's see what happens when I revoke all of Test's permissions.

M2M replaced all of the records for Test and replaced them with one which revokes all permissions. Finally, watch what happens when I grant all permissions back to test.

Notice how Test simply has all permissions and none of the records included previously like VBPROJ and FCUST are referenced? I spoke with M2M Support about my findings and they admitted that they had no idea why M2M behaves this way.

Well, Mr. Wizard, what's the point?

The point is that M2M is inconsistent in it's permissions table. The point also is that M2M's algorithm still knows how to contruct permissions for each user for every object in M2M from what little is recorded in the UTACC table. If you want to audit permissions in M2M, you essentially have three options.

First, you can continue to use the terrible User Report (RPUSER), wait forever each time it runs, and go through many irrelevant pages trying to look for the records which matter.

Second, you can attempt to export the user permissions using the RPUSER, import them into Access or something similar, and write reports based on them. This process gives you better access to your data, but is still time consuming.

Three, you can stay tuned because I'm working on a solution to this problem using SQL Server Integration Services and will share it with everyone when it's done.

P.S. Are you folks growing tired yet of my "P" alliteration theme in regards to permission problems?

4 comments to Permissions Report Pecipitates Predicaments

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>