Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2009
    Posts
    12

    Unanswered: Add a Informix Readonly User

    Morning @all,

    i have a Problem with adding a user to my informix db.

    I used GRANT and ROLE but it does not realy work well...
    And IDENTIFIED BY "passwort" does also not word

    I will add a User with an Password, who has only SELECT rights.

    Can someone helps me?

    Sorry for my bad english, greetz laza

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    What exactly not work?
    If you have insert/delete/update grants on the table to PUBLIC , this will override your ROLE.

    And where you want to use "IDENTIFIED BY" ?? in CONNECT statement?
    Take look on SQL SYNTAX...
    CONNECT
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Apr 2009
    Posts
    12
    Thanks ceinma for your answer.

    Ok, in the Syntax CONNECT is no "IDENTIFIED BY" command...

    The problem is, i have not realy much knowhow concerning Informix Database.

    Can somebody give me an example, how i add a read only user with a password?
    Or is the Code to complex?

    Thanking you in anticipation!

    Greez laza

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    To configure a user with read-only you just need:
    - Have a user defined (Informix works with OS users)
    - Be sure you don't have any grant to PUBLIC or this grant will override your role of grant for user.
    - define a ROLE or grants only with SELECT for all tables.

    If you are using version 11.10 or 11.50 , is more easily use ROLEs with function sysdbopen (search in Informix Inforcenter).

    To create the ROLE or the GRANTs automatically you can write a simple SQL , shell script or Stored Procedure.
    Using role, the code will appear like this:
    Code:
    select "grant select on "||trim(tabname)||" to myrole" from systables where tabid > 99;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Apr 2009
    Posts
    12
    Hello ceinma,

    my Informix Version is 11.10...

    I have tried to add an informix read only user.

    ..but..

    i doesn´t work :-(
    how must i creat a user? CREAT username ?!
    I have no idea...

    would you be kind enough to creat me a small script?

    I would be deeply grateful, for your help!

    Greetz laza
    Last edited by laza86; 05-08-09 at 05:07.

  6. #6
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    On Informix , don't exists CREATE USER statement, you just need execute:
    GRANT CONNECT TO user_xyz;
    GRANT SELECT ON table_xyz TO user_xyz;

    The default authentication method on Informix is the OS user.
    So, if you need grant any access to user , on machine where Informix are instaled :
    - Create the user on the OS (Linux/Unix/Windows) and define a password
    - Execute a GRANT CONNECT and GRANT SELECT for this user.
    - When connect to Informix, need to inform the user/password created on the OS.

    There others ways to work with authentication, but this is the default.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  7. #7
    Join Date
    Apr 2009
    Posts
    12
    Thx ceinma,

    now i m understand the authentification!
    And it seems to work.

    But i will set the SELECT permission to all tabels.

    This make the permission only by one table.
    >> GRANT SELECT ON table_xyz TO user_xyz;

    Can you help me again?

    Greez laza

    EDIT:

    i have used:
    select "grant select on "||trim(tabname)||" to myrole" from systables where tabid > 99;

    and i think it works!!!

    i give a feedback after the testing.
    Last edited by laza86; 05-11-09 at 05:09.

  8. #8
    Join Date
    Apr 2009
    Posts
    12
    Ohhh yes the user works

    BUT... the rights do not functioning

    The user has all rights.. why?

    My order is:

    1. Add a windows user with user rights
    2. GRANT CONNECT TO user_asdf;
    3. select "grant select on "||trim(tabname)||" to user_asdf" from systables where tabid > 99;

    Is there any mistake?

    Thanks laza

  9. #9
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    you save and execute the output of the item 3 ?
    and just for correction, miss a ";" :
    Code:
    select "grant select on "||trim(tabname)||" to user_asdf ; " from systables where tabid > 99;
    If you work with version 11.x , to make all this easy , you can use Default ROLE for users or ROLEs + sysdbopen fuction, and avoid execute a lots of grants for each user.
    Read this topics in the manuals:
    Using SYSDBOPEN and SYSDBCLOSE Procedures
    Granting a Default Role
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  10. #10
    Join Date
    Apr 2009
    Posts
    12
    >>you save and execute the output of the item 3 ?
    Yes of curse..

    I don´t think that this semikolon ( ; ) has any consequence.

    I have execute this 3 points again, with another username,
    but the user has all rights, too

    Roles... :/ another chapter, which i have no knowless ...

    It must work over GRANTs, isnt it?

    when i make a "SELECT * FROM sysusers",

    my solution is:
    ---------------------------------------
    username informix
    usertype D
    priority 9
    password
    defrole

    username informix_readonly
    usertype C
    priority 5
    password
    defrole
    ---------------------------------------

    Is the usertype relevant? or the priority?


    Thank you, laza (Sorry 4 my bad english)

  11. #11
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Ok, lets step-by-step.
    1) Generate the GRANTs , and copy here the firsts line...
    2) when you execute the GRANT, occur any error?
    3) what user you use to execute the GRANT? must be the user "informix" or the user used to create the database (with DBA grant).

    Information about table sysusers , please search in Informix Info Center: SYSUSERS

    to illustrate here is a example how create all this, tested on Informix 11.50.
    In this situation I'm connected with user "ix_dbsa" and use the dbspace "dados1'":
    Code:
    create database test_db in dados1 with buffered log  ;
    grant dba to ix_dbsa;
    create table tab1 ( cod  integer , desc char(10) );
    revoke all on tab1 from public;
    create table tab2 ( cod  integer , desc char(10) );
    revoke all on tab2 from public;
    
    grant connect to user_write;
    grant connect to user_read;
    
    grant select,insert,update,delete on tab1 to user_write;
    grant select,insert,update,delete on tab2 to user_write;
    grant select on tab1 to user_read;
    grant select on tab2 to user_read;
    
    -- To using role , replace the GRANTs above for this code:
    create role role_read;
    create role role_write;
    
    grant select on tab1 to role_read;
    grant select on tab2 to role_read;
    grant select,insert,update,delete on tab1 to role_write;
    grant select,insert,update,delete on tab2 to role_write;
    
    grant default role role_read to user_read;
    grant default role role_read to guess_users;
    
    grant default role role_write to user_write;
    grant default role role_write to director_company;
    grant default role role_write to own_company;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  12. #12
    Join Date
    Apr 2009
    Posts
    12
    First thanks ceinma for your help!

    Ok i have used both variants. With role and withour role.

    Here my log:
    --------------------------------------------------------
    create role role_read;
    -Role created.

    select "grant select on "||trim(tabname)||" to role_read;" from systables where tabid > 99;
    -(expression) grant select on *tbl_name* to role_read;
    -(expression) grant select on *tbl_name* to role_read;
    -(expression) grant select on *tbl_name* to role_read;
    -(expression) grant select on *tbl_name* to role_read;
    -(expression) grant select on *tbl_name* to role_read;
    ...

    grant default role role_read to readuser;
    - Permission granted.

    --------------------------------------------------------

    I think, everything is ok.

    And when i register in windows with: "readuser", i can create new tabels.
    But i dont think the problem is the user creation..
    I suspect, that i have user:"informix" rights in "dbaccess".. it may be, that i am logged in as "informix" user and not as "readuser" ?

    Is there any command, with which i can see my registration username and my rights in the DB?

    Greez laza

  13. #13
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    "grant select on *tbl_name* to role_read;" is a very odd output.. I never see asterisk on table name... or did you edited this output to not show the real table name?

    What's not work? what the error number? all error return a error code, copy here.


    To see what user you are logged use the "user" function:
    Code:
    select user from sysmaster:sysdual;
    To identify the rights , you have tree options
    1) INFO SQL statement (INFO)
    info privileges for table table_xyz ;
    2) select * from systabauth;
    3) dbschema -d test_db -p all
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  14. #14
    Join Date
    Apr 2009
    Posts
    12
    >>"grant select on *tbl_name* to role_read;" is a very odd output.. I never see asterisk on table name... or did you edited this output to not show the real table name?

    Yes, the *tbl_name* is to show not the real table name. sorry..

    >>What's not work? what the error number? all error return a error code, copy here.

    There is no error. The Problem is, that the user has more then SELECT rights.

    This is a interessting command:
    >>select * from systabauth;

    in this table the user "readuser" has more rights then SELECT, or?

    grantor readuser
    grantee public
    tabid 578
    tabauth su-idx---

    What happens if i make "tabauth su-idx---" to "tabauth s--------" ?
    Is this the mistake?

    And i m logged in as readuser.
    >>select user from sysmaster:sysdual;
    - readuser

    Then the problem is not the wrong user, its the rights..

    Is there any way to shirk the user "readuser" the rights. That this user has only SELECT rights?

    thanks in advance ceinma!

    laza

  15. #15
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    About systabauth values: SYSTABAUTH

    This show the readuser have INsert / Delete and Create Index grants.

    Do not update any sys* tables , you take the risk to crash your database...

    Use the revoke command to remove the grants. Search the syntax on Informix Information Center...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •