Re: Relation of OS user to Informix database user

anupam.mukherjee@xxxxxxxxx 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@xxxxxxxxxxxxx, jleffler@xxxxxxxxxx
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
.

Relevant Pages

  • RE: user permissions in a database as a whole or tables
    ... user permissions in a database as a whole or tables ... The way to give permissions to a user is a simple GRANT. ...
    (comp.databases.informix)
  • Re: user permissions in a database as a whole or tables
    ... like PDF better but that was not helping this time either. ... user permissions in a database as a whole or tables ... The way to give permissions to a user is a simple GRANT. ...
    (comp.databases.informix)
  • Re: SQL Server 2005 Stored Procedure security annoyances
    ... stored proecedure on a database wide level? ... GRANT EXECUTE ON SCHEMA::MySchema TO MyRole ... I am sure I will forget some SP's and probably forget to set the rights ... permissions because not all stored procedures are equal. ...
    (microsoft.public.sqlserver.security)
  • Re: Fwd: That whole "Linux stealing our code" thing
    ... are no longer granted those permissions. ... holder decide to "go commercial" he can not revoke permissions already ... from the date of execution of the grant; ... the right of publication of the work, ...
    (Linux-Kernel)
  • RE: [Maybe spam] Re: Relation of OS user to Informix database
    ... user who has those rights because they're a member of public? ... then you can't revoke their rights without revoking public's ... > RESOURCE permissions from the informix DBA user. ... When you created the database, was a MODE ANSI database, or a logged or ...
    (comp.databases.informix)