add table and column privilege management to mysql_grant

2 files changed, 273 insertions, 206 deletions

diff --git a/lib/puppet/provider/mysql_grant/mysql.rb b/lib/puppet/provider/mysql_grant/mysql.rb
index 61c32d9..b782f12 100644
--- a/lib/puppet/provider/mysql_grant/mysql.rb
+++ b/lib/puppet/provider/mysql_grant/mysql.rb
@@ -1,155 +1,210 @@
 # A grant is either global or per-db. This can be distinguished by the syntax
 # of the name:
-# 	user@host => global
-# 	user@host/db => per-db
+#   user@host => global
+#   user@host/db => per-db
 
 require 'puppet/provider/package'
 
 MYSQL_USER_PRIVS = [ :select_priv, :insert_priv, :update_priv, :delete_priv,
-	:create_priv, :drop_priv, :reload_priv, :shutdown_priv, :process_priv,
-	:file_priv, :grant_priv, :references_priv, :index_priv, :alter_priv,
-	:show_db_priv, :super_priv, :create_tmp_table_priv, :lock_tables_priv,
-	:execute_priv, :repl_slave_priv, :repl_client_priv, :create_view_priv,
-	:show_view_priv, :create_routine_priv, :alter_routine_priv,
-	:create_user_priv
+  :create_priv, :drop_priv, :reload_priv, :shutdown_priv, :process_priv,
+  :file_priv, :grant_priv, :references_priv, :index_priv, :alter_priv,
+  :show_db_priv, :super_priv, :create_tmp_table_priv, :lock_tables_priv,
+  :execute_priv, :repl_slave_priv, :repl_client_priv, :create_view_priv,
+  :show_view_priv, :create_routine_priv, :alter_routine_priv,
+  :create_user_priv
 ]
 
 MYSQL_DB_PRIVS = [ :select_priv, :insert_priv, :update_priv, :delete_priv,
-	:create_priv, :drop_priv, :grant_priv, :references_priv, :index_priv,
-	:alter_priv, :create_tmp_table_priv, :lock_tables_priv, :create_view_priv,
-	:show_view_priv, :create_routine_priv, :alter_routine_priv, :execute_priv
+  :create_priv, :drop_priv, :grant_priv, :references_priv, :index_priv,
+  :alter_priv, :create_tmp_table_priv, :lock_tables_priv, :create_view_priv,
+  :show_view_priv, :create_routine_priv, :alter_routine_priv, :execute_priv
 ]
 
+MYSQL_TABLE_PRIVS = [ :select_priv, :insert_priv, :update_priv, :delete_priv, 
+                      :create_priv, :drop_priv, :grant_privt, :references_priv, 
+                      :index_priv, :alter_priv, :create_view_priv, :show_view_priv, 
+                      :trigger_priv
+]
+
+MYSQL_COLUMN_PRIVS = [ :select_priv, :insert_priv, :update_priv, :references_priv ]
+
 Puppet::Type.type(:mysql_grant).provide(:mysql) do
 
