RE: AS/400 as ODBC database server question

*Disclaimer* I am not a security expert (nor any other sort for that matter)

Are all of the ODBC users also AS/400 Users? (If not so, create a profile for them) If so, and if your efforts on fixing security don't conflict, you might be able to set something up with the user profile. Specifically I am thinking of setting up a group that has no special restrictions and used to simply identify the ODBC folks. You could also use the accounting code field in the user profile for identification.

I guess I am also assuming that you can identify how the user "got here" as well.

-----Original Message-----
From: Loyd Goodbar [mailto:lgoodbar@ispchannel.com]
Sent: Thursday, July 26, 2001 23:03
To: midrange-l@midrange.com
Subject: AS/400 as ODBC database server question

I have a question regarding the 400 as a database server for ODBC connections.

Up to now, all of our AS/400 applications were green screen, with some
browser-based inquiries. Fairly easy to control, or at least understood,
security-wise.

We have just  installed an application [email privately for more details:
lgoodbar@ispchannel.com or lgoodbar@afs.bwauto.com] that uses the As/400 as a
database server only. The application is primarily inventory request/item
request, and PM work order requests.

The database is defined via SQL, with the data in one library and
journals/receivers in another library. The application server is Windows 2000
server, and we use Citrix for the client/desktop portion. A user profile owns
the database library and acts as the application administrator (*ALL authority
to the library & files). The sole method of access in the application is ODBC.
The app supports SQLServer, Oracle, and DB2 for database hosts. As such,
stored procedures are not used, only direct SQL access.

I have set PUBLIC(*EXCLUDE) to the libraries and objects in this application.
The application owner has no authority except to its application libraries.
The way we have chosen to allow signons in the package is a single generic
signon, and "trust" the users to enter their application login to perform
functions. The other alternative is to set up individual user profiles solely
to use this program.

The way I understand the security in the application is this: when a user logs
into the application, the app queries the 400 whether the login user has a
user profile on the system. If true, then the app queries its own login table
to assess privileges. As our facility has upward of 600 people, that's a lot
of profiles to maintain just to use this one package - the reason we're
sticking with a single general signon for now. App administrators will have
their own signons. I do not know yet if we can isolate the application owner
profile from daily use.

The consultant performing installation knows very little about the AS/400. The
documentation does not mention database security at all, only application
security.  We called one of their AS/400 people for the "rules" to setting up
user profiles for the package, and general AS/400 security with the package.
What I was told basically was, "A user needs change authority to the
database." From what I gather, other installations utilizing the AS/400  just
left the system wide open and not worried about access. I don't want to do
that.

The obvious problem is if a user has change authority, they can ODBC into the
files at any time and delete, alter, or read the data.

Here's the question: How do I allow a generic login (or a specific user, for
that matter) to use the application legally, and not allow that user to
connect via Access, direct ODBC, SQLServer Query Analyzer, Excel, etc etc?

I believe one answer are database exit points QIBM_QZDA_*. I have done a
little research just before writing to know they can control ODBC access. What
I haven't looked for is what is passed to an exit program to make that
decision. I assume that the user and query (SQL) are passed. Is some type of
application name, ODBC version, IP address, or other identifying information
sent also? We have been beat up the past couple of years over AS/400 security,
and while we are in the process of fixing it, I would rather not add a gaping
hole to our system.

Please, no advice for creating views, etc. This is a vendor package which
already contains some views (logical files). I don't know if the views are for
security or data sequencing. The only thing I can think of changing filewise
is to add a trigger program (see below).

All of the files are journaled (but I don't believe the app used commitment
control), so we should have good ideas of who did what, but does the journal
tell me if someone cleared a file? Journaling is a new concept for us. I know
a little about them but have not used them much on an application level,
except to peek in the security journal once in a while. What we have usually
done is add trigger programs to critical files to capture before/after image
changes with a timestamp and user. Files like this are far easier to query
than the journals, in my limited experience.

Any advice is helpful (other than chucking the software)!

TIA,
Loyd
-- 
"The killer doesn't see the world like everyone else."
"How does he see it?" "Differently." --Millennium
lgoodbar@ispchannel.com  ICQ#504581  http://lgoodbar2.pointclark.net/
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---