Hi Jiri and all,
We are running FB v2.5, and I am using the ADO v5.5.0 DLL to connect from
my VS2013 C# project to 4 different databases. I have a little tool for
user management, and to narrow the focus, one of the operations is to
delete the user by:
- REVOKE ALL GRANTs across all DBs
- DROP the USER
The approach I have taken is to use
the System.Transactions.TransactionScope object, a
single FirebirdSql.Data.FirebirdClient.FbConnection object (which is
enlisted), and when switching DBs I call FbConnection::ChangeDatabase().
This seems to work fine the first time, but the very next time to call this
method to delete a different user, the error message appears (also
attached):
unsuccessful metadata update
ERASE RDB$USER_PRIVILEGES failed in REVOKE ALL ON ALL
lock conflict on no wait transaction
Acquire lock for relation (RDB$USER_PRIVILEGES) failed
This occurs in both Debug and Release builds. Opening and closing the EXE
between operations and the problem goes away. It seems as if the first
deletion of the user and its associated REVOKEs has left some hanging hook
into things that prevents further operations from being performed. I have
tried changing the IsolationLevel on the connection but that doesn't seem
to matter.
Is the approach taken below the recommended flow? Any tips/ideas would be
welcome.
Best regards,
David
using (System.Transactions.TransactionScope scope = new
System.Transactions.TransactionScope())
{
System.Transactions.IsolationLevel isolationLevel =
System.Transactions.Transaction.Current.IsolationLevel;
System.Diagnostics.Debug.WriteLine("IsolationLevel = "
+ isolationLevel.ToString() + " versus " + csb.IsolationLevel.ToString());
csb.IsolationLevel = IsolationLevel.Serializable;
System.Diagnostics.Debug.WriteLine("ConnectionTimeout =
" + csb.ConnectionTimeout.ToString());
csb.ConnectionTimeout = 4;
csb.Enlist = true;
using (FirebirdSql.Data.FirebirdClient.FbConnection
conn = new
FirebirdSql.Data.FirebirdClient.FbConnection(csb.ConnectionString))
{
conn.Open();
// REVOKE all rights for the given user. This must
be done for each database.
//
http://www.firebirdsql.org/refdocs/langrefupd25-security-grant-revoke.html#langrefupd25-security-revoke-all-on-all
string sUser = lstUsers.SelectedItem.ToString();
string sSQL = "REVOKE ALL ON ALL FROM " + sUser;
FbCommand cmd;
foreach (Globals.DbAliases alias in
Enum.GetValues(typeof(Globals.DbAliases)))
{
conn.ChangeDatabase(Globals.m_dictDbAliases[alias].Alias);
System.Diagnostics.Debug.WriteLine("Revoking
from DB " + conn.Database.ToString());
cmd = new FbCommand(sSQL, conn);
cmd.ExecuteNonQuery();
}
// Delete the user.
//
http://www.firebirdsql.org/refdocs/langrefupd25-security-sql-user-mgmt.html#langrefupd25-security-drop-user
sSQL = "DROP USER " + sUser;
cmd = new FbCommand(sSQL, conn);
cmd.ExecuteNonQuery();
} // using conn
//
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.complete.aspx
scope.Complete();
} // using scope
[image: Inline images 1]
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
