Re: Informix 7.12: Stored Procs vs Roles

There is one significant problem with set role. If someone know the role name they can set it themselves (via an ODBC connection perhaps) and gain the same access to the database. Of course someone who know the stored procedures could update the database via them in an ODBC application as well. Possibly a little harder, but not that much. I know of no way you can currently protect your data in an Informix database against malicious users if you have enabled access for a client program running on a Windows based PC. Roles and/or stored procedures makes it a only little harder then with a wide open database like you seem to have now. With this current situation anyone can easily install ODBC on their PC (if they don't allready have it), start MS Access (or any other ODBC application), use the same username and password they use in their PowerBuilder application, and gain free access to the database. With MS Access they can even get a table displayed in a grid, start typing anywhere in the table and have the data updated. I am working on a proposition for Informix that would make this allmost impossible. I don't know if Informix will do anything about it. To my best knowledge this situation is equally bad with any other database. Nothing particular to Informix that I know of. Oracle has a password option on their set role statement, but I can't see that it is very helpfull in itself. Someone will have to know that password anyway. You could of course embed it in your application and not let the user know it, but that's not my kind of solution. As a conclusion I would not use stored procedures for a PowerBuilder application for this reason. The extra hassle in programming would be much more work than it's worth to me. It's another issue to use stored procedures where they can give you better performance. philcya@aol.com wrote: :We are using PowerBuilder 5 for our development tool. However, the :Director of our Data Architecture group is toying with the idea that to :ensure database integrity ALL access to the database must be done via :stored procedures. I am trying to convince him that action will cause :loss of productivity on the development side of the house (since PB's data :window has a good Update function) and that we could do the same thing :using Informix's ROLE ability. Every use would be granted minimal access :rights from their login and each application would then set their role for :the duration of the application session to whatever role gives them the :access rights needed. This way we could get the best of both worlds: data :integrity and faster development. :I was just wondering what others thought of this. Is there something I'm :missing that stored procs would be a better way to go? :BTW - I do know that it is possible to intercept the datawindow update in :the sqlpreview event and call a stored procedure instead. I was hoping to :avoid as much additional coding as possible! :Phil Yandel | "I believe that life is 10% what happens :Sr Application Developer | to me and 90% how I react to it." :Amercian Medical Association | - Chuck Swindoll :phil_yandel@ama-assn.org | All opinions are my own... :PhilCYa@aol.com | ...no one else wants them Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org