Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26480

Add source of PACKAGE and PACKAGE BODY to information_schema.routines(ROUTINE_DEFINITION)

    XMLWordPrintable

    Details

      Description

      Currently, DBeaver, a GUI interface for database products, is querying mysql.proc to get the source code of packages and package bodies. There are two issues with this:

      • Not all accounts have access to the mysql schema
      • Granting select on mysql.proc gives access to source of all routines

      The source of routines such as procedures and functions is available in information_schema.routines(ROUTINE_DEFINITION), which limits the user to seeing only the code to which he has privileges. Packages and Package bodies are listed on information_schema.routines, but the ROUTINE_DEFINITION is empty.

      Example:

      MariaDB [dentro]> delimiter //
      MariaDB [dentro]> CREATE OR REPLACE PACKAGE doit AS
          ->   FUNCTION sometext() RETURN varchar(100);
          -> END;
          -> //
      MariaDB [dentro]> delimiter ;
       
      MariaDB [(none)]> select ROUTINE_DEFINITION from information_schema.routines
          -> where ROUTINE_NAME='doit' and ROUTINE_TYPE='PACKAGE';
      +--------------------+
      | ROUTINE_DEFINITION |
      +--------------------+
      |                    |
      +--------------------+
      1 row in set (0.001 sec)
       
      MariaDB [(none)]> select body from mysql.proc where name='doit' and type='PACKAGE';
      +---------------------------------------------------+
      | body                                              |
      +---------------------------------------------------+
      | AS
        FUNCTION sometext() RETURN varchar(100);
      END |
      +---------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [(none)]>
      

        Attachments

          Activity

            People

            Assignee:
            ralf.gebhardt@mariadb.com Ralf Gebhardt
            Reporter:
            edward Edward Stoever
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration