SQL security management (Informix/Oracle)

Path: emory!swrinde!cs.utexas.edu!uunet!ftpbox!mothost!techmpc!daves From: daves@techmpc.csg.gss.mot.com (Dave Schmitt) Newsgroups: comp.databases Keywords: sql, informix, oracle, security Message-ID: <1991Sep6.020024.18002@techmpc.csg.gss.mot.com> Date: 6 Sep 91 02:00:24 GMT Organization: Motorola Cellular Subscriber Group MIS We wish to control data access for several large new Informix and Oracle databases (hundreds of users, tables, and application modules). Considering the way the database engines work, it seems the only way to restrict access is through the SQL "GRANT" and "REVOKE" operations. Here are our concerns: 1. Looking up permissions for 400 users will degrade performance. The product of 400 users times 200 tables (ignoring column level GRANTs) is 80,000 dictionary rows to be searched for each SELECT or UPDATE (yes, we know about PUBLIC permissions, indexed searches and buffer caches, but still...) 2. The administration of these permissions will be a nightmare. Every application must be analyzed for data access requirements. Someone must define each user's abilities. Periodically all permissions must be revoked and rebuilt from scratch (unless we discover a more reasonable algorithm for keeping GRANTs current). 3. Remote user access needs must be accounted for. We're beginning to use Informix*Net/Star and Oracle Sql*Net, and may need tighter controls for remote access (though we don't claim to understand all the security issues here). 4. We'd like a solution we can use everywhere. Ideally, it would be great if we could use the same procedures and data structures (if not actual programs) for all systems (small or large, Informix, Oracle or any other SQL). What experiences have other's had? Can you recommend any approaches? Are there flaws in our reasoning? Is there a better way? Any advice, warnings, or even (gulp) sales pitches are welcome. I will post a summary of responses. PS--I'm sorry if this has been discussed before. We just started receiving the comp.databases group 3 weeks ago, and we need a solution yesterday (of course) -- David Schmitt, Systems Administrator Voice: (708)632-5562 Cellular Subscriber Group MIS FAX: (708)632-4421 Motorola, Inc., Room S356 Email: daves@csg.gss.mot.com 1475 W. Shure Dr., Arlington Hts, IL 60004