Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    78

    Unanswered: grant schema objects for a user problem

    Hello,
    I have a question about granting.
    The db manager is DB2 v7

    There is a user "X" on a database, and it creates tables X.table_t.
    There is another user "Y" on this database.
    What I want is that , whenever X creates a table on db, user Y can automatically have select/insert/update/delet rights on this table.

    I'm now doing the grantings one by one for each table.But I don't want to follow the future tables.I want it to be done automaticallly.

    I made the following grant:
    "grant alterin on schema X to user Y"
    but it did not work.
    What can i do?
    thanks all, have a nice day...

  2. #2
    Join Date
    May 2003
    Posts
    113

    Re: grant schema objects for a user problem

    grant schema objects, will only work for stored procedures and user-defined functions, or specify a comment for distinct types, cast functions that are generated for distinct types, stored procedures, triggers, and user-defined functions in the designated schemas.

    that stmt won't work for tables/views

    Originally posted by shedb
    Hello,
    I have a question about granting.
    The db manager is DB2 v7

    There is a user "X" on a database, and it creates tables X.table_t.
    There is another user "Y" on this database.
    What I want is that , whenever X creates a table on db, user Y can automatically have select/insert/update/delet rights on this table.

    I'm now doing the grantings one by one for each table.But I don't want to follow the future tables.I want it to be done automaticallly.

    I made the following grant:
    "grant alterin on schema X to user Y"
    but it did not work.
    What can i do?
    thanks all, have a nice day...

  3. #3
    Join Date
    May 2003
    Posts
    113

    Re: grant schema objects for a user problem

    you many consider use stmt:
    GRANT USE OF tablespace TO y
    If you can ensure only X create tables in such tablespace.
    Originally posted by nidm
    grant schema objects, will only work for stored procedures and user-defined functions, or specify a comment for distinct types, cast functions that are generated for distinct types, stored procedures, triggers, and user-defined functions in the designated schemas.

    that stmt won't work for tables/views

  4. #4
    Join Date
    Dec 2003
    Posts
    78

    Question

    thank a lot nidm,
    what if y is not a very privileged user as x?
    if y is only allowed to "select" the tables of x?
    can anything be done in this case?

  5. #5
    Join Date
    May 2003
    Posts
    113
    sorry, I don't have any better idea. let's see whether some experieced
    DBA may have.
    Originally posted by shedb
    thank a lot nidm,
    what if y is not a very privileged user as x?
    if y is only allowed to "select" the tables of x?
    can anything be done in this case?

  6. #6
    Join Date
    Apr 2003
    Posts
    191
    Hi shedb,

    you have the option to share os users among x and y, i.e. give them the same login. To take this to extremes, you may want to develop/create your database tables with the same user that your application uses.

    If this is not an option consider writing an application along these lines:

    for each ( table = ( select tabschema . '.' . tabname from syscat.tables where type <> 'A' ) )
    do
    grant select, insert, update, delete on table to public
    done

    Generally, DB2 requires creators of tables and views to explicitly grant other users privileges.

    Johann

    Originally posted by shedb
    thank a lot nidm,
    what if y is not a very privileged user as x?
    if y is only allowed to "select" the tables of x?
    can anything be done in this case?

Posting Permissions

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