Denormalizing Authorization Tables For Easy Audit Of User Authorizations

[Former Member]

Here is the situation:

• You come to a new customer
• You don't want to change anything in already existing systems
• You don't want to depend on anything but your own scripts and tools.
• You want to get an overview of the security settings quickly.
• ...or you are simply curious...

SUIM is a powerful and flexible tool to determine the effective authorizations a user has. It has it's quirks and if you don't know about them, you may come to the wrong conclusions. However, if you have an audit program with hundreds of checks, executing SUIM manually is not feasible.

It would be great if you could run a simple SQL statement to determine which users are authorized to perform a certain activity. However, this is not as easy as it sounds. What we need would be a table, that contains the Username, it's assigned authorization objects and their values. If we had this, we would be able to easily retrieve all authorizations assigned to the user and look for the critical ones.

Unfortunately such a table doesn't exist (except the buffer tables but depending on the system configuration the buffers may not be up to date and get rebuild as soon as a user logs on). Let's see how we get there. These are the relevant tables:

• USR02: Contains the user logon information including passwords, lock status, validity date and so on.
• UST10S: Describes the single profiles and the authorization objects they contain
• UST10C: Describes the composite roles and which single roles they contain
• UST04: Connects Users with their profiles. It can either refer to single profiles or contain the names of composite profiles.
• UST12: the actual values of the authorization fields

And this is how they are related to each other:

Let's download the relevant tables into your favorite desktop database. There are tons of options on how to accomplish this. For example here: Import tables directly into Access from SAP using RFCs or this one: RFC_READ_TABLE data into MS Access (along with the table structure)

Let's start our process by creating a table that contains the username, profile name, authorization object and the name of the profile as it's stored in the user master record. The SQL statement may need to get adapted for your platform:

insert into denormalized ("MANDT", "BNAME", "PROFN", "OBJCT", "AUTH")

select b."MANDT", b."BNAME", a."PROFN", a."OBJCT", a."AUTH"

  from "UST10S" a,

       "UST04" b

  where b."MANDT" = a."MANDT"

    and b."PROFILE" = a."PROFN"

    and a."AKTPS" = 'A';

It creates a new table and inserts the values we need into it. The next step is to resolve the composite profiles into single profiles and add these values:

insert into denormalized ("MANDT", "BNAME", "PROFN", "OBJCT", "AUTH")
select a."MANDT", a."BNAME", c."PROFN", c."OBJCT", c."AUTH"
  from "UST10S" c,
       "UST10C" b,
       "UST04" a
where a."MANDT" = b."MANDT"
  and a."MANDT" = c."MANDT"
  and a."PROFILE" = b."PROFN"
  and b."SUBPROF" = c."PROFN"
  and c."AKTPS" = 'A'
  and b."AKTPS" = 'A';

Composite profiles in UST10C may refer to other composite profiles. That means, the field SUBPROF contains another composite profile instead of a single profile. That means, we need to add an additional level:

insert into denormalized ("MANDT", "BNAME", "PROFN", "OBJCT", "AUTH")
select a."MANDT", a."BNAME", c."PROFN", c."OBJCT", c."AUTH"
  from "UST10S" c,
       "UST10C" b,
       "UST10C" d,
       "UST04" a
where a."MANDT" = b."MANDT"
   and a."MANDT" = c."MANDT"
   and a."MANDT" = d."MANDT"
   and a."PROFILE" = b."PROFN"
   and b."SUBPROF" = d."PROFN"
   and d."SUBPROF" = c."PROFN"
   and c."AKTPS" = 'A'
   and b."AKTPS" = 'A'
   and d."AKTPS" = 'A';

We need to proceed and add additional levels until no further records can be found. As a next step we need to map the records in our table denormalized to the actual values in table UST12. A view would be the easiest and fastest:

CREATE OR REPLACE VIEW "V_USR_UST12" as

    SELECT denormalized."BNAME",

           "UST12"."AKTPS",

           denormalized."OBJCT",

           denormalized."AUTH",

           "UST12"."FIELD",

           "UST12"."VON",

           "UST12"."BIS"

      FROM denormalized INNER JOIN "UST12"

        ON denormalized."MANDT" = "UST12"."MANDT"

       AND denormalized."OBJCT" = "UST12"."OBJCT"

       AND denormalized."AUTH" = "UST12"."AUTH"

And there it is, the view that allows us to easily retrieve the the users that have a certain combination of authorization objects and values using SQL. This statement shows the usernames with access to SE16:

SELECT *

  from "V_USR_UST12"

where "OBJCT" = 'S_TCODE'

   and "FIELD" = 'TCD'

   and "VON" = 'SE16'

However, nothing is ever as easy as it seems... Let's say we have a user that is allowed to execute any transaction and thus has a "*" in the VON field. Our simple SQL statement from above will not return that user.

Another challenge is the query when an authorization object consists of multiple fields. Like for example S_DEVELOP with ACTVT=02 (change) and OBJTYPE=DEBUG.

To get around these issues you need to get creative with your SQL statements. It's not too hard and I don't want you to rob you of the fun of figuring it out :wink:

You may want to join the results to other USR* tables for example to select only unlocked users or retrieve first names, last names, departments, office locations and so on.