Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Revoke Role public from user

    Posted 11/09/22 08:21 AM
    Hello.
    I have an old Informix 9. Now I need to create a user with select permission only.

    1. I have created an OS user and am using GRANT CONNECT.
    2. I checked that the user can connect and see my tables, but can edit the data.
    3. I tried REVOKE ALL ON table_name FROM user_name, it worked but the user can still edit the data.
    4. I understand that this is because  PUBLIC have permissions.
    5. I tried REVOLE ALL for table_name FROM PUBLIC and GRANT SELECT for table_name TO user_name, i  t worked.

    But I will need to do this for all my tables. And I'm a little afraid to revoke permissions from PUBLIC, because this is a very important base, and I'm not fully administrating it.
    Is there a way to REVOKE PUBLIC from user_name?

    ------------------------------
    Alexander Ivanov
    ------------------------------

    #Informix


  • 2.  RE: Revoke Role public from user
    Best Answer

    Posted 11/09/22 08:31 AM
    Alexander:

    No. PUBLIC is not a true role but a special internal user, similar to user informix, that just incidentally shares its privileges with all connected users.

    You will have to determine what privs each authorized user is supposed to have then grant those to each user individually or, better, create a ROLE for each set of privs, grant those privs to the roles, then make the appropriate role the default for each user, and finally revoke all from public for every table. It's a lot of work but worth it in the end as once the roles are defined and priv'd it's easy to add users and checking privs becomes faster.

    Art


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Revoke Role public from user

    Posted 11/09/22 08:33 AM
    Ph. Also, it's past time to upgrade to a supported release! V14.10 is MUCH faster and is supported on MUCH faster modern hardware.

    Arr


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 4.  RE: Revoke Role public from user

    Posted 11/09/22 08:44 AM
    Thanks for the quick response. Yes, my current task is to upgrade to 14.10.

    ------------------------------
    Alexander Ivanov
    ------------------------------

    Original Message


  • 5.  RE: Revoke Role public from user

    Posted 11/10/22 02:08 PM
    Edited by System Admin 01/20/23 04:25 PM
    Alexander,

    I have had to deal with this problem myself.  What we did was:

    1. Get a list of all permissions granted to public, both for tables and functions;
    2. Create a new role 'xyz_public';
    3. Grant the same permissions to xyz_public that were granted to public;
    4. For any existing roles, add GRANT xyz_public TO rolename; (this adds the permissions of xyz_public to whatever permissions were already in rolename);
    5. For any user that did not already have a default role, do GRANT DEFAULT ROLE xyz_public TO username;
    6. Revoke all permissions (in our case, this included SELECT) from public.
    [edit to add] In your case, you would exclude your SELECT-ONLY user from step 5.  [end edit]

    Later, in a separate exercise, we embarked on the process that Art advocated above, in which we divided our user base into roles, evaluated exactly what permissions each role required, granted those permissions to those roles, and revoked the corresponding permissions from xyz_public.  Eventually, xyz_publix was left with no permissions, at which time we did a REVOKE xyz_public FROM rolename (for all of the roles) and then DROP ROLE xyz_publc;

    We may not have had to do that last REVOKE, probably the DROP would have cleaned it up, but it was just a way to make sure that everything was cleaned up. 

    Now, every user in our database has a DEFAULT ROLE, and public has no permissions.  Until someone screws up and forgets to do the REVOKE ALL ON tabname FROM public when they create a new table.  Thus, you still have to monitor for that if there are other users who can create new tables.

    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 6.  RE: Revoke Role public from user

    Posted 11/10/22 01:31 AM
    Hi Alexander.

    We have an article on that:

    https://www.oninitgroup.com/faq-items/informix-read-only-access

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



Related Content