usermanager.pas

MysqlFront的源码

            ShowPrivilegesControls(true, false, false);
            CheckListBoxPrivs.Checked[0] := ZQueryDBs.Fields[3].AsString = 'Y'; // Select
            CheckListBoxPrivs.Checked[1] := ZQueryDBs.Fields[4].AsString = 'Y'; // Insert
            CheckListBoxPrivs.Checked[2] := ZQueryDBs.Fields[5].AsString = 'Y'; // Update
            CheckListBoxPrivs.Checked[3] := ZQueryDBs.Fields[6].AsString = 'Y'; // Delete
            CheckListBoxPrivs.Checked[4] := ZQueryDBs.Fields[7].AsString = 'Y'; // Create
            CheckListBoxPrivs.Checked[5] := ZQueryDBs.Fields[8].AsString = 'Y'; // Drop
            CheckListBoxPrivs.Checked[6] := ZQueryDBs.Fields[9].AsString = 'Y'; // Grant
            CheckListBoxPrivs.Checked[7] := ZQueryDBs.Fields[10].AsString = 'Y'; // References
            CheckListBoxPrivs.Checked[8] := ZQueryDBs.Fields[11].AsString = 'Y'; // Index
            CheckListBoxPrivs.Checked[9] := ZQueryDBs.Fields[12].AsString = 'Y'; // Alter
          end;
          ZQueryDBs.Next;
        end;
      end;

    2 : begin // table-privileges
        ZQueryTables.First;
        for i:=1 to ZQueryTables.RecordCount do
        begin
          if (ZQueryTables.Fields[0].AsString = Host)
            and (ZQueryTables.Fields[1].AsString = Node.Parent.Text)
            and (ZQueryTables.Fields[2].AsString = User)
            and (ZQueryTables.Fields[3].AsString = Node.Text) then
          begin // found the according record!
            // some privs are set:
            ShowPrivilegesControls(true, false, false);
            // find values in set-field:
            for j:=0 to CheckListBoxPrivs.Items.Count-1 do
              CheckListBoxPrivs.Checked[j] := pos(CheckListBoxPrivs.Items[j], ZQueryTables.Fields[6].AsString) > 0;
          end;
          ZQueryTables.Next;
        end;
      end;

    3 : begin // column-privileges
        ZQueryColumns.First;
        for i:=1 to ZQueryColumns.RecordCount do
        begin
          if (ZQueryColumns.Fields[0].AsString = Host)
            and (ZQueryColumns.Fields[1].AsString = Node.Parent.Parent.Text)
            and (ZQueryColumns.Fields[2].AsString = User)
            and (ZQueryColumns.Fields[3].AsString = Node.Parent.Text)
            and (ZQueryColumns.Fields[4].AsString = Node.Text) then
          begin
            // some privs are set:
            ShowPrivilegesControls(true, false, false);
            // find values in set-field:
            for j:=0 to CheckListBoxPrivs.Items.Count-1 do
              CheckListBoxPrivs.Checked[j] := pos(CheckListBoxPrivs.Items[j], ZQueryColumns.Fields[6].AsString) > 0;
          end;
          ZQueryColumns.Next;
        end;
      end;
  end;

end;




procedure TUserManagerForm.ButtonSetClick(Sender: TObject);

function checked2yn(index: Byte): String;
begin
  if CheckListBoxPrivs.Checked[index] then
    result := 'Y'
  else
    result := 'N';
end;

var
  sql : String;
  i   : Byte;
