News
Abstract
List of GitHub Gists by Scott Forstie and iSee video tutorials by Scott Forstie and Tim Rowe related to Security
Content
| 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. | Connections *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 |
| SWAP_DYNUSRPRF.sql 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… | SQL DYNUSRPRF setting |
| 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… | Connections |
| 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. | Connections |
| Row permissions control for ZDA access.sql Row permissions control for ZDA access | Connections |
| Authority Collection.sql IBM i DB2 security - Scott's examples on create mask - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcrtmask.htm | 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 Use of QSECOFR |
| Well isn't 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... | MODINVAU 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 |
| prtprvaut.sql Someone sent me an "SQL Challenge". Challenge accepted! #SQLcandoit | PRTPVTAUT |
| 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 |
| Seeing programs that are configured to use adopted authority.sql The request... I don’t find any examples for reporting on adopted authority. Specifically, we would like to know what pgms in non-IBM libraries use adopted authority. | Adopted authority |
| Library list security.sql The challenge... How do we protect ourselves from an 'Uncontrolled Search Path Element' attack? | Library list |
IOSYSCFG
| 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. | Connections |
| 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 |
| Who Owns the Objects? Every system has piles of objects in both the ILE and in IFS. Do you have an understanding of all these objects ? Who Owns them ? How much space are the objects for each use using ? In this iSee we will explore this question using the QSYS2.Object_Ownership service along with a few trick that are part of the SQL language. We will even use a OLAP function! | Object ownership |
| iSee Object Ownership During the PowerUp 2022 event, Scott and Tim recorded some iSee videos. This one covers Object Ownership. Why is this important? Well you can learn a lot about what is happening on your system by having a understand of what objects are owned and by whom. We will look at who owns what in both the IFS and the QSYS file systems. | Object ownership |
| iSee Live from St Louis – Authorization Lists During the Fall Navigate Conference Scott and Tim did a full length iSee session. Multiple topics were covered, this is the recording of one of these topics. Authorization lists – Do you have any Are they used ? This session will explore how to help you find and learn about the authorization lists on your system. | Authorization lists |
| iSee Live from St Louis – Keeping Passwords Secret within SQL During the Fall Navigate Conference Scott and Tim did a full length iSee session. Multiple topics were covered, this is the recording of one of these topics. Passwords are a vital part of security, and there are often interfaces within SQL that require a password to be used. But, if the world can see your password… not very secure. This session will explore a technique that can hide your password from the view of watchful eyes.. | Password protection |
| iSee Audit Journal Management Audit Journals, these are a powerful tool that is often overlooked. This iSee will review best practices in managing audit journals. How much storage are your audit journals using ? What is your strategy for the maintenance of these journals? We will show you how easy it is now to see what auditing features are enabled, and easy way to keep your journals clean and tidy. | Audit journal |
| iSee Manage User Profiles with SQL In the security conscience world we live in, one would expect user profiles on our systems to have at least good passwords. Do you know that to be the case on your systems ? Way too often we see profiles that still have a ‘Default password’ !!! Craziness!! This iSee video will give you some SQL to not only find these sorts of occurrences, but give you tools to automatically disable that profile! Review user profiles and automatic updates.. sounds like a winner. | User profiles |
| iSee – Who Executed a CL Command Ever wonder who might be using a CL command? Or maybe what commands a specific user runs on a regular basis? Easy as pie….this iSee will show you how to set up Command Auditing and how to review the data to see CL command audit detail. | Auditing command usage |
| iSee DataAreas in the Light of Security Data areas, we all use them, the question is do we abuse them. In this iSee we will review ALL the data areas on a system, do a scan to see if there is data stored in them that should not be there! Ie Security related data. We will also review the access privileges for data areas, who can access and who should access the contents. | Data areas |
| iSee Checking Contents of Files We have many text and property files on our system. Some of these have setting that could compromise the security of your system. How do you find such things ? In this example, we will focus on the Apache server configuration files, looking for a specific configuration. This will look at all Apache server configurations regardless if its active today or not. The principles shown, can be used for a wide range of things. | Configuration files |
| iSee Profiles and Passwords IBM i is very ‘securable’ capable of locking all sorts of stuff down, including user profile passwords. The question was asked, ‘How can I be sure that all the IBM i users are using 15 character ‘secure’ passwords? In response to that.. and to help both see how to set that up AND force all your users to comply, Scott and Tim take to the SQL air waves. We have included the SQL so you too can start to ensure your users are compliant. | User profiles |
| Enable & Disable Many User Profiles at Once User Profiles, we all have them, sometimes we have too many! Do you ever have a need to do a mass update of user profiles? The use case for this video was someone that needed to do some sensitive testing and did not want ANYONE to sign in during the test. Easy way to make that happen ? Disable ALL user profiles on the system for a period, and then revert all those changed back. The SQL provided will show you how to build your list of profiles, run the action and then make sure you clean up after. Sure we are showing Disable and Enable, but think of other user cases, you want to force all profiles to reset their password based on new security password rules, this SQL can easily be adopted for that as well. | User profiles |
| iSee Adoption - A Study of Programs that Adopt Authority Adoption! Well, not the getting a new one.. but rather the scary security risk of running a job not with your authority… but by adopting additional authority. That may sound like great fun to you, but as an administrator, not sure I want Sally or Joe user out there running and accessing things that they really should not be using. This session will help you take a look at the programs on your system that are using adopted authority. This brilliant SQL-based insight should help you better understand your security posture. | Adopted authority |
| iSee Libraries Security Attributes that Matter Security… its poking us at every turn. What are the security attributes on your created libraries ? Want to know ? This iSee shows some super fast ways to list all the libraries on the system and review the important security attributes that you really need to take better care of. | Libraries |
| iSee – Integrated Multi-factor Authentication What ? MFA integrated ? Yes!! With the new IBM i 7.6 release, the operating system now includes support for even higher securability. Every user profile can now leverage MFA. The best part, there is no external servers needed, meaning that it even works when in restricted state! Or from the SST signon screen. Crazy!! In this session we will explore how this support works, and how to configure. | Multi-factor authentication |
| iSee Library List Security The library list for a job is incredibly powerful and flexible. Its so convenient to work with…but as we know any time we see the word convenient… we should be wary as there are very likely security concerns. Well the library list, also provides some security concerns if you don’t properly handle. In this iSee we look at what makes up the library list as well as some practices to keep it secure. | Library list |
| iSee Security by Default IOSYSCFG Read Only Security, continues to dive lots of what we do and have to care about. With IBM i 7.6 security by default take another step forward. The IOSYSCFG special authority is a very powerful authority setting, in this iSee we will demo the better security feature of the operating system and then show the brand new IOSYSFG Read Only support! Allowing users to no long require IOSYSFG special authority when all they need is to be able to read various system configuration details. | IOSYSCFG |
| iSee Authority Collector - Users The authority collector is a feature that was introduced in the IBM i operating system back in 7.3!! So it’s certainly not new, BUT it’s incredibly powerful. In this iSee we will show you the Users support for the authority collector. What does this mean ? When enabled for a user profile, EVERY (yea.. sorry to yell) object that user profile touches (did I mention every object…) is recorded in a flight recorder, it will also show you the authority needed to touch that object and how the user ended up getting the needed authority . If you are looking to change your security posture, removing user from have say *ALLOBJ special authority, this iSee will give you the tools you need to see what changes you need to make so that user can continue to do their job but just not have excessive authority. | Authority collection |
| iSee Authority Collector - Objects Part 2 of the Authority Collector series….The authority collector is a feature that was introduced in the IBM i operating system back in 7.3!! So it’s certainly not new, BUT it’s incredibly powerful. In this iSee we will show you the Objects support for the authority collector. What does this mean ? When enabled you can ‘watch’ 1 or more objects, every time those objects are accessed, the flights recorded is updated. This can give you an amazing view on who is actually touching an object for ANY reason. Might be interesting to turn this on for say some database and actually see who is touching that object. A very powerful tool in helping to understand how your applications on your system are used. | Authority collection |
| iSee Secure - Limited Capabilities The iSee blog is getting started on a series on Security. With the continued scrutiny on security, we figured it was time to review how you can use SQL to review various security features on the IBM i. This session, it’s all about Limited Capabilities. Learn how that can be put to work for you. | Limited capabilities |
| iSee Secure - Database Attack Vectors Entry 2 in the iSee Security Series, it’s all about protecting the data! This session will look at how secure your database are. Can any one read or worse, write to your databases! | Database |
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"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"}]
Was this topic helpful?
Document Information
Modified date:
26 October 2025
UID
ibm16340283