Relation of OS user to Informix database user

Hi, I had just installed Informix Advanced Server version 10.0 for Windows and was checking out the security features. I created two operating system users, say A and B, gave both GRANT CONNECT and GRANT RESOURCE permissions from the informix DBA user. Now, I could access the entire database, including both A and B's tables by logging in as either of the users. I tried connecting as A from dbaccess and doing a REVOKE SELECT ON TABLE T FROM B. This gave an error saying no record inISAM and Unable to revoke permissions. It however allowed me to do a GRANT SELECT ON TABLE T TO B. This did not change a thing since B couldalready access A's tables. So I did a REVOKE SELECT ON TABLE T FROM B. Next, I actually logged out and logged back in as B and still, inexplicably enough, I was able to see A's tables easily enough. Any explanations would be most welcome as I have been struggling to get this working for sometime now. Also any pointers to the relation of Informix's users to the operating system level users would be welcome. Regards, Anupam

have you revoked from "public"? anupam.mukherjee@gmail.com wrote: > Hi, > I had just installed Informix Advanced Server version 10.0 for > Windows and was checking out the security features. I created two > operating system users, say A and B, gave both GRANT CONNECT and GRANT > RESOURCE permissions from the informix DBA user. Now, I could access > the entire database, including both A and B's tables by logging in as > either of the users. this would be expected as you have probably not revoke select from public I tried connecting as A from dbaccess and doing a > REVOKE SELECT ON TABLE T FROM B. This gave an error saying no record in> ISAM and Unable to revoke permissions. Correct B probably didn't have a specific select priviledge on table B that could be revoked It however allowed me to do a > GRANT SELECT ON TABLE T TO B. OK was this on the database that A created? I bet after you did this you could then do the revoke you tried above as there would now be This did not change a thing since B could > already access A's tables. So I did a REVOKE SELECT ON TABLE T FROM B. > Next, I actually logged out and logged back in as B and still, > inexplicably enough, I was able to see A's tables easily enough. Yip you still haven't revoked from public so you can still get access Any > explanations would be most welcome as I have been struggling to get > this working for sometime now. > Also any pointers to the relation of Informix's users to the > operating system level users would be welcome. i am pretty sure there is some stuff on GRANT and REVOKE in the manuals might be worth a quick look! > Regards, > Anupam

Run dbschema -d <database> | more Is one of the users a dba for the database? Run dbschema -d <database> -t <tablename> | more and that will show you the grants and revokes on that table. You probably need to revoke select fro public.

anupam.mukherjee@gmail.com wrote: > I had just installed Informix Advanced Server version 10.0 for > Windows and was checking out the security features. I created two > operating system users, say A and B, gave both GRANT CONNECT and GRANT > RESOURCE permissions from the informix DBA user. First suggestion - don't make user 'informix' the DBA; the user already has an incredible amount of power (it's God w.r.t IDS). However, this wasn't a factor in your observations. When you created the database, was a MODE ANSI database, or a logged or an unlogged database. I am 95% sure it wasn't MODE ANSI... > Now, I could access > the entire database, including both A and B's tables by logging in as > either of the users. Yes. By default, in a non-ANSI database, public is given access permission on all tables automatically, unless you have NODEFDAC set correctly in the environment when the tables are created. So, regardless of which user (A, B, or any other user C), what you saw is expected behaviour. In a MODE ANSI database, no public access is given by default - one of the reasons I'm fairly sure you're not using such a database. > I tried connecting as A from dbaccess and doing a > REVOKE SELECT ON TABLE T FROM B. This gave an error saying no record in> ISAM and Unable to revoke permissions. Only DBAs can revoke permissions on behalf of other users. Since A is only resource-level user, A can only revoke permission that they've granted. The permission should not have been removed. > It however allowed me to do a > GRANT SELECT ON TABLE T TO B. Possibly - but did it actually add anything to the systabauth table? If you read the GRANT manual pages carefully, there appears to be a loophole such that a GRANT statement might execute 'OK' without granting the permissions. > This did not change a thing since B could > already access A's tables. So I did a REVOKE SELECT ON TABLE T FROM B. Since A doesn't own the table, and A is not a DBA (just resource), this should not achieve anything. > Next, I actually logged out and logged back in as B and still, > inexplicably enough, I was able to see A's tables easily enough. Since the table owner didn't do the revoking, and the DBA didn't do the revoking, nothing was revoked. > Any > explanations would be most welcome as I have been struggling to get > this working for sometime now. You can't revoke permissions you don't have permission to revoke. > Also any pointers to the relation of Informix's users to the > operating system level users would be welcome. There's a one-to-one correspondence between Informix users and O/S users. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Thanks for the great pointers. Sorry I'm a bit of a newbie and hopefully my questions were not too basic. I did try reading up and googling but could not find much. I have one more question . Is it at all possible to create two different tables with the same name in the same database but under two different users?

scottishpoet wrote: > have you revoked from "public"? > > > > > anupam.mukherjee@gmail.com wrote: > > Hi, > > I had just installed Informix Advanced Server version 10.0 for > > Windows and was checking out the security features. I created two > > operating system users, say A and B, gave both GRANT CONNECT and GRANT > > RESOURCE permissions from the informix DBA user. Now, I could access > > the entire database, including both A and B's tables by logging in as > > either of the users. > > this would be expected as you have probably not revoke select from > public > > I tried connecting as A from dbaccess and doing a > > REVOKE SELECT ON TABLE T FROM B. This gave an error saying no record in> > ISAM and Unable to revoke permissions. > Correct B probably didn't have a specific select priviledge on table B > that could be revoked > > It however allowed me to do a > > GRANT SELECT ON TABLE T TO B.> > OK was this on the database that A created? I bet after you did this > you could then do the revoke you tried above as there would now be Absolutely true. I did try that. All of this activity was in a database which the informix user had created and which I wanted A and B to share. > > > This did not change a thing since B could > > already access A's tables. So I did a REVOKE SELECT ON TABLE T FROM B. > > Next, I actually logged out and logged back in as B and still, > > inexplicably enough, I was able to see A's tables easily enough. > > Yip you still haven't revoked from public so you can still get access > > > > Any > > explanations would be most welcome as I have been struggling to get > > this working for sometime now. > > Also any pointers to the relation of Informix's users to the > > operating system level users would be welcome. > > i am pretty sure there is some stuff on GRANT and REVOKE in the manuals > might be worth a quick look! > I downloaded their pdfs which were really good about the database level and table level privileges etc., but I understood more about the practical aspects as such from this thread of conversation > > Regards, > > Anupam