Daniel Berlin on Security Insight on SAP security, development stuff… and all the rest

15Apr 12

Source – ZS_TABLES_WITHOUT_AUTH_GROUP

*&---------------------------------------------------------------------*
*& Report ZS_TABLES_WITHOUT_AUTH_GROUP
*&---------------------------------------------------------------------*
*& Purpose: List tables not assigned to a table authorisation group
*& Author : Daniel Berlin
*& Version: 1.1.2
*& License: CC BY 3.0 (http://creativecommons.org/licenses/by/3.0/)
*&---------------------------------------------------------------------*
 
REPORT zs_tables_without_auth_group.
 
TABLES:     dd02l, tddat, tdevc.
TYPE-POOLS: icon, slis.
 
DATA: gv_ucomm    TYPE sy-ucomm,
      BEGIN OF gt_tablist OCCURS 0,
        tabname   TYPE dd02l-tabname,  " Table name
        ddtext    TYPE dd02t-ddtext,   " Table description
        cclass    TYPE tddat-cclass,   " Authorisation group
        bezei     TYPE tbrgt-bezei,    " Auth. group description
        hasdata   TYPE icon-id,        " Table contains data
        clidep    TYPE icon-id,        " Table is client-dependent
        protokoll TYPE icon-id,        " Logging of data changes
        contflag  TYPE dd02l-contflag, " Delivery class
        devclass  TYPE tadir-devclass, " Package
        tabclass  TYPE dd02l-tabclass, " Table category
        alvbox    TYPE c,              " ALV checkbox status
      END OF gt_tablist,
      gv_total    TYPE i.
 
SELECTION-SCREEN: BEGIN OF SCREEN 101 AS SUBSCREEN.
SELECT-OPTIONS: r_table FOR dd02l-tabname.
SELECTION-SCREEN: SKIP,
                  BEGIN OF LINE,
                  COMMENT 1(33) n_count.
PARAMETERS: p_count AS CHECKBOX USER-COMMAND cnt.
SELECTION-SCREEN: END OF LINE,
                  BEGIN OF LINE,
                  COMMENT 3(31) n_empty.
PARAMETERS: p_empty AS CHECKBOX.
SELECTION-SCREEN: END OF LINE,
                  END OF SCREEN 101.
 
SELECTION-SCREEN: BEGIN OF SCREEN 102 AS SUBSCREEN.
SELECT-OPTIONS: r_autgrp FOR tddat-cclass.
SELECTION-SCREEN: SKIP.
SELECT-OPTIONS: r_tabcls FOR dd02l-tabclass,
                r_viewcl FOR dd02l-viewclass,
                r_contfl FOR dd02l-contflag.
SELECTION-SCREEN: SKIP.
SELECT-OPTIONS: r_devcls FOR tdevc-devclass.
SELECTION-SCREEN: END OF SCREEN 102.
 
SELECTION-SCREEN: BEGIN OF TABBED BLOCK a FOR 15 LINES,
                  TAB (10) n_tables USER-COMMAND ucomm1 DEFAULT SCREEN 101,
                  TAB (20) n_advanc USER-COMMAND ucomm2 DEFAULT SCREEN 102,
                  END OF BLOCK a.
 
INITIALIZATION.
 
  AUTHORITY-CHECK OBJECT 'S_TABU_DIS'
                  ID 'ACTVT'     FIELD '03'
                  ID 'DICBERCLS' DUMMY.
 
  IF sy-subrc <> 0.
    MESSAGE 'Missing authorisation.' TYPE 'E'.              "#EC NOTEXT
    LEAVE PROGRAM.
  ENDIF.
 
  " --- Titles and texts
  n_tables = 'Selection'.                                   "#EC NOTEXT
  n_count  = 'Check tables for data'.                       "#EC NOTEXT
  n_empty  = 'Show empty tables'.                           "#EC NOTEXT
  n_advanc = 'Advanced options'.                            "#EC NOTEXT
 
  " --- Exclude Structures and Append Structures
  r_tabcls-sign   = 'E'.
  r_tabcls-option = 'EQ'.
  r_tabcls-low    = 'INTTAB'. APPEND r_tabcls.
  r_tabcls-low    = 'APPEND'. APPEND r_tabcls.
 
  " --- Exclude Help -, Structure -, Append Views and View Structures
  r_viewcl-sign   = 'E'.
  r_viewcl-option = 'EQ'.
  r_viewcl-low    = 'H'.   APPEND r_viewcl.
  r_viewcl-low    = 'S'.   APPEND r_viewcl.
  r_viewcl-low    = 'A'.   APPEND r_viewcl.
  r_viewcl-low    = space. APPEND r_viewcl.
 
  " --- Exclude delivery class 'L' (tables for storing temporary data)
  r_contfl-sign   = 'E'.
  r_contfl-option = 'EQ'.
  r_contfl-low    = 'L'. APPEND r_contfl.
 
  " --- Include authorisation group '&NC&' and (empty)
  r_autgrp-sign   = 'I'.
  r_autgrp-option = 'EQ'.
  r_autgrp-low    = '&NC&'. APPEND r_autgrp.
  r_autgrp-low    = space.  APPEND r_autgrp.
 
  " --- Exclude non-transportable packages
  r_devcls-sign   = 'E'.
  r_devcls-option = 'EQ'.
  SELECT devclass INTO r_devcls-low FROM tdevc WHERE korrflag <> 'X'.
    APPEND r_devcls.
  ENDSELECT.
 
AT SELECTION-SCREEN.
 
  gv_ucomm = sy-ucomm.
 
AT SELECTION-SCREEN OUTPUT.
 
  LOOP AT SCREEN.
    CASE screen-name.
      WHEN 'P_EMPTY'.
        IF gv_ucomm = 'CNT'.
          p_empty = p_count.
        ENDIF.
        IF p_count = 'X'.
          screen-input = 1.
        ELSE.
          screen-input = 0.
        ENDIF.
        MODIFY SCREEN.
    ENDCASE.
  ENDLOOP.
 
START-OF-SELECTION.
 
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      text = 'Searching...'.                                "#EC NOTEXT
 
  " --- Get all tables, views, etc.
  SELECT *
    FROM dd02l
    INTO CORRESPONDING FIELDS OF gt_tablist
   WHERE tabname  IN r_table
     AND tabclass IN r_tabcls
     AND ( tabclass <> 'VIEW' OR viewclass IN r_viewcl )
     AND as4local =  'A'
     AND contflag IN r_contfl
   ORDER BY tabname.
 
    IF gt_tablist-clidep = 'X'.
      gt_tablist-clidep = icon_okay.
    ELSE.
      gt_tablist-clidep = icon_dummy.
    ENDIF.
 
    " --- Get table description
    SELECT ddtext UP TO 1 ROWS
      FROM dd02t
      INTO gt_tablist-ddtext
     WHERE tabname    = gt_tablist-tabname
       AND ddlanguage = sy-langu
       AND as4local   = 'A'.
    ENDSELECT.
 
    " --- Get authorisation group
    SELECT cclass UP TO 1 ROWS
      FROM tddat
      INTO gt_tablist-cclass
     WHERE tabname = gt_tablist-tabname.
    ENDSELECT.
 
    " --- Table not found or authorisation group explicitly selected
    IF sy-dbcnt = 0 OR gt_tablist-cclass IN r_autgrp.
      " --- Get authorisation group description
      IF sy-dbcnt > 0 AND gt_tablist-cclass <> space.
        SELECT bezei UP TO 1 ROWS
          FROM tbrgt
          INTO gt_tablist-bezei
         WHERE spras = sy-langu
           AND brobj = 'S_TABU_DIS'
           AND brgru = gt_tablist-cclass.
        ENDSELECT.
      ENDIF.
 
      " --- Get package
      SELECT devclass UP TO 1 ROWS
        FROM tadir
        INTO gt_tablist-devclass
       WHERE pgmid    =  'R3TR'
         AND object   IN ('TABL', 'VIEW')
         AND obj_name =  gt_tablist-tabname.
      ENDSELECT.
 
      " --- Filter by selected package(s)
      IF gt_tablist-devclass NOT IN r_devcls.
        CLEAR gt_tablist.
        CONTINUE.
      ENDIF.
 
      " --- Get logging flag
      SELECT COUNT(*) UP TO 1 ROWS
        FROM dd09l
       WHERE tabname   = gt_tablist-tabname
         AND as4local  = 'A'
         AND protokoll = 'X'.
 
      IF sy-dbcnt > 0.
        gt_tablist-protokoll = icon_okay.
      ELSE.
        gt_tablist-protokoll = icon_dummy.
      ENDIF.
 
      IF p_count = 'X'.
        TRY.
            " --- Check, if the table is empty
            SELECT COUNT(*) UP TO 1 ROWS FROM (gt_tablist-tabname).
            IF sy-dbcnt > 0.
              gt_tablist-hasdata = icon_okay.
            ELSE.
              IF p_empty <> 'X'.
                CLEAR gt_tablist.
                CONTINUE.
              ENDIF.
              gt_tablist-hasdata = icon_dummy.
            ENDIF.
 
          CATCH cx_sy_sql_error.
            " --- SQL error (e.g. due to a defective view)
            gt_tablist-hasdata = icon_failure.
        ENDTRY.
      ENDIF.
 
      APPEND gt_tablist.
    ENDIF.
 
    CLEAR gt_tablist.
  ENDSELECT.
 
  " --- Total number of tables examined
  gv_total = sy-dbcnt.
 
  PERFORM alv_show_result.
 
*&---------------------------------------------------------------------*
*&      Form  alv_show_result
*&---------------------------------------------------------------------*
FORM alv_show_result.
 
  DATA: lt_fieldcat TYPE slis_t_fieldcat_alv WITH HEADER LINE,
        ls_layout   TYPE slis_layout_alv.
 
  " --- Field catalogue
  lt_fieldcat-fieldname   = 'TABNAME'.
  lt_fieldcat-ref_tabname = 'DD02L'.
  lt_fieldcat-key         = 'X'.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'DDTEXT'.
  lt_fieldcat-ref_tabname = 'DD02T'.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'CCLASS'.
  lt_fieldcat-ref_tabname = 'TDDAT'.
  lt_fieldcat-outputlen   = 8.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'BEZEI'.
  lt_fieldcat-ref_tabname = 'TBRGT'.
  lt_fieldcat-outputlen   = 14.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'HASDATA'.
  IF p_count = 'X'.
    lt_fieldcat-seltext_s = 'Data'.                         "#EC NOTEXT
    lt_fieldcat-seltext_m = 'Table contains data'.          "#EC NOTEXT
    lt_fieldcat-icon      = 'X'.
    lt_fieldcat-outputlen = 4.
  ELSE.
    lt_fieldcat-tech      = 'X'.
  ENDIF.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'CLIDEP'.
  lt_fieldcat-ref_tabname = 'DD02L'.
  lt_fieldcat-icon        = 'X'.
  lt_fieldcat-outputlen   = 8.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'PROTOKOLL'.
  lt_fieldcat-ref_tabname = 'DD09L'.
  lt_fieldcat-icon        = 'X'.
  lt_fieldcat-outputlen   = 4.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'CONTFLAG'.
  lt_fieldcat-ref_tabname = 'DD02L'.
  lt_fieldcat-just        = 'C'.
  lt_fieldcat-outputlen   = 8.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'DEVCLASS'.
  lt_fieldcat-ref_tabname = 'TADIR'.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  lt_fieldcat-fieldname   = 'TABCLASS'.
  lt_fieldcat-ref_tabname = 'DD02L'.
  APPEND lt_fieldcat. CLEAR lt_fieldcat.
 
  " --- Layout
  ls_layout-box_fieldname     = 'ALVBOX'.
* ls_layout-colwidth_optimize = 'X'.
  ls_layout-no_input          = 'X'.
  ls_layout-zebra             = 'X'.
 
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_callback_program      = sy-repid
      i_callback_user_command = 'ALV_CALLBACK_USER_COMMAND'
      i_callback_top_of_page  = 'ALV_CALLBACK_TOP_OF_PAGE'
      is_layout               = ls_layout
      it_fieldcat             = lt_fieldcat[]
      i_save                  = 'X'
    TABLES
      t_outtab                = gt_tablist
    EXCEPTIONS
      OTHERS                  = 1.
 
ENDFORM.                    "alv_show_result
 
*&---------------------------------------------------------------------*
*&      Form  alv_callback_top_of_page
*&---------------------------------------------------------------------*
FORM alv_callback_top_of_page.                              "#EC CALLED
 
  DATA: ls_header    TYPE slis_listheader,
        lt_header    TYPE slis_t_listheader,
        li_found     TYPE i,
        lc_found(10) TYPE c,
        lc_total(10) TYPE c.
 
  ls_header-typ = 'S'.
  DESCRIBE TABLE gt_tablist LINES li_found.
  lc_found = li_found.
  lc_total = gv_total.
  CONCATENATE 'In total' lc_found 'of' lc_total             "#EC NOTEXT
              'tables match.'
         INTO ls_header-info SEPARATED BY space.
 
  APPEND ls_header TO lt_header.
 
  CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
    EXPORTING
      it_list_commentary = lt_header.
 
ENDFORM.                    "alv_callback_top_of_page
 
*&---------------------------------------------------------------------*
*&      Form  alv_callback_user_command
*&---------------------------------------------------------------------*
FORM alv_callback_user_command                              "#EC CALLED
                         USING pv_ucomm    LIKE sy-ucomm
                               ps_selfield TYPE slis_selfield.
 
  READ TABLE gt_tablist INDEX ps_selfield-tabindex.
 
  IF pv_ucomm = '&IC1'.
    CASE ps_selfield-fieldname.
      WHEN 'TABNAME'.
        PERFORM goto_se11 USING gt_tablist-tabname.
      WHEN 'DDTEXT'.
        PERFORM goto_se16 USING gt_tablist-tabname.
      WHEN 'CCLASS'.
        PERFORM goto_sm30 USING 'V_DDAT_54'.
*     WHEN 'BEZEI'.
      WHEN 'HASDATA'.
        PERFORM show_table_count USING gt_tablist-tabname.
      WHEN 'CLIDEP'.
        CALL FUNCTION 'F4IF_FIELD_VALUE_REQUEST'
          EXPORTING
            tabname   = 'DD02L'
            fieldname = ps_selfield-fieldname
          EXCEPTIONS
            OTHERS    = 1.
      WHEN 'PROTOKOLL'.
        PERFORM goto_se13 USING gt_tablist-tabname.
      WHEN 'CONTFLAG'.
        CALL FUNCTION 'F4IF_FIELD_VALUE_REQUEST'
          EXPORTING
            tabname   = 'DD02L'
            fieldname = ps_selfield-fieldname
          EXCEPTIONS
            OTHERS    = 1.
      WHEN 'DEVCLASS'.
        PERFORM goto_se21 USING gt_tablist-devclass.
      WHEN 'TABCLASS'.
        CALL FUNCTION 'F4IF_FIELD_VALUE_REQUEST'
          EXPORTING
            tabname   = 'DD02L'
            fieldname = ps_selfield-fieldname
          EXCEPTIONS
            OTHERS    = 1.
    ENDCASE.
  ENDIF.
 
ENDFORM.                    "alv_callback_user_command
 
*&---------------------------------------------------------------------*
*&      Form  goto_se11
*&---------------------------------------------------------------------*
FORM goto_se11 USING tabname LIKE rsedd0-ddobjname.
 
  CALL FUNCTION 'RS_DD_SHOW'
    EXPORTING
      objname = tabname
      objtype = 'U'
    EXCEPTIONS
      OTHERS  = 1.
 
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
 
ENDFORM.                                                    "goto_se11
 
*&---------------------------------------------------------------------*
*&      Form  goto_se13
*&---------------------------------------------------------------------*
FORM goto_se13 USING tabname LIKE dd09v-tabname.
 
  " --- Technical settings not available for these table categories
  CASE gt_tablist-tabclass.
    WHEN 'APPEND'. EXIT.
    WHEN 'INTTAB'. EXIT.
    WHEN 'VIEW'.   EXIT.
  ENDCASE.
 
  DATA: lt_bdcdata LIKE bdcdata OCCURS 0 WITH HEADER LINE.
 
  lt_bdcdata-program  = 'SAPMSEDS'.
  lt_bdcdata-dynpro   = 10.
  lt_bdcdata-dynbegin = 'X'.
  lt_bdcdata-fnam     = 'DD09V-TABNAME'.
  lt_bdcdata-fval     = tabname.
  APPEND lt_bdcdata. CLEAR lt_bdcdata.
 
  lt_bdcdata-fnam = 'BDC_OKCODE'.
  lt_bdcdata-fval = 'ANZG'.
  APPEND lt_bdcdata.
 
  CALL FUNCTION 'ABAP4_CALL_TRANSACTION'
    EXPORTING
      tcode      = 'SE13'
      mode_val   = 'E'
      update_val = 'S'
    TABLES
      using_tab  = lt_bdcdata
    EXCEPTIONS
      OTHERS     = 1.
 
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
 
ENDFORM.                                                    "goto_se13
 
*&---------------------------------------------------------------------*
*&      Form  goto_se16
*&---------------------------------------------------------------------*
FORM goto_se16 USING tabname LIKE dd02l-tabname.
 
  CALL FUNCTION 'RS_TABLE_LIST_CREATE'
    EXPORTING
      table_name = tabname
    EXCEPTIONS
      OTHERS     = 1.
 
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
 
ENDFORM.                                                    "goto_se16
 
*&---------------------------------------------------------------------*
*&      Form  goto_se21
*&---------------------------------------------------------------------*
FORM goto_se21 USING devclass LIKE trdevclass-devclass.
 
  CALL FUNCTION 'TR_SHOW_DEVCLASS'
    EXPORTING
      iv_devclass = devclass.
 
ENDFORM.                                                    "goto_se21
 
*&---------------------------------------------------------------------*
*&      Form  goto_sm30
*&---------------------------------------------------------------------*
FORM goto_sm30 USING viewname LIKE dd02v-tabname.
 
  CALL FUNCTION 'VIEW_MAINTENANCE_CALL'
    EXPORTING
      action                     = 'U'
      view_name                  = viewname
      no_warning_for_clientindep = 'X'
      check_ddic_mainflag        = 'X'
    EXCEPTIONS
      OTHERS                     = 1.
 
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
 
ENDFORM.                                                    "goto_sm30
 
*&---------------------------------------------------------------------*
*&      Form  show_table_count
*&---------------------------------------------------------------------*
FORM show_table_count USING tabname LIKE dd02l-tabname.
 
  DATA: count TYPE string,
        title TYPE string,
        text  TYPE string.
 
  TRY.
      SELECT SINGLE COUNT(*) FROM (tabname) INTO count.
    CATCH cx_sy_sql_error.
      EXIT.
  ENDTRY.
 
  CONCATENATE 'Table' tabname                               "#EC NOTEXT
         INTO title SEPARATED BY space.
 
  CONCATENATE 'Number of entries:' count                    "#EC NOTEXT
         INTO text SEPARATED BY space.
 
  CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT'
    EXPORTING
      titel     = title
      textline1 = text.
 
ENDFORM.                    "show_table_count