IDS Forum

Re: access

Posted By: Jonathan Leffler
Date: Sunday, 23 January 2011, at 1:13 p.m.

In Response To: Re: access (SMITH JOHN)

On Sun, Jan 23, 2011 at 08:38, SMITH JOHN <daylight@webmails.com> wrote:

> but I cannot set privileges; it doesn't work!
>
> I set "grant connect to user" but it lets the user update tables contents.
>

GRANT CONNECT TO someuser; lets the user connect to the database. The table
level permissions then control what the user can do. By default, the
general populace is granted all permissions on the table.

> i want to let the user do only 'select' on the tables
>

So:

REVOKE ALL ON EachTable FROM PUBLIC:
REVOKE ALL ON EachTable FROM someuser;

GRANT SELECT ON EachTable TO PUBLIC;

The first removes all public access permissions; everyone always has the
permissions granted to public, so if the public can update the table, so can
every user. Even if the user also has access control records that don't
mention update - the base permissions are controlled by what the public can
do.

The second ensures there is no permission granted to someuser by the current
user -- however, if there are other people empowered to grant permissions on
the table and one of those has granted someuser permission to update the
table, then someuser can update the table. (A similar caveat applies to the
permissions granted to public; if two DBAs or one DBA and the
(RESOURCE-privileged) table owner both give permissions to public, then you
have more cleanup to do.)

The third allows everyone to read the table, but since public has no
privileges other than that (if you did the clean up - or never got the
granted privileges into a mess in the first place), they cannot update (or
delete from, or insert into) the table.

There is an environment variable NODEFDAC which can be set to enforce 'no
permission granted to public when table is created'. There may also be an
ONCONFIG parameter for the job. AFAIK, it is a per session env var; you
can't set it for the server and have it take effect.

MODE ANSI databases operate as if NODEFDAC is in effect - you have to
explicitly grant all permissions.

--
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."

--001636c5b15b1a89ac049a8757da

Messages In This Thread

• access
  SMITH JOHN -- Wednesday, 19 January 2011, at 2:50 p.m.
  • Re: access
    Jonathan Leffler -- Wednesday, 19 January 2011, at 4:24 p.m.
  • Re: access
    Art Kagel -- Wednesday, 19 January 2011, at 8:14 p.m.
    • Re: access
      SMITH JOHN -- Sunday, 23 January 2011, at 11:23 a.m.
      • Re: access
        Art Kagel -- Sunday, 23 January 2011, at 11:25 a.m.
      • Re: access
        SMITH JOHN -- Sunday, 23 January 2011, at 11:34 a.m.
        • Re: access
          SMITH JOHN -- Sunday, 23 January 2011, at 11:38 a.m.
          • Re: access
            Jonathan Leffler -- Sunday, 23 January 2011, at 1:13 p.m.
            • Re: access
              SMITH JOHN -- Monday, 24 January 2011, at 9:19 a.m.
          • Re: access
            Art Kagel -- Sunday, 23 January 2011, at 6:05 p.m.
            • Re: access
              SMITH JOHN -- Monday, 24 January 2011, at 9:19 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.