public void AddUserTest()
{
FbSecurity securitySvc = new FbSecurity();
securitySvc.ConnectionString = this.BuildServicesConnectionString(false);
FbUserData user = new FbUserData();
user.UserName = "******";
user.UserPassword = "******";
securitySvc.AddUser(user);
}
public void DisplayUsers()
{
FbSecurity securitySvc = new FbSecurity();
securitySvc.ConnectionString = this.BuildServicesConnectionString(false);
FbUserData[] users = securitySvc.DisplayUsers();
Console.WriteLine("User List");
for (int i = 0; i < users.Length; i++)
{
Console.WriteLine("User {0} name {1}", i, users[i].UserName);
}
}
public void DisplayUser()
{
FbSecurity securitySvc = new FbSecurity();
securitySvc.ConnectionString = this.BuildServicesConnectionString(false);
FbUserData user = securitySvc.DisplayUser("SYSDBA");
Console.WriteLine("User name {0}", user.UserName);
}
public void DeleteUser()
{
FbSecurity securitySvc = new FbSecurity();
securitySvc.ConnectionString = this.BuildServicesConnectionString(false);
FbUserData user = new FbUserData();
user.UserName = "******";
securitySvc.DeleteUser(user);
}
public override void RecreateDataBase()
{
// ConnectionString Builder
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource = "localhost";
csb.Dialect = 3;
csb.Charset = "UTF8";
csb.Pooling = false;
csb.UserID = "SYSDBA"; // default user
csb.Password = "******"; // default password
string serverConnectionString = csb.ToString();
csb.Database = csb.Database = FQDBFile;
string databaseConnectionString = csb.ToString();
Console.WriteLine("-------------------------");
Console.WriteLine("Using Firebird Database ");
Console.WriteLine("-------------------------");
base.RecreateDataBase();
// Create simple user
FbSecurity security = new FbSecurity();
security.ConnectionString = serverConnectionString;
var userData = security.DisplayUser(FbUserName);
if (userData == null)
{
userData = new FbUserData();
userData.UserName = FbUserName;
userData.UserPassword = FbUserPass;
security.AddUser(userData);
}
// Try to shutdown & delete database
if (File.Exists(FQDBFile))
{
FbConfiguration configuration = new FbConfiguration();
configuration.ConnectionString = databaseConnectionString;
try
{
configuration.DatabaseShutdown(FbShutdownMode.Forced, 0);
Thread.Sleep(1000);
}
finally
{
File.Delete(FQDBFile);
}
}
// Create the new DB
FbConnection.CreateDatabase(databaseConnectionString, 4096, true, true);
if (!File.Exists(FQDBFile)) throw new Exception("Database failed to create");
// Create the Schema
string script = @"
CREATE TABLE Users(
UserId integer PRIMARY KEY NOT NULL,
Name varchar(200),
Age integer,
DateOfBirth timestamp,
Savings decimal(10,5),
Is_Male smallint,
UniqueId char(38),
TimeSpan time,
TestEnum varchar(10),
HouseId integer,
SupervisorId integer
);
CREATE TABLE ExtraUserInfos(
ExtraUserInfoId integer PRIMARY KEY NOT NULL,
UserId integer NOT NULL,
Email varchar(200),
Children integer
);
CREATE TABLE Houses(
HouseId integer PRIMARY KEY NOT NULL,
Address varchar(200)
);
CREATE TABLE CompositeObjects(
Key1ID integer PRIMARY KEY NOT NULL,
Key2ID integer NOT NULL,
Key3ID integer NOT NULL,
TextData varchar(512),
DateEntered timestamp NOT NULL,
DateUpdated timestamp
);
CREATE GENERATOR USERS_USERID_GEN;
CREATE GENERATOR EXTRAUSERINFOS_ID_GEN;
CREATE GENERATOR HOUSES_HOUSEID_GEN;
SET TERM ^ ;
CREATE TRIGGER BI_USERS_USERID FOR USERS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.USERID IS NULL) THEN
NEW.USERID = GEN_ID(USERS_USERID_GEN, 1);
END^
CREATE TRIGGER BI_EXTRAUSERINFOS_ID1 FOR EXTRAUSERINFOS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.EXTRAUSERINFOID IS NULL) THEN
NEW.EXTRAUSERINFOID = GEN_ID(EXTRAUSERINFOS_ID_GEN, 1);
END^
CREATE TRIGGER BI_HOUSES_HOUSEID FOR HOUSES
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.HOUSEID IS NULL) THEN
NEW.HOUSEID = GEN_ID(HOUSES_HOUSEID_GEN, 1);
END^
SET TERM ; ^
CREATE ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Users TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON ExtraUserInfos TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Houses TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON CompositeObjects TO ROLE %role%;
GRANT %role% TO %user%;
".Replace("%role%", FbRole).Replace("%user%", FbUserName);
/*
* Using new connection so that when a transaction is bound to Connection if it rolls back
* it doesn't blow away the tables
*/
using (var conn = new FbConnection(databaseConnectionString))
{
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(conn, fbScript);
fbBatch.Execute(true);
conn.Open();
Console.WriteLine("Tables (CreateDB): " + Environment.NewLine);
var dt = conn.GetSchema("Tables", new[] {null, null, null, "TABLE"});
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row[2]);
}
conn.Close();
}
}
public void fb_up(string pass)
{
//теперь нужно создать пользователя, под которым будет производится установка
FbConnectionStringBuilder fc = new FbConnectionStringBuilder();
fc.DataSource = "localhost";
fc.UserID = "SYSDBA";
fc.Password = this.t_sysdba.Text;
FbSecurity fb = new FbSecurity();
fb.ConnectionString = fc.ConnectionString;
FbUserData fu = new FbUserData();
fu.UserName = "******";
fu.UserPassword = pass;
try
{
fb.ModifyUser(fu);
}
catch { }
finally { }
}
public void fb_add(string username, string pass, bool is_full, string database)
{
//теперь нужно создать пользователя, под которым будет производится установка
FbConnectionStringBuilder fc = new FbConnectionStringBuilder();
fc.DataSource = "localhost";
fc.UserID = "SYSDBA";
if (this.is_new_sysdba.Checked)
{
fc.Password = this.t_sysdba_new.Text;
}
else
{
fc.Password = this.t_sysdba.Text;
}
if (database != "")
{
fc.Database = database;
}
FbSecurity fb = new FbSecurity();
fb.ConnectionString = fc.ConnectionString;
FbUserData fu = new FbUserData();
fu.UserName = username;
if (is_full)
{
fu.RoleName = "FULL_ACCESS";
}
fu.UserPassword = pass;
try
{
fb.AddUser(fu);
}
catch { }
finally { }
}