-	desc "Uses mysql as database."
-
-	commands :mysql => '/usr/bin/mysql'
-	commands :mysqladmin => '/usr/bin/mysqladmin'
-
-	def mysql_flush 
-		mysqladmin "flush-privileges"
-	end
-
-	# this parses the
-	def split_name(string)
-		matches = /^([^@]*)@([^\/]*)(\/(.*))?$/.match(string).captures.compact
-		case matches.length 
-			when 2
-				{
-					:type => :user,
-					:user => matches[0],
-					:host => matches[1]
-				}
-			when 4
-				{
-					:type => :db,
-					:user => matches[0],
-					:host => matches[1],
-					:db => matches[3]
-				}
-		end
-	end
-
-	def create_row
-		unless @resource.should(:privileges).empty?
-			name = split_name(@resource[:name])
-			case name[:type]
-			when :user
-				mysql "mysql", "-e", "INSERT INTO user (host, user) VALUES ('%s', '%s')" % [
-					name[:host], name[:user],
-				]
-			when :db
-				mysql "mysql", "-e", "INSERT INTO db (host, user, db) VALUES ('%s', '%s', '%s')" % [
-					name[:host], name[:user], name[:db],
-				]
-			end
-			mysql_flush
-		end
-	end
-
-	def destroy
-		mysql "mysql", "-e", "REVOKE ALL ON '%s'.* FROM '%s@%s'" % [ @resource[:privileges], @resource[:database], @resource[:name], @resource[:host] ]
-	end
-	
-	def row_exists?
-		name = split_name(@resource[:name])
-		fields = [:user, :host]
-		if name[:type] == :db
-			fields << :db
-		end
-		not mysql( "mysql", "-NBe", 'SELECT "1" FROM %s WHERE %s' % [ name[:type], fields.map do |f| "%s = '%s'" % [f, name[f]] end.join(' AND ')]).empty?
-	end
-
-	def all_privs_set?
-		all_privs = case split_name(@resource[:name])[:type]
-			when :user
-				MYSQL_USER_PRIVS
-			when :db
-				MYSQL_DB_PRIVS
-		end
-		all_privs = all_privs.collect do |p| p.to_s end.sort.join("|")
-		privs = privileges.collect do |p| p.to_s end.sort.join("|")
-
-		all_privs == privs
-	end
-
-	def privileges 
-		name = split_name(@resource[:name])
-		privs = ""
-
-		case name[:type]
-		when :user
-			privs = mysql "mysql", "-Be", 'select * from user where user="%s" and host="%s"' % [ name[:user], name[:host] ]
-		when :db
-			privs = mysql "mysql", "-Be", 'select * from db where user="%s" and host="%s" and db="%s"' % [ name[:user], name[:host], name[:db] ]
-		end
-
-		if privs.match(/^$/) 
-			privs = [] # no result, no privs
-		else
-			# returns a line with field names and a line with values, each tab-separated
-			privs = privs.split(/\n/).map! do |l| l.chomp.split(/\t/) end
-			# transpose the lines, so we have key/value pairs
-			privs = privs[0].zip(privs[1])
-			privs = privs.select do |p| p[0].match(/_priv$/) and p[1] == 'Y' end
-		end
-
-		privs.collect do |p| symbolize(p[0].downcase) end
-	end
-
-	def privileges=(privs) 
-		unless row_exists?
-			create_row
-		end
-
-		# puts "Setting privs: ", privs.join(", ")
-		name = split_name(@resource[:name])
-		stmt = ''
-		where = ''
-		all_privs = []
-		case name[:type]
-		when :user
-			stmt = 'update user set '
-			where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ]
-			all_privs = MYSQL_USER_PRIVS
-		when :db
-			stmt = 'update db set '
-			where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ]
-			all_privs = MYSQL_DB_PRIVS
-		end
-
-		if privs[0] == :all 
-			privs = all_privs
-		end
-	
-		# puts "stmt:", stmt
-		set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ')
-		# puts "set:", set
-		stmt = stmt << set << where
-
-		mysql "mysql", "-Be", stmt
-		mysql_flush
-	end
+  desc "Uses mysql as database."
+
+  commands :mysql => '/usr/bin/mysql'
+  commands :mysqladmin => '/usr/bin/mysqladmin'
+
+  def mysql_flush 
+    mysqladmin "flush-privileges"
+  end
+
+  # this parses the
+  def split_name(string)
+    matches = /^([^@]*)@([^\/]*)(\/(.*))?(\/(.*))?(\/(.*))?$/.match(string).captures.compact
+    case matches.length 
+      when 2
+        {
+          :type => :user,
+          :user => matches[0],
+          :host => matches[1]
+        }
+      when 4
+        {
+          :type => :db,
+          :user => matches[0],
+          :host => matches[1],
+          :db => matches[3]
+        }
+      when 6
+        {
+          :type => :table,
+          :user => matches[0],
+          :host => matches[1],
+          :db => matches[3],
+          :table => matches[5]
+        }
+      when 8
+        {
+          :type => :table,
+          :user => matches[0],
+          :host => matches[1],
+          :db => matches[3],
+          :table => matches[5],
+          :column => matches[7]
+        }
+    end
+  end
+
+  def create_row
+    unless @resource.should(:privileges).empty?
+      name = split_name(@resource[:name])
+      case name[:type]
+      when :user
+        mysql "mysql", "-e", "INSERT INTO user (host, user) VALUES ('%s', '%s')" % [
+          name[:host], name[:user],
+        ]
+      when :db
+        mysql "mysql", "-e", "INSERT INTO db (host, user, db) VALUES ('%s', '%s', '%s')" % [
+          name[:host], name[:user], name[:db],
+        ]
+      when :table
+        mysql "mysql", "-e", "INSERT INTO tables_priv (host, user, db, table) VALUES ('%s', '%s', '%s', '%s')" % [
+          name[:host], name[:user], name[:db], name[:table],
+        ]
+      when :column
+        mysql "mysql", "-e", "INSERT INTO columns_priv (host, user, db, table, column_name) VALUES ('%s', '%s', '%s', '%s', '%s')" % [
+          name[:host], name[:user], name[:db], name[:table], name[:column],
+        ]
+      end
+      mysql_flush
+    end
+  end
+
+  def destroy
+    mysql "mysql", "-e", "REVOKE ALL ON '%s'.* FROM '%s@%s'" % [ @resource[:privileges], @resource[:database], @resource[:name], @resource[:host] ]
+  end
+  
+  def row_exists?
+    name = split_name(@resource[:name])
+    fields = [:user, :host]
+    if name[:type] == :db
+      fields << :db
+    end
+    if name[:type] == :table
+      fields << :table
+    end
+    if name[:type] == :column
+      fields << :column
+    end
+    not mysql( "mysql", "-NBe", 'SELECT "1" FROM %s WHERE %s' % [ name[:type], fields.map do |f| "%s = '%s'" % [f, name[f]] end.join(' AND ')]).empty?
+  end
+
+  def all_privs_set?
+    all_privs = case split_name(@resource[:name])[:type]
+      when :user
+        MYSQL_USER_PRIVS
+      when :db
+        MYSQL_DB_PRIVS
+      when :table
+        MYSQL_TABLE_PRIVS
+      when :column
+        MYSQL_COLUMN_PRIVS
+    end
+    all_privs = all_privs.collect do |p| p.to_s end.sort.join("|")
+    privs = privileges.collect do |p| p.to_s end.sort.join("|")
+
+    all_privs == privs
+  end
+
+  def privileges 
+    name = split_name(@resource[:name])
+    privs = ""
+
+    case name[:type]
+    when :user
+      privs = mysql "mysql", "-Be", 'select * from user where user="%s" and host="%s"' % [ name[:user], name[:host] ]
+    when :db
+      privs = mysql "mysql", "-Be", 'select * from db where user="%s" and host="%s" and db="%s"' % [ name[:user], name[:host], name[:db] ]
+    when :table
+      privs = mysql "mysql", "-Be", 'select * from tables_priv where User="%s" and Host="%s" and Db="%s" and Table="%s"' % [ name[:user], name[:host], name[:db], name[:table] ]
+    when :column
+      privs = mysql "mysql", "-Be", 'select * from columns_priv where User="%s" and Host="%s" and Db="%s" and Table_name="%s" and Column_name="%s"' % [ name[:user], name[:host], name[:db], name[:table], name[:column] ]
+    end
+
+    if privs.match(/^$/) 
+      privs = [] # no result, no privs
+    else
+      # returns a line with field names and a line with values, each tab-separated
+      privs = privs.split(/\n/).map! do |l| l.chomp.split(/\t/) end
+      # transpose the lines, so we have key/value pairs
+      privs = privs[0].zip(privs[1])
+      privs = privs.select do |p| p[0].match(/_priv$/) and p[1] == 'Y' end
+    end
+
+    privs.collect do |p| symbolize(p[0].downcase) end
+  end
+
+  def privileges=(privs) 
+    unless row_exists?
+      create_row
+    end
+
+    # puts "Setting privs: ", privs.join(", ")
+    name = split_name(@resource[:name])
+    stmt = ''
+    where = ''
+    all_privs = []
+    case name[:type]
+    when :user
+      stmt = 'update user set '
+      where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ]
+      all_privs = MYSQL_USER_PRIVS
+    when :db
+      stmt = 'update db set '
+      where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ]
+      all_privs = MYSQL_DB_PRIVS
+    when :table
+      stmt = 'update table_priv set '
+      where = ' where user="%s" and host="%s" and Db="%s"' % [ name[:user], name[:host], name[:db] ]
+      all_privs = MYSQL_DB_PRIVS    
+    when :column
+      stmt = 'update columns_priv set '
+      where = ' where user="%s" and host="%s" and Db="%s" and Table="%s"' % [ name[:user], name[:host], name[:db], name[:table] ]
+      all_privs = MYSQL_DB_PRIVS    
+    end
+
+    if privs[0] == :all 
+      privs = all_privs
+    end
+  
+    # puts "stmt:", stmt
+    set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ')
+    # puts "set:", set
+    stmt = stmt << set << where
+
+    mysql "mysql", "-Be", stmt
+    mysql_flush
+  end
 end
 
