|
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)]>
|
|
user_d_cannot_see_his_packages_without_table_proc.jpg