Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2016

    Unanswered: Queries regarding the user rights

    Hi all,

    i have a Problem with users rights
    I used GRANT and ROLE but it does not really work well...
    I added a User who has only SELECT & connect rights.

    Can someone helps me?

    Table creates call userlist
    Create role if not exists 'grpdata';
    Grant connect to 'pkelaska';
    Grant select on userlist to 'grpread' as 'informix';
    revoke dba from 'grpread';
    revoke dba from 'pkelaska';
    revoke all on userlist from 'public';

    Apart from this the user called pkelaska can insert & drop the new & old table.

    Please find the execute output for reference.

    dbaccess sbtor -

    Database selected.

    > drop table if exists orders1;

    Table dropped.

    > create table orders1 (
    login char(18),
    name varchar(80),
    firstname varchar(80),
    permission char(3),
    department char(3),
    group char(12)
    create unique index if not exists iu_orders1 on orders(login);> > > > > > >
    Table created.


    Index created.

    My queries below
    1. How I can assign the only select permission on permanent basis .
    2. How we can remove the other permission.

    Please suggest on above queries


  2. #2
    Join Date
    May 2008

    probably the user inherited the permissions from "public".
    When you create tables in informix, the default setting is to grant automatically all rights (insert/select/delete/update) to public.
    There are two exceptions (I know about):
    - setting the environment variable NODEFDAC to "yes" (without the quotes) before creating the table within the session
    - using ANSI logging


    dbschema -p pkelaska -d sbtor

    to see the permissions.

    So, in order to create a user or role that only as select permissions, you have to revoke the persssions from public first (after verifying the above) and granting the permissions everybody else is allowed to have afterwards.

    Note that you have to specify the exact user / role names afterwards, not public! As the above mentioned user is also part of "public".

    Note as well: If you revoke permissions from "public" on a live system, it can have dire consequences, as permissions are revoked from all users at once which were not granted permissions explicitly . You may have to plan for a downtime!

Posting Permissions

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