Bu unit program database informix işleminde kullanılır.
Kod: Tümünü seç
unit ads_Informix;
interface
Uses WinProcs, classes, dbtables, SysUtils, Dialogs;
Function CaseInsensitive(sgFieldName, sgFieldValue : String): String;
Function CreateRoleWithDbs(dbsDatabase: TDatabase; sgRoleName: String;boMsgOn: Boolean): Boolean;
Function DropRole(sgDatabaseName, sgRoleName: String;boMsgOn: Boolean): Boolean;
Function DropRoleWithDbs(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Function GrantRole(sgDatabaseName, sgRoleName, sgUserName: String;boCanGrant: Boolean;boMsgOn: Boolean): Boolean;
Function GrantRoleWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boCanGrant, boMsgOn: Boolean): Boolean;
Function MakeCaseInsensitive(sgFieldName, sgFieldValue : String): String;
Function RevokeRole(sgDatabaseName, sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Function RevokeRoleWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Function RoleCreate(sgDatabaseName, sgRoleName: String;boMsgOn: Boolean): Boolean;
Function RoleCreateWithDbs(dbsDatabase: TDatabase; sgRoleName: String;boMsgOn: Boolean): Boolean;
Function CreateRole(sgDatabaseName, sgRoleName: String; boMsgOn: Boolean): Boolean;
Function RoleDrop(sgDatabaseName, sgRoleName: String; boMsgOn: Boolean): Boolean;
Function RoleDropWithDbs(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Function RoleGrant(sgDatabaseName, sgRoleName, sgUserName: String;boCanGrant: Boolean;boMsgOn: Boolean): Boolean;
Function RoleGrantWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boCanGrant, boMsgOn: Boolean): Boolean;
Function RoleRevoke(sgDatabaseName, sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Function RoleRevokeWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Function RoleSet(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Function SetRole(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Function WhereCaseInsensitive(sgFieldName, sgFieldValue : String): String;
Function SetRoleToNone(dbsDatabase : TDatabase): Boolean;
Function UnSetRole(dbsDatabase : TDatabase): Boolean;
Function RoleGenerator(sgDatabaseName, sg2CharPrefix: String;inRoles: Integer;boMsgOn: Boolean): Boolean;
Function IsInSysUsersDetail(var qry: TQuery; sgDatabaseName, sgUserName, sgUserType, sgProcName: String; boMsgOn: Boolean): Boolean;
Function IsUser(var qry: TQuery; sgDatabaseName, sgUserName, sgProcName: String; boMsgOn: Boolean): Boolean;
Function IsRole(var qry: TQuery; sgDatabaseName, sgUserName, sgProcName: String; boMsgOn: Boolean): Boolean;
Function ValCheckUserName(sgName, sgProcName : String; boMsgOn : Boolean): Boolean;
Function ValCheckTablePrivilege(sgPrivilege: String; boMsgOn : Boolean): Boolean;
Function ValCheckTablePrivilegeCanColLevel(sgPrivilege: String; boMsgOn : Boolean): Boolean;
Function IsColumn(sgDatabaseName, sgTableName, sgTableOwner, sgColumnName: String; boMsgOn: Boolean): Boolean;
Function IsTable(sgDatabaseName, sgTableName, sgTableOwner: String; boMsgOn: Boolean): Boolean;
Function GrantTablePrivilege(sgDatabaseName, sgTableName, sgTableOwner, sgColumnName, sgPrivilege, sgGrantee, sgGrantor: String; boWithGrantOption, boMsgOn: Boolean): Boolean;
Function ServerName(sgDatabaseName: String): String;
Function SessionID(sgDatabaseName: String): String;
Function DatabasesOnServer(
sgServerName: String;
sgAliasName : String;
sgUserName : String;
sgPassword : String;
lstCombined : TStrings;
lstDatabases: TStrings;
lstDBOwners : TStrings;
boMsgOn: Boolean):Boolean; OverLoad;
Function DatabasesOnServer(
dbsDatabase : TDatabase;
lstCombined : TStrings;
lstDatabases: TStrings;
lstDBOwners : TStrings;
boMsgOn: Boolean):Boolean; OverLoad;
implementation
Function WhereCaseInsensitive(sgFieldName, sgFieldValue : String): String;
Var
sgQuotedValue : String;
Begin
Result := '';
If Trim(sgFieldName) = '' Then Exit;
If Trim(sgFieldValue) = '' Then Exit;
sgQuotedValue := '"'+sgFieldValue+'%"';
Result :=
sgFieldName +
' Like ' +
sgQuotedValue +
' And ' +
'Length(' +
sgFieldName +
') = ' +
IntToStr(Length(sgFieldValue));
End;
Function CreateRole(sgDatabaseName, sgRoleName: String; boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
boError : Boolean;
sgProcName : String;
Begin
sgProcName := 'CreateRole';
Result := False;
Try
If sgDatabaseName = '' Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': no DatabaseName was provided!');
Exit;
End;
If Not ValCheckUserName(sgRoleName, sgProcName, boMsgOn) Then Exit;
sgRoleName := LowerCase(sgRoleName);
qry := TQuery.Create(nil);
Try
//Test whether this Role Name already exists in the SysUsers Table
If IsRole(qry, sgDatabaseName, sgRoleName, sgProcName, False) Then Begin Result := True; Exit; End;
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Create Role '+sgRoleName);
boError := False;
Try
qry.ExecSql;
Except
boError := True;
End;
If boError Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': Creation of '+sgRoleName+' Role Failed!');
Exit;
End;
(*
What follows from here on is fail safe testing so I am going
to default the result to true meaning the process was successful.
*)
Result := True;
(*
Now test that it actually worked, i.e., the role was created.
The only reason for this added precaution is that frequently
Informix does not throw and error and the role was not actually
created. To do this we have to test that the new role is in
the SysUsers table and that the usertype is set to "G" for group.
*)
If IsRole(qry, sgDatabaseName, sgRoleName, sgProcName, False) Then Begin Result := True; Exit; End;
(*
If the code gets to here it means that the role was not
successfully created and a more dangerous approach needs
to be used to create the role. The SysUsers table will
be edited directly.
*)
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Insert Into SysUsers');
qry.Sql.Add('Values('+sgRoleName+',G,5,NULL)');
boError := False;
Try
qry.ExecSql;
Except
boError := True;
End;
If boError Then Exit;
Finally
qry.Free;
End;
Except
Result := False;
End;
End;
Function RoleCreateWithDbs(dbsDatabase: TDatabase; sgRoleName: String;boMsgOn: Boolean): Boolean;
Begin
Try
If dbsDatabase = nil Then
Begin
If boMsgOn Then ShowMessage('Function RoleCreateWithDbs: no database was provided!');
Result := False;
Exit;
End;
Result := CreateRole(dbsDatabase.Name, sgRoleName, boMsgOn);
Except
Result := False;
End;
End;
Function CreateRoleWithDbs(dbsDatabase: TDatabase; sgRoleName: String;boMsgOn: Boolean): Boolean;
Begin
Result := RoleCreateWithDbs(dbsDatabase, sgRoleName, boMsgOn);
End;
Function RoleCreate(sgDatabaseName, sgRoleName: String; boMsgOn: Boolean): Boolean;
Begin
Result := CreateRole(sgDatabaseName, sgRoleName, boMsgOn);
End;
Function MakeCaseInsensitive(sgFieldName, sgFieldValue : String): String;
Begin
Result := WhereCaseInsensitive(sgFieldName, sgFieldValue);
End;
Function CaseInsensitive(sgFieldName, sgFieldValue : String): String;
Begin
Result := WhereCaseInsensitive(sgFieldName, sgFieldValue);
End;
Function DropRole(sgDatabaseName, sgRoleName: String;boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
sgProcName : String;
Begin
sgProcName := 'DropRole';
Result := False;
Try
If sgDatabaseName = '' Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': no DatabaseName was provided!');
Exit;
End;
If Not ValCheckUserName(sgRoleName, sgProcName, boMsgOn) Then Exit;
sgRoleName := LowerCase(sgRoleName);
qry := TQuery.Create(nil);
Try
//Test whether this Role Name already exists in the SysUsers Table
If Not IsRole(qry, sgDatabaseName, sgRoleName, sgProcName, False) Then Begin Result := True; Exit; End;
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Drop Role '+sgRoleName);
Try
qry.ExecSql;
Except
End;
(*
Now test that it actually worked, i.e., the role was dropped.
The only reason for this added precaution is that frequently
Informix does not throw and error and the role was not actually
dropped. To do this we have to test that the role is not in
the SysUsers table.
*)
If Not IsRole(qry, sgDatabaseName, sgRoleName, sgProcName, False) Then Begin Result := True; Exit; End;
(*
If the code gets to here it means that the role was not
successfully dropped and a more dangerous approach needs
to be used to drop the role. The SysUsers table with
be edited directly.
*)
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Delete From SysUsers');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('UserName', sgRoleName));
qry.Sql.Add('And');
qry.Sql.Add('UserType = "G"');
Try
qry.ExecSql;
Result := True;
Except
End;
Finally
qry.Free;
End;
Except
Result := False;
End;
End;
Function DropRoleWithDbs(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Begin
Try
If dbsDatabase = nil Then
Begin
If boMsgOn Then ShowMessage('Function DropRoleWithDbs: no database was provided!');
Result := False;
Exit;
End;
Result := DropRole(dbsDatabase.Name, sgRoleName, boMsgOn);
Except
Result := False;
End;
End;
Function RoleDrop(sgDatabaseName, sgRoleName: String; boMsgOn: Boolean): Boolean;
Begin
Result := DropRole(sgDatabaseName, sgRoleName, boMsgOn);
End;
Function RoleDropWithDbs(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Begin
Result := DropRoleWithDbs(dbsDatabase, sgRoleName, boMsgOn);
End;
Function GrantRole(sgDatabaseName, sgRoleName, sgUserName: String;boCanGrant: Boolean;boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
boError : Boolean;
boFailedGrant1: Boolean;
sgProcName : String;
Begin
sgProcName := 'GrantRole';
Result := False;
Try
If sgDatabaseName = '' Then
Begin
If boMsgOn Then ShowMessage('Function GrantRole: no DatabaseName was provided!');
Exit;
End;
If Not ValCheckUserName(sgRoleName, sgProcName, boMsgOn) Then Exit;
If Not ValCheckUserName(sgUserName, sgProcName, boMsgOn) Then Exit;
sgRoleName := LowerCase(sgRoleName);
sgUserName := LowerCase(sgUserName);
qry := TQuery.Create(nil);
Try
//Test whether this Role Name exists in the SysUsers Table
If Not IsRole(qry, sgDatabaseName, sgRoleName, sgProcName, boMsgOn) Then Begin Result := False; Exit; End;
//Test whether this User Name exists in the SysUsers Table
If Not IsUser(qry, sgDatabaseName, sgUserName, sgProcName, boMsgOn) Then Begin Result := False; Exit; End;
//Test whether this Role has already been granted to the User Name in the
//SysRoleAuth Table
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('RoleName,');
qry.Sql.Add('Grantee,');
qry.Sql.Add('is_grantable');
qry.Sql.Add('from');
qry.Sql.Add('SysRoleAuth');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('RoleName', sgRoleName));
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('Grantee', sgUserName));
boError := False;
Try
qry.Active := True;
Except
boError := True;
End;
If boError Then
Begin
If boMsgOn Then ShowMessage('Function GrantRole: Failed testing User Name "'+sgUserName+'" in SysRoleAuth table!');
Exit;
End;
If qry.EOF and qry.BOF Then
Begin
(*
The query is empty so this role/User Name does not exist in
the SysRoleAuth table.
*)
End
Else
Begin
(*
The query is not empty so this User/Role Name does exist in
the SysRoleAuth table. Must check that the grant option is correct.
*)
boError := False;
If (qry.FieldByName('is_grantable').AsString = 'n') And boCanGrant Then boError := True;
If (qry.FieldByName('is_grantable').AsString = 'y') And (Not boCanGrant) Then boError := True;
(*
If boError is False then the grant already exists and nothing further
needs to be done, otherwise we need to continue with the process
of granting the role.
*)
If Not boError Then
Begin
Result := True;
Exit;
End;
End;
(*
Now we try to Grant the Role
*)
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Grant Role '+sgRoleName+' To '+sgUserName);
If boCanGrant Then qry.Sql.Add('WITH GRANT OPTION');
boFailedGrant1 := False;
Try
qry.ExecSql;
Except
boFailedGrant1 := True;
End;
Result := Not boFailedGrant1;
(*
Now test that it actually worked, i.e., the role was granted.
The only reason for this added precaution is that frequently
Informix does not throw and error and the role was not actually
granted. To do this we have to test that the new role is in
the SysRoleAuth table.
*)
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('RoleName,');
qry.Sql.Add('Grantee,');
qry.Sql.Add('is_grantable');
qry.Sql.Add('from');
qry.Sql.Add('SysRoleAuth');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('RoleName', sgRoleName));
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('Grantee', sgUserName));
boError := False;
Try
qry.Active := True;
Except
boError := True;
End;
If boError Then
Begin
//not going to show any messages
Exit;
End;
If Not (qry.EOF and qry.BOF) Then
Begin
(*
The query is not empty so this User/Role Name does exist in
the SysRoleAuth table.
*)
Exit;
End;
(*
If the code gets to here it means that the role was not
successfully granted and a more dangerous approach needs
to be used to create the role. The SysRoleAuth table with
be edited directly.
*)
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Insert Into SysRoleAuth');
If boCanGrant Then
Begin
qry.Sql.Add('Values("'+sgRoleName+'","'+sgUserName+'","y")');
End
Else
Begin
qry.Sql.Add('Values("'+sgRoleName+'","'+sgUserName+'","n")');
End;
boError := False;
Try
qry.ExecSql;
Except
boError := True;
End;
If Not Result Then
Begin
If Not boError Then Result := True;
End;
Finally
qry.Free;
End;
Except
Result := False;
End;
End;
Function GrantRoleWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boCanGrant, boMsgOn: Boolean): Boolean;
Begin
Try
If dbsDatabase = nil Then
Begin
If boMsgOn Then ShowMessage('Function GrantRoleWithDbs: no database was provided!');
Result := False;
Exit;
End;
Result := GrantRole(dbsDatabase.Name, sgRoleName, sgUserName, boCanGrant, boMsgOn);
Except
Result := False;
End;
End;
Function RoleGrant(sgDatabaseName, sgRoleName, sgUserName: String;boCanGrant: Boolean;boMsgOn: Boolean): Boolean;
Begin
Result := GrantRole(sgDatabaseName, sgRoleName, sgUserName, boCanGrant, boMsgOn);
End;
Function RoleGrantWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boCanGrant, boMsgOn: Boolean): Boolean;
Begin
Result := GrantRole(dbsDatabase.Name, sgRoleName, sgUserName, boCanGrant, boMsgOn);
End;
Function RevokeRole(sgDatabaseName, sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
boError : Boolean;
boFailedRevoke: Boolean;
sgProcName : String;
Begin
sgProcName := 'RevokeRole';
Result := False;
Try
If sgDatabaseName = '' Then
Begin
If boMsgOn Then ShowMessage('Function RevokeRole: no DatabaseName was provided!');
Exit;
End;
If Not ValCheckUserName(sgRoleName, sgProcName, boMsgOn) Then Exit;
If Not ValCheckUserName(sgUserName, sgProcName, boMsgOn) Then Exit;
sgRoleName := LowerCase(sgRoleName);
sgUserName := LowerCase(sgUserName);
qry := TQuery.Create(nil);
Try
//Test whether this Role Name exists in the SysUsers Table
If Not IsRole(qry, sgDatabaseName, sgRoleName, sgProcName, False) Then Begin Result := True; Exit; End;
//Test whether this User Name exists in the SysUsers Table
If Not IsUser(qry, sgDatabaseName, sgUserName, sgProcName, False) Then Begin Result := True; Exit; End;
//Test whether this Role has already been revoked from the User Name in the
//SysRoleAuth Table
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('RoleName,');
qry.Sql.Add('Grantee,');
qry.Sql.Add('is_grantable');
qry.Sql.Add('from');
qry.Sql.Add('SysRoleAuth');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('RoleName', sgRoleName));
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('Grantee', sgUserName));
boError := False;
Try
qry.Active := True;
Except
boError := True;
End;
If boError Then
Begin
If boMsgOn Then ShowMessage('Function RevokeRole: Failed testing User Name "'+sgUserName+'" in SysRoleAuth table!');
Exit;
End;
If qry.EOF and qry.BOF Then
Begin
(*
The query is empty so this role/User Name does not exist in
the SysRoleAuth table.
*)
Result := True;
Exit;
End
Else
Begin
(*
The query is not empty so this User/Role Name does exist in
the SysRoleAuth table.
*)
End;
(*
Now we try to Revoke the Role
*)
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Revoke Role '+sgRoleName+' From '+sgUserName);
boFailedRevoke := False;
Try
qry.ExecSql;
Except
boFailedRevoke := True;
End;
Result := Not boFailedRevoke;
(*
Now test that it actually worked, i.e., the role was revoked.
The only reason for this added precaution is that frequently
Informix does not throw and error and the role was not actually
granted. To do this we have to test that the new role is in
the SysRoleAuth table.
*)
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('RoleName,');
qry.Sql.Add('Grantee,');
qry.Sql.Add('is_grantable');
qry.Sql.Add('from');
qry.Sql.Add('SysRoleAuth');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('RoleName', sgRoleName));
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('Grantee', sgUserName));
boError := False;
Try
qry.Active := True;
Except
boError := True;
End;
If boError Then
Begin
//not going to show any messages
Exit;
End;
If qry.EOF and qry.BOF Then
Begin
(*
The query is empty so this User/Role Name does not exist in
the SysRoleAuth table.
*)
Result := True;
Exit;
End;
(*
If the code gets to here it means that the role was not
successfully revoked and a more dangerous approach needs
to be used to revoke the role. The SysRoleAuth table with
be edited directly.
*)
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Delete');
qry.Sql.Add('From');
qry.Sql.Add('SysRoleAuth');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('RoleName', sgRoleName));
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('Grantee', sgUserName));
boError := False;
Try
qry.ExecSql;
Except
boError := True;
End;
If Not Result Then
Begin
If Not boError Then Result := True;
End;
Finally
qry.Free;
End;
Except
Result := False;
End;
End;
Function RevokeRoleWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Begin
Try
If dbsDatabase = nil Then
Begin
If boMsgOn Then ShowMessage('Function RevokeRoleWithDbs: no database was provided!');
Result := False;
Exit;
End;
Result := RevokeRole(dbsDatabase.Name, sgRoleName, sgUserName, boMsgOn);
Except
Result := False;
End;
End;
Function RoleRevoke(sgDatabaseName, sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Begin
Result := RevokeRole(sgDatabaseName, sgRoleName, sgUserName, boMsgOn);
End;
Function RoleRevokeWithDbs(dbsDatabase: TDatabase;sgRoleName, sgUserName: String;boMsgOn: Boolean): Boolean;
Begin
Result := RevokeRoleWithDbs(dbsDatabase, sgRoleName, sgUserName, boMsgOn);
End;
Function SetRole(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
sgDatabaseName: String;
boFailed : Boolean;
sgProcName : String;
Begin
sgProcName := 'SetRole';
Result := False;
Try
If dbsDatabase = nil Then
Begin
If boMsgOn Then ShowMessage('Function SetRole: no Database was provided!');
Exit;
End;
sgDatabaseName := dbsDatabase.DatabaseName;
If sgDatabaseName = '' Then
Begin
If boMsgOn Then ShowMessage('Function SetRole: no DatabaseName was provided!');
Exit;
End;
If Not ValCheckUserName(sgRoleName, sgProcName, boMsgOn) Then Exit;
sgRoleName := LowerCase(sgRoleName);
qry := TQuery.Create(nil);
Try
If (sgRoleName <> 'null') And (sgRoleName <> 'none') Then
Begin
//Test whether this Role Name exists in the SysUsers Table
If Not IsRole(qry, sgDatabaseName, sgRoleName, sgProcName, boMsgOn) Then Begin Result := False; Exit; End;
End;
(*
Now we try to Set the Role
*)
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Set Role '+sgRoleName);
boFailed := False;
Try
qry.ExecSql;
Except
boFailed := True;
End;
Result := Not boFailed;
Except
qry.Free;
End;
Except
Result := False;
End;
End;
Function RoleSet(dbsDatabase: TDatabase;sgRoleName: String;boMsgOn: Boolean): Boolean;
Begin
Result := SetRole(dbsDatabase, sgRoleName, boMsgOn);
End;
Function SetRoleToNone(dbsDatabase : TDatabase): Boolean;
Begin
Result := SetRole(dbsDatabase, 'none', False);
End;
Function UnSetRole(dbsDatabase : TDatabase): Boolean;
Begin
Result := SetRoleToNone(dbsDatabase);
End;
Function RoleGenerator(sgDatabaseName, sg2CharPrefix: String;inRoles: Integer;boMsgOn: Boolean): Boolean;
Var
inMax : Integer;
inSuccesses : Integer;
inGenChars : Integer;
sgTempRole : String;
inCounter : Integer;
inCounter2 : Integer;
sgNewChar : String;
Begin
Result := False;
inMax := inRoles * 2;
sg2CharPrefix := Trim(LowerCase(sg2CharPrefix));
If Length(sg2CharPrefix) > 2 Then sg2CharPrefix := Copy(sg2CharPrefix,1,2);
inGenChars := 8 - Length(sg2CharPrefix);
inSuccesses := 0;
For inCounter := 1 To inMax Do
Begin
sgTempRole := sg2CharPrefix;
For inCounter2 := 1 To inGenChars Do
Begin
Randomize;
sleep(231);
sgNewChar := String(Chr(97+Random(25)));
sgTempRole:= sgTempRole + sgNewChar;
End;
If CreateRole(sgDatabaseName, sgTempRole, boMsgOn) Then
Begin
inSuccesses := inSuccesses + 1;
End;
If inSuccesses >= inRoles Then
Begin
Result := True;
Break;
End;
End;
End;
Function IsInSysUsersDetail(var qry: TQuery; sgDatabaseName, sgUserName, sgUserType, sgProcName: String; boMsgOn: Boolean): Boolean;
Var
boError : Boolean;
Begin
//Test whether this User Name exists in the SysUsers Table
Result := True;
If LowerCase(Trim(sgUserName)) = 'public' Then
Begin
If UpperCase(sgUserType) <> 'G' Then Exit;
End;
Result := False;;
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('UserName,');
qry.Sql.Add('UserType');
qry.Sql.Add('from');
qry.Sql.Add('SysUsers');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('UserName', sgUserName));
boError := False;
Try
qry.Active := True;
Except
boError := True;
End;
If boError Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': Failed testing existance of '+sgUserName+' in SysUsers Table!');
Exit;
End;
If qry.EOF and qry.BOF Then
Begin
(*
The query is empty so this Name does not exist
in the SysUsers table.
*)
If boMsgOn Then ShowMessage('Function '+sgProcName+': '+sgUserName+' does not exist in SysUsers Table!');
Exit;
End
Else
Begin
(*
The query is not empty so this User Name does exist
in the SysUsers table.
*)
Result := (UpperCase(qry.FieldByName('UserType').AsString) = UpperCase(Trim(sgUserType)));
End;
End;
Function IsUser(var qry: TQuery; sgDatabaseName, sgUserName, sgProcName: String; boMsgOn: Boolean): Boolean;
Begin
If LowerCase(Trim(sgUserName)) = 'informix' Then Begin Result := True; Exit; End;
If LowerCase(Trim(sgUserName)) = 'public' Then Begin Result := True; Exit; End;
Result := IsInSysUsersDetail(qry, sgDatabaseName, sgUserName, 'C', sgProcName, False);
If Result Then Exit;
Result := IsInSysUsersDetail(qry, sgDatabaseName, sgUserName, 'D', sgProcName, False);
If Result Then Exit;
Result := IsInSysUsersDetail(qry, sgDatabaseName, sgUserName, 'R', sgProcName, False);
If (Not Result) And boMsgOn Then ShowMessage('Function '+sgProcName+': '+sgUserName+' does not exist in SysUsers Table!');
End;
Function IsRole(var qry: TQuery; sgDatabaseName, sgUserName, sgProcName: String; boMsgOn: Boolean): Boolean;
Begin
Result := IsInSysUsersDetail(qry, sgDatabaseName, sgUserName, 'G', sgProcName, boMsgOn);
End;
Function ValCheckUserName(sgName, sgProcName : String; boMsgOn : Boolean): Boolean;
Var
boValidChar : Boolean;
pc : PChar;
ch : Char;
inCounter : Integer;
inFirstLetter : Integer;
sgFirstLetter : String;
Begin
Result := False;
If Length(sgName) > 8 Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': Name is too long, limit 8 characters!');
Exit;
End;
If Trim(sgName) = '' Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': username cannot be blank!');
Exit;
End;
If Pos(' ',Trim(sgName)) > 0 Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': username cannot contain spaces!');
Exit;
End;
sgFirstLetter := LowerCase(Copy(sgName,1,1));
pc := PChar(sgFirstLetter);
ch := Char(pc[0]);
inFirstLetter := Ord(ch);
If (inFirstLetter < 97) Or (inFirstLetter > 122) Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': username name must begin with a letter!');
Exit;
End;
For inCounter := 2 To Length(sgName)-1 Do
Begin
boValidChar := False;
sgFirstLetter := LowerCase(Copy(sgName,inCounter,1));
pc := PChar(sgFirstLetter);
ch := Char(pc[0]);
inFirstLetter := Ord(ch);
If (inFirstLetter >= 97) And (inFirstLetter <= 122) Then boValidChar := True;
If (inFirstLetter >= 48) And (inFirstLetter <= 57) Then boValidChar := True;
If (inFirstLetter = 95) Then boValidChar := True;
If Not boValidChar Then
Begin
If boMsgOn Then ShowMessage('Function '+sgProcName+': Invalid character "'+sgFirstLetter+'" in username!');
Exit;
End;
End;
Result := True;
End;
Function ValCheckTablePrivilege(sgPrivilege: String; boMsgOn : Boolean): Boolean;
Begin
Result := True;
sgPrivilege := LowerCase(Trim(sgPrivilege));
If sgPrivilege = 'all' Then Exit;
If sgPrivilege = 'insert' Then Exit;
If sgPrivilege = 'delete' Then Exit;
If sgPrivilege = 'select' Then Exit;
If sgPrivilege = 'update' Then Exit;
If sgPrivilege = 'references' Then Exit;
If sgPrivilege = 'index' Then Exit;
If sgPrivilege = 'alter' Then Exit;
If boMsgOn Then ShowMessage('ValCheckTablePrivilege: '+sgPrivilege+' is not a valid table level privilege');
Result := False;
End;
Function ValCheckTablePrivilegeCanColLevel(sgPrivilege: String; boMsgOn : Boolean): Boolean;
Begin
Result := True;
sgPrivilege := LowerCase(Trim(sgPrivilege));
If sgPrivilege = 'select' Then Exit;
If sgPrivilege = 'update' Then Exit;
If sgPrivilege = 'references' Then Exit;
If boMsgOn Then ShowMessage('ValCheckTablePrivilege: '+sgPrivilege+' is not a valid table level privilege');
Result := False;
End;
Function IsTable(sgDatabaseName, sgTableName, sgTableOwner: String; boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
Begin
Result := False;
If Trim(sgDatabaseName) = '' Then
Begin
If boMsgOn Then ShowMessage('IsColumn: DatabaseName cannot be blank');
Exit;
End;
If Trim(sgTableName) = '' Then
Begin
If boMsgOn Then ShowMessage('IsColumn: TableName cannot be blank');
Exit;
End;
If Trim(sgTableOwner) = '' Then sgTableOwner := 'informix';
qry := TQuery.Create(nil);
Try
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('TabName,');
qry.Sql.Add('Owner');
qry.Sql.Add('from');
qry.Sql.Add('SysTables');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('TabName', sgTableName));
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('Owner', sgTableOwner));
Try
qry.Active := True;
Except
End;
If qry.EOF and qry.BOF Then
Begin
(*
The query is empty so this Table/Owner Name does not exist in
the SysTables table.
*)
If boMsgOn Then ShowMessage('IsTable: the "'+sgTableOwner+'".'+sgTableName+' table does not exist');
Exit;
End
Else
Begin
(*
The query is not empty so this Table/Owner Name does exist in
the SysTables table.
*)
Result := True;
End;
Finally
qry.Free;
End;
End;
Function IsColumn(sgDatabaseName, sgTableName, sgTableOwner, sgColumnName: String; boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
Begin
Result := False;
If Trim(sgDatabaseName) = '' Then
Begin
If boMsgOn Then ShowMessage('IsColumn: DatabaseName cannot be blank');
Exit;
End;
If Trim(sgTableName) = '' Then
Begin
If boMsgOn Then ShowMessage('IsColumn: TableName cannot be blank');
Exit;
End;
If Trim(sgColumnName) = '' Then
Begin
If boMsgOn Then ShowMessage('IsColumn: ColumnName cannot be blank');
Exit;
End;
If Trim(sgTableOwner) = '' Then sgTableOwner := 'informix';
If Not IsTable(sgDatabaseName, sgTableName, sgTableOwner, boMsgOn) Then Exit;
qry := TQuery.Create(nil);
Try
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('b.ColName');
qry.Sql.Add('from');
qry.Sql.Add('SysTables a,');
qry.Sql.Add('SysColumns b');
qry.Sql.Add('Where');
qry.Sql.Add(WhereCaseInsensitive('a.TabName', sgTableName));
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('a.Owner', sgTableOwner));
qry.Sql.Add('And');
qry.Sql.Add('a.TabID = b.TabID');
qry.Sql.Add('And');
qry.Sql.Add(WhereCaseInsensitive('b.ColName', sgColumnName));
Try
qry.Active := True;
Except
End;
If qry.EOF and qry.BOF Then
Begin
(*
The query is empty so this Table/Owner/Column Name does not exist in
the SysTables and SysColumns tables.
*)
If boMsgOn Then ShowMessage('IsColumn: the "'+sgTableOwner+'".'+sgTableName+'.'+sgColumnName+' column does not exist');
Exit;
End
Else
Begin
(*
The query is not empty so this Table/Owner Name does exist in
the SysTables table.
*)
Result := True;
End;
Finally
qry.Free;
End;
End;
Function GrantTablePrivilege(sgDatabaseName, sgTableName, sgTableOwner, sgColumnName, sgPrivilege, sgGrantee, sgGrantor: String; boWithGrantOption, boMsgOn: Boolean): Boolean;
Var
qry : TQuery;
sgProcName : String;
sgSQL : String;
inCounter : Integer;
Begin
sgProcName := 'GrantTablePrivilege';
sgDatabaseName := LowerCase(Trim(sgDatabaseName));
sgTableName := LowerCase(Trim(sgTableName));
sgTableOwner := LowerCase(Trim(sgTableOwner));
sgColumnName := LowerCase(Trim(sgColumnName));
sgPrivilege := LowerCase(Trim(sgPrivilege));
sgGrantee := LowerCase(Trim(sgGrantee));
sgGrantor := LowerCase(Trim(sgGrantor));
Result := False;
If sgTableOwner = '' Then sgTableOwner := 'informix';
If sgDatabaseName = '' Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': DatabaseName cannot be blank');
Exit;
End;
If sgTableName = '' Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': TableName cannot be blank');
Exit;
End;
If Not IsTable(sgDatabaseName, sgTableName, sgTableOwner, boMsgOn) Then Exit;
If sgColumnName <> '' Then
Begin
If Not IsColumn(sgDatabaseName, sgTableName, sgTableOwner, sgColumnName, boMsgOn) Then Exit;
If Not ValCheckTablePrivilegeCanColLevel(sgPrivilege, boMsgOn) Then Exit;
End;
If Not ValCheckTablePrivilege(sgPrivilege, boMsgOn) Then Exit;
qry := TQuery.Create(nil);
Try
If Not (IsUser(qry,sgDatabaseName,sgTableOwner,sgProcName,False) Or
IsRole(qry,sgDatabaseName,sgTableOwner,sgProcName,False))Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': '+sgTableOwner+' user does not exist');
Exit;
End;
If Not (IsUser(qry,sgDatabaseName,sgGrantee,sgProcName,False) Or
IsRole(qry,sgDatabaseName,sgGrantee,sgProcName,False))Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': '+sgGrantee+' user does not exist');
Exit;
End;
If sgGrantor <> '' Then
Begin
If Not (IsUser(qry,sgDatabaseName,sgGrantor,sgProcName,False) Or
IsRole(qry,sgDatabaseName,sgGrantor,sgProcName,False))Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': '+sgGrantor+' user does not exist');
Exit;
End;
End;
qry.Active := False;
qry.RequestLive := True;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Grant');
If sgColumnName <> '' Then
Begin
qry.Sql.Add(sgPrivilege+'('+sgColumnName+')');
End
Else
Begin
qry.Sql.Add(sgPrivilege);
End;
qry.Sql.Add('To');
qry.Sql.Add(sgGrantee);
If boWithGrantOption Then
Begin
qry.Sql.Add('WITH GRANT OPTION');
End;
If sgGrantor <> '' Then
Begin
qry.Sql.Add('AS');
qry.Sql.Add(sgGrantor);
End;
sgSql := '';
For inCounter := 0 To qry.Sql.Count - 1 Do
Begin
sgSql := sgSql + qry.Sql[inCounter]+' ';
End;
Try
qry.ExecSql;
Result := True;
Except
If boMsgOn Then ShowMessage(sgProcName+': Failed!: '+sgSql);
Exit;
End;
//ZZZZZ Could try to verify success here
Finally
qry.Free;
End;
End;
Function ServerName(sgDatabaseName: String): String;
Var
qry : TQuery;
Begin
Result := '';
qry := TQuery.Create(nil);
Try
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select DBServerName ServerName From SysTables Where tabname = "systables"');
Try
qry.Active := True;
Except
Exit;
End;
Result := qry.FieldByName('ServerName').AsString;
Finally
qry.Free;
End;
End;
Function SessionID(sgDatabaseName: String): String;
Var
qry : TQuery;
Begin
Result := '';
qry := TQuery.Create(nil);
Try
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName:= sgDatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select DBINFO("SessionID") SessionID From SysTables Where tabname = "systables"');
Try
qry.Active := True;
Except
Exit;
End;
Result := qry.FieldByName('SessionID').AsString;
Finally
qry.Free;
End;
End;
Function DatabasesOnServer(
sgServerName: String;
sgAliasName : String;
sgUserName : String;
sgPassword : String;
lstCombined : TStrings;
lstDatabases: TStrings;
lstDBOwners : TStrings;
boMsgOn: Boolean):Boolean;
Var
dbsDatabase: TDataBase;
qry : TQuery;
boError : Boolean;
sgProcName : String;
Begin
dbsDatabase := TDataBase.Create(nil);
Try
dbsDatabase.Connected := False;
dbsDatabase.Params.Clear;
dbsDatabase.Params.Add('SERVER NAME='+sgServerName);
dbsDatabase.Params.Add('DATABASE NAME=sysmaster');
dbsDatabase.Params.Add('USER NAME='+sgUserName);
dbsDatabase.Params.Add('PASSWORD='+sgPassword);
If Trim(sgPassword) <> '' Then dbsDatabase.LoginPrompt := False;
dbsDatabase.DatabaseName := 'Get_Databases';
dbsDatabase.AliasName := sgAliasName;
Result := DatabasesOnServer(dbsDatabase,lstCombined,lstDatabases,lstDBOwners,boMsgOn);
Finally
dbsDatabase.Free;
End;
(*
Result := False;
If (lstCombined = nil) And
(lstDatabases = nil) And
(lstDBOwners = nil) Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': at least one list has to be provided');
Exit;
End;
If Trim(sgServerName) = '' Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': the server name cannot be blank');
Exit;
End;
dbs := TDataBase.Create(nil);
qry := TQuery.Create(nil);
Try
If lstCombined <> nil Then lstCombined.Clear;
If lstDatabases <> nil Then lstDatabases.Clear;
If lstDBOwners <> nil Then lstDBOwners.Clear;
dbs.Connected := False;
dbs.Params.Clear;
dbs.Params.Add('SERVER NAME='+sgServerName);
dbs.Params.Add('DATABASE NAME=sysmaster');
dbs.Params.Add('USER NAME='+sgUserName);
dbs.Params.Add('PASSWORD='+sgPassword);
If Trim(sgPassword) <> '' Then dbs.LoginPrompt := False;
dbs.DatabaseName := 'Get_Databases';
dbs.AliasName := sgAliasName;
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName := dbs.DatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('Name,');
qry.Sql.Add('Owner');
qry.Sql.Add('From');
qry.Sql.Add('sysdatabases');
qry.Sql.Add('Order By');
qry.Sql.Add('Owner,');
qry.Sql.Add('Name');
boError := False;
Try
dbs.Connected := True;
qry.Active := True;
Except
boError := True;
End;
If boError Then
Begin
dbs.LoginPrompt := True;
dbs.Params.Values['USER NAME'] := '';
dbs.Params.Values['password'] := '';
Try
dbs.Connected := True;
Except
If boMsgOn Then ShowMessage(sgProcName+': Could not extract databases on '+sgServerName+' server');
Exit;
End;
Try
qry.Active := True;
Except
If boMsgOn Then ShowMessage(sgProcName+': Could not extract databases on '+sgServerName+' server');
Exit;
End;
End;
//If the result is empty exit
If qry.BOF and qry.Eof Then Exit;
qry.First;
While Not qry.EOF Do
Begin
If lstCombined <> nil Then lstCombined.Add(qry.FieldByName('Owner').AsString+'.'+qry.FieldByName('Name').AsString);
If lstDatabases <> nil Then lstDatabases.Add(qry.FieldByName('Name').AsString);
If lstDBOwners <> nil Then lstDBOwners.Add(qry.FieldByName('Owner').AsString);
qry.Next;
End;
Result := True;
Finally
qry.Active := False;
dbs.Connected := False;
dbs.Free;
qry.Free;
End;
*)
End;
Function DatabasesOnServer(
dbsDatabase : TDatabase;
lstCombined : TStrings;
lstDatabases: TStrings;
lstDBOwners : TStrings;
boMsgOn: Boolean):Boolean; OverLoad;
Var
qry : TQuery;
boError : Boolean;
sgProcName : String;
Begin
sgProcName := 'DatabasesOnServer';
Result := False;
If (lstCombined = nil) And
(lstDatabases = nil) And
(lstDBOwners = nil) Then
Begin
If boMsgOn Then ShowMessage(sgProcName+': at least one list has to be provided');
Exit;
End;
qry := TQuery.Create(nil);
Try
If lstCombined <> nil Then lstCombined.Clear;
If lstDatabases <> nil Then lstDatabases.Clear;
If lstDBOwners <> nil Then lstDBOwners.Clear;
qry.Active := False;
qry.RequestLive := False;
qry.DatabaseName := dbsDatabase.DatabaseName;
qry.Sql.Clear;
qry.Sql.Add('Select');
qry.Sql.Add('Name,');
qry.Sql.Add('Owner');
qry.Sql.Add('From');
qry.Sql.Add('sysdatabases');
qry.Sql.Add('Order By');
qry.Sql.Add('Owner,');
qry.Sql.Add('Name');
boError := False;
Try
qry.Active := True;
Except
boError := True;
End;
If boError Then
Begin
dbsDatabase.LoginPrompt := True;
dbsDatabase.Params.Values['USER NAME'] := '';
dbsDatabase.Params.Values['password'] := '';
Try
dbsDatabase.Connected := True;
Except
If boMsgOn Then ShowMessage(sgProcName+': Could not extract databases on the server');
Exit;
End;
Try
qry.Active := True;
Except
If boMsgOn Then ShowMessage(sgProcName+': Could not extract databases on the server');
Exit;
End;
End;
//If the result is empty exit
If qry.BOF and qry.Eof Then Exit;
qry.First;
While Not qry.EOF Do
Begin
If lstCombined <> nil Then lstCombined.Add(qry.FieldByName('Owner').AsString+'.'+qry.FieldByName('Name').AsString);
If lstDatabases <> nil Then lstDatabases.Add(qry.FieldByName('Name').AsString);
If lstDBOwners <> nil Then lstDBOwners.Add(qry.FieldByName('Owner').AsString);
qry.Next;
End;
Result := True;
Finally
qry.Active := False;
dbsDatabase.Connected := False;
qry.Free;
End;
End;
end.