Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2014

    Unanswered: new readonly database user, I am missing something

    Sometime ago I set up a user called "repuser" on my server. Before I use it I want to make sure this user only has readonly access to my informix database tables. So, before I actually begin using the repuser id, I've been doing testing to make sure it really only has read only access. However, my testing results are showing that the repuser id can actually perform updates on the tables and I am unable to explain why. I'm hoping this is where you will be able to assist me. Here is some information that may aid in uncovering the issue:

    If I run this select as the informix user: SELECT username, usertype FROM sysusers WHERE username = "repuser";
    It shows me repuser is correctly a usertype of "Connect" and NOT "DBA" - it originally was a D and I changed it to a C

    If I run dbschema -d mylivedb -p repuser
    this is returned: No permissions for user repuser

    If I run dbschema -d mylivedb -p all | grep repuser
    this is returned: grant connect to "repuser"

    In this example, I will use the fund table:

    If I run this: SELECT * FROM systabauth WHERE tabid = (SELECT tabid FROM systables WHERE tabname = 'fund');
    I get this in return:

    grantor grantee tabid tabauth
    informix public 109 s--------
    informix web 109 su-id----

    Notice that repuser is not listed for the fund table. That means the user "public"'s permissions should take over (which are select only - not update). So, why is it that I can run these commands below successfully on my database table (fund) while logged in as repuser?

    UPDATE fund SET pay_date = "2013-01-01" WHERE (herid= "123");
    I then immediately change it back (still logged in as repuser):
    UPDATE fund SET pay_date = null WHERE (herid= "123");

    I would not have expected being able to update this fund table as repuser. What am I missing? Thanks so much for your help, this is one of the first users I have set up so I really appreciate any help you can give me! Happy Halloween!!!

  2. #2
    Join Date
    Sep 2011
    Pont l'Abbé, Brittany, France
    Provided Answers: 1
    Did you explicitly "revoke DBA from repuser" or just change 'D' to 'C' in the sysusers table ?
    I know, stupid question, but I doubt that the latter would give you the expected results.

    I would, in your case
    revoke dba from repuser;
    revoke connect from repuser;
    grant connect to repuser;
    revoke UPDATE,INSERT,DELETE on funds FROM repuser ;
    check with
    dbschema -d database -t funds
    to see what the engine understands as of permissions for this table

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts