News
Abstract
List of GitHub Gists by Scott Forstie and iSee video tutorials by Scott Forstie and Tim Rowe related to Security
Content
You are in: IBM i Tutorials, Demos, and SQL examples > 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. |
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 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... |
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 |
| 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 |
[{"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:
25 May 2024
UID
ibm16340283