Controlling SQL Updates Using Network Security
By Oshan Indika
Over the years, users have relied on commands like STRSQL and RUNSQL to provide instant and powerful access to the data on their Power Systems servers. All types of users—from programmers to system administrators to end users—use these commands as their primary interface for extracting and updating data.
However, allowing a user to view, update, and even delete data without control by the normal application, represents a serious system vulnerability. While some might argue that you can control these activities using object authority, it is a huge risk to depend entirely on object security. It’s better to have an in-depth strategy using a layered approach to protect your vital data assets.
The STRSQL Problem
One major issue with the STRSQL command is that there is no record of the SQL string used to read, update, or delete the data. Although the user has the option to save the details of the SQL session, there is no enforcement or log file created, and they can simply exit the session without saving it. If the user journals the data files, you can see the before and after results in the data, but it’s still difficult to get the full picture without the originating SQL statement.
One way to control this is to revoke authority to the STRSQL command object so that no one can run the command from the command line. Then, require users to use a tool, such as Navigator for i, to run their SQL statements. However, you need to ensure that the tool is available only for the people who require it, and that only selected functions are installed, since it provides capabilities beyond SQL.
Any SQL statements run from Navigator for i go through the *SQLSVR (ODBC) exit point, so you can use an exit program, such as PowerTech’s Network Security, to monitor them. That way, you’re in full control of who can run SQL directives against the data, with an audit trail of all user requests.
With PowerTech Network Security you can control a user’s access by:
- Location (TCP/IP information)
- User profile or group profile
- SQL command (PREPARE, EXECUTE, FETCH, and so on)
Network Security also helps you meet compliance standards and regulations (such as PCI and SOX) that require a full event log of network transactions executed against the server. If you have a Security Information and Event Management (SIEM) solution, the transactions can trigger real-time notifications sent in Syslog format using PowerTech Interact.
Beyond Green Screen
PowerTech Network Security can override a user’s authority level to grant them more or less authority. For example, you can take a user profile with *ALLOBJ special authority and override its SQL requests to allow only read access to data. This is impossible using green screen controls.
With solutions, like Network Security from PowerTech, you enjoy full control and documentation of SQL commands, a solid strategy to protect your system and key data, and peace of mind.
Oshan Indika has more than a dozen years of IT experience in enterprise infrastructure management, including system administration on a variety of platforms, (System i [AS/400], Windows, UNIX, Linux, and Solaris); LAN/WAN network administration (frame relay); and security firewalls. He is a Certified Information Systems Security Professional (CISSP) and Certified Information Systems Auditor (CISA). Previously, he held CCNA and MCP certifications in network and systems management.