diff --git a/lib/puppet/type/mysql_grant.rb b/lib/puppet/type/mysql_grant.rb
index 415f5aa..c6311e5 100644
--- a/lib/puppet/type/mysql_grant.rb
+++ b/lib/puppet/type/mysql_grant.rb
@@ -1,77 +1,89 @@
 # This has to be a separate type to enable collecting
 Puppet::Type.newtype(:mysql_grant) do
-	@doc = "Manage a database user's rights."
-	#ensurable
+  @doc = "Manage a database user's rights."
 
-	autorequire :mysql_db do
-		# puts "Starting db autoreq for %s" % self[:name]
-		reqs = []
-		matches = self[:name].match(/^([^@]+)@([^\/]+)\/(.+)$/)
-		unless matches.nil?
-			reqs << matches[3]
-		end
-		# puts "Autoreq: '%s'" % reqs.join(" ")
-		reqs
-	end
+  #ensurable
+  autorequire(:service) { 'mysqld' }
 
-	autorequire :mysql_user do
-		# puts "Starting user autoreq for %s" % self[:name]
-		reqs = []
-		matches = self[:name].match(/^([^@]+)@([^\/]+).*$/)
-		unless matches.nil?
-			reqs << "%s@%s" % [ matches[1], matches[2] ]
-		end
-		# puts "Autoreq: '%s'" % reqs.join(" ")
-		reqs
-	end
+  autorequire :mysql_table do
+    reqs = []
+    matches = self[:name].match(/^([^@]*)@([^\/]*)\/(.+)\/(.+)$/)
+    unless matches.nil?
+      reqs << matches[4]
+    end
+    # puts "Autoreq: '%s'" % reqs.join(" ")
+    reqs
+  end
 
-	newparam(:name) do
-		desc "The primary key: either user@host for global privilges or user@host/database for database specific privileges"
-	end
-	newproperty(:privileges, :array_matching => :all) do
-		desc "The privileges the user should have. The possible values are implementation dependent."
-		munge do |v|
-			symbolize(v)
-		end
+  autorequire :mysql_db do
+    # puts "Starting db autoreq for %s" % self[:name]
+    reqs = []
+    matches = self[:name].match(/^([^@]+)@([^\/]+)\/(.+)$/)
+    unless matches.nil?
+      reqs << matches[3]
+    end
+    # puts "Autoreq: '%s'" % reqs.join(" ")
+    reqs
+  end
 
-		def should_to_s(newvalue = @should)
-			if newvalue
-				unless newvalue.is_a?(Array)
-					newvalue = [ newvalue ]
-				end
-				newvalue.collect do |v| v.to_s end.sort.join ", "
-			else
-				nil
-			end
-		end
+  autorequire :mysql_user do
+    # puts "Starting user autoreq for %s" % self[:name]
+    reqs = []
+    matches = self[:name].match(/^([^@]+)@([^\/]+).*$/)
+    unless matches.nil?
+      reqs << "%s@%s" % [ matches[1], matches[2] ]
+    end
+    # puts "Autoreq: '%s'" % reqs.join(" ")
+    reqs
+  end
 
-		def is_to_s(currentvalue = @is)
-			if currentvalue
-				unless currentvalue.is_a?(Array)
-					currentvalue = [ currentvalue ]
-				end
-				currentvalue.collect do |v| v.to_s end.sort.join ", "
-			else
-				nil
-			end
-		end
+  newparam(:name) do
+    desc "The primary key: either user@host for global privilges or user@host/database for database specific privileges"
+  end
+  newproperty(:privileges, :array_matching => :all) do
+    desc "The privileges the user should have. The possible values are implementation dependent."
+    munge do |v|
+      symbolize(v)
+    end
 
-		# use the sorted outputs for comparison
-		def insync?(is)
-			if defined? @should and @should
-				case self.should_to_s 
-				when "all"
-					self.provider.all_privs_set?
-				when self.is_to_s(is)
-					true
-				else
-					false
-				end
-			else
-				true
-			end
-		end
+    def should_to_s(newvalue = @should)
+      if newvalue
+        unless newvalue.is_a?(Array)
+          newvalue = [ newvalue ]
+        end
+        newvalue.collect do |v| v.to_s end.sort.join ", "
+      else
+        nil
+      end
+    end
 
-	end
+    def is_to_s(currentvalue = @is)
+      if currentvalue
+        unless currentvalue.is_a?(Array)
+          currentvalue = [ currentvalue ]
+        end
+        currentvalue.collect do |v| v.to_s end.sort.join ", "
+      else
+        nil
+      end
+    end
+
+    # use the sorted outputs for comparison
+    def insync?(is)
+      if defined? @should and @should
+        case self.should_to_s 
+        when "all"
+          self.provider.all_privs_set?
+        when self.is_to_s(is)
+          true
+        else
+          false
+        end
+      else
+        true
+      end
+    end
+
+  end
 end