grant/revoke questions

Posted in 2005

Topics: Error Codes & Troubleshooting, Security, Permissions & Auditing

Brian McLaughlin — Wed, 23 Mar 2005 10:40:34 -0800 — source: Usenet: comp.databases.informix

This is a multi-part message in MIME format. ------_=_NextPart_001_01C52FD7.CC8655EA Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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! =20 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. =20 So I tried a "grant select on table3 to testuser" and I get: 302: No GRANT option or illegal option on multi-table view =20 ( table3 is just a simple table with a couple of columns in it and a half-dozen rows ) =20 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. =20 I'm pretty sure I'm missing something here. =20 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!=20 =20 Thanks, =20 Brian McLaughlin Administrative Computing George Fox University (503) 554-2587 =20 =20 ------_=_NextPart_001_01C52FD7.CC8655EA Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" = xmlns=3D"http://www.w3.org/TR/REC-html40">; <head> <meta http-equiv=3DContent-Type content=3D"text/html; = charset=3Dus-ascii"> <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)"> <o:SmartTagType = namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" name=3D"PlaceType"/> <o:SmartTagType = namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" name=3D"PlaceName"/> <o:SmartTagType = namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" name=3D"place"/> <o:SmartTagType = namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" name=3D"PersonName"/>  <style>  </style> </head> <body lang=3DEN-US link=3Dblue vlink=3Dpurple> <div class=3DSection1> 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!<o:p></o:p> <o:p> </o:p> 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 ontable3 from testuser” I get:<o:p></o:p> 580: Cannot revoke = permission.<o:p></o:p> 111: ISAM error: no record  = found.<o:p></o:p> <o:p> </o:p> So I tried a “grant select on table3 to = testuser” and I get:<o:p></o:p> 302: No GRANT option or illegal option on multi-table = view<o:p></o:p> <o:p> </o:p> ( table3 is just a simple table with a couple of = columns in it and a half-dozen rows )<o:p></o:p> <o:p> </o:p> 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.<o:p></o:p> <span = style=3D'font-size:10.0pt; font-fa

↪ replying to Brian McLaughlin

Jonathan Leffler — Thu, 24 Mar 2005 06:43:27 GMT — source: Usenet: comp.databases.informix

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/

Glossary

ISAM error