begin
  // Grant specified Privileges
  Screen.Cursor := crHourglass;
  case TreeViewUsers.Selected.Level of
    0 : begin // general
        sql := 'UPDATE mysql.user SET ';
        for i:=0 to CheckListBoxPrivs.Items.Count-1 do begin
          if i > 0 then
            sql := sql + ', ';
          sql := sql + ' ' + CheckListBoxPrivs.Items[i] + '_priv = ''' + checked2yn(i) + '''';
        end;
        sql := sql + ' WHERE Host = ''' + Host + ''' AND User = ''' + User + '''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        GetResUsers;
      end;

    1 : begin // db
        if editcurrent then begin
          sql := 'UPDATE mysql.db SET ';
          for i:=0 to CheckListBoxPrivs.Items.Count-1 do begin
            if i > 0 then
              sql := sql + ', ';
            sql := sql + ' ' + CheckListBoxPrivs.Items[i] + '_priv = ''' + checked2yn(i) + '''';
          end;
          sql := sql + ' WHERE Host = ''' + Host + ''' AND Db = ''' + TreeViewUsers.Selected.Text + ''' AND User = ''' + User + '''';
        end
        else begin
          sql := 'INSERT INTO mysql.db (Host, Db, User';
          for i:=0 to CheckListBoxPrivs.Items.Count-1 do
            sql := sql + ', ' + CheckListBoxPrivs.Items[i] + '_priv';
          sql := sql + ') VALUES (''' + Host + ''', ''' + TreeViewUsers.Selected.Text + ''', ''' + User + '''';
          for i:=0 to CheckListBoxPrivs.Items.Count-1 do
            sql := sql + ', ''' + checked2yn(i) + '''';
          sql := sql + ')';
          editcurrent := true;
        end;
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        GetResDBs;
      end;

    2 : begin // table
        if editcurrent then begin
          sql := 'UPDATE mysql.tables_priv SET Table_priv = ''';
          for i:=0 to CheckListBoxPrivs.Items.Count-1 do begin
            if CheckListBoxPrivs.Checked[i] then
              sql := sql + CheckListBoxPrivs.Items[i] + ',';
          end;
          if sql[length(sql)] = ',' then
            delete(sql, length(sql), 1); // last comma
          sql := sql + ''' WHERE Host = ''' + Host + ''' AND Db = ''' + TreeViewUsers.Selected.Parent.Text + ''' AND User = ''' + User + ''' AND Table_name = ''' + TreeViewUsers.Selected.Text + '''';
        end
        else begin
          sql := 'INSERT INTO mysql.tables_priv (Host, Db, User, Table_name, Grantor, Table_priv) VALUES ('''+Host+''','''+TreeViewUsers.Selected.Parent.Text+''','''+User+''','''+TreeViewUsers.Selected.Text+''','''+TMDIChild(Mainform.ActiveMDIChild).ZConn.User+''',''';
          for i:=0 to CheckListBoxPrivs.Items.Count-1 do begin
            if CheckListBoxPrivs.Checked[i] then
              sql := sql + CheckListBoxPrivs.Items[i] + ',';
          end;
          if sql[length(sql)] = ',' then
            delete(sql, length(sql), 1); // last comma
          sql := sql + ''')';
          editcurrent := true;
        end;
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        GetResTables;
      end;

    3 : begin // column
        if editcurrent then begin
          sql := 'UPDATE mysql.columns_priv SET Column_priv = ''';
          for i:=0 to CheckListBoxPrivs.Items.Count-1 do begin
            if CheckListBoxPrivs.Checked[i] then
              sql := sql + CheckListBoxPrivs.Items[i] + ',';
          end;
          if sql[length(sql)] = ',' then
            delete(sql, length(sql), 1); // last comma
          sql := sql + ''' WHERE Host = ''' + Host + ''' AND Db = ''' + TreeViewUsers.Selected.Parent.Parent.Text + ''' AND User = ''' + User + ''' AND Table_name = ''' + TreeViewUsers.Selected.Parent.Text + ''' AND Column_name = ''' + TreeViewUsers.Selected.Text + '''';
        end
        else begin
          sql := 'INSERT INTO mysql.columns_priv (Host, Db, User, Table_name, Column_name, Column_priv) VALUES ('''+Host+''','''+TreeViewUsers.Selected.Parent.Parent.Text+''','''+User+''','''+TreeViewUsers.Selected.Parent.Text+''','''+TreeViewUsers.Selected.Text+''',''';
          for i:=0 to CheckListBoxPrivs.Items.Count-1 do begin
            if CheckListBoxPrivs.Checked[i] then
              sql := sql + CheckListBoxPrivs.Items[i] + ',';
          end;
          if sql[length(sql)] = ',' then
            delete(sql, length(sql), 1); // last comma
          sql := sql + ''')';
          editcurrent := true;
        end;
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        GetResColumns;
      end;
  end;
  TMDIChild(Mainform.ActiveMDIChild).ExecQuery('FLUSH PRIVILEGES');
  ButtonRevoke.Enabled := editcurrent;
  Screen.Cursor := crDefault;
end;




procedure TUserManagerForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  // free memory
  ZQueryUsers.Active := False;
  ZQueryDBs.Active := False;
  ZQueryTables.Active := False;
  ZQueryColumns.Active := False;
  ZQueryF.Active := False;
end;


procedure TUserManagerForm.CheckListBoxPrivsClickCheck(Sender: TObject);
begin
  ButtonSet.Enabled := true;
end;


procedure TUserManagerForm.ButtonRevokeClick(Sender: TObject);
var sql : String;
begin
  // Delete some Privs
  Screen.Cursor := crHourglass;
  case TreeViewUsers.Selected.Level of
    0 : // delete user
      if MessageDLG('Delete User '''+User+''' and all its privileges?', mtConfirmation, [mbNo, mbYes], 0) = mrYes then begin
        sql := 'DELETE FROM mysql.user WHERE Host='''+Host+''' AND User='''+User+'''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        sql := 'DELETE FROM mysql.db WHERE Host='''+Host+''' AND User='''+User+'''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        sql := 'DELETE FROM mysql.tables_priv WHERE Host='''+Host+''' AND User='''+User+'''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        sql := 'DELETE FROM mysql.columns_priv WHERE Host='''+Host+''' AND User='''+User+'''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        TreeViewUsers.Selected.Delete;
        ZQueryDBs.Active := False;
        ZQueryTables.Active := False;
        ZQueryColumns.Active := False;
        GetResUsers;
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery('FLUSH PRIVILEGES');
      end;
    1 : // delete db-privs
      begin
        sql := 'DELETE FROM mysql.db WHERE Host='''+Host+''' AND User='''+User+''' AND Db='''+TreeViewUsers.Selected.Text+'''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        ShowPrivilegesControls(false, false, true);
        GetResDBs;
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery('FLUSH PRIVILEGES');
      end;
    2 : // delete table-privs
      begin
        sql := 'DELETE FROM mysql.tables_priv WHERE Host='''+Host+''' AND User='''+User+''' AND Db='''+TreeViewUsers.Selected.Parent.Text+''' AND Table_name='''+TreeViewUsers.Selected.Text+'''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        ShowPrivilegesControls(false, false, true);
        GetResTables;
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery('FLUSH PRIVILEGES');
      end;
    3 : // delete column-privs
      begin
        sql := 'DELETE FROM mysql.columns_priv WHERE Host='''+Host+''' AND User='''+User+''' AND Db='''+TreeViewUsers.Selected.Parent.Parent.Text+''' AND Table_name='''+TreeViewUsers.Selected.Parent.Text+''' AND Column_name='''+TreeViewUsers.Selected.Text+'''';
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery(sql);
        ShowPrivilegesControls(false, false, true);
        GetResColumns;
        TMDIChild(Mainform.ActiveMDIChild).ExecQuery('FLUSH PRIVILEGES');
      end;
  end;
  Screen.Cursor := crDefault;
end;

procedure TUserManagerForm.ButtonEditUserClick(Sender: TObject);
begin
  FormEditUser.ShowModal;
end;

procedure TUserManagerForm.Button1Click(Sender: TObject);
begin
  // free memory
  ZQueryUsers.Active := False;
  ZQueryDBs.Active := False;
  ZQueryTables.Active := False;
  ZQueryColumns.Active := False;
  ZQueryF.Active := False;
  ShowPrivilegesControls(false, true, false);
  TreeViewUsers.Items.Clear;
  PageControl1.OnChange(self);
end;


procedure TUserManagerForm.GetResUsers;
begin
  TMDIChild(Mainform.ActiveMDIChild).GetResults( 'SELECT * FROM mysql.user', ZQueryUsers );
end;

procedure TUserManagerForm.GetResDBs;
begin
  TMDIChild(Mainform.ActiveMDIChild).GetResults( 'SELECT * FROM mysql.db', ZQueryDBs );
end;

procedure TUserManagerForm.GetResTables;
begin
  TMDIChild(Mainform.ActiveMDIChild).GetResults( 'SELECT * FROM mysql.tables_priv', ZQueryTables );
end;

procedure TUserManagerForm.GetResColumns;
begin
  TMDIChild(Mainform.ActiveMDIChild).GetResults( 'SELECT * FROM mysql.columns_priv', ZQueryColumns );
end;

end.