table privileges
Amy asked why granting a user SELECT-only on a table didn't stop them updating it, given PUBLIC had all privileges on every table. The answers confirmed her suspicion: PUBLIC privileges apply to everyone as a baseline, and user-level grants only add to them. The fix is to REVOKE the PUBLIC privileges on those tables down to the lowest acceptable level (usually none or select), then grant explicit privileges per user, or via roles (default roles available from v10/11.x). She was on 11.5 and accepted the explanation.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All the tables in our db have their privileges set so that public has all privileges. Now I want to limit some tables for one user to have select only. When I enter select grant on table to user I can still do updates in the table. Is that because the public privilege is used? If so how can I restrict just certain users?
we did this a few years ago.
I think you may have to revoke public from all tables then assign privillegs
for each user
e.g
revoke all on $table_list from public
then do something like this (this is scripted)
echo " grant select on $table_list to $USER " | dbaccess $DBNAME
echo " grant update on $table_list to $USER" | dbaccess $DBNAME
echo " grant insert on $table_list to $USER" | dbaccess $DBNAME
echo " grant delete on $table_list to $USER" | dbaccess $DBNAME
echo " grant index on $table_list to $USER" | dbaccess $DBNAME
One of the experts may be able to confirm or deny this.
Another way of doing things is assining users to roles
VERSION INFORMATION? Yes, the public privileges kick in for any object that the user does not have either role specific or user level privileges for. You will have to drop the public privileges on that table and grant explicit privileges to specific users. If you have a sufficiently late version you can create roles with the correct privileges and grant the roles to the users, however, unless you have 11.xx which supports a default role, each user will have to SET ROLE to the appropriate role manually in order to get the appropriate privileges (see why we need to see your version info?) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 2, 2011 at 4:01 PM, AMY SIPULESKI <asipuleski@ssww.com> wrote: > All the tables in our db have their privileges set so that public has all > privileges. Now I want to limit some tables for one user to have select > only. > When I enter select grant on table to user I can still do updates in the > table. Is that because the public privilege is used? If so how can I > restrict > just certain users? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175ce020bc48b9049b53b350
On Wed, Feb 2, 2011 at 13:01, AMY SIPULESKI <asipuleski@ssww.com> wrote: > All the tables in our db have their privileges set so that public has all > privileges. Now I want to limit some tables for one user to have select > only. > When I enter select grant on table to user I can still do updates in the > table. Is that because the public privilege is used? If so how can I > restrict > just certain users? > Everybody has the same permissions as PUBLIC; they may also have some extra permissions. If you want to restrict tables, you must first take away PUBLIC permissions until those are the lowest acceptable common denominator (typically no permissions, sometimes select-only permission). Then you can add permissions for specific groups via roles, or specific users by explicit grants. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --20cf3054ad0189bf90049b54a7d5
Small correction. Default roles were introduced in V10. But if you're in v10 you should consider upgrade. It's on limited support and will be out of support this year. Regards. On Wed, Feb 2, 2011 at 9:55 PM, Art Kagel <art.kagel@gmail.com> wrote: > VERSION INFORMATION? > > Yes, the public privileges kick in for any object that the user does not > have either role specific or user level privileges for. You will have to > drop the public privileges on that table and grant explicit privileges to > specific users. If you have a sufficiently late version you can create > roles with the correct privileges and grant the roles to the users, > however, > unless you have 11.xx which supports a default role, each user will have to > SET ROLE to the appropriate role manually in order to get the appropriate > privileges (see why we need to see your version info?) > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Feb 2, 2011 at 4:01 PM, AMY SIPULESKI <asipuleski@ssww.com> wrote: > > > All the tables in our db have their privileges set so that public has all > > privileges. Now I want to limit some tables for one user to have select > > only. > > When I enter select grant on table to user I can still do updates in the > > table. Is that because the public privilege is used? If so how can I > > restrict > > just certain users? > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0015175ce020bc48b9049b53b350 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c1c66dacfdd049b55a7e2
Sorry I left out the version. We are at 11.5. SO it is behaving as I thought. Thanks for verifying.