Permissions per user

While reorganizing data in the database, we apply individual user permissions per person, per table to provide database security. The issue we have is anytime we drop and re-create the database, we have to apply all of the grants, in a recent restore it took 36 hours to apply the grants for 933 users to 899 tables (6 statements X 899 tables X 933 users). Is there a quicker way to apply the grants using onconfig parameters for tuning? Currently we open permissions up to public until the grants have been applied, then remove public. system parameters AIX 5.1 IDS 9.30/tools 7.30UC7 on a 4 way 8 gig IBM 690

If your application is role-aware, you could reduce it from over 5 million statements to fewer than 2000 by creating a role, doing role privilege grants to each table, and granting the role to each user. >>> "PHILLIP SHELTON" <lshelton@aholdusa.com> 4/27/2004 7:46:53 AM >>> While reorganizing data in the database, we apply individual user permissions per person, per table to provide database security. The issue we have is anytime we drop and re-create the database, we have to apply all of the grants, in a recent restore it took 36 hours to apply the grants for 933 users to 899 tables (6 statements X 899 tables X 933 users). Is there a quicker way to apply the grants using onconfig parameters for tuning? Currently we open permissions up to public until the grants have been applied, then remove public. system parameters AIX 5.1 IDS 9.30/tools 7.30UC7 on a 4 way 8 gig IBM 690

Consider roles - you grant the detailed privileges to different roles, and then grant the appropriate roles to people. The downside is that applications need to be modified to set the correct role. You should not need 6 statements per user - you can combine both users and properties... GRANT SELECT, INSERT, UPDATE ON SomeTable TO user1, user2, user3, user4; That's one statement instead of 12 - use it. Only DBA's and resource level users need index or alter permission (or reference permission); that means that you should only be using 4 statements for each user, not 6. Equivalently, what are the fifth and sixth permissions you are granting? (It just took me under 2 seconds to grant SELECT, INSERT, DELETE, UPDATE permissions on a single table to 1000 users in a single statement - IDS 9.40.UC1 running on a 333 MHz UltraSparc 10, Solaris 8). Note that grouping the permissions reduces the number of operations - running six grants per user means one insert and five updates per row in systabauth. Also note that column level permissions would complicate things, but you probably aren't doing that. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" forum.subscriber@iiug.org wrote on 04/27/2004 07:46:53 AM: > While reorganizing data in the database, we apply individual user > permissions per person, per table to provide database security. The > issue we have is anytime we drop and re-create the database, we have > to apply all of the grants, in a recent restore it took 36 hours to > apply the grants for 933 users to 899 tables (6 statements X 899 > tables X 933 users). Is there a quicker way to apply the grants > using onconfig parameters for tuning? Currently we open permissions > up to public until the grants have been applied, then remove public. > > system parameters > AIX 5.1 > IDS 9.30/tools 7.30UC7 > on a 4 way 8 gig IBM 690 >