AS400 ODBC

20 pts.
Tags:
AS/400
Database permissions
DB2 administration
DB2/400
ODBC
Hi Our company uses AS400 for our financial processing application. The users access the AS400 through an emulator. They also access the application database through ODBC. They extract the data to excel sheets to create certain reports for their own purpose. Recently, one the data base files in AS400 were replaced by such a user accidentally. However, since we do not have log for this, we are not sure who did this or how this happened. Can you tell me how can we control the data access to our database files through ODBC? Also, can we change any settings so that a log is created every time a user accesses the database files through ODBC? Thanks for all the help. Sandeep

Answer Wiki

Thanks. We'll let you know when a new response is added.

just a simple answer i’ll give you do implement microsoft source safe tool to secure your database no need to follow any body…

ashlashraina

you can also setup the ODBC connections to only allow Select (or Reads depending on the interface) This would work if your users only need to bring data to their Excel Spreadsheets

Kevin <a href=”http://www.aresgrp.com”>Affiliated</a>

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gilly400
    Hi, I'd agree with Kevin on this - you should never allow users to update/change/delete database files through ODBC. If there are updates necessary, they should be done through the use of programs designed to do the updates. For example, if users delete records from a customer file, then you software stops invoicing the deleted customers. Keep ODBC restricted to read only access and you shouldn't have any problems. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • Sloopy
    I did write an answer, which has somewhat been superceded by Kevin and Martin G. Ashlash - please don't remove other contributors' answers when you add your own! My original answer was: "One solution is to use a different User ID through ODBC than the User ID used through the emulator. Then you can use the AS/400's built-in security - edit the authority for the files and add the ODBC User ID with read-only permission." However, I am in agreement with the other contributors - use Selects / Reads in the Excel macro or VB code. However, if the code is altered or subverted then changes to the iSeries database can still happen - so using the iSeries authority structure may provide you with that extra layer of protection. Sloopy
    2,195 pointsBadges:
    report
  • Tpinky
    Hello, There is also a way to give and deny access to ODBC through iSeries Navigator. If you right click on the system name, select Application Administration, select the Client Applications Tab, click on the + sign beside Client Access for Windows and this will open to the Applications. Click on ODBC Support and then select Customize. Through here you have the abitlity to add or remove individual profiles, group profiles, etc. TPinky
    4,165 pointsBadges:
    report
  • TomLiotta
    ...you should never allow users to update/change/delete database files through ODBC. This is highly unlikely and unnecessary. That is, there need not be any difference between capabilities given for ODBC and for any other database access method including green-screen. The issue isn't ODBC; the issue is that authorities aren't appropriate on the database at all. If authorities are set so that users don't have greater than *READ to data, it doesn't matter what they do with ODBC or STRSQL. There is also a way to give and deny access to ODBC through iSeries Navigator. Yes and no. This only affects the ODBC driver from iSeries Access. Any ODBC driver that doesn't honor the setting is unaffected. And note that this is also true for JDBC and any other network database access. Tom
    125,585 pointsBadges:
    report
  • Teandy
    No matter what anybody says, updating records through ODBC is a bad idea. For one thing most auditors we have had through here frown very heavily on it. For another, it is too easy for users to make bone head mistakes. I had one user that needed update authority to a file to do their job. He got the "bright" idea to use M$ access so that he could send up all his updates at once rather than use the ERP screens provided. Since he created his own queries, and there was no validation of the information being sent, he wound up totally destroying the file. Every time he did this he would call me in a panic and ask me to restore the file from the previous nights back up. After he did this for the third time, I set up an exit program to stop all adds, updates, or deletes through ODBC.
    5,860 pointsBadges:
    report
  • Craig Hatmaker
    I'm agreeing w/TomLiotta on this. Yes - Teandy is right about auditors frowning, if not outright banning updates via ODBC. It is my opinion that is a decision based on fear, not knowledge, from experiences with sloppy practices as described in the opener. As Tom said "there need not be any difference between capabilities given for ODBC and for any other database access method." He's right. Poorly written RPG programs can mangle databases just as thoroughly as any ODBC utility. Updates via STRSQL can destroy information just as easily. So it's not ODBC that is the problem. The problem is granting untrained, or poorly trained people unrestricted access. So Teandy, it is possible to layer discipline and standards over ODBC so as to prevent user errors just as effectively as ANY other method. That is the key. I'm not talking theory here. I do it all the time. For years I have let users access DB2 files via ODBC AND UPDATE them straight out of Excel - and we've NEVER had a problem. Each record gets a user ID and date/time stamp. Each field is checked for integrity. No update is made unless it passes the same stringent criteria of any RPG or COBOL program. If you want to see how check out my blog. If you want to add warnings to readers, please leave comments.Beyond Excel: VBA and Database Manipulation
    1,715 pointsBadges:
    report
  • Teandy
    Chatmaker, I looked at your blog and what I saw there was very good information for developers on how to write applications that the end user could use to update files on the i5 using ODBC. I did not see anything that would stop the division controller from using either excel or access to update the general ledger with wrong account information. That is what happened here.
    5,860 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following