Alexander,
I have had to deal with this problem myself. What we did was:
- Get a list of all permissions granted to public, both for tables and functions;
- Create a new role 'xyz_public';
- Grant the same permissions to xyz_public that were granted to public;
- For any existing roles, add GRANT xyz_public TO rolename; (this adds the permissions of xyz_public to whatever permissions were already in rolename);
- For any user that did not already have a default role, do GRANT DEFAULT ROLE xyz_public TO username;
- 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:
Sent: Wed November 09, 2022 08:43 AM
From: Alexander Ivanov
Subject: Revoke Role public from user
Thanks for the quick response. Yes, my current task is to upgrade to 14.10.
------------------------------
Alexander Ivanov
Original Message:
Sent: Wed November 09, 2022 08:32 AM
From: Art Kagel
Subject: Revoke Role public from user
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
Original Message:
Sent: Wed November 09, 2022 08:20 AM
From: Alexander Ivanov
Subject: Revoke Role public from user
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