Topic
  • 7 replies
  • Latest Post - ‏2012-12-08T07:16:44Z by SystemAdmin
Ulli_Zu
Ulli_Zu
62 Posts

Pinned topic 3 questions about schema's

‏2012-11-29T15:42:13Z |
Hi there,

I have 3 questions about DB2 schema's , for which I can't find the answer. They are:

  • What is the difference between the SYSPROC and SYSFUN schema's (as they both contain functions)?

  • Why is, in non-restricted databases, public granted both select on tables in SYSIBM, as select on the views in SYSCAT; I would say the last should be enough?

  • Is select on the views in the SYSSTAT schema neccesary for a user to be able to use the query optimizer (this might be a dumb question, I'm not sure)?

Thanks in advance.
Updated on 2012-12-08T07:16:44Z at 2012-12-08T07:16:44Z by SystemAdmin
  • nivanov1
    nivanov1
    231 Posts

    Re: 3 questions about schema's

    ‏2012-11-29T18:27:32Z  
    > Is select on the views in the SYSSTAT schema neccesary for a user to be able to use the query optimizer

    Users do not "use the optimizer". The optimizer accesses the catalog tables when compiling user queries, but that's a different thing. If your question actually is, "if I revoke SELECT privileges on SYSSTAT view from PUBLIC, will users still be able to run queries", the answer is yes.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: 3 questions about schema's

    ‏2012-11-30T11:59:52Z  
    Hello,

    > What is the difference between the SYSPROC and SYSFUN schema's (as they both contain functions)?

    SYSPROC contains procedure and administrative table functions.

    SYSFUN contains SQL built in functions, which are sometimes also in SYSIBM, maybe with a slightly different specification.

    SYSIBMADM contains administrative views.

    > Why is, in non-restricted databases, public granted both select on tables in SYSIBM, as select on the views in SYSCAT; I would say the last should be enough?

    It is true that SYSCAT views are documented while SYSIBM tables are not. Having said that, I don't think read access to SYSIBM catalog tables would lead to a security issue.

    On the other hand, from a security point of view, you might want to revoke public access to security catalog tables and views :
    - schema SYSIBM, tables SYS%AUTH
    - schema SYSCAT, views %AUTH

    Regards
    Yves-Antoine Emmanuelli
  • db2girl_bella
    db2girl_bella
    20 Posts

    Re: 3 questions about schema's

    ‏2012-11-30T16:36:23Z  
    4th question:
    Why does db2 grant CREATEIN on implicitly created schemas for databases created using RESTRICTIVE option?

    I know the manual states "An implicitly created schema has CREATEIN privilege automatically granted to PUBLIC", but why is this done??
    I don't think db2 should grant this privilege for RESTRICTIVE databases.
  • Ulli_Zu
    Ulli_Zu
    62 Posts

    Re: 3 questions about schema's

    ‏2012-12-05T08:51:51Z  
    Thanks for the answers!
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: 3 questions about schema's

    ‏2012-12-08T00:23:37Z  
    • Ulli_Zu
    • ‏2012-12-05T08:51:51Z
    Thanks for the answers!
    Some products like SAS and Cognos require access to the catalog tables. Make sure you compensate for this.

    Randy Wilson
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: 3 questions about schema's

    ‏2012-12-08T07:04:07Z  
    Hello,

    > What is the difference between the SYSPROC and SYSFUN schema's (as they both contain functions)?

    SYSPROC contains procedure and administrative table functions.

    SYSFUN contains SQL built in functions, which are sometimes also in SYSIBM, maybe with a slightly different specification.

    SYSIBMADM contains administrative views.

    > Why is, in non-restricted databases, public granted both select on tables in SYSIBM, as select on the views in SYSCAT; I would say the last should be enough?

    It is true that SYSCAT views are documented while SYSIBM tables are not. Having said that, I don't think read access to SYSIBM catalog tables would lead to a security issue.

    On the other hand, from a security point of view, you might want to revoke public access to security catalog tables and views :
    - schema SYSIBM, tables SYS%AUTH
    - schema SYSCAT, views %AUTH

    Regards
    Yves-Antoine Emmanuelli
    SYSIBM: Built-in functions such as AVG, "+", DECIMAL, SUBSTR
    SYSFUN, SYSPROC: User-defined functions
    Differences of SYSIBM, SYSFUN, and SYSPROC are clearly described
    in Informaion Center
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/c0006211.html
    OR
    in PDF Manual: "DB2 SQL Reference, Volume 1" --> "Chapter 3. Functions" --> "Functions overview"

    Functions overview

    A function is an operation that is denoted by a function name followed by a pair of parentheses enclosing the specification of arguments (there may be no arguments).

    Built-in functions are provided with the database manager; they return a single result value, and are identified as part of the SYSIBM schema.
    Built-in functions include column functions (such as AVG), operator functions (such as "+"), casting functions (such as DECIMAL), and others (such as SUBSTR).

    User-defined functions are registered to a database in SYSCAT.ROUTINES (using the CREATE FUNCTION statement).
    User-defined functions are never part of the SYSIBM schema.
    One such set of functions is provided with the database manager in a schema called SYSFUN, and another in a schema called SYSPROC.


    Though I think that it(Differences of SYSIBM, SYSFUN, and SYSPROC) must be a basic/neccesary knowledge to use DB2 efficiently,
    not so many persons don't know it, even rather skilled engineers including IBM employees.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: 3 questions about schema's

    ‏2012-12-08T07:16:44Z  
    SYSIBM: Built-in functions such as AVG, "+", DECIMAL, SUBSTR
    SYSFUN, SYSPROC: User-defined functions
    Differences of SYSIBM, SYSFUN, and SYSPROC are clearly described
    in Informaion Center
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/c0006211.html
    OR
    in PDF Manual: "DB2 SQL Reference, Volume 1" --> "Chapter 3. Functions" --> "Functions overview"

    Functions overview

    A function is an operation that is denoted by a function name followed by a pair of parentheses enclosing the specification of arguments (there may be no arguments).

    Built-in functions are provided with the database manager; they return a single result value, and are identified as part of the SYSIBM schema.
    Built-in functions include column functions (such as AVG), operator functions (such as "+"), casting functions (such as DECIMAL), and others (such as SUBSTR).

    User-defined functions are registered to a database in SYSCAT.ROUTINES (using the CREATE FUNCTION statement).
    User-defined functions are never part of the SYSIBM schema.
    One such set of functions is provided with the database manager in a schema called SYSFUN, and another in a schema called SYSPROC.


    Though I think that it(Differences of SYSIBM, SYSFUN, and SYSPROC) must be a basic/neccesary knowledge to use DB2 efficiently,
    not so many persons don't know it, even rather skilled engineers including IBM employees.
    Basics of SYSCAT and SYSSTAT are described here
    in Information Cener
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008443.html

    or
    in PDF Manual
    "DB2 SQL Reference, Volume 1" --> "Appendix D. System catalog views"
    System catalog views

    The database manager creates and maintains two sets of system catalog views that are defined on top of the base system catalog tables.

    •SYSCAT views are read-only catalog views that are found in the SYSCAT schema.
    The RESTRICT option on CREATE DATABASE statement determines how SELECT privilege is granted.
    When the RESTRICT option is not specified, SELECT privilege is granted to PUBLIC.

    •SYSSTAT views are updatable catalog views that are found in the SYSSTAT schema.
    The updatable views contain statistical information that is used by the optimizer.
    The values in some columns in these views can be changed to test performance.
    (Before changing any statistics, it is recommended that the RUNSTATS command be invoked so that all the statistics reflect the current state.)

    Applications should be written to the SYSCAT and SYSSTAT views rather than the base catalog tables.

    ...
    ...