#!/bin/sh
mysql="mysql --defaults-file=/etc/mysql/debian.cnf"

# Why do you run this daily? Because you normally do not need SQL SECURITY DEFINER
# for your views. Why do you have them then? Because MySQL uses that as default.
#
# Now.. watch what happens when you change/remove a user that did the defining.
# UPDATE mysql.user SET host = 'new-host' WHERE host = 'old-host';
# SELECT * FROM my_sample_view;
# ERROR 1449 (HY000): There is no 'walter'@'old-host' registered
# And your entire application breaks.
#
# The baseline is: unless you know you want to, you don't want SQL SECURITY
# DEFINER.

# We really want every view/proc to by of security type INVOKER, but we allow
# them for users that will always exist (root@localhost).

count=`$mysql -e "select count(*) from information_schema.views where definer <> 'root@localhost' and security_type <> 'INVOKER';" | tail -n1`
if [ $count -gt 0 ]; then
	echo 'WARNING! One or more VIEWS on your local MySQLd have a non-root DEFINER with'
	echo 'non-INVOKER SECURITY. You should fix this by recreating the views with SQL'
	echo 'SECURITY INVOKER.'
	echo
	$mysql --table -e "select concat(table_schema, '.', table_name) as view_name, definer, security_type from information_schema.views where definer <> 'root@localhost' and security_type <> 'INVOKER';"
	echo
fi

count=`$mysql -e "select count(*) from information_schema.routines where definer <> 'root@localhost' and security_type <> 'INVOKER';" | tail -n1`
if [ $count -gt 0 ]; then
	echo 'WARNING! One or more FUNCTIONS/PROCEDURES on your local MySQLd have a non-root'
	echo 'DEFINER with non-INVOKER SECURITY. You should fix this by recreating the'
	echo 'functions/procedures with SQL SECURITY INVOKER.'
	echo
	$mysql --table -e "select concat(routine_schema, '.', routine_name) as proc_name, definer, security_type from information_schema.routines where definer <> 'root@localhost' and security_type <> 'INVOKER';"
	echo
fi

# Because MySQL #$#%'s in more ways than one, we check for utf8_unicode_ci here
# as well. Why? Because MySQL somehow does not do equality comparison over
# columns where one has utf8_general_ci collation and the other one
# utf8_unicode_ci collation.

where="table_schema not in ('information_schema', 'mysql') and (character_set_name is not null and character_set_name <> 'ascii') and collation_name <> 'utf8_unicode_ci'"
count=`$mysql -e "select count(*) from information_schema.columns where $where;" | tail -n1`
if [ $count -gt 0 ]; then
	echo 'WARNING! One or more TABLES/VIEWS on your local MySQLd have non-utf8_unicode_ci'
	echo 'collations. You should fix this by re-creating/altering the tables/views with'
	echo 'the appropriate COLLATE utf8_unicode_ci modifiers.'
	echo
	$mysql --table -e "select concat(table_schema, '.', table_name, '.', column_name) as column_name, concat(character_set_name, '.', collation_name) as charset_collation from information_schema.columns where $where;"
	echo
fi