Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    33

    Red face Unanswered: Read only access to database

    Hello everybody,

    can we give read only access for a particular user to a database.I heard about Roles but never really worked on it.Can anyone tell me this can be done or any script needed for it.Any assistance is greatly appreciated

    Thanks
    Mallik

  2. #2
    Join Date
    Nov 2004
    Posts
    143
    GRant connect access to the database and only the "select" access to all the tables in the db.

  3. #3
    Join Date
    Sep 2008
    Posts
    33
    Thanks Math..exactly trying to do that.......

  4. #4
    Join Date
    Sep 2008
    Posts
    33
    Hi Nitin,

    Is der anyway of granting select privledge to schema.My database contains many tables and i need to grant select privledge to a user who want to run reports.Any assistance is greatly appreciated...

    Thanks
    Mallik

  5. #5
    Join Date
    Nov 2004
    Posts
    143
    Not sure whether I have understood your question, but if you want select access for few users and dont want to grant table level select access everytime, then grant select to public for all the tables. You can generate a sql which will help you

    select "grant select on ", tabname, " to public;" from systables
    where tabid > 99
    and tabtype = "T"
    and tabname not matches "sys*";

    regards,

    Nitin

  6. #6
    Join Date
    Aug 2005
    Posts
    140
    I think the last 2 conditions from
    select "grant select on ", tabname, " to public;" from systables
    where tabid > 99
    and tabtype = "T"
    and tabname not matches "sys*";

    are not necessary.

    If he wants to grant select to all tables then it is reasonable to grant -for example- views too.
    And the last condition "sys*" is surely not necessary. System tables have id <99 and if there exists tables with name like "systematicka" or something like that, then the above select will not work for them.

  7. #7
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311

    Exclamation Be careful!!!

    Be careful!!!

    If you have INSERT/UPDATE/DELETE granted to public on all this tables , this grant overwrite any other SELECT grant to specified user. In the other words, any other grant are useless.

    Roles are very useful, but will be only if you use the Informix version 11.XX , then you can use the buildin function sysdbopen() (look the manual for more information)
    If you have the version previous 11.XX you will need change the program code to active the role manually.

    Roles are like profiles where can be used by the user.
    To define a role, is something like this (consult the Sql Syntax manual):
    Code:
    create table testA_table (code int);
    create table testB_table (code int);
    revoke all on testA_table from public;
    revoke all on testB_table from public;
    -- create roles
    create role r_readonly;
    create role r_readwrite;
    -- define what user can use the role
    grant r_readonly to user_cesar;
    grant r_readonly to user_stan;
    grant r_readwrite to user_stan;
    -- define the table grants to role
    grant select on testA_table to r_readonly;
    grant select on testB_table to r_readonly;
    grant select,insert,update,delete on testA_table to r_readwrite
    grant select,insert,update,delete on testB_table to r_readwrite;
    -- define the default role to user
    grant default role r_readonly to user_stan;
    -- if want to remove the default role:
    -- revoke default role to user_stan
    This way , to user xpto_stan ways start the session with select grants...
    The user xpto_cesar need to active the role to use them:
    Code:
    -- when connect with xpto_cesar
    select * from testA_table; --got error
    set role r_readonly;
    select * from testA_table; --works
    ________________________________________
    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).
    ________________________________________

  8. #8
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    ops, a little correction,

    If you use version 10 or greater you don't need use the sysdbopen() function, the GRANT DEFAULT ROLE already do the job.
    ________________________________________
    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).
    ________________________________________

  9. #9
    Join Date
    Sep 2008
    Posts
    33
    Thanks folks...still trying to develop a complete script with your valuable suggestions......

Posting Permissions

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