I am creating a firebird database dynamically using SYSDBA. I want to create a user with some privileges and then open this database using the new user. Can anyone help, please?
var csb = new FbConnectionStringBuilder(@"DataSource=localhost;User=SYSDBA;Password=mw;Database=test.fdb;") { Pooling = false };
FbConnection.CreateDatabase(csb.ConnectionString, 16384, false, true);
FbConnection myConn = new FbConnection(csb.ConnectionString);
try
{
myConn.Open();
string sql = "CREATE USER user123 PASSWORD 'user123pass';
FbCommand cmd = new FbCommand(sql, myConn);
cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
error = ex.ToString();
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
When executing this code, it throws the following exception.
FirebirdSql.Data.FirebirdClient.FbException (0x80004005): add record error violation of PRIMARY or UNIQUE KEY constraint "INTEG_5" on table "PLG$SRP"
violation of PRIMARY or UNIQUE KEY
- that means you are trying to insert a row into a table that already exists.
in your specific case, the user (by SRP plugin) with such a name was already created, and you can not create two users (by one and the same plugin) with the same name.
Check users existing: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-pseudo-users.html
select * from sec$users
Then either drop user
before re-creating him, or set some new, not yet used user name
P.S. note that by default users are created in the server instance, so it is not like you create one and the same user for every database again and again. Once you created user - it exists ON THE SERVER for all the databases there. This is default configuration albeit it can be overrode using Firebird's databases.conf
file. Sadly, you can not change this configuration by your application's connection string, while you are creating your new databases.
P.P.S. personally I would not create the database command by command but rather would restore the ready-made database from pre-prepared backup file. YMMV.
User contributions licensed under CC BY-SA 3.0