Re: Informix permissions (IDS 9.30)
You can manipulate system tables directly as "informix" and reduce the
number of statements dramatically as follows:
1) Remove all permissions:
SELECT tabid FROM systables
WHERE tabid > 99 -- exclude system tables
AND tabtype = "T" -- exclude views and synonyms
INTO TEMP usertables;
DELETE FROM systabauth
WHERE tabid IN (SELECT * FROM usertables);
DELETE FROM syscolauth
WHERE tabid IN (SELECT * FROM usertables);
2) Run a script to set default permissions for each table/column.
3) Apply non-default permissions only per user.
4) Combine statements, eg. "GRANT SELECT, UPDATE ON ... TO ...".
5) Backup your settings:
UNLOAD TO "systabauth.unl"
SELECT * FROM systabauth
WHERE tabid IN (SELECT * FROM usertables);
UNLOAD TO "syscolauth.unl"
SELECT * FROM syscolauth
WHERE tabid IN (SELECT * FROM usertables);
6) Restore your settings by repeating (1) followed by:
LOAD FROM "systabauth.unl"
INSERT INTO systabauth;
LOAD FROM "syscolauth.unl"
INSERT INTO syscolauth;
Regards,
Doug Lawry
www.douglawry.webhop.org
"bobglass" <ssrmg1@yahoo.com> wrote:
> 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