Controlling SQL Updates Using Powertech Exit Point Manager for IBM i

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 any 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.

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, and no log is file created. And, the user 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 a 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 Exit Point Manager for IBM i, 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 Exit Point Manager for IBM i 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)

Powertech Exit Point Manager for IBM i 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 SIEM Agent for IBM i.

Powertech Exit Point Manager for IBM i 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 Powertech Exit Point Manager for IBM i, you enjoy full control and documentation of SQL commands, a solid strategy to protect your system and key data, and peace of mind.