Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    10

    Unanswered: Revoking Create table rights from user

    Hi,
    I have a user XYZ. I want to revoke the create (DDL) rights from him. I have already revoked CREATETAB and IMPLICIT_SCHEMA rights fromthe user and also from PUBLIC. The output of "get authorizations" is

    Direct SYSADM authority = NO
    Direct SYSCTRL authority = NO
    Direct SYSMAINT authority = NO
    Direct DBADM authority = YES
    Direct CREATETAB authority = NO
    Direct BINDADD authority = YES
    Direct CONNECT authority = YES
    Direct CREATE_NOT_FENC authority = YES
    Direct IMPLICIT_SCHEMA authority = NO
    Direct LOAD authority = YES
    Direct QUIESCE_CONNECT authority = YES
    Direct CREATE_EXTERNAL_ROUTINE authority = YES
    Direct SYSMON authority = NO

    Indirect SYSADM authority = YES
    Indirect SYSCTRL authority = NO
    Indirect SYSMAINT authority = NO
    Indirect DBADM authority = NO
    Indirect CREATETAB authority = NO
    Indirect BINDADD authority = YES
    Indirect CONNECT authority = YES
    Indirect CREATE_NOT_FENC authority = NO
    Indirect IMPLICIT_SCHEMA authority = NO
    Indirect LOAD authority = NO
    Indirect QUIESCE_CONNECT authority = NO
    Indirect CREATE_EXTERNAL_ROUTINE authority = NO
    Indirect SYSMON authority = NO


    However after making the changes still I am able to create tables. Also the output of the commands are as follows

    db2 => select * from syscat.dbauth where grantee='ADMINISTRATOR'

    GRANTOR GRANTORTYPE GRANTEE GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH EXTERNALROUTINEAUTH IMPLSCHEMAAUTH LOADAUTH NOFENCEAUTH QUIESCECONNECTAUTH LIBRARYADMAUTH SECURITYADMAUTH SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH ACCESSCTRLAUTH
    -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------- --------- ------------------- -------------- -------- ----------- ------------------ -------------- --------------- ---------- ---------- ----------- -------------- --------------
    SYSTEM U ADMINISTRATOR U Y Y N Y Y N Y Y Y N Y N N N Y Y

    1 record(s) selected.

    db2 => select * from syscat.schemaauth where grantee='ADMINISTRATOR'

    GRANTOR GRANTORTYPE GRANTEE GRANTEETYPE SCHEMANAME ALTERINAUTH CREATEINAUTH DROPINAUTH
    -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ ----------

    0 record(s) selected.


    After doing all this still I am able to create tables using XYZ user.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If the user has SYSADM privileges, you can't prevent/block anything because SYSADM has - per definition - all privileges needed to do any kind of operation in the database system.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2009
    Posts
    10
    Thanks Stolze,
    The user XYZ has the SYSADM perivilege Indirectly. Is there any way I can revoke that privilege

    Thanks
    Siddharth Shah

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    as long as XYZ is member of the group assiged as SYSADM there is not much you (=DBA) can do.
    See to it that XYZ is removed from the group
    OR
    Remove the group from SYSADM_GROUPS

Posting Permissions

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