RE: Question on use of CLI and table privileges
I'm going through the same process,
} -----Original Message-----
} From: Richard Spitz [SMTP:richard.spitz@ana.med.uni-muenchen.de]
} Sent: Friday, November 14, 1997 5:56 AM
} To: informix-list@rmy.emory.edu
} Subject: Re: Question on use of CLI and table privileges
}
} thorner@hboc.com wrote:
} > Problem:
} > Need to restrict user access when using ODBC to read-only, since
} > this is an accounting system and the auditors demand that all
} > updates be done through the 4GL front end. Any ideas on how I
} > can restrict ODBC access to RO, while maintaining the RW privilege
} > for 4GL users?
}
} This is my pet peeve I have with Informix network access. Since
} user authorization is done via UNIX accounts, anyone who knows the
} UNIX user name and password can connect to the database via any
} Informix-NET based ODBC product and happily fire away at the data.
} Any restrictions that are imposed on the user by the application
} that is normally started by that login are taken away.
}
} One thinkable solution is to make the application setuid, so it will
} connect to the database as another user that is unknown to the
} actual users. This means, however, that this app must be owned by
} root and have the "s"-bit enabled which may raise other security
} concerns. And remember that this "solution" only implements
} "security by obscurity" which can mean no security at all.
}
} > There are 600 tables and 6000 users on this system. The idea I have
} > come up with so far is to restrict ODBC access using
} /etc/hosts.equiv
} > to a few users. Give these users RO access, and keep all other
} > users at RW access. However, this means setting specific tables
} > access privileges for all users for all tables, a maintenance
} > nightmare at best.
}
} It is generally not a good idea to grant write privileges to public.
} ANYONE who can connect to the database can then manipulate your data.
} It is naive to rely on the application to manage security, as you
} have already found out the hard way.
}
} You will have to define access privileges on a user-by-user and
} table-by-table basis.
[Scott] We have done this only to end up with 60 or 70 extents
on the syscolauth, etc tables. This obviously kills engine
performance... There doesn't appear to be a better solution other than
being less stringent with our permissions (something by boss says is
unacceptable ;-)
} Write a small program that will issue the
} necessary GRANT statements, this works when you build these
} statements from strings with the user name and table name as
} variables and then PREPARE and EXECUTE these statements.
[Scott] My idea was to select from systabauth, syscolauth, and
sysusers, on a similar user, load this into a temp table, change the
username and then insert back into the sys tables. <phew> This has it's
own set of problems. For example Informix tends to ignore the rows that
I insert until I do a dbschema and then run the resulting grant
statements.
} E-Mail me if you need a crude example.
[Scott] I would love an example (crude or otherwise) as my
method is killing database performance!
} Hope this helps,
} Richard
} --
} +--------------------------+------------------------------------------
} +
} | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de
} |
} | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413
} |
} | Klinikum Grosshadern | FAX : +49-89-7095-8886
} |
} | 81366 Munich, Germany | GSM : +49-172-8933578
} |
} +--------------------------+------------------------------------------
} +