WL#925 - Privileges for stored routines

  Implement fine-grained control over access to stored procedures
  Privileges are cached (same way as existing table/column privs)
  

3 files changed, 78 insertions, 12 deletions

diff --git a/scripts/mysql_create_system_tables.sh b/scripts/mysql_create_system_tables.sh
index 7a4da55f851..be99a081bcb 100644
--- a/scripts/mysql_create_system_tables.sh
+++ b/scripts/mysql_create_system_tables.sh
@@ -41,7 +41,7 @@ c_hk=""
 i_ht=""
 c_tzn="" c_tz="" c_tzt="" c_tztt="" c_tzls=""
 i_tzn="" i_tz="" i_tzt="" i_tztt="" i_tzls=""
-c_p=""
+c_p="" c_pp=""
 
 # Check for old tables
 if test ! -f $mdata/db.frm
@@ -69,14 +69,17 @@ then
   c_d="$c_d   Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
   c_d="$c_d   Create_view_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
   c_d="$c_d   Show_view_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
+  c_d="$c_d   Create_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
+  c_d="$c_d   Alter_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
+  c_d="$c_d   Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
   c_d="$c_d PRIMARY KEY Host (Host,Db,User),"
   c_d="$c_d KEY User (User)"
   c_d="$c_d ) engine=MyISAM"
   c_d="$c_d CHARACTER SET utf8 COLLATE utf8_bin"
   c_d="$c_d comment='Database privileges';"
   
-  i_d="INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y');
-  INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y');"
+  i_d="INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N');
+  INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N');"
 fi
 
 if test ! -f $mdata/host.frm
@@ -141,6 +144,8 @@ then
   c_u="$c_u   Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
   c_u="$c_u   Create_view_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
   c_u="$c_u   Show_view_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
+  c_u="$c_u   Create_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
+  c_u="$c_u   Alter_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
   c_u="$c_u   ssl_type enum('','ANY','X509', 'SPECIFIED') DEFAULT '' NOT NULL,"
   c_u="$c_u   ssl_cipher BLOB NOT NULL,"
   c_u="$c_u   x509_issuer BLOB NOT NULL,"
@@ -155,24 +160,24 @@ then
 
   if test "$1" = "test" 
   then
-    i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
-    INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
-    REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
+    i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
+    INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
+    REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
     INSERT INTO user (host,user) values ('localhost','');
     INSERT INTO user (host,user) values ('$hostname','');"
   else
-    i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);"
+    i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);"
     if test "$windows" = "0"
     then
       i_u="$i_u
-           INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
+           INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
            INSERT INTO user (host,user) values ('$hostname','');
            INSERT INTO user (host,user) values ('localhost','');"
     else
       i_u="$i_u
