I was asked to provide a technique for the SQL user to access the special authorities granted to user and group profiles, and return the data in a non-list form for ease of reporting and analysis. To accomplish this request, I used the SYSTOOLS.SPLIT table function, but had to be careful to use the perfect split character (3 spaces), trim off bl…

Well isnt that special.sql

--
-- Complete review of special authorities granted to user profiles
--
select user_name, ordinal_position, ltrim(element) as special_authority
  from qsys2.user_info, table (
         systools.split(rtrim(special_authorities), '   ')
       ) b
  where user_name not in (select authorization_name
             from qsys2.authids
             where authorization_attr = 'GROUP');
stop;

--
-- Complete review of special authorities granted to group profiles
--
select user_name as group_profile_name, ordinal_position,
       ltrim(element) as special_authority
  from qsys2.user_info, table (
         systools.split(rtrim(special_authorities), '   ')
       ) b
  where user_name in (select authorization_name
        from qsys2.authids
        where authorization_attr = 'GROUP');
stop;