Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Feb 2009
    Posts
    14

    Question Unanswered: Restrict priveleges on all table except one IN DB2

    I'm totally new to DB2 and need some assistance please, I need to create a user to have access to only one table on a schema. I was able to create the user. On the table permissions i gave it the relevant priveleges as well as on the schema but the user has access to the other tables as well in that schema. How is it possible to restrict access on all the other tables (NO read update insert etc. on any other tables)
    Please assist

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Dont give schema priv
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Feb 2009
    Posts
    14
    I removed the access from the schema, but user still has access to the other tables. User can still update read insert on other tables. Need to restrict him on all tables except one

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You should create the database using the RESTRICTIVE keyword. Then you grant only those privileges that your users shall have.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2009
    Posts
    14
    The database is already created, the schemas already exist for some time now. Just need to create a new user with restricted access on all the other tables except one. There is a way but it will take alot of time (to grant the exclude permssions for the user on each table, and there are alot of tables really alot. There must me simple way of doing this

  6. #6
    Join Date
    Feb 2009
    Posts
    14

    Unhappy

    Does anyone have a solution to my problem Theres 3000 tables I dont want to go to the permissions of each table, please assist in this question

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The simple answer is : Create a OS user and grant CONNECT privilege on the database and SELECT access to the table to that user.

    --
    Connect to the database as the user and post the output of

    db2 get authorizations

    ---

    What are the permissions on the other tables ? Granted to public? Granted to a group in which the user is a part of ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Feb 2009
    Posts
    14
    I have done that, I created the user granted access to the database and relevant table, but it has access to the other table by default somehow. The
    public user has change access(read, add, update, delete, execute).

  9. #9
    Join Date
    Feb 2009
    Posts
    14
    I have done that, I created the user granted access to the database and relevant table, but it has access to the other table by default somehow. The
    public user has change access(read, add, update, delete, execute) and the user i created i did not place it in any group

  10. #10
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    are you on windows ? check the user you are creating dont have admin rights on the server
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  11. #11
    Join Date
    Feb 2009
    Posts
    14
    This is a OS400 machine, and I created the user on the database level only User doesn't have admin privileges or any other privileges on the operating system level

  12. #12
    Join Date
    Feb 2009
    Posts
    14
    This is a OS400 machine, and I created the user on the database level only User doesn't have admin privileges or any other privileges on the operating system level

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    DB2 on os400 is a totally different beast ..

    The access may be due to the operating system policies ... Request your os400 admin to check if the new user has read access to the data file.

    Unlike other platforms, in simple terms, db2 on iseries is a SQL wrapper to access the native data files. Therefore, the permissions you grant for tables is same as the OS permissions you grant to the data file.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Feb 2009
    Posts
    14
    so in other words if the user has read,update access to the datafile, he'll have read, update access to all the tables. I have to individually set the permissions for each table to restrict access. Thats not very user friendly

  15. #15
    Join Date
    Feb 2009
    Posts
    14
    I guess there no true answer to this question, thanks all for your assistance, logged a call with IBM/DB2 speacilists will update this subject if they come up with anything positive.
    Thanks again

Posting Permissions

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