Controlling SQL Access

Important Updates to Cybersecurity Software

HelpSystems is updating the names of our cybersecurity software, including the Powertech product line. Don’t worry—the functionality of your products won’t change.

See the new names here > New, Clearer Names for HelpSystems Security Software.

--------------------

If you are required to control SQL access so that All Users have ‘Read’ access only for ALL Files on the system, use Exit Point Manager’s Object List to limit user access.  
Access to specific files in a library through the SQL server is not as straight forward as with other servers because the SQL server requires access to tables in QSYS and QSYS2 libraries. 
(Note: The use of Object Lists has a greater performance impact than location rules, user rules, and memorized transactions.) 

Follow this procedure to set up the ‘Read’ access to ‘All’ files using Object Lists.

Create the ‘Read’ Object List for all of the files on the system.

1. Create an Object List for all of the files on the system.
   1. From the Exit Point Manager Main Menu, choose 4 (Work with Security by Object), then 1, Work with Object Lists.
   2. Create the Object List – READOBJLST

      READOBJLST Q [ASP Group] Read Only Object List

      
      
      Press Enter twice. This will bring you back to the Work with Object Lists screen with your Object List entered and visible.
      
      

   3. Enter an 8 next to your Object List (READOBJLST) and press Enter.

      The Work with Object List Entries screen appears where you can define your new Object List.

      

      Enter 1 under Opt and * (asterisk) for Library and Object. Enter *FILE for Type.

      Press Enter twice to accept the changes.

      This brings you back to the Work with Object List Entries screen with your new entry visible.

      

   4. Press F3 to return to Work with Object Lists. Setup the object rules for the READOBJLST Object list.

      Use option 9 next to your Object List and press Enter.

      Using the next screen, add each individual user OR group profile to have the following rules:

      User/Group Profile *ALL *REJECT Y N N *NONE

      User/Group Profile *READ *OS400 Y N N *NONE

      

      Enter 1 under Opt and then enter the User/Group name, the operation you want them to have, the authority, Y, N, N for Auditing, Message, and Capture flags, then press Enter twice.

      For each user, activate the *SQLSRV server by placing a 1 next to *SQLSRV and pressing Enter twice.

      

      Note - You must have a rule to reject everything except the read.

2. *SQLSRV - Verify the Object Rules & Clear the cache for the new rules
   1. From the main Exit Point Manager Menu, take option 1 – Work with Security by Server.
   2. Next to the *SQLSRV server, enter UA to work with the User’s Access.

      You will see the User/Group with *MEMOBJ for the object list that you just created (see below).

      

   3. Change *PUBLIC’s authority from *OS400 to *REJECT to only allow the users in the Object List to have authority through the SQL server.

      Results:

      *ALL *PUBLIC *REJECT Y N N *NONE

      *ALL User/Group *MEMOBJ Y N N *NONE

      Note – Clear the cache using the Server Properties (Procedure Below) to enforce this new rule.

Procedure to clear the server cache

The procedure to clear the cache is a green screen procedure.

1. From the Exit Point Manager Main Menu, choose option 1.
2. On the Work with Security By Server Screen, enter SP for *SQLSRV.
3. Change the enforce rules to N and save the change by pressing Enter.
4. Select the server properties for *SQLSRV again (SP).
5. Change the enforce rules to Y and save the change by pressing Enter.

This will create the new cache for the server with the new rules, clearing the cache.