Support SQL security setting in view editor via drop down menu. See h…

…ttp://www.heidisql.com/forum.php?t=21740
HeidiSQL · Dec 14, 2016 · 5312a37 · 5312a37
1 parent e4a8540
commit 5312a37
Show file tree

Hide file tree

Showing 3 changed files with 44 additions and 8 deletions.
diff --git a/source/dbconnection.pas b/source/dbconnection.pas
@@ -2675,6 +2675,8 @@ function TMySQLConnection.GetCreateViewCode(Database, Name: String): String;
         Result := Result + 'ALGORITHM='+Uppercase(Algorithm)+' ';
       if Definer <> '' then
         Result := Result + 'DEFINER='+QuoteIdent(Definer, True, '@')+' ';
+      if not SQLSecurity.IsEmpty then
+        Result := Result + 'SQL SECURITY '+SQLSecurity+' ';
       Result := Result + 'VIEW '+Obj.QuotedName+' AS '+AlternativeSelectCode+' ';
       // WITH .. CHECK OPTION is already contained in the source
     end;
@@ -5065,15 +5067,18 @@ procedure TDBConnection.ParseViewStructure(CreateCode: String; DBObj: TDBObject;
     rx.Expression := 'CREATE\s+(OR\s+REPLACE\s+)?'+
       '(ALGORITHM\s*=\s*(\w*)\s*)?'+
       '(DEFINER\s*=\s*(\S+)\s+)?'+
-      '(SQL\s+SECURITY\s+\w+\s+)?'+
+      '(SQL\s+SECURITY\s+(\S+)\s+)?'+
       'VIEW\s+[^\(]+\s+'+
       '(\([^\)]+\)\s+)?'+
       'AS\s+(.+)(\s+WITH\s+(\w+\s+)?CHECK\s+OPTION\s*)?$';
     if rx.Exec(CreateCode) then begin
       Algorithm := rx.Match[3];
       Definer := DeQuoteIdent(rx.Match[5], '@');
-      CheckOption := Trim(rx.Match[10]);
-      SelectCode := rx.Match[8];
+      SQLSecurity := rx.Match[7];
+      if SQLSecurity.IsEmpty then
+        SQLSecurity := 'DEFINER';
+      CheckOption := Trim(rx.Match[11]);
+      SelectCode := rx.Match[9];
     end else
       raise Exception.CreateFmt(_('Regular expression did not match the VIEW code in %s: %s'), ['ParseViewStructure()', CRLF+CRLF+CreateCode]);
     rx.Free;

diff --git a/source/view.dfm b/source/view.dfm
@@ -16,7 +16,7 @@ object frmView: TfrmView
   end
   object lblSelect: TLabel
     Left = 3
-    Top = 124
+    Top = 149
     Width = 85
     Height = 13
     Caption = 'Select statement:'
@@ -39,6 +39,13 @@ object frmView: TfrmView
     Height = 13
     Caption = 'Definer:'
   end
+  object lblSecurity: TLabel
+    Left = 408
+    Top = 32
+    Width = 64
+    Height = 13
+    Caption = 'SQL security:'
+  end
   object editName: TEdit
     Left = 84
     Top = 3
@@ -51,7 +58,7 @@ object frmView: TfrmView
   end
   object rgAlgorithm: TRadioGroup
     Left = 3
-    Top = 32
+    Top = 57
     Width = 391
     Height = 86
     Caption = 'Algorithm'
@@ -65,9 +72,9 @@ object frmView: TfrmView
   end
   object SynMemoBody: TSynMemo
     Left = 3
-    Top = 143
+    Top = 168
     Width = 693
-    Height = 324
+    Height = 299
     SingleLineMode = False
     Anchors = [akLeft, akTop, akRight, akBottom]
     Font.Charset = DEFAULT_CHARSET
@@ -125,7 +132,7 @@ object frmView: TfrmView
   end
   object rgCheck: TRadioGroup
     Left = 408
-    Top = 32
+    Top = 57
     Width = 288
     Height = 86
     Anchors = [akLeft, akTop, akRight]
@@ -159,4 +166,14 @@ object frmView: TfrmView
     OnChange = Modification
     OnDropDown = comboDefinerDropDown
   end
+  object comboSecurity: TComboBox
+    Left = 489
+    Top = 30
+    Width = 207
+    Height = 21
+    Style = csDropDownList
+    Anchors = [akLeft, akTop, akRight]
+    TabOrder = 8
+    OnChange = Modification
+  end
 end
diff --git a/source/view.pas b/source/view.pas
@@ -22,6 +22,8 @@   TfrmView = class(TFrame)
     lblDisabledWhy: TLabel;
     lblDefiner: TLabel;
     comboDefiner: TComboBox;
+    lblSecurity: TLabel;
+    comboSecurity: TComboBox;
     procedure btnHelpClick(Sender: TObject);
     procedure btnSaveClick(Sender: TObject);
     procedure btnDiscardClick(Sender: TObject);
@@ -54,6 +56,8 @@ constructor TfrmView.Create(AOwner: TComponent);
   SynMemoBody.Highlighter := Mainform.SynSQLSyn1;
   Mainform.SynCompletionProposal.AddEditor(SynMemoBody);
   editName.MaxLength := NAME_LEN;
+  comboSecurity.Items.Add('Definer');
+  comboSecurity.Items.Add('Invoker');
 end;
 
 
@@ -63,6 +67,7 @@ constructor TfrmView.Create(AOwner: TComponent);
 procedure TfrmView.Init(Obj: TDBObject);
 var
   Algorithm, CheckOption, SelectCode, Definer, SQLSecurity: String;
+  i: Integer;
 begin
   inherited;
   lblDisabledWhy.Font.Color := clRed;
@@ -78,6 +83,12 @@ procedure TfrmView.Init(Obj: TDBObject);
     rgCheck.ItemIndex := rgCheck.Items.IndexOf(CheckOption);
     if rgCheck.ItemIndex = -1 then
       rgCheck.ItemIndex := 0;
+    for i:=0 to comboSecurity.Items.Count-1 do begin
+      if LowerCase(SQLSecurity) = LowerCase(comboSecurity.Items[i]) then begin
+        comboSecurity.ItemIndex := i;
+        Break;
+      end;
+    end;
     SynMemoBody.Text := SelectCode;
     // User may not be allowed to run SHOW CREATE VIEW, in which case we have an empty CreateCode.
     // Disable editor in this case.
@@ -93,6 +104,7 @@ procedure TfrmView.Init(Obj: TDBObject);
     rgAlgorithm.ItemIndex := 0;
     rgCheck.Enabled := True;
     rgCheck.ItemIndex := 0;
+    comboSecurity.ItemIndex := 0;
     SynMemoBody.Text := 'SELECT ';
     lblDisabledWhy.Hide;
   end;
@@ -155,6 +167,8 @@ function TfrmView.ApplyModifications: TModalResult;
     sql := sql + 'ALGORITHM = '+Uppercase(rgAlgorithm.Items[rgAlgorithm.ItemIndex])+' ';
   if comboDefiner.Text <> '' then
     sql := sql + 'DEFINER='+DBObject.Connection.QuoteIdent(comboDefiner.Text, True, '@')+' ';
+  if comboSecurity.Text <> '' then
+    sql := sql + 'SQL SECURITY ' + UpperCase(comboSecurity.Text)+' ';
   sql := sql + 'VIEW ' + viewname+' AS '+SynMemoBody.Text+' ';
   if rgCheck.Enabled and (rgCheck.ItemIndex > 0) then
     sql := sql + 'WITH '+Uppercase(rgCheck.Items[rgCheck.ItemIndex])+' CHECK OPTION';