IBM Support




List of GitHub Gists by Scott Forstie and iSee video tutorials by Scott Forstie and Tim Rowe related to Security


GitHub Gists Topic
Finding user profile names.sql
Finding user profile names... the fastest way possible...
User profiles
ALLOBJ users coming in over non SSL network interfaces .sql
This is a probe of current network activity, showing what non-SSL interfaces are being used by users who have *ALLOBJ special authority. The idea here is to raise awareness to sensitive data being accessed over unencrypted interfaces.
*ALLOBJ authority
TopN user storage report.sql
This example takes a previous example and extends it. The idea here is that you want to proactively manage user consumption of storage. For the top storage consumers, return a report that lists their largest objects (either in QSYS or IFS) and provide some contextual detail.
User storage
Object ownership by user - total report.sql
This gist combines several IBM i (SQL) Services to produce an easy to consume UDTF. Pass in a user name and you'll get back all the QSYS and IFS objects they own, ordered by size descending.
Object ownership
With the latest Db2 PTF Groups for IBM i 7.3 and 7.4, you can now identify and fix those *PGM and *SRVPGM's that use SQL and were built incorrectly. This example finds those cases where *OWNER will be used for static SQL, but *USER will be used for dynamic SQL. The procedure swaps the dynamic user profile setting to *OWNER. This utility approach…
ALLOBJ users with default passwords.sql
Security implementations can and should be monitored closely and on a regular cadence. This is one example where SQL can be used instead of the Analyze Default Passwords (ANZDFTPWD) command. 
User profiles
Mask birthdays with RCAC
This example shows how to use a Column Mask to prevent users from seeing the actual birthdate.
Column masks
ZDA mystery solved
This example shows several things worthy of attention. System managers can utilize exit program to establish improved auditing, understanding, and real time business rules using SQL. For QZDASOINIT jobs, it can be easily considered an unsolvable mystery. With the help of Db2 for i Client Special Registers, we can understand a great deal about ZD…
Remove *IOSYSCFG from users and groups
This example shows the power of the REGEXP_REPLACE built-in function, when combined with IBM i Services for security and dynamic SQL.
User profiles
User profile ownership and basic authorities.sql
The first query identifies those users who are lacking authority to use their own *USRPRF. This lack of authority can cause annoying failures in software products. The other queries are used to review whether the *USRPRF ownership implementation matches the strategy.
User profiles
Retrieve details for active 5250 sessions.sql
Use SQL's NETSTAT and ACTIVE_JOB_INFO services to identify and explore active 5250 sessions.
Row permissions control for ZDA access.sql
Row permissions control for ZDA access
Authority Collection.sql
IBM i DB2 security - Scott's examples on create mask -
Column masks
Tracking ALLOBJ users through time
The idea of this Gist is to take a step beyond access to a live view of who has *ALLOBJ user special authority, to also being able to see how the topic is changing over time. With the addition of a time dimension, clients can more easily focus on the delta changes.
*ALLOBJ authority
Kitchen Sink for the Admin.sql
More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell.
Audit Journal
Well isnt that special.sql
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...
Special authorities
Controlling adopted authority.sql
If you use adopted authority, how do you avoid allowing code that you call from taking a free ride on your elevated authority? One answer lies within this gist...
Adopted authority 
Auditing a job queue
I was asked, "how can you determine which user held a job queue"? While there is more than one approach to answering this question, here's an example that leverages the secure audit journal log.
Object auditing
Someone sent me an "SQL Challenge". Challenge accepted! #SQLcandoit
Authority collection - split to the rescue.sql
This Gist shows how SQL can be used to simplify the task of analyzing Authority Collection runtime authority data.
Authority Collection
SQL alternative to the command ANZDFTPWD ACTION(*NONE).sql
The request was, is there an SQL alternative to the command ANZDFTPWD ACTION(*NONE)? The answer was a resounding YES.
User profiles
Searching the IFS for objects with "log4j" in the name.sql
The request from a client was to provide an SQL approach to search all of the IFS, finding any object that has "log4j" in its name, and producing an SQL table with the search results.
Object scanning
A client asked how they could identify which objects were set to *PUBLIC *EXCLUDE (good!), but where the object owner also had *EXCLUDE (not so good). Herein lies one solution.
Authority Collection
AUINTERNALS made easy with SQL.sql
The request... find an alternative to STRSST for monitoring AUINTERNALS security limits
User Profiles
iSee Video Tutorials Topic
iSee How You Connect to the IBM i
We have been asked if you can tell how people connect the IBM i.  The good news is there are some ways.  You can easily determine who is connecting using what interface for any database connection!  You can see what level of ACS is being used if users are still using the old Windows product and other connections.  This session will show you how to set up an exit point, and then query that data to see how people are connecting to your IBM i.
User Profile Know-How
This video will look at new ways to explore user profiles.  Do you know if your profiles on your system have default user profiles?   Can you tell what users have *ALLOBJ special authority even if they have that elevated authority from the group or supplemental profiles?   With these SQL scripts and techniques, you can quickly gain insight and gain some control over the user profiles on your system.
User profiles
Understand trending of *ALLOBJ users with Temporal
In this iSee we will look at a real example of how Temporal tables can be leveraged in a simple way to 'Audit' and understand the trends of users that have *ALLOBJ Special authority.  This gives you a template that can not only be used directly but with some simple modification can be used to understand a trend for virtually anything on your system. Knowledge is power, this iSee gives you insight, which is knowledge helping you better figure out what is actually happening on your IBM i.
User profiles
How to Use RCAC Column Masking
Tim and Scott branch out a little in this session. While we are still using ACS and SQL we are using them to accomplish a new purpose.  How to provide masking of data in a database table.  If you have sensitive data that people should not have access to, this session is for you.  We will show you using RCAC how you can control at the Data level who can see data and what part of the data they should have access too. The best part, while we are using SQL to set this up and demo things, this can be applied to either a DDS or DDL based table!
Column masks
How to Leverage RCAC Row Permissions
In this session, Tim and Scott take a look at the 2nd half of Row Column Access Controls - Row Permissions. We dive into how to set up and activate this support and show you what it looks like to prohibit a user from accessing data in a database table.  The source and examples are provided which should give you a great head start in enabling this on your favorite database table.
Row permissions
IBM i Audit Journal Peek-a-boo
Audit Journals on IBM i are a treasure chest of information.  The problem is that there is a huge pile of information that can be terribly difficult to dig through to find the gems. The great thing, with the latest IBM i Services you now have a way to unlock the mystery. There are 4 new services that allow you to quickly see the information and how you can now easily see what is happening in your audit journals.
Audit journals


[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000C4BAAU","label":"IBM i"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Document Information

Modified date:
30 June 2022