Topic
  • 3 replies
  • Latest Post - ‏2013-07-11T13:51:41Z by nivanov1
pranalichoudhary
pranalichoudhary
7 Posts

Pinned topic query on system table

‏2013-06-26T12:44:33Z | delete on query system tables

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

    Re: query on system table

    ‏2013-06-27T14:40:51Z  

    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.

     

     

  • pranalichoudhary
    pranalichoudhary
    7 Posts

    Re: query on system table

    ‏2013-07-11T06:20:11Z  
    • jerrys01
    • ‏2013-06-27T14:40:51Z

    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.

     

     

    Thank you very much for replying :)

  • nivanov1
    nivanov1
    231 Posts

    Re: query on system table

    ‏2013-07-11T13:51:41Z  

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