Re: Informix 7.12: Stored Procs vs Roles

mmcnul@wall.ny.jpmorgan.com (Mark McNulty) wrote: :In article <57f7o5$sk3@nntp.idgonline.no>, :Nils Myklebust <Nils.Myklebust@idg.no> wrote: :>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. :[snip] :>To my best knowledge this situation is equally bad with any other :>database. Nothing particular to Informix that I know of. :Actually, you can gain some security through other databases. In :Sybase, you can grant roles and permissions for users AND stored :procedures, so that the only way for a user to get to the database :is by executing a stored procedure. Even if the users know :their roles, they don't have permission to change them. Only :certain individuals (SA's, some programmers) can modify them, :and the users can't. They can only execute stored procedures :created by the programmers and installed by the DBA, since :the role setting is also done by a stored procedure. This :seems pretty well thought out. :I haven't used stored procedures with Informix, but I would :hope Informix thought this through. I guess we'll see in the :next week or two. ;-> Yes, this works about the same way in Informix. :This obviously doesn't mean you have total security, but it would :solve the Access problem, to a degree, in that the users could only :use the existing stored procedures. So while you don't get :total security, you definately get MORE security than blindly :allowing users to execute SQL. Yes you do gain more security. The problem still remains that the security has prety big gaping holes that aren't all that difficult to get through for someone who wants to. I am working on a suggestion to Informix for ways to close these holes much more effectively. That can be done reasonably simply, but requires some additions to the engine. These may be possible to implement in Universal Server, so I will wait for some more info on that engine. :You could also cheat, to a degree, by modifying the user's password :and/or login before sending it to the database, so that they really :can't use the same password for another application, because they :don't really know the real password/login. :Again, this isn't total security, but it is better security. :I guess some would say that anything less than %100 secure isn't :secure, but I have yet to see something %100 secure. True. We simply want a little more than what is currently available, including the things you have described here. :Good luck, :Mark McNulty :mmcnul@jyacc.com :Note- these views are mine and not those of JP Morgan or JYACC. 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