RE: AS/400 as ODBC database server question

Since I don't know how much (if any) control you have with how the program
works, this is just an idea.  If the end user provides a username/password
to the program for validation, and the program then sends its own
username/password to the AS/400, the end user doesn't need to know a real
AS/400 username/password and thus can't access the data any other way.

>-----Original Message-----
>From: owner-midrange-l@midrange.com
>[mailto:owner-midrange-l@midrange.com]On Behalf Of Loyd Goodbar
>Sent: Thursday, July 26, 2001 11:03 PM
>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
>+---
>

+---
| 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
+---