mysql schema

There are several ways to see the names and values of system variables:

• To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:

  mysqld --verbose --help
  

• To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:

  mysqld --no-defaults --verbose --help
  

• To see the current values used by a running server, use the SHOW VARIABLES statement.

---------------------------------------------------------------------------------------------
the information schema is an ANSI standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information which some databases make available through non-standard commands, such as the SHOW command of MySQL and the DESCRIBE command of Oracle.
----------------------------------------------------------------------

INFORMATION_SCHEMA provides access to database metadata.

Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.

INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.

In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them.

--------------------------------------------------------

18.2.2. Stored Routines and MySQL Privileges

Beginning with MySQL 5.0.3, the grant system takes stored routines into account as follows:

• The CREATE ROUTINE privilege is needed to create stored routines.

• The ALTER ROUTINE privilege is needed to alter or drop stored routines. This privilege is granted automatically to the creator of a routine if necessary, and dropped from the creator when the routine is dropped.

• The EXECUTE privilege is required to execute stored routines. However, this privilege is granted automatically to the creator of a routine if necessary (and dropped from the creator when the routine is dropped). Also, the default SQL SECURITY characteristic for a routine is DEFINER, which enables users who have access to the database with which the routine is associated to execute the routine.

• If the automatic_sp_privileges system variable is 0, the EXECUTE and ALTER ROUTINE privileges are not automatically granted to and dropped from the routine creator.

• The creator of a routine is the account used to execute the CREATE statement for it. This might not be the same as the account named as the DEFINER in the routine definition.

The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table.

---------------------------------------------------

22.4.15: Is there a way to view all stored procedures and stored functions in a given database?

Yes. For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:

SELECT ROUTINE_TYPE, ROUTINE_NAME
   FROM INFORMATION_SCHEMA.ROUTINES
-----------------------------------------
http://www.futhark.ch/mysql/114.html
**********************************
Implementing the Stored Routine Debugger

    

Our Stored Routine Debugger first of all needs its own database where it keeps all it's runtime information, the debugging output and of course the stored routines used to implement the debugger itself.

    

CREATE DATABASE `srdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;


    

It's also good practice to give it its own user account. So nobody else can temper with the debugger's internals. The debugger of course needs full data manipulation rights (SELECT, INSERT, UPDATE and DELETE) on all of its tables as well as EXECUTE rights to call its own stored routines. We also provide it with the CREATE ROUTINE and ALTER ROUTINE rights, so its easy for us to update the debuggers code by just logging in as the srdb user and re-create its stored routines with additional functionality.

    

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE
ON srdb.* TO 'srdb' IDENTIFIED BY 'srdb_password';


    

Any user you want to allow to work with the debugger just needs EXECUTE rights on the srdb database and nothing else. We will encapsulate all of the debugger functionality in stored routines and give those routines that need to be called by users the SQL SECURITY of the DEFINER (which will be the above created user srdb).

    

GRANT EXECUTE ON srdb.* TO 'debugger_user';
----------------------------
   WHERE ROUTINE_SCHEMA='dbname';

For more information, see Section 19.14, “The INFORMATION_SCHEMA ROUTINES Table”.