ViewVC Help

View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing

root/public/ibx/trunk/runtime/IBExtract.pas

Comparing ibx/trunk/runtime/IBExtract.pas (file contents):
Revision 101 by tony, Thu Jan 18 14:37:18 2018 UTC vs.
Revision 143 by tony, Fri Feb 23 12:11:21 2018 UTC

#	Line 19 | Line 19
19		{    IBX For Lazarus (Firebird Express)                                  }
20		{    Contributor: Tony Whyman, MWA Software http://www.mwasoftware.co.uk }
21		{    Portions created by MWA Software are copyright McCallum Whyman      }
22	<	{    Associates Ltd 2011                                                 }
22	>	{    Associates Ltd 2011 - 2018                                               }
23		{                                                                        }
24		{************************************************************************}
25
#	Line 31 | Line 31 | Database Triggers (2.1)
31		Global Temporary Tables (2.1)
32		Boolean Type (3.0)
33		Identity Column Type (3.0)
34	+	DDL Triggers (3.0)
35		}
36
37		unit IBExtract;
#	Line 57 | Line 58 | type
58
59		TExtractType =
60		(etDomain, etTable, etRole, etTrigger, etForeign,
61	<	etIndex, etData, etGrant, etCheck);
61	>	etIndex, etData, etGrant, etCheck, etGrantsToUser);
62
63		TExtractTypes = Set of TExtractType;
64
#	Line 77 | Line 78 | type
78		function GetDatabase: TIBDatabase;
79		function GetIndexSegments ( indexname : String) : String;
80		function GetTransaction: TIBTransaction;
81	<	function GetTriggerType(TypeID: integer): string;
81	>	function GetTriggerType(TypeID: Int64): string;
82		procedure SetDatabase(const Value: TIBDatabase);
83		procedure SetTransaction(const Value: TIBTransaction);
84		function PrintValidation(ToValidate : String;       flag : Boolean) : String;
85	<	procedure ShowGrants(MetaObject: String; Terminator : String);
85	>	procedure ShowGrants(MetaObject: String; Terminator : String; NoUserGrants: boolean=false);
86		procedure ShowGrantsTo(MetaObject: String; ObjectType: integer;
87		Terminator: String);
88		procedure ShowGrantRoles(Terminator : String);
89		procedure GetProcedureArgs(Proc : String);
90		protected
91	<	function ExtractDDL(Flag: Boolean; TableName: String; IncludeData: boolean =
92	<	false): Boolean;
91	>	function ExtractDDL(Flag: Boolean; TableName: String; ExtractTypes: TExtractTypes =
92	>	[]): Boolean;
93		function ExtractListTable(RelationName, NewName: String; DomainFlag: Boolean): Boolean;
94		procedure ExtractListView (ViewName : String);
95		procedure ListData(ObjectName : String);
96	<	procedure ListRoles(ObjectName : String = '');
97	<	procedure ListGrants;
96	>	procedure ListRoles(ObjectName : String = ''; IncludeGrants:boolean=false);
97	>	procedure ListGrants(ExtractTypes : TExtractTypes = []);
98		procedure ListProcs(ProcDDLType: TProcDDLType = pdCreateProc; ProcedureName : String = '';
99		IncludeGrants:boolean=false);
100		procedure ListAllTables(flag : Boolean);
#	Line 274 | Line 275 | const
275		'  FDIM.RDB$FIELD_NAME = :FIELDNAME ' +
276		'ORDER BY FDIM.RDB$DIMENSION';
277
278	+	type
279	+	TTriggerPhase = (tpNone,tpCreate,tpAlter,tpDrop);
280	+
281	+	TDDLTriggerMap = record
282	+	ObjectName: string;
283	+	Bits: integer;
284	+	Bit1: TTriggerPhase;
285	+	Bit2: TTriggerPhase;
286	+	Bit3: TTriggerPhase;
287	+	end;
288	+
289	+	const
290	+	DDLTriggers : array [0..15] of TDDLTriggerMap = (
291	+	(ObjectName: 'TABLE'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
292	+	(ObjectName: 'PROCEDURE'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
293	+	(ObjectName: 'FUNCTION'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
294	+	(ObjectName: 'TRIGGER'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
295	+	(ObjectName: 'Empty slot'; Bits: 3; Bit1: tpNone; Bit2: tpNone; Bit3: tpNone),
296	+	(ObjectName: 'EXCEPTION'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
297	+	(ObjectName: 'VIEW'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
298	+	(ObjectName: 'DOMAIN'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
299	+	(ObjectName: 'ROLE'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
300	+	(ObjectName: 'INDEX'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
301	+	(ObjectName: 'SEQUENCE'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
302	+	(ObjectName: 'USER'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
303	+	(ObjectName: 'COLLATION'; Bits: 2; Bit1: tpCreate; Bit2: tpDrop; Bit3: tpNone),
304	+	(ObjectName: 'CHARACTER SET'; Bits: 1; Bit1: tpAlter; Bit2: tpNone; Bit3: tpNone),
305	+	(ObjectName: 'PACKAGE'; Bits: 3; Bit1: tpCreate; Bit2: tpAlter; Bit3: tpDrop),
306	+	(ObjectName: 'PACKAGE BODY'; Bits: 2; Bit1: tpCreate; Bit2: tpDrop; Bit3: tpNone)
307	+	);
308	+
309		{ TIBExtract }
310
311		{                       ArrayDimensions
#	Line 331 | Line 363 | begin
363		inherited;
364		end;
365
366	<	function TIBExtract.ExtractDDL(Flag: Boolean; TableName: String; IncludeData: boolean = false) : Boolean;
366	>	function TIBExtract.ExtractDDL(Flag: Boolean; TableName: String;
367	>	ExtractTypes: TExtractTypes): Boolean;
368		var
369		DidConnect : Boolean;
370		DidStart : Boolean;
#	Line 368 | Line 401 | begin
401		ListFunctions;
402		ListDomains;
403		ListAllTables(flag);
404	<	if IncludeData then
404	>	if etData in ExtractTypes then
405		ListData('');
406		ListIndex;
407		ListForeign;
408	<	if IncludeData then
408	>	if etData in ExtractTypes then
409		ListGenerators('',[etData])
410		else
411		ListGenerators;
#	Line 382 | Line 415 | begin
415		ListProcs(pdCreateStub);
416		ListTriggers;
417		ListProcs(pdAlterProc);
418	<	ListGrants;
418	>	ListGrants(ExtractTypes);
419		end;
420
421		if DidStart then
#	Line 436 | Line 469 | const
469		var
470		Collation, CharSetId : integer;
471		i : integer;
472	<	ColList, Column, Constraint : String;
472	>	Column, Constraint : String;
473		SubType : integer;
474		IntChar : integer;
475		qryTables, qryPrecision, qryConstraints, qryRelConstraints, qryGenerators : TIBSQL;
#	Line 446 | Line 479 | var
479		TableType: integer;
480		begin
481		Result := true;
449	–	ColList := '';
482		IntChar := 0;
483		ValidRelation := false;
484
#	Line 755 | Line 787 | var
787		qryViews, qryColumns : TIBSQL;
788		RelationName, ColList : String;
789		begin
790	+	ColList := '';
791		qryViews := TIBSQL.Create(FDatabase);
792		qryColumns := TIBSQL.Create(FDatabase);
793		try
#	Line 899 | Line 932 | begin
932		Result := FTransaction;
933		end;
934
935	<	function TIBExtract.GetTriggerType(TypeID: integer): string;
935	>	function TIBExtract.GetTriggerType(TypeID: Int64): string;
936	>	const
937	>	AllDDLTriggers = $7FFFFFFFFFFFDFFF shr 1;
938		var separator: string;
939	+	i: integer;
940	+
941	+	function GetDDLEvent(Phase: TTriggerPhase; ObjectName: string): string;
942	+	begin
943	+	Result := '';
944	+	case Phase of
945	+	tpCreate:
946	+	Result := separator + 'CREATE ' + ObjectName;
947	+	tpAlter:
948	+	Result := separator + 'ALTER ' + ObjectName;
949	+	tpDrop:
950	+	Result := separator + 'Drop ' + ObjectName;
951	+	end;
952	+	if Result <> '' then
953	+	separator := ' OR ';
954	+	end;
955	+
956		begin
957		if TypeID and $2000 <> 0 then
958		{database trigger}
#	Line 920 | Line 972 | begin
972		end;
973		end
974		else
975	+	if TypeID and $4000 <> 0 then
976	+	{DDL Trigger}
977	+	begin
978	+	if TypeID and $01 <> 0 then
979	+	Result := 'AFTER '
980	+	else
981	+	Result := 'BEFORE ';
982	+	TypeID := TypeID shr 1;
983	+	separator := '';
984	+	i := 0;
985	+	if TypeID = AllDDLTriggers then
986	+	Result += 'ANY DDL STATEMENT'
987	+	else
988	+	repeat
989	+	if (DDLTriggers[i].Bits > 0) and (TypeID and $01 <> 0) then
990	+	Result += GetDDLEvent(DDLTriggers[i].Bit1,DDLTriggers[i].ObjectName);
991	+
992	+	if (DDLTriggers[i].Bits > 1) and (TypeID and $02 <> 0) then
993	+	Result += GetDDLEvent(DDLTriggers[i].Bit2,DDLTriggers[i].ObjectName);
994	+
995	+	if (DDLTriggers[i].Bits > 2) and (TypeID and $04 <> 0) then
996	+	Result += GetDDLEvent(DDLTriggers[i].Bit3,DDLTriggers[i].ObjectName);
997	+	TypeID := TypeID shr DDLTriggers[i].Bits;
998	+	Inc(i);
999	+	until TypeID = 0;
1000	+	end
1001	+	else
1002	+	{Normal Trigger}
1003		begin
1004		Inc(TypeID);
1005		if TypeID and $01 <> 0 then
#	Line 940 | Line 1020 | begin
1020		Result += 'DELETE';
1021		end;
1022		TypeID := TypeID shr 2;
1023	<	until TypeID = 0;
1024	<	end;
1023	>	until TypeID = 0
1024	>	end
1025		end;
1026
1027		{          ListAllGrants
#	Line 949 | Line 1029 | end;
1029		Print the permissions on all user tables.
1030		Get separate permissions on table/views and then procedures }
1031
1032	<	procedure TIBExtract.ListGrants;
1032	>	procedure TIBExtract.ListGrants(ExtractTypes: TExtractTypes);
1033		const
1034		SecuritySQL = 'SELECT * FROM RDB$RELATIONS ' +
1035		'WHERE ' +
#	Line 957 | Line 1037 | const
1037		'  RDB$SECURITY_CLASS STARTING WITH ''SQL$'' ' +
1038		'ORDER BY RDB$RELATION_NAME';
1039
1040	+	DomainSQL  = 'select RDB$FIELD_NAME from RDB$FIELDS '+
1041	+	'where RDB$SYSTEM_FLAG <> 1 and RDB$FIELD_NAME not Similar to ''RDB$%|SEC$%|MON$%|SQL$%'' '+
1042	+	'order BY RDB$FIELD_NAME';
1043	+
1044	+	CharacterSetSQL = 'Select * From RDB$CHARACTER_SETS  '+
1045	+	'Where RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL ' +
1046	+	'Order by RDB$CHARACTER_SET_NAME';
1047	+
1048	+	CollationsSQL = 'Select * From RDB$COLLATIONS  '+
1049	+	'Where RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL ' +
1050	+	'Order by RDB$COLLATION_NAME';
1051	+
1052		ProcedureSQL = 'select * from RDB$PROCEDURES '+
1053		'Where RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL ' +
1054		'Order BY RDB$PROCEDURE_NAME';
#	Line 969 | Line 1061 | const
1061		'Where RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL ' +
1062		'Order BY RDB$GENERATOR_NAME';
1063
1064	+	MetadataGrantsSQL =
1065	+	'Select PR.RDB$USER, PR.RDB$USER_TYPE, '+
1066	+	'T.RDB$TYPE_NAME as USER_TYPE_NAME, '+
1067	+	'Case PR.RDB$PRIVILEGE '+
1068	+	'  When ''C'' then ''CREATE'' '+
1069	+	'  When ''O'' then ''DROP ANY'' '+
1070	+	'  When ''L'' then ''ALTER ANY''  End as Privilege, '+
1071	+	'Case PR.RDB$RELATION_NAME '+
1072	+	'  When ''SQL$COLLATIONS'' then ''COLLATION'' '+
1073	+	'  When ''SQL$CHARSETS'' then ''CHARACTER SET'' '+
1074	+	'  When ''SQL$DATABASE'' then ''DATABASE'' '+
1075	+	'  When ''SQL$DOMAINS'' then ''DOMAIN'' '+
1076	+	'  When ''SQL$EXCEPTIONS'' then ''EXCEPTION'' '+
1077	+	'  When ''SQL$FILTERS'' then ''FILTER'' '+
1078	+	'  When ''SQL$FUNCTIONS'' then ''FUNCTION'' '+
1079	+	'  When ''SQL$GENERATORS'' then ''GENERATOR'' '+
1080	+	'  When ''SQL$PACKAGES'' then ''PACKAGE'' '+
1081	+	'  When ''SQL$PROCEDURES'' then ''PROCEDURE'' '+
1082	+	'  When ''SQL$ROLES'' then ''ROLE'' '+
1083	+	'  When ''SQL$VIEWS'' then ''VIEW'' '+
1084	+	'  When ''SQL$TABLES'' then ''TABLE'' End as METAOBJECTNAME,'+
1085	+
1086	+	'case when coalesce(RDB$GRANT_OPTION,0) <> 0 then '' WITH GRANT OPTION'' '+
1087	+	'ELSE '''' End as GRANTOPTION '+
1088	+	'FROM RDB$USER_PRIVILEGES PR '+
1089	+	'JOIN RDB$TYPES T On T.RDB$TYPE = PR.RDB$USER_TYPE and T.RDB$FIELD_NAME = ''RDB$OBJECT_TYPE'' '+
1090	+	'Where PR.RDB$RELATION_NAME like ''SQL$%'' and PR.RDB$PRIVILEGE in (''L'',''C'',''O'')';
1091	+
1092		var
1093		qryRoles : TIBSQL;
1094		RelationName : String;
#	Line 989 | Line 1109 | begin
1109		while not qryRoles.Eof do
1110		begin
1111		RelationName := Trim(qryRoles.FieldByName('rdb$relation_Name').AsString);
1112	<	ShowGrants(RelationName, Term);
1112	>	ShowGrants(RelationName, Term, not (etGrantsToUser in ExtractTypes));
1113		qryRoles.Next;
1114		end;
1115		finally
#	Line 998 | Line 1118 | begin
1118
1119		ShowGrantRoles(Term);
1120
1121	<	qryRoles.SQL.Text := ExceptionSQL;
1122	<	qryRoles.ExecQuery;
1123	<	try
1124	<	while not qryRoles.Eof do
1125	<	begin
1126	<	ShowGrants(Trim(qryRoles.FieldByName('RDB$EXCEPTION_NAME').AsString), Term);
1127	<	qryRoles.Next;
1121	>	if FDatabaseInfo.ODSMajorVersion >= ODS_VERSION12 then
1122	>	begin
1123	>	qryRoles.SQL.Text := DomainSQL;
1124	>	qryRoles.ExecQuery;
1125	>	try
1126	>	while not qryRoles.Eof do
1127	>	begin
1128	>	ShowGrants(Trim(qryRoles.FieldByName('RDB$FIELD_NAME').AsString), Term,
1129	>	not (etGrantsToUser in ExtractTypes));
1130	>	qryRoles.Next;
1131	>	end;
1132	>	finally
1133	>	qryRoles.Close;
1134	>	end;
1135	>
1136	>	qryRoles.SQL.Text := CharacterSetSQL;
1137	>	qryRoles.ExecQuery;
1138	>	try
1139	>	while not qryRoles.Eof do
1140	>	begin
1141	>	ShowGrants(Trim(qryRoles.FieldByName('RDB$CHARACTER_SET_NAME').AsString), Term,
1142	>	not (etGrantsToUser in ExtractTypes));
1143	>	qryRoles.Next;
1144	>	end;
1145	>	finally
1146	>	qryRoles.Close;
1147	>	end;
1148	>
1149	>	qryRoles.SQL.Text := CollationsSQL;
1150	>	qryRoles.ExecQuery;
1151	>	try
1152	>	while not qryRoles.Eof do
1153	>	begin
1154	>	ShowGrants(Trim(qryRoles.FieldByName('RDB$COLLATION_NAME').AsString), Term,
1155	>	not (etGrantsToUser in ExtractTypes));
1156	>	qryRoles.Next;
1157	>	end;
1158	>	finally
1159	>	qryRoles.Close;
1160	>	end;
1161	>
1162	>	qryRoles.SQL.Text := ExceptionSQL;
1163	>	qryRoles.ExecQuery;
1164	>	try
1165	>	while not qryRoles.Eof do
1166	>	begin
1167	>	ShowGrants(Trim(qryRoles.FieldByName('RDB$EXCEPTION_NAME').AsString), Term,
1168	>	not (etGrantsToUser in ExtractTypes));
1169	>	qryRoles.Next;
1170	>	end;
1171	>	finally
1172	>	qryRoles.Close;
1173	>	end;
1174	>
1175	>	qryRoles.SQL.Text := GeneratorSQL;
1176	>	qryRoles.ExecQuery;
1177	>	try
1178	>	while not qryRoles.Eof do
1179	>	begin
1180	>	ShowGrants(Trim(qryRoles.FieldByName('RDB$GENERATOR_NAME').AsString), Term,
1181	>	not (etGrantsToUser in ExtractTypes));
1182	>	qryRoles.Next;
1183	>	end;
1184	>	finally
1185	>	qryRoles.Close;
1186		end;
1009	–	finally
1010	–	qryRoles.Close;
1187		end;
1188
1189	<	qryRoles.SQL.Text := GeneratorSQL;
1189	>	qryRoles.SQL.Text := ProcedureSQL;
1190		qryRoles.ExecQuery;
1191		try
1192		while not qryRoles.Eof do
1193		begin
1194	<	ShowGrants(Trim(qryRoles.FieldByName('RDB$GENERATOR_NAME').AsString), Term);
1194	>	ShowGrants(Trim(qryRoles.FieldByName('RDB$PROCEDURE_NAME').AsString), Term,
1195	>	not (etGrantsToUser in ExtractTypes));
1196		qryRoles.Next;
1197		end;
1198		finally
1199		qryRoles.Close;
1200		end;
1201
1202	<	qryRoles.SQL.Text := ProcedureSQL;
1203	<	qryRoles.ExecQuery;
1204	<	try
1202	>	{Metadata Grants}
1203	>	if FDatabaseInfo.ODSMajorVersion >= ODS_VERSION12 then
1204	>	begin
1205	>	qryRoles.SQL.Text := MetadataGrantsSQL;
1206	>	qryRoles.ExecQuery;
1207		while not qryRoles.Eof do
1208		begin
1209	<	ShowGrants(Trim(qryRoles.FieldByName('RDB$PROCEDURE_NAME').AsString), Term);
1209	>	if (etGrantsToUser in ExtractTypes) or
1210	>	(qryRoles.FieldByName('RDB$USER_TYPE').AsInteger <> obj_user) or
1211	>	(qryRoles.FieldByName('RDB$USER').AsString = 'PUBLIC') then
1212	>	FMetaData.Add(Format('GRANT %s %s TO %s "%s" %s%s', [
1213	>	qryRoles.FieldByName('Privilege').AsString,
1214	>	qryRoles.FieldByName('METAOBJECTNAME').AsString,
1215	>	qryRoles.FieldByName('USER_TYPE_NAME').AsString,
1216	>	qryRoles.FieldByName('RDB$USER').AsString,
1217	>	qryRoles.FieldByName('GRANTOPTION').AsString,
1218	>	Term]));
1219		qryRoles.Next;
1220		end;
1033	–	finally
1221		qryRoles.Close;
1222		end;
1223		finally
#	Line 1303 | Line 1490 | begin
1490		SList.Add(Format('CREATE TRIGGER %s%s%s %s POSITION %d',
1491		[QuoteIdentifier(FDatabase.SQLDialect, TriggerName),
1492		LineEnding, InActive,
1493	<	GetTriggerType(qryTriggers.FieldByName('RDB$TRIGGER_TYPE').AsInteger),
1493	>	GetTriggerType(qryTriggers.FieldByName('RDB$TRIGGER_TYPE').AsInt64),
1494		qryTriggers.FieldByName('RDB$TRIGGER_SEQUENCE').AsInteger]));
1495
1496		if RelationName <> '' then
#	Line 2614 | Line 2801 | end;
2801
2802		procedure TIBExtract.SetDatabase(const Value: TIBDatabase);
2803		begin
2804	<	if FDatabase <> Value then
2804	>	if (csLoading in ComponentState) or (FDatabase <> Value) then
2805		begin
2806		FDatabase := Value;
2807		if (not Assigned(FTransaction)) and (FDatabase <> nil) then
#	Line 2646 | Line 2833 | begin
2833		end;
2834		FMetaData.Clear;
2835		case ObjectType of
2836	<	eoDatabase : ExtractDDL(true, '', etData in ExtractTypes);
2836	>	eoDatabase : ExtractDDL(true, '', ExtractTypes);
2837		eoDomain :
2838		if etTable in ExtractTypes then
2839		ListDomains(ObjectName, etTable)
#	Line 2706 | Line 2893 | begin
2893		eoGenerator : ListGenerators(ObjectName,ExtractTypes);
2894		eoException : ListException(ObjectName);
2895		eoBLOBFilter : ListFilters(ObjectName);
2896	<	eoRole : ListRoles(ObjectName);
2896	>	eoRole : ListRoles(ObjectName,etGrant in ExtractTypes);
2897		eoTrigger :
2898		if etTable in ExtractTypes then
2899		begin
#	Line 2807 | Line 2994 | end;
2994		Grant various privileges to procedures.
2995		All privileges may have the with_grant option set. }
2996
2997	<	procedure TIBExtract.ShowGrants(MetaObject: String; Terminator: String);
2997	>	procedure TIBExtract.ShowGrants(MetaObject: String; Terminator: String;
2998	>	NoUserGrants: boolean);
2999		const
3000		GrantsBaseSelect =
3001	<	'Select Trim(RDB$USER) as RDB$USER,List("Privileges") as Privileges, '+
3002	<	'coalesce(RDB$GRANT_OPTION,0) as RDB$GRANT_OPTION,METAOBJECTNAME, '+
3003	<	'RDB$USER_TYPE, RDB$OBJECT_TYPE, '+
3004	<	'case  RDB$OBJECT_TYPE '+
3005	<	'When 0 then ''TABLE'' '+
3006	<	'When 5 then ''PROCEDURE'' '+
3007	<	'When 7 then ''EXCEPTION'' '+
3008	<	'When 11 then ''CHARACTER SET'' '+
3009	<	'When 14 then ''GENERATOR'' '+
3010	<	'ELSE NULL END as OBJECT_TYPE_NAME, '+
3011	<	'case RDB$USER_TYPE '+
3012	<	'When 5 then ''PROCEDURE'' '+
3013	<	'When 2 then ''TRIGGER'' '+
3014	<	'When 8 then ''USER'' '+
3015	<	'When 13 then ''ROLE'' '+
3016	<	'ELSE NULL END as USER_TYPE_NAME, '+
3017	<	'case '+
3018	<	'When coalesce(RDB$GRANT_OPTION,0) <> 0 and RDB$USER_TYPE = 13 then '' WITH ADMIN OPTION'' '+
3019	<	'When coalesce(RDB$GRANT_OPTION,0) <> 0 and RDB$USER_TYPE <> 13 then '' WITH GRANT OPTION'' '+
3020	<	'ELSE '''' End as GRANTOPTION '+
3021	<	'From (  '+
3022	<	'Select PR.RDB$USER,PR.RDB$RELATION_NAME as METAOBJECTNAME, LIST(DISTINCT Trim(Case PR.RDB$PRIVILEGE  '+
3023	<	'When ''X'' then ''EXECUTE''  '+
3024	<	'When ''S'' then ''SELECT''  '+
3025	<	'When ''U'' then ''UPDATE''   '+
3026	<	'When ''D'' then ''DELETE''  '+
3027	<	'When ''R'' then ''REFERENCES''  '+
3028	<	'When ''G'' then ''USAGE''  '+
3029	<	'When ''I'' then ''INSERT'' end )) as "Privileges",  '+
3030	<	'PR.RDB$GRANT_OPTION,  PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE,OW.RDB$OWNER_NAME  '+
3031	<	'FROM RDB$USER_PRIVILEGES PR  '+
3032	<	'JOIN ObjectOwners OW On OW.METAOBJECTNAME = PR.RDB$RELATION_NAME and OW.ObjectType = PR.RDB$OBJECT_TYPE '+
3033	<	'Where PR.RDB$PRIVILEGE <> ''M'' and (PR.RDB$PRIVILEGE <> ''U'' or PR.RDB$FIELD_NAME is null)  '+
3034	<	'Group By PR.RDB$USER,PR.RDB$RELATION_NAME,PR.RDB$GRANT_OPTION, PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE,OW.RDB$OWNER_NAME  '+
3035	<	'UNION  '+
3036	<	'Select PR.RDB$USER,PR.RDB$RELATION_NAME, ''Update('' || List(Trim(PR.RDB$FIELD_NAME)) || '')'',  '+
3037	<	'PR.RDB$GRANT_OPTION, PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE, OW.RDB$OWNER_NAME   '+
3038	<	'FROM RDB$USER_PRIVILEGES PR  '+
3039	<	'JOIN ObjectOwners OW On OW.METAOBJECTNAME = PR.RDB$RELATION_NAME and OW.ObjectType = PR.RDB$OBJECT_TYPE '+
3040	<	'Where PR.RDB$PRIVILEGE = ''U'' and PR.RDB$FIELD_NAME is not null   '+
3041	<	'Group By PR.RDB$USER,PR.RDB$RELATION_NAME,PR.RDB$GRANT_OPTION, PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE, OW.RDB$OWNER_NAME)  '+
2854	<	'Where METAOBJECTNAME = :METAOBJECTNAME and RDB$USER <> RDB$OWNER_NAME  '+
2855	<	'Group By RDB$USER,RDB$GRANT_OPTION,  RDB$USER_TYPE, RDB$OBJECT_TYPE,METAOBJECTNAME '+
2856	<	'ORDER BY RDB$USER, RDB$OBJECT_TYPE';
3001	>	'Select Trim(RDB$USER) as RDB$USER,List("Privileges") as Privileges,  '+
3002	>	'coalesce(RDB$GRANT_OPTION,0) as RDB$GRANT_OPTION,METAOBJECTNAME,  '+
3003	>	'RDB$USER_TYPE, RDB$OBJECT_TYPE,  '+
3004	>	'case T2.RDB$TYPE_NAME  '+
3005	>	'  When ''RELATION'' then ''TABLE''  '+
3006	>	'  When ''FIELD'' then ''DOMAIN''  '+
3007	>	'Else T2.RDB$TYPE_NAME End as OBJECT_TYPE_NAME,  '+
3008	>	'T1.RDB$TYPE_NAME as USER_TYPE_NAME,  '+
3009	>	'case  '+
3010	>	'When coalesce(RDB$GRANT_OPTION,0) <> 0 and RDB$USER_TYPE = 13 then '' WITH ADMIN OPTION''  '+
3011	>	'When coalesce(RDB$GRANT_OPTION,0) <> 0 and RDB$USER_TYPE <> 13 then '' WITH GRANT OPTION''  '+
3012	>	'ELSE '''' End as GRANTOPTION,  '+
3013	>	'case When RDB$OWNER_NAME = RDB$GRANTOR then '''' '+
3014	>	'else coalesce('' GRANTED BY "'' || Trim(RDB$GRANTOR) || ''"'','''') END as GRANTEDBY  '+
3015	>	'From (   '+
3016	>	'Select PR.RDB$USER,PR.RDB$RELATION_NAME as METAOBJECTNAME, LIST(DISTINCT Trim(Case PR.RDB$PRIVILEGE   '+
3017	>	'When ''X'' then ''EXECUTE''   '+
3018	>	'When ''S'' then ''SELECT''   '+
3019	>	'When ''U'' then ''UPDATE''    '+
3020	>	'When ''D'' then ''DELETE''   '+
3021	>	'When ''R'' then ''REFERENCES''   '+
3022	>	'When ''G'' then ''USAGE''   '+
3023	>	'When ''I'' then ''INSERT''  '+
3024	>	'end )) as "Privileges",   '+
3025	>	'PR.RDB$GRANT_OPTION,  PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE,OW.RDB$OWNER_NAME,PR.RDB$GRANTOR   '+
3026	>	'FROM RDB$USER_PRIVILEGES PR   '+
3027	>	'JOIN ObjectOwners OW On OW.METAOBJECTNAME = PR.RDB$RELATION_NAME and OW.ObjectType = PR.RDB$OBJECT_TYPE  '+
3028	>	'Where PR.RDB$PRIVILEGE <> ''M'' and (PR.RDB$PRIVILEGE <> ''U'' or PR.RDB$FIELD_NAME is null)   '+
3029	>	'Group By PR.RDB$USER,PR.RDB$RELATION_NAME,PR.RDB$GRANT_OPTION, PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE,OW.RDB$OWNER_NAME,PR.RDB$GRANTOR   '+
3030	>	'UNION   '+
3031	>	'Select PR.RDB$USER,PR.RDB$RELATION_NAME, ''Update('' || List(Trim(PR.RDB$FIELD_NAME)) || '')'',   '+
3032	>	'PR.RDB$GRANT_OPTION, PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE, OW.RDB$OWNER_NAME,PR.RDB$GRANTOR    '+
3033	>	'FROM RDB$USER_PRIVILEGES PR   '+
3034	>	'JOIN ObjectOwners OW On OW.METAOBJECTNAME = PR.RDB$RELATION_NAME and OW.ObjectType = PR.RDB$OBJECT_TYPE  '+
3035	>	'Where PR.RDB$PRIVILEGE = ''U'' and PR.RDB$FIELD_NAME is not null    '+
3036	>	'Group By PR.RDB$USER,PR.RDB$RELATION_NAME,PR.RDB$GRANT_OPTION, PR.RDB$USER_TYPE, PR.RDB$OBJECT_TYPE, OW.RDB$OWNER_NAME,PR.RDB$GRANTOR) A '+
3037	>	'JOIN RDB$TYPES T1 On T1.RDB$TYPE = RDB$USER_TYPE and T1.RDB$FIELD_NAME = ''RDB$OBJECT_TYPE''  '+
3038	>	'JOIN RDB$TYPES T2 On T2.RDB$TYPE = RDB$OBJECT_TYPE and T2.RDB$FIELD_NAME = ''RDB$OBJECT_TYPE''  '+
3039	>	'Where METAOBJECTNAME = :METAOBJECTNAME and RDB$USER <> RDB$OWNER_NAME   '+
3040	>	'Group By RDB$USER,RDB$GRANT_OPTION,  RDB$USER_TYPE, RDB$OBJECT_TYPE,METAOBJECTNAME,RDB$GRANTOR,T2.RDB$TYPE_NAME,T1.RDB$TYPE_NAME,RDB$OWNER_NAME  '+
3041	>	'ORDER BY RDB$USER, RDB$OBJECT_TYPE';
3042
3043		GrantsSQL12 =
3044	<	'with ObjectOwners As ( '+
3045	<	'Select RDB$RELATION_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 0 as ObjectType '+
3046	<	'From RDB$RELATIONS '+
3047	<	'UNION '+
3048	<	'Select RDB$PROCEDURE_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 5 as ObjectType '+
3049	<	'From RDB$PROCEDURES '+
3050	<	'UNION '+
3051	<	'Select RDB$EXCEPTION_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 7 as ObjectType '+
3052	<	'From RDB$EXCEPTIONS '+
3053	<	'UNION '+
3054	<	'Select RDB$GENERATOR_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 14 as ObjectType '+
3055	<	'From RDB$GENERATORS '+
3056	<	'UNION '+
3057	<	'Select RDB$CHARACTER_SET_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 11 as ObjectType '+
3058	<	'From RDB$CHARACTER_SETS '+
3044	>	'with ObjectOwners As (  '+
3045	>	'  Select RDB$RELATION_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 0 as ObjectType  '+
3046	>	'  From RDB$RELATIONS  '+
3047	>	'  UNION  '+
3048	>	'  Select RDB$PROCEDURE_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 5 as ObjectType  '+
3049	>	'  From RDB$PROCEDURES  '+
3050	>	'  UNION  '+
3051	>	'  Select RDB$EXCEPTION_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 7 as ObjectType  '+
3052	>	'  From RDB$EXCEPTIONS  '+
3053	>	'  UNION  '+
3054	>	'  Select RDB$FIELD_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 9 as ObjectType  '+
3055	>	'  From RDB$FIELDS Where RDB$FIELD_NAME not Similar to ''RDB$%|SEC$%|MON$%|SQL$%'' '+
3056	>	'  UNION  '+
3057	>	'  Select RDB$GENERATOR_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 14 as ObjectType  '+
3058	>	'  From RDB$GENERATORS  '+
3059	>	'  UNION  '+
3060	>	'  Select RDB$CHARACTER_SET_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 11 as ObjectType  '+
3061	>	'  From RDB$CHARACTER_SETS  '+
3062	>	'  UNION  '+
3063	>	'  Select RDB$COLLATION_NAME as METAOBJECTNAME, RDB$OWNER_NAME, 17 as ObjectType  '+
3064	>	'  From RDB$COLLATIONS  '+
3065		') '+ GrantsBaseSelect;
3066
3067		GrantsSQL =
#	Line 2907 | Line 3098 | begin
3098		qryOwnerPriv.ExecQuery;
3099		while not qryOwnerPriv.Eof do
3100		begin
3101	<	FMetaData.Add(Format('GRANT %s ON %s "%s" TO %s "%s" %s%s', [
3101	>	if not NoUserGrants or (qryOwnerPriv.FieldByName('RDB$USER_TYPE').AsInteger <> obj_user)
3102	>	or (qryOwnerPriv.FieldByName('RDB$USER').AsString = 'PUBLIC') then
3103	>	FMetaData.Add(Format('GRANT %s ON %s "%s" TO %s "%s" %s%s%s', [
3104		qryOwnerPriv.FieldByName('Privileges').AsString,
3105		qryOwnerPriv.FieldByName('OBJECT_TYPE_NAME').AsString,
3106		qryOwnerPriv.FieldByName('METAOBJECTNAME').AsString,
3107		qryOwnerPriv.FieldByName('USER_TYPE_NAME').AsString,
3108		qryOwnerPriv.FieldByName('RDB$USER').AsString,
3109		qryOwnerPriv.FieldByName('GRANTOPTION').AsString,
3110	+	qryOwnerPriv.FieldByName('GRANTEDBY').AsString,
3111		Terminator]));
3112		qryOwnerPriv.Next;
3113		end;
#	Line 3283 | Line 3477 | begin
3477		end;
3478		end;
3479
3480	<	procedure TIBExtract.ListRoles(ObjectName: String);
3480	>	procedure TIBExtract.ListRoles(ObjectName: String; IncludeGrants: boolean);
3481		const
3482		RolesSQL =
3483		'select * from RDB$ROLES WHERE RDB$SYSTEM_FLAG = 0 ' +
#	Line 3330 | Line 3524 | begin
3524		PrevOwner := OwnerName;
3525		end;
3526		FMetaData.Add('CREATE ROLE ' + RoleName + Term);
3527	+	if IncludeGrants then
3528	+	ShowGrantsTo(qryRoles.FieldByName('rdb$Role_Name').AsString,obj_sql_role,Term);
3529		qryRoles.Next;
3530		end;
3531		finally

Diff Legend

–	Removed lines
+	Added lines
<	Changed lines
>	Changed lines