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

    Question Unanswered: authentication for db and stored procedures

    In our DB2 v7.2 server database working in AIX, we had a user USER1(let's say) in the group which has the dbadmin privileges on the database.
    Now I have to restrict the priviliges of USER1, such that he can only *create tables,views,indexes
    *select/update/insert/delete tables(but not of the SYSCAT,SYSIBM or SYSSTAT)
    *execute,create,change stored procedures(java stored procedures and sql procedures) that have been created.

    -I have created another group that has no privileges, remove USER1 from dbadmin group and add him to this group.
    -I have granted the user bindadd,connect, createtab,load on this database.
    -I have granted "all privileges" on tables whose schemas are not SYSIBM,SYSCAT or SYSSTAT.

    My questions are:
    ? I did not grant select on tables owned by SYSIBM,SYSCAT or SYSSTAT.Will it cause any problem when USER1 wants to run the restricted priviliges above(marked with *)?
    ? I don't know the commands to authenticate USER1 for the stored procedure part above.Can you also help me with this?
    ? Do i need to make any additional grants?

    Thanks a lot,

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    You will need to grant select rights to the system (SYSIBM, SYSCAT, SYSSTAT) schemas. They need to be able to read the catalog to perform just about anything else.

    To grant for stored procedures:
    Grant EXECUTE on package <package name> to group <group name>

    The problem with this is that you need to know the package name of the stored procedure. I think this can be obtained from the syscat.packagedep view. Version 8 has fixed this.

    Additional grants; You will need to grant execute (same as above) for UDFs.



Posting Permissions

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