WL#3582: Online Backup: Users, Triggers

Affects: Server-6.0   —   Status: Assigned 

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, 2024, Oracle Corporation and/or its affiliates. All rights reserved.