Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Location
    Denver, CO
    Posts
    4

    Question Unanswered: User Privleges - dropping a table

    Question to you all - I am running UDB 7.2 on Windows2K. I set up the system with a db2 userid that has full authority. The other id on this box is an id the application uses to connect to the db. This id needs to be able to select, sort, and update the data. However it should not be able to create or most importantly delete tables. I have tried testing this and ran into this problem. Signed in as the db2 id I create a table lets call it db2.newtable. Creates fine with the schema of db2. I log out and log back in as the application id and from the command line I am able to drop the table that was created with the db2 id. In the settings for the application id i have it granted, BINDADD, CONNECT, LOAD. What am I doing wrong here?

    thanks for the help in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: User Privleges - dropping a table

    Probably the application user is in the SYSADM_GROUP

    Cheers

    Sathyaram

    Originally posted by smeier
    Question to you all - I am running UDB 7.2 on Windows2K. I set up the system with a db2 userid that has full authority. The other id on this box is an id the application uses to connect to the db. This id needs to be able to select, sort, and update the data. However it should not be able to create or most importantly delete tables. I have tried testing this and ran into this problem. Signed in as the db2 id I create a table lets call it db2.newtable. Creates fine with the schema of db2. I log out and log back in as the application id and from the command line I am able to drop the table that was created with the db2 id. In the settings for the application id i have it granted, BINDADD, CONNECT, LOAD. What am I doing wrong here?

    thanks for the help in advance.

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: User Privleges - dropping a table

    Hi,
    Also make sure your revoke all the privileges from user PUBLIC.

    Dollar

    Originally posted by smeier
    Question to you all - I am running UDB 7.2 on Windows2K. I set up the system with a db2 userid that has full authority. The other id on this box is an id the application uses to connect to the db. This id needs to be able to select, sort, and update the data. However it should not be able to create or most importantly delete tables. I have tried testing this and ran into this problem. Signed in as the db2 id I create a table lets call it db2.newtable. Creates fine with the schema of db2. I log out and log back in as the application id and from the command line I am able to drop the table that was created with the db2 id. In the settings for the application id i have it granted, BINDADD, CONNECT, LOAD. What am I doing wrong here?

    thanks for the help in advance.

  4. #4
    Join Date
    Aug 2002
    Location
    Denver, CO
    Posts
    4

    Re: User Privleges - dropping a table

    The application ID is not in the sysadm, sysctrl or sysmnt groups. However here is another question. The original db and tables were created when the application id had these authorities. The names of these tables are for instance applicationid.events. I take this db and restore it on another server where the application id no longer should have privleges other than select, update. When I log on with the application id I can still drop those tables. Does this mean that the authority that an id had when the table was created is embeded with the table and followed the db during the restore? How can I get around this problem without creating all new tables with different schemas? That is the big issue. For some reason the application programmers decided to hard code the schema names and cannot change them. Help is appreciated.


    Originally posted by sathyaram_s
    Probably the application user is in the SYSADM_GROUP

    Cheers

    Sathyaram

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: User Privleges - dropping a table

    Yes ... authorities are maintained across restores .. If the application user created the tables, then, AFAIK, you cannot revoke privileges from the creator ...

    Cheers

    Sathyaram

    Originally posted by smeier
    The application ID is not in the sysadm, sysctrl or sysmnt groups. However here is another question. The original db and tables were created when the application id had these authorities. The names of these tables are for instance applicationid.events. I take this db and restore it on another server where the application id no longer should have privleges other than select, update. When I log on with the application id I can still drop those tables. Does this mean that the authority that an id had when the table was created is embeded with the table and followed the db during the restore? How can I get around this problem without creating all new tables with different schemas? That is the big issue. For some reason the application programmers decided to hard code the schema names and cannot change them. Help is appreciated.

  6. #6
    Join Date
    Aug 2002
    Location
    Denver, CO
    Posts
    4

    Re: User Privleges - dropping a table

    Thank you for your response. One final question then. What if I export all the data, drop the tables, re-create them while logged in as the dba id but create them with the old schema names ie applicationid.event, and then import the data. This would get around the idea of the programmers who hard coded the schema names. What do you think?

    Thanks again for your time

    Originally posted by sathyaram_s
    Yes ... authorities are maintained across restores .. If the application user created the tables, then, AFAIK, you cannot revoke privileges from the creator ...

    Cheers

    Sathyaram

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: User Privleges - dropping a table

    That should work fine ...

    Remember to grant the requisite permissions on the table to the user ...

    If I were you, I'll give tables meaningful schema names and for the application, I'll create aliases ....


    Originally posted by smeier
    Thank you for your response. One final question then. What if I export all the data, drop the tables, re-create them while logged in as the dba id but create them with the old schema names ie applicationid.event, and then import the data. This would get around the idea of the programmers who hard coded the schema names. What do you think?

    Thanks again for your time

Posting Permissions

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