- 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.
This was first published in August 2007