access
The poster (IDS 9.40) asked whether a database must be MODE ANSI to manage access with GRANT. Answer: no — logging mode and permissions are unrelated; ANSI mode should be avoided unless specifically needed, with UNBUFFERED LOGGING preferred. Switching logging mode via ondblog only takes effect after a level-0 archive (he used ontape -s -L 0 -U database). His privileges problem was that GRANT CONNECT alone left default PUBLIC table rights, so users could update; the fix was REVOKE ALL ON each table FROM PUBLIC (and from the user), then GRANT SELECT, noting NODEFDAC/ANSI mode avoid default public grants. He confirmed it worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi to all i want to manage access with grant sql statements on a database under IDS 9.40 FC 9, should the database be in logged in the Ansi Mode ? thank's in advance for your experiance.
On Wed, Jan 19, 2011 at 11:50, SMITH JOHN <daylight@webmails.com> wrote: > I want to manage access with grant sql statements on a database under IDS > 9.40.FC9; > should the database be in logged in the Ansi Mode ? > There is no compulsion for it to be a MODE ANSI database - you can manage the access just as easily if it is a logged (or even unlogged) database. The logging mode and access control are orthogonal issues; they really don't impinge on each other at all. -- 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." --20cf3054ace3b0af53049a39a4e8
Avoid ANSI mode unless you have a specific need for it. Prefer UNBUFFERED LOGGING. 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, Jan 19, 2011 at 2:50 PM, SMITH JOHN <daylight@webmails.com> wrote: > hi to all > > i want to manage access with grant sql statements on a database under IDS > 9.40 > FC 9, should the database be in logged in the Ansi Mode ? > > thank's in advance for your experiance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5c1ebb431ac049a3cd818
the undblog unbuf "database" doesn't work how to set existing database is unbuffered mode ? thank you
You have to take a level zero archive. Ondblog just sets a flag that tales effect after the archive completes. Art On Jan 23, 2011 9:23 AM, "SMITH JOHN" <daylight@webmails.com> wrote: > the undblog unbuf "database" doesn't work > > how to set existing database is unbuffered mode ? thank you > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf30433fd0792f06049a85ef04
i finally used ontape -s -L 0 -U database
it works
but i cannot set priviliges, it does'nt work i set "grant connect to user" but it lets the user update tables contents. i want to let the user do only 'select' on the tables how can i do this ? thanks in advance
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 tablelevel 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
You have to revoke privileges from 'public'. Those are default privy for users. Once you do that you can create specific prove for specific users. Art On Jan 23, 2011 9:38 AM, "SMITH JOHN" <daylight@webmails.com> wrote: > but i cannot set priviliges, it does'nt work > > i set "grant connect to user" but it lets the user update tables contents. > > i want to let the user do only 'select' on the tables > > how can i do this ? > > thanks in advance > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf3054a511269935049a8b8398
it works thank you very much
thank you