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.

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.

Thanks Stolze,
The user XYZ has the SYSADM perivilege Indirectly. Is there any way I can revoke that privilege

Thanks
Siddharth Shah

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