Re: grant/revoke questions

Brian McLaughlin wrote: > This is a multi-part message in MIME format. Please don't post MIME. > I've searched the web and found all kinds of information on the syntax > for the grant/revoke statements. But in my experimenting, it's become > obvious I'm missing something! > > When I create a user on the server and connect to Informix as that new > user, the user seems to have full privileges to select, insert, delete, > etc. When, as the Informix user, I try to "revoke select on table3 from > testuser" I get: > > 580: Cannot revoke permission. > 111: ISAM error: no record found. That could be because there is no permission granted to testuser; only PUBLIC has the permission, so you can only revoke the permission from PUBLIC. > So I tried a "grant select on table3 to testuser" and I get: > > 302: No GRANT option or illegal option on multi-table view Which o/s and version? Which version of IDS (XPS, SE, OnLine)? Is there any chance NFS is involved? Or a synonym to a second database? > (table3 is just a simple table with a couple of columns in it and a > half-dozen rows) > > The syntax for removing permissions (other than database-level > privileges) appears to be table-by-table. Assuming I can get past the > problems above, I'd like to revoke all from all tables and then go back > and grant certain privileges on certain tables to certain users. Is > there a simple way to do that? It seems that I could add users to a > role that revokes everything and then add the priv's back on the > individual user, but A. It's still a pain to set the role up revoking > all on each table. B. As new tables are created, the role would have to > be updated to revoke privileges on it. You might want to play with the NODEFDAC environment variable. If it is set (or set correctly), then no default permissions are granted on a table when you create it - meaning PUBLIC is not granted any permission. Roles (and privileges generally) are cumulative. If PUBLIC has a permission, everyone has it. If PUBLIC has a permission that a role does not have, it doesn't matter - everyone has the public permission as well as the role permission. There isn't a simple way to fix up permissions once the permissions are too sloppy. You basically have to analyze the systabauth and (sometimes) syscolauth tables - not to mention sysprocauth, etc. Then, as the DBA, you can consistently use the GRANT and REVOKE commands with the AS grantor option to alter the permissions to the state you want. With lots of users, you script it. > I'm pretty sure I'm missing something here. > > If someone has that single piece that will make be go "Ah Hah!" I'd like > to hear what it is! Or, if it really is more complicated than that, do > you have any recommendations for a web site or a book that will clue me > in? That'd be great too! > > Thanks, > > Brian McLaughlin > Administrative Computing > George Fox University > (503) 554-2587 -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/