Get started Bring yourself up to speed with our introductory content.

Using object level security to control data access

Expert Carol Woodbury explains how to use object level security to control employees' access to files without limiting their method of access.

I'm a controller for a mid-size retailer. My staff write SQL queries directly from our AS400 data tables. Our MIS department says that in order for us to keep that ability, they must grant us complete command line access to the system. Our CIO and I are uncomfortable with that. What I'd like to know is:
  • Is it possible to have the ability to write SQL programs without having full access to the system? We write these using MS Query (via Excel) and Crystal Reports.
  • Is it possible to write a ODBC DSN connection file that will provide this limitation?
  • Can we limit a group of files from even being accessed? i.e.: anything with a DBPR*?

I don't like the "all or nothing" solution.

This is not an all-or-nothing situation. The best way to control what your staff can do is to not attempt to limit the method by which they access the files, but limit access to the files themselves by using object level security on the files.

Without *USE authority to the files, you could not write a SQL statement or a native i5/OS query statement, or download the file to Excel or FTP the file to another system. You see, there are many ways to access a file -- and more are being created every day (there are several vendors that provide SQL access without requiring access to the command line.) If you limit access through that vendor interface but allow access through sockets, http or a command line, they still have access to the data.

Your solution is to restrict access to the files by using object level security -- at either the library (shutting them out from everything in the library) or at the file itself.

Dig Deeper on Security Tools

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.