To provide complete and consistent DDL information on every
object, MySQL should implement the following statements:
SHOW CREATE USER
SHOW CREATE TRIGGER
The information is needed to ensure that our backup/restore
feature has easy access to all DDL and DCL information needed.
The Task
--------
Implement the following statements:
SHOW CREATE USER user_name;
SHOW CREATE TRIGGER trigger_name;
The "SHOW object_type object_name" statements are for creating a single
string that is useful for re-creating the object. They produce a one-row
result set. The new SHOW statements are all derived from older statements
(INFORMATION_SCHEMA selections or other SHOW statements), so the required
privileges and the format of each clause will be the same as for the
underlying statements.
The new SHOW statements will have the general form:
SHOW object_type [qualifier.] object_name;
The models will be the old SHOW statements:
SHOW CREATE { DATABASE | EVENT | TABLE | VIEW }
Rules
-----
- Every clause which is necessary to re-create the object will appear
in the output.
- If a clause would specify the default value, and the default is not
affected by other environment factors such as the mysqld --startup
options, then the implementer may decide to omit the clause.
- If sql_mode=ansi, the implementer may decide to omit all non-ANSI
clauses because that's what happens with CREATE TABLE (see BUG#3352 ).
- If sql_mode=oracle|etc., the implementer will ignore Guilhem's
suggestions for syntax to look similar to Oracle|etc. (see WL#2397).
- For each statement, there will be two columns: NAME and
CREATE_. Compare the columns "Table" and "Create Table" that
appear for SHOW CREATE TABLE.
- There is no third SQL_MODE column as there is for SHOW CREATE PROCEDURE.
- Column names for the new SHOW statements will not be reserved words
and will be upper case, so they'll look different from old SHOW statements.
- The columns are defined as VARCHAR CHARACTER SET UTF8 but the user
will probably not notice because the SHOW will always translate to the
connection character set.
- Inside the output, any object references will become delimited but
usually not qualified, for example "CREATE INDEX I ON t ..." becomes
"CREATE INDEX `i` ON `t` ...".
- The action of all ALTER statements will affect the output;
that is, after a CREATE statement, SHOW CREATE will
display the definition provided by the CREATE statement. But once an
object's definition has been amended by an ALTER statement,
SHOW CREATE will show the amended definition.
Specific Statements and Examples
--------------------------------
(1) SHOW CREATE USER user_name
- Shows the CREATE USER statement that creates the given user.
- If the user was created implicitly by a GRANT statement, the same information
will be available in SHOW CREATE USER as if the user had been created explicitly
with a CREATE USER statement.
- The privileges are the same as for mysql.user.
- The output columns are USER_NAME and CREATE_USER. The user name may be in
either single quotes or double quotes.
- Example:
CREATE USER u IDENTIFIED BY PASSWORD 'ww';
SHOW CREATE USER u;
or
GRANT ON TO 'u' IDENTIFIED BY PASSWORD 'ww';
both return
USER_NAME CREATE_USER
--------- -----------
u@% CREATE USER "u"@"%"
/* no IDENTIFIED BY password */
provided that user 'u' didn't exist before the GRANT statement was
executed, of course.
(2) SHOW CREATE TRIGGER trigger_name
- Shows the CREATE TRIGGER statement that creates the given trigger.
- The privileges are the same as for SHOW TRIGGERS.
- The output columns are TRIGGER_NAME and CREATE_TRIGGER.
- Example:
CREATE DEFINER = 'u' TRIGGER `t`
BEFORE INSERT ON `table_name`
FOR EACH ROW
SET @a=5;
SHOW CREATE TRIGGER t;
returns
TRIGGER_NAME CREATE_TRIGGER
------------ --------------
t CREATE TRIGGER `t`
DEFINER "u"@"%" /* no '=' */
BEFORE INSERT ON `table_name`
FOR EACH ROW
SET @a=5
NB: There is already a suggestion for SHOW CREATE TRIGGER,
from Marc Alff, see BUG#18161 .
Other Objects
-------------
Indexes:
There is no need for a SHOW CREATE INDEX statement. Indexes
are generally initially created using a CREATE TABLE statement.
When indexes are added or altered, the actions of the CREATE
INDEX statement are mapped to the ALTER TABLE statement, and
that information is subsequently already available with
SHOW CREATE TABLE.
Privileges:
There is no need for a SHOW GRANT USER statement. The SHOW GRANTS
[FOR user] statement is equivalent and shows all privileges granted
to a user. Its output is affected by both GRANT and REVOKE, so the
information it provides is always current. But the SHOW GRANTS
statement does need some additional functionality to provide the
metadata needed for online backup; see WL#3588 "Online Backup: Add
SHOW GRANTS with more information".
Future Objects
--------------
Future MySQL object types will also need SHOW CREATE support.
A note has been added to the following WL tasks to reflect this:
WL#744 CREATE COLLATION, CREATE CREATE CHARACTER SET
WL#827 CREATE SEQUENCE
WL#933 CREATE DISTINCT TYPE
WL#988 CREATE ROLE
WL#1048 CREATE SYNONYM
References
----------
See also:
WL#3574 Online backup: SHOW commands for various metadata
WL#3588 Online Backup: Add SHOW GRANTS with more information
WL#3917 Online Backup: SHOW commands (LOGFILE, TABLESPACE)
SHOW CREATE TRIGGER already exists due to WL#4052 .
sql_lex.h:
add SQLCOM_SHOW_CREATE_TRIGGER, SQLCOM_SHOW_CREATE_USER command to
enum enum_sql_command
mysqld.cc:
add "Com_show_create_trigger", "Com_show_create_user" to status_var array
sp_head.cc:
add SQLCOM_SHOW_CREATE_TRIGGER, SQLCOM_SHOW_CREATE_USER cases to
sp_get_flags_for_command() function
sql_prepare.cc:
add SQLCOM_SHOW_CREATE_TRIGGER, SQLCOM_SHOW_CREATE_USER cases to
check_prepared_statement() function
mysql_priv.h:
add function declaration
bool mysql_show_create_trigger(THD *thd, sp_name *trig);
sql_trigger.h:
method Table_triggers_list::get_trigger_info(), split 'definer' parameter on
two:
LEX_STRING *definer_user,
LEX_STRING *definer_host.
sql_trigger.cc:
add new function
bool mysql_show_create_trigger(THD *thd, sp_name *trig)
bool get_trigger_create_info(THD* thd, sp_name *trig, TABLE_LIST *tables,
String *buffer)
sql_show.cc:
change get_schema_triggers_record() code according to
new Table_triggers_list::get_trigger_info() method
sql_yacc.yy:
add SHOW_CREATE_TRIGGER, SHOW_CREATE_USER support
sql_acl.h:
add function declaration
bool mysql_show_create_user(THD *thd, LEX_USER *user);
sql_acl.cc:
add new functions
bool get_user_create_info(THD *thd, LEX_USER *user, String *buffer)
bool mysql_show_create_user(THD *thd, LEX_USER *user)
t/show_check.test
test case
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.