ACLs

Have you ever tried to figure out why specific user have some permissions on sysobject?

Normally (e.g. w/o application permissions and MACL) Documentum use following rules to calculate permissions for specific user:

  1. sysobject owner or administator (docbase owner, member of dm_superusers group, or user with superuser privileges) have at least DF_PERMIT_READ, corresponding acl record – dm_owner
  2. members of dm_browse_all and dm_read_all groups have at least DF_PERMIT_BROWSE and DF_PERMIT_READ permissions correspondingly
  3. members of dm_escalated_read, dm_escalated_relate, dm_escalated_version, dm_escalated_write, dm_escalated_full_control have at least DF_PERMIT_READ, DF_PERMIT_RELATE, DF_PERMIT_VERSION, DF_PERMIT_WRITE, DF_PERMIT_DELETE permissions correspondingly

The SQL-script below takes into account these rules and in the most cases (e.g. w/o application permissions, dynamic groups, MACL) can efficiently determine why specific user have or not have some permissions on sysobject:

WITH usr
     AS (SELECT 'user' AS source,
                user_name AS accessor,
                user_name AS user_name
           FROM dm_user_s
         UNION
         SELECT 'dm_world' AS source,
                'dm_world' AS accessor,
                user_name AS user_name
           FROM dm_user_s
         UNION
         SELECT 'superuser' AS source,
                'dm_owner' AS accessor,
                user_name AS user_name
           FROM dm_user_s
          WHERE BITAND (user_privileges, 16) = 16
         UNION
         SELECT 'docbase owner' AS source,
                'dm_owner' AS accessor,
                user_name AS user_name
           FROM dm_user_s
          WHERE LOWER (USER) = LOWER (user_name)
         UNION
         SELECT 'primary group' AS source,
                user_group_name AS accessor,
                user_name AS user_name
           FROM dm_user_s
         UNION
         SELECT DISTINCT
                'group' AS source,
                gr2.i_supergroups_names AS accessor,
                gr1.users_names AS user_name
           FROM dm_group_r gr1, dm_group_r gr2
          WHERE     gr1.r_object_id = gr2.r_object_id
                AND gr1.users_names IS NOT NULL
                AND gr2.i_supergroups_names != ' ')
SELECT usr.user_name AS user_name,
       usr.accessor,
       source,
       DECODE (
          dm_acl_r.r_accessor_name,
          'dm_owner', GREATEST (r_accessor_permit, sys_s.owner_permit, 3),
          DECODE (
             usr.accessor,
             'dm_browse_all', GREATEST (NVL (r_accessor_permit, 1), 2),
             'dm_read_all', GREATEST (NVL (r_accessor_permit, 1), 3),
             'dm_escalated_read', GREATEST (NVL (r_accessor_permit, 1), 3),
             'dm_escalated_relate', GREATEST (NVL (r_accessor_permit, 1), 4),
             'dm_escalated_version', GREATEST (NVL (r_accessor_permit, 1), 5),
             'dm_escalated_write', GREATEST (NVL (r_accessor_permit, 1), 6),
             'dm_escalated_full_control', GREATEST (NVL (r_accessor_permit, 1), 7),
             r_accessor_permit))
          r_accessor_permit,
       dm_acl_r.r_accessor_name AS acl_record
  FROM dm_sysobject_s sys_s
       INNER JOIN dm_acl_s
          ON (sys_s.acl_name = dm_acl_s.object_name
              AND sys_s.acl_domain = dm_acl_s.owner_name)
       INNER JOIN dm_acl_r
          ON (dm_acl_s.r_object_id = dm_acl_r.r_object_id)
       RIGHT JOIN usr
          ON ( (usr.accessor = dm_acl_r.r_accessor_name
                OR ( (sys_s.owner_name = usr.accessor
                      OR usr.accessor = 'dm_superusers')
                    AND dm_acl_r.r_accessor_name = 'dm_owner'))
              AND sys_s.r_object_id = :r_object_id)
 WHERE usr.user_name = :user_name;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s