Topic
3 replies Latest Post - ‏2013-07-11T13:51:41Z by nivanov1
pranalichoudhary
pranalichoudhary
6 Posts
ACCEPTED ANSWER

Pinned topic query on system table

‏2013-06-26T12:44:33Z |

Hi,

i am doing small POC in that i want to delete DB2 entities (DBuser,Group,Role)

 For delete DBuser in DB2 with command line,i am using following query

1) DELETE FROM SYSCAT.DBAUTH DBAUTH
   WHERE DBAUTH.GRANTEE = 'DBuser'

2) DELETE FROM SYSIBM.DBAUTH DBAUTH
WHERE DBAUTH.GRANTEE = 'DBuser'

this gives error. that we can not delete row in system table.

 

is there any other way or query to delete DBuser and Group and Role .

 

  • jerrys01
    jerrys01
    104 Posts
    ACCEPTED ANSWER

    Re: query on system table

    ‏2013-06-27T14:40:51Z  in response to pranalichoudhary

    IBM has established in their documentation that the SYSCAT tables (actually views) are read-only, so you can't directly remove rows from the tables.

    I think you want to just get rid of the users / groups / roles

    Users and groups are under OS control (but see note below).  You can remove a role (assuming you are the instance owner) by issuing:

    DROP ROLE <<role_name>>;

    While you can't drop a user, per se, you can remove all of the objects that user has (assuming the user is not the instance owner).

    DROP SCHEMA <<schema_name>>  RESTRICT;

    While a schema_name can be different than a user name, the default schema name for a user is his user name.

    After you have dropped the roles and the schemas, the OS administrator should then be able to remove the groups and users.

     

     

  • nivanov1
    nivanov1
    231 Posts
    ACCEPTED ANSWER

    Re: query on system table

    ‏2013-07-11T13:51:41Z  in response to pranalichoudhary

    The right way to remove user privileges and authorizations is to use the REVOKE statement.