create user and permissions
A user asked how to create database users with differing privileges (select, insert, update, alter, backup) in Informix. Answers: Informix doesn't create its own users — logins are authenticated by the OS; you control access with SQL GRANT/REVOKE (including revoking privileges from PUBLIC so only explicit grants apply) and CREATE ROLE, granting privileges to a role, granting the role to users, who then issue SET role. Backup rights come from OS group membership (DBSA group owning $INFORMIXDIR/etc, or the onbar group for On-BAR). Pointers to the SQL Syntax/Reference manuals were given.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear All I want to create the different user with different permission like(select,insert,update,alter and backup). so please guide me to achieve this or refer me any book from where i can get the reference. Regards- Nierjesh --001636426d3bbea8720473835520
nierjesh kumar wrote:
> Dear All
>
> I want to create the different user with different permission
> like(select,insert,update,alter and backup). so please guide me to achieve
> this or
> refer me any book from where i can get the reference.
Well, the manuals can be found off here:
http://www-01.ibm.com/software/data/informix/techdocs.html
A starting point for SELECT, INSERT and UPDATE (and DELETE) can be found
here for IDS 11.50:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.o
at.doc/ids_oat_093.htm
And for ALTER can be found here:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.o
at.doc/ids_oat_032.htm
Backups are a bit more subtle and involve either becoming a member of
the optional onbar group (if you're using On-BAR) or becoming a member
of the DBSA group, which generally means changing the "group owner" of
the directory $INFORMIXDIR/etc to something like "dbsa" and then making
the person a member of that group.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Dear All
Is there any sql command for creating the users and their roles.please guide
me.
Thanks in advance.
Regards-
Nierjesh
On Mon, Sep 14, 2009 at 12:09 PM, Obnoxio The Clown <obnoxio@serendipita.com
> wrote:
> nierjesh kumar wrote:
> > Dear All
> >
> > I want to create the different user with different permission
> > like(select,insert,update,alter and backup). so please guide me to
> achieve
> > this or
> > refer me any book from where i can get the reference.
>
> Well, the manuals can be found off here:
> http://www-01.ibm.com/software/data/informix/techdocs.html
>
> A starting point for SELECT, INSERT and UPDATE (and DELETE) can be found
> here for IDS 11.50:
>
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.o
at.doc/ids_oat_093.htm
>
> And for ALTER can be found here:
>
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.o
at.doc/ids_oat_032.htm
>
> Backups are a bit more subtle and involve either becoming a member of
> the optional onbar group (if you're using On-BAR) or becoming a member
> of the DBSA group, which generally means changing the "group owner" of
> the directory $INFORMIXDIR/etc to something like "dbsa" and then making
> the person a member of that group.
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e64f688c09578b04738413dd
nierjesh kumar wrote: > Dear All > > Is there any sql command for creating the users and their roles.please guide > me. Look in the links I sent you for GRANT and CREATE ROLE. You don't create users in Informix, they're authenticated by the operating system. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
The references are the Guide to SQL Reference and the Guide to SQL Syntax manuals as well as the Guide to SQL Tutorial. What you have to do is to REVOKE all privileges from the default pseudo-user 'PUBLIC'. Once you have done that each userid will only have privileges that you explicitely GRANT to it or to a ROLE that the userid has privileges to assume. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Sep 14, 2009 at 2:03 AM, nierjesh kumar <nierjeshkumar@gmail.com>wrote: > Dear All > > I want to create the different user with different permission > like(select,insert,update,alter and backup). so please guide me to achieve > this or > refer me any book from where i can get the reference. > > Regards- > Nierjesh > > --001636426d3bbea8720473835520 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517448ad2a899dc04738bd9cf
Informix does not require you to define users within the database. It does
not maintain its own independent set of login ids. IDS validates users
connecting to the server by asking the OS to authorize the user, so, any
userid which the the OS will validate is a valid IDS userid. If the OS
authorized the user, then IDS checks to see if that userid has CONNECT
privilege or higher (RESOURCE or DBA) or failing that if the pseudo-user
PUBLIC does. If the user passes that check he is connected to the server.
As to roles, look at the CREATE ROLE command in the Guide to SQL Syntax
manual, ROLES are explained well there. Here's a quick summary. You create
the new role, then, once the role exists, you can GRANT object privileges to
the role. Then you have to GRANT permission to assume the role to each
userid that requires that set of privileges:
CREATE ROLE updater_role;
REVOKE UPDATE OF mytable FROM public;
REVOKE UPDATE OF mytable FROM some_user_id, another_user_id;
GRANT UPDATE OF mytable TO updater_role;
GRANT INSERT OF mytable TO updater_role;
GRANT updater_role TO some_user_id;
Now the user 'some_user_id' can assume the 'updater_role' role with:
SET updater_role;
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Mon, Sep 14, 2009 at 2:50 AM, nierjesh kumar <nierjeshkumar@gmail.com>wrote:
> Dear All
>
> Is there any sql command for creating the users and their roles.please
> guide
> me.
>
> Thanks in advance.
>
> Regards-
> Nierjesh
>
> On Mon, Sep 14, 2009 at 12:09 PM, Obnoxio The Clown <
> obnoxio@serendipita.com
> > wrote:
>
> > nierjesh kumar wrote:
> > > Dear All
> > >
> > > I want to create the different user with different permission
> > > like(select,insert,update,alter and backup). so please guide me to
> > achieve
> > > this or
> > > refer me any book from where i can get the reference.
> >
> > Well, the manuals can be found off here:
> > http://www-01.ibm.com/software/data/informix/techdocs.html
> >
> > A starting point for SELECT, INSERT and UPDATE (and DELETE) can be found
> > here for IDS 11.50:
> >
> >
> >
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.o
at.doc/ids_oat_093.htm
> >
> > And for ALTER can be found here:
> >
> >
> >
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.o
at.doc/ids_oat_032.htm
> >
> > Backups are a bit more subtle and involve either becoming a member of
> > the optional onbar group (if you're using On-BAR) or becoming a member
> > of the DBSA group, which generally means changing the "group owner" of
> > the directory $INFORMIXDIR/etc to something like "dbsa" and then making
> > the person a member of that group.
> >
> > --
> > Cheers,
> > Obnoxio The Clown
> >
> > http://obotheclown.blogspot.com
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > believed to be clean.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0016e64f688c09578b04738413dd
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747beea9f7f5c04738c1188