-	   INSERT INTO user VALUES ('%','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
-	   INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
-	   INSERT INTO user VALUES ('%','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);"
+	   INSERT INTO user VALUES ('%','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
+	   INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
+	   INSERT INTO user VALUES ('%','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);"
     fi
   fi 
 fi
@@ -236,6 +241,27 @@ then
   c_c="$c_c   comment='Column privileges';"
 fi
 
+if test ! -f $mdata/procs_priv.frm
+then
+  if test "$1" = "verbose" ; then
+    echo "Preparing procs_priv table" 1>&2;
+  fi
+
+  c_pp="$c_pp CREATE TABLE procs_priv ("
+  c_pp="$c_pp   Host char(60) binary DEFAULT '' NOT NULL,"
+  c_pp="$c_pp   Db char(64) binary DEFAULT '' NOT NULL,"
+  c_pp="$c_pp   User char(16) binary DEFAULT '' NOT NULL,"
+  c_pp="$c_pp   Routine_name char(64) binary DEFAULT '' NOT NULL,"
+  c_pp="$c_pp   Grantor char(77) DEFAULT '' NOT NULL,"
+  c_pp="$c_pp   Timestamp timestamp(14),"
+  c_pp="$c_pp   Proc_priv set('Execute','Alter Routine','Grant') DEFAULT '' NOT NULL,"
+  c_pp="$c_pp   PRIMARY KEY (Host,Db,User,Routine_name),"
+  c_pp="$c_pp   KEY Grantor (Grantor)"
+  c_pp="$c_pp ) engine=MyISAM"
+  c_pp="$c_pp CHARACTER SET utf8 COLLATE utf8_bin"
+  c_pp="$c_pp   comment='Procedure privileges';"
+fi
+
 if test ! -f $mdata/help_topic.frm
 then
   if test "$1" = "verbose" ; then
@@ -718,6 +744,7 @@ $c_tzls
 $i_tzls
 
 $c_p
+$c_pp
 
 END_OF_DATA
 
diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql
index cae6a1d07b9..d4f095f5201 100644
--- a/scripts/mysql_fix_privilege_tables.sql
+++ b/scripts/mysql_fix_privilege_tables.sql
@@ -15,6 +15,7 @@ ALTER TABLE host type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 ALTER TABLE func type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 ALTER TABLE columns_priv type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 ALTER TABLE tables_priv type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
+ALTER TABLE procs_priv type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 ALTER TABLE user change Password Password char(41) binary not null default '';
 ALTER TABLE user add File_priv enum('N','Y') NOT NULL;
 CREATE TABLE IF NOT EXISTS func (
@@ -171,8 +172,46 @@ ALTER TABLE user ADD Show_view_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Cre
 UPDATE user SET Create_view_priv=Create_priv, Show_view_priv=Create_priv where user<>"" AND @hadCreateViewPriv = 0;
 
 #
+#
+#
+SET @hadCreateRoutinePriv:=0;
+SELECT @hadCreateRoutinePriv:=1 FROM user WHERE Create_routine_priv LIKE '%';
+
+#
+# Create PROCEDUREs privileges (v5.0)
+#
+ALTER TABLE db ADD Create_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_view_priv;
+ALTER TABLE user ADD Create_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_view_priv;
+
+#
+# Alter PROCEDUREs privileges (v5.0)
+#
+ALTER TABLE db ADD Alter_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Create_routine_priv;
+ALTER TABLE user ADD Alter_routine_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Create_routine_priv;
+
+ALTER TABLE db ADD Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Alter_routine_priv;
+
+#
+# Assign create/alter routine privileges to people who have create privileges
+#
+UPDATE user SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv where user<>"" AND @hadCreateRoutinePriv = 0;
+UPDATE db SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute_priv=Select_priv where user<>"" AND @hadCreateRoutinePriv = 0;
+
+#
 # Create some possible missing tables
 #
+CREATE TABLE IF NOT EXISTS procs_priv (
+Host char(60) binary DEFAULT '' NOT NULL,
+Db char(64) binary DEFAULT '' NOT NULL,
+User char(16) binary DEFAULT '' NOT NULL,
+Routine_name char(64) binary DEFAULT '' NOT NULL,
+Grantor char(77) DEFAULT '' NOT NULL,
+Timestamp timestamp(14),
+Proc_priv set('Execute','Alter Routine','Grant') DEFAULT '' NOT NULL,
+PRIMARY KEY (Host,Db,User,Routine_name),
+KEY Grantor (Grantor)
+) CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';
+
 CREATE TABLE IF NOT EXISTS help_topic (
 help_topic_id int unsigned not null,
 name varchar(64) not null,
diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh
index b4f59790e73..3be1320f0b0 100644
--- a/scripts/mysql_install_db.sh
+++ b/scripts/mysql_install_db.sh
@@ -3,7 +3,7 @@
 # For a more info consult the file COPYRIGHT distributed with this file.
 
 # This scripts creates the privilege tables db, host, user, tables_priv,
-# columns_priv in the mysql database, as well as the func table.
+# columns_priv, procs_priv in the mysql database, as well as the func table.
 #
 # All unrecognized arguments to this script are passed to mysqld.