insert/update/delete permissions
Hey all, We have a system with 300 users on it. We want to revoke insert/update/and delete on one table from one user. To do so, we have to revoke insert/update/delete from public, and then grant it to the othere 299 users. So the questions are 1) will it affect the table performance to have 299 * 3 grant statements on it? 2) Is there a limit when the performance problems arise? 3) Is there a limit of the number of grants on one table? Thanks! Kate Tomchik [ kate@iiug.org ] www.iiug.org International Informix Users Group Board of Directors The last person to have this job was a Greek named Sisyphus.
Why not create a role and grant that role to the 299 users. j. On Apr 6, 2012, at 10:00 AM, Kate wrote: > Hey all,=20 > We have a system with 300 users on it. We want to revoke = insert/update/and=20 > delete on one table from one user. To do so, we have to revoke=20 > insert/update/delete from public, and then grant it to the othere 299 = users.=20 >=20 > So the questions are=20 > 1) will it affect the table performance to have 299 * 3 grant = statements on=20 > it?=20 > 2) Is there a limit when the performance problems arise?=20 > 3) Is there a limit of the number of grants on one table?=20 >=20 > Thanks!=20 > Kate Tomchik [ kate@iiug.org ] www.iiug.org=20 > International Informix Users Group Board of Directors=20 >=20 > The last person to have this job was a Greek named Sisyphus.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
On Fri, Apr 6, 2012 at 07:00, Kate <kate@iiug.org> wrote:
> Hey all,
> We have a system with 300 users on it. We want to revoke insert/update/and
> delete on one table from one user. To do so, we have to revoke
> insert/update/delete from public, and then grant it to the othere 299
> users.
>
> So the questions are
> 1) will it affect the table performance to have 299 * 3 grant statements
> on it?
>
It will take marginally longer to check permissions when a statement is
prepared for execution. Otherwise, it has no effect.
> 2) Is there a limit when the performance problems arise?
>
Not really.
> 3) Is there a limit of the number of grants on one table?
>
No.
Can you exploit roles for this? For your untrusted user(s), have a role
without the modify privileges and for your more trusted users (the 299),
have a (default) role that gives them the modify privileges?
This suggestion isn't a slam-dunk "thou shalt do it this way" option
because (at the moment) only one role can be active at a time as the
default role. But you might be able to leverage it by a more complex
system of role privileges.
For example:
CREATE ROLE rd_tableX;
CREATE ROLE up_tableX;
REVOKE ALL ON tableX FROM rd_tableX, up_tableX;
GRANT SELECT ON tableX TO rd_tableX, up_tableX;
GRANT INSERT, DELETE, UPDATE ON tableX TO up_tableX;
CREATE ROLE mostly_trusted;
GRANT up_tableX TO mostly_trusted;CREATE ROLE not_so_trusted;
GRANT rd_tableX TO not_so_trusted;
-- Now you have:
GRANT DEFAULT ROLE not_so_trusted TO unprivileged_user;
-- Repeat 299 times:
GRANT DEFAULT ROLE mostly_trusted TO more_priv_user001;
GRANT DEFAULT ROLE mostly_trusted TO more_priv_user002;...
GRANT DEFAULT ROLE mostly_trusted TO more_priv_user299;
This way, you grant one role to each user, but that role describes the
permissions they're allowed to use.
> The last person to have this job was a Greek named Sisyphus.
>
Cute - I hadn't heard he'd been allowed to get away from rock rolling at
all.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--f46d040891eb4d27e004bd03b9bd
Kate,
It would be better to create one or more roles, assign those roles to users
based on similar access requirements, then grant privileges to the roles
instead.
MUCH easier to maintain. Then when/if you add a new user, you normally
just have to determine which set of privs the new users should get, grant
that role to the user and make it his/her default role and done. If a new
class of user comes to be, you then have to add that role, but it would be
done once for the first user of the new access class and after that life is
easy.
Performance of thousands of privs on thousands of tables can be slow.
Bruce's site, for example, used to take 30 mins to execute a dbschema
because if the privs. After switching to roles such things are back to
reasonable.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Fri, Apr 6, 2012 at 10:00 AM, Kate <kate@iiug.org> wrote:
> Hey all,
> We have a system with 300 users on it. We want to revoke insert/update/and
> delete on one table from one user. To do so, we have to revoke
> insert/update/delete from public, and then grant it to the othere 299
> users.
>
> So the questions are
> 1) will it affect the table performance to have 299 * 3 grant statements on
> it?
> 2) Is there a limit when the performance problems arise?
> 3) Is there a limit of the number of grants on one table?
>
> Thanks!
> Kate Tomchik [ kate@iiug.org ] www.iiug.org
> International Informix Users Group Board of Directors
>
> The last person to have this job was a Greek named Sisyphus.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340815a783d204bd03c263