#!/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