Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to revoke privileges from PUBLIC group on functions?

    Hi,
    on db2 V8.2 on Windows I would like to revoke all of the privileges from all of the objects on database from PUBLIC group. I have manage to revoke all privileges except bellow commands.

    I have opened Control Center and copy/paste from "SHOW COMMAND" button. Executing bellow commands and getting error bellow.

    How can I revoke all of the bellow privileges?

    P.S. All privileges can be seen using command: SELECT * FROM SYSCAT.ROUTINEAUTH WHERE GRANTEE='PUBLIC'
    Regards

    Commands:
    REVOKE EXECUTE ON FUNCTION SYSPROC.DB2LK_DEP_FIRST(VARCHAR(),VARCHAR(),VARCHA R()) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_CONTAINER(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_DB(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_DETAILLOG(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_DYN_SQL(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_STO_PATHS(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_TAB(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_TBSP(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;
    REVOKE EXECUTE ON FUNCTION SYSPROC.SNAP_GET_TBSP_PART(VARCHAR(),INTEGER) FROM PUBLIC RESTRICT;

    Error messages:
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSPROC.DB2LK_DEP_OF", of type "ROUTINE", which depends o
    it.  SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPCONT", of type "VIEW", which depends on it.
    SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPDB", of type "VIEW", which depends on it.
    SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPDETAILLOG", of type "VIEW", which depends o
    it.  SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPDYNSQL", of type "VIEW", which depends on
    it.  SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPSTOPATHS", of type "VIEW", which depends on
    it.  SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPTAB", of type "VIEW", which depends on it.
    SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPTBSPACE", of type "VIEW", which depends on
    it.  SQLSTATE=42893
    
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0478N  DROP or REVOKE on object type "FUNCTION" cannot be processed becaus
    there is an object "SYSCATV82.SNAPTBSPACEPART", of type "VIEW", which depends
    on it.  SQLSTATE=42893
    Last edited by grofaty; 02-20-12 at 10:00.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you have to revoke the parent object before
    there is a syscat view for dependencies
    you can find all parent obejcts using these functions
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    I have investigated and it looks like I have fallen into bugs zone:
    https://www-304.ibm.com/support/docv...id=swg1IY75588 is for SYSPROC.DB2LK_DEP_OF object. I am using db2 v8.2 fixpack 9 (and I am not gonna migrate to upper fixpack level to prevent getting any non-existing surprise problem like this one).

    For objects that depend on objects with schema SYSCATV82 - I have checked and there are no privileges on dependent objects. It looks privileges can't be revoked from objects that are referenced to this particular SYSCATV82 schema. According to the following web page there are some objects referencing "Snapshot Monitor Table Functions" Snapshot Monitor Table Functions | DB2 LUW wiki on DBPedias | All your database are belong to you
    Maybe there is also some bug not allowing to revoke privileges from syscatv82 schema objects.

Posting Permissions

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