[firebird-support] Can non-sysdba really alter users?
Hello, I'd be very grateful if someone could repeat the scenario described below and confirm I'm not daydreaming (should take about 1 minute). I've tested it on FB 2.5.0.26074 CS (Linux 32 and 64 bit). According to http://www.firebirdsql.org/refdocs/langrefupd25-security-sql-user-mgmt.html any user with RDB$ADMIN role in the security database and at least one other database should be able to create/alter/drop other users. If so, in my opinion the following scenario should complete without errors (creating a new database is not relevant, but I included it to make sure we start from a clean setup). 1. Run isql as SYSDBA and execute: create database 'test.fdb'; 2. Close isql, run it again and connect to test.fdb as sysdba (e.g. isql -user sysdba -password topsecret test.fdb) and execute: create user U1 password '1'; commit; alter user U1 grant admin role; commit; grant RDB$ADMIN to U1; commit; 3. Close isql. At this point, we have a user U1, who satisfies the requirements from the manual mentioned above. So, run isql again, connecting as the new user: isql -user U1 -password 1 -role 'RDB$ADMIN' test.fdb and execute: create user U2 password '1'; commit; alter user U2 password '2'; commit; The last alter user statement fails with message Statement failed, SQLSTATE = HY000 record not found for user: U2 However, the create user works fine (gsec shows, that U2 had been created). Any subsequent attempts to change U1 fail, though. Is there anything I'm missing? Should I somehow tell Firebird that, when connecting as U1, I'd like to assume admin role not only in test.fdb, but also in the security database? Any help appreciated. regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
Hello Tomasz, I'd be very grateful if someone could repeat the scenario described below and confirm I'm not daydreaming (should take about 1 minute). I've tested it on FB 2.5.0.26074 CS (Linux 32 and 64 bit). According to http://www.firebirdsql.org/refdocs/langrefupd25-security-sql-user-mgmt.html any user with RDB$ADMIN role in the security database and at least one other database should be able to create/alter/drop other users. If so, in my opinion the following scenario should complete without errors (creating a new database is not relevant, but I included it to make sure we start from a clean setup). 1. Run isql as SYSDBA and execute: create database 'test.fdb'; 2. Close isql, run it again and connect to test.fdb as sysdba (e.g. isql -user sysdba -password topsecret test.fdb) and execute: create user U1 password '1'; commit; alter user U1 grant admin role; commit; grant RDB$ADMIN to U1; commit; 3. Close isql. At this point, we have a user U1, who satisfies the requirements from the manual mentioned above. So, run isql again, connecting as the new user: isql -user U1 -password 1 -role 'RDB$ADMIN' test.fdb and execute: create user U2 password '1'; commit; alter user U2 password '2'; commit; The last alter user statement fails with message Statement failed, SQLSTATE = HY000 record not found for user: U2 However, the create user works fine (gsec shows, that U2 had been created). Any subsequent attempts to change U1 fail, though. Is there anything I'm missing? Should I somehow tell Firebird that, when connecting as U1, I'd like to assume admin role not only in test.fdb, but also in the security database? Any help appreciated. The following works fine for me with Firebird 2.5.2 RC1 64-bit on Windows 7 Prof. C:\Firebird\Firebird_252_3051\binisql Use CONNECT or CREATE DATABASE to specify a database SQL connect localhost/3051:fbsmptest_1.fdb user sysdba password masterkey; Database: localhost/3051:fbsmptest_1.fdb, User: sysdba SQL create user utest password 'utest' grant admin role; SQL commit; SQL grant rdb$admin to utest; SQL commit; SQL connect localhost/3051:fbsmptest_1.fdb user utest password utest role RDB$ADMIN; Database: localhost/3051:fbsmptest_1.fdb, User: utest, Role: RDB$ADMIN SQL create user utest2 password 'utest2'; SQL commit; SQL alter user utest2 password 'utest3'; SQL commit; SQL connect localhost/3051:fbsmptest_1.fdb user utest2 password utest3; Database: localhost/3051:fbsmptest_1.fdb, User: utest2 SQL Perhaps it might be related to CORE-3398, but I'm not sure. Any chance to give 2.5.1 or 2.5.2 RC1 a try? -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-17 13:07, Thomas Steinmaurer wrote: Perhaps it might be related to CORE-3398, but I'm not sure. Any chance to give 2.5.1 or 2.5.2 RC1 a try? Thomas, Thanks a lot for the suggestion. Looks like there's something wrong with my security2.fdb (it was upgraded from previous versions of FB). In a fresh 2.5.1 installation everything worked fine. However, after I had restored my old security DB on 2.5.1, the error started to appear again. I've backed up security2.fdb with my 2.5.0 gbak, then installed FB 2.5.1 and restored the security to a different file, and finally overwritten the 2.5.1 security2.fdb with the resored file (while making sure FB is off). Is there something more I can do to have my security db in order? Starting with a clean security DB is not an option (about 50 servers in different companies, dozens of users on each server). regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
[firebird-support] Periodic database slowdown - troubleshooting steps?
I have an FB 2.1.5 Classic server running on a Windows 2003 server, with a single hard drive for the operating system, and a 3 disk raid 5 array for the database. We have one database on this machine, which is a dialect 1 database that was started on IB6.0 many years ago, currently at 90GB. We have sweep disabled, and each night run gbak, gfix sweep, as well as reindex all tables via a script. The database has very little OLTP, and is mostly used for reporting and serving web pages to internal business users. We do alot of ETL starting very early each morning, and create a mix of scheduled reports as well as allowing users to specify parameters to create pre-designed reports in an ad-hoc manner. Once or twice per month, the system slows down tremendously. One ETL process typically runs at a pace of about 1000 records per 10 seconds. During these slow periods, the same ETL will run 1000 transactions per 60-80 seconds. When processing a file with 1mil+ records, this slow down costs us hours. I have not been able to determine the reason for these slow periods. They do not coincide with higher cpu or disk usage most of the time I'm seeing very little usage of anything disk/cpu/network/memory. I do see more connections to the database during these periods typically we have 10 to 15 connections, and the number may double during the problem times. This is due to the fact that the reports that users are requesting are taking longer to run, and our pooled-connection application server or web server creates more connections to satisfy new user requests. I do see more queries running by the time I am notified of the problem. Again though, this is a coincidence of the slowdown and not the cause queries are taking longer to run, and therefore we have more chance of overlapping user requests than usual. Most of the time we need to reboot once or even twice to fix the problem. That is not a viable long-term solution though, and I'm looking for more ideas to determine what may be happening. Any ideas would be most helpful. I have included gstat h output of the database as it is suffering from the issue below: Database header page information: Flags 0 Checksum12345 Generation 43136192 Page size 8192 ODS version 11.1 Oldest transaction 40789582 Oldest active 41467442 Oldest snapshot 41467442 Next transaction42431040 Bumped transaction 1 Sequence number 0 Next attachment ID 705070 Implementation ID 16 Shadow count0 Page buffers2048 Next header page0 Database dialect1 Creation date May 2, 2009 22:22:39 Attributes force write, no reserve Variable header data: Sweep interval: 0 *END* Thank you for your assistance, Bob M.. [Non-text portions of this message have been removed]
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-17 13:07, Thomas Steinmaurer wrote: Perhaps it might be related to CORE-3398, but I'm not sure. Any chance to give 2.5.1 or 2.5.2 RC1 a try? Thomas, Thanks a lot for the suggestion. Looks like there's something wrong with my security2.fdb (it was upgraded from previous versions of FB). In a fresh 2.5.1 installation everything worked fine. However, after I had restored my old security DB on 2.5.1, the error started to appear again. I've backed up security2.fdb with my 2.5.0 gbak, then installed FB 2.5.1 and restored the security to a different file, and finally overwritten the 2.5.1 security2.fdb with the resored file (while making sure FB is off). Is there something more I can do to have my security db in order? Starting with a clean security DB is not an option (about 50 servers in different companies, dozens of users on each server). Look here: http://www.firebirdnews.org/?p=5027 But, I can't find the mentioned upgrade sql script whether in 2.5.1 nor in 2.5.2. -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Membership provider, hash passwords, login
On Mon, 17 Sep 2012 17:45:22 +0300, Vasilis .. ne...@hotmail.com wrote: Greets, anyone has come up with this problem: In a ASP.NET website i develop, using the membership provider and having hashed passwords in the database the user cannot login If i change the password storage to clear the login works fine. Any idea how to solve this or is it a known bug? Thanks This question is probably better asked on the Firebird .NET mailinglist, see http://www.firebirdsql.org/en/mailing-lists/ for subscription details. Mark
[firebird-support] Install Firebird 2.5.2 CS i386 RC1 in they Debian squeeze
I am trying to install Firebird 2.5.2 CS i386 RC1 in they Debian squeeze, but it does not start. And I do not see the cause of the problem. Has somebody had this problem? Best Regards. = || ISMAEL || =
[firebird-support] Survey on Jaybird
The Jaybird team would like to invite developers and users of Jaybird to fill out a survey on your use of Jaybird (Firebird JDBC driver). You can find the survey at http://infopoll.net/live/surveys/s36021.htm. The results of the survey will be used to decide where to focus development of the upcoming Jaybird versions. Mark PS Helen, I hope you don't mind ;) -- Mark Rotteveel ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Number of instances of firebird
Hi all I have a client who has software wich runs a test in which the software collects data. What my client would like to do is be able to store each test's data in an independent database located on a network share. This is a little beyound me and something I had never really thought about if anyone has any info or suggestions where I could look for info. I would appreciate it. Thanks John [Non-text portions of this message have been removed]
RE: [firebird-support] Membership provider, hash passwords, login
Thanks Paul, it worked! To: firebird-support@yahoogroups.com From: paul.mer...@almexa.ro Date: Mon, 17 Sep 2012 21:50:02 +0300 Subject: Re: [firebird-support] Membership provider, hash passwords, login On 2012.09.17 5:45 PM, Vasilis .. wrote: Greets, anyone has come up with this problem: In a ASP.NET website i develop, using the membership provider and having hashed passwords in the database the user cannot login If i change the password storage to clear the login works fine. Any idea how to solve this or is it a known bug? Thanks Hi Vasilis I had the same problem as you, nobody answered to this problem. Good news , it's not a bug, just documentation issue. I found that the algorithm used before .Net 4 was SHA1 and from .Net 4 is SHA256 . The solution for your trouble is to specify in web.config for membership to use : membership defaultProvider=DefaultMembershipProvider hashAlgorithmType=SHA256 HTH, Paul [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hi Bob, I have an FB 2.1.5 Classic server running on a Windows 2003 server, with a single hard drive for the operating system, and a 3 disk raid 5 array for the database. We have one database on this machine, which is a dialect 1 database that was started on IB6.0 many years ago, currently at 90GB. We have sweep disabled, and each night run gbak, gfix –sweep, as well as reindex all tables via a script. The database has very little OLTP, and is mostly used for reporting and serving web pages to internal business users. We do alot of ETL starting very early each morning, and create a mix of scheduled reports as well as allowing users to specify parameters to create pre-designed reports in an ad-hoc manner. Once or twice per month, the system slows down tremendously. One ETL process typically runs at a pace of about 1000 records per 10 seconds. During these slow periods, the same ETL will run 1000 transactions per 60-80 seconds. When processing a file with 1mil+ records, this slow down costs us hours. I have not been able to determine the reason for these slow periods. They do not coincide with higher cpu or disk usage – most of the time I'm seeing very little usage of anything – disk/cpu/network/memory. I do see more connections to the database during these periods – typically we have 10 to 15 connections, and the number may double during the problem times. This is due to the fact that the reports that users are requesting are taking longer to run, and our pooled-connection application server or web server creates more connections to satisfy new user requests. I do see more queries running by the time I am notified of the problem. Again though, this is a coincidence of the slowdown and not the cause – queries are taking longer to run, and therefore we have more chance of overlapping user requests than usual. Most of the time we need to reboot once or even twice to fix the problem. That is not a viable long-term solution though, and I'm looking for more ideas to determine what may be happening. Any ideas would be most helpful. I have included gstat –h output of the database as it is suffering from the issue below: Database header page information: Flags 0 Checksum12345 Generation 43136192 Page size 8192 ODS version 11.1 Oldest transaction 40789582 Oldest active 41467442 Oldest snapshot 41467442 Next transaction42431040 Bumped transaction 1 Sequence number 0 Next attachment ID 705070 Implementation ID 16 Shadow count0 Page buffers2048 Next header page0 Database dialect1 Creation date May 2, 2009 22:22:39 Attributes force write, no reserve Variable header data: Sweep interval: 0 *END* 1): The most obvious thing according to the header page is a very large gap between the oldest active transaction and the next transaction. This means, you have a long-running/stuck transaction. If you are lucky, you can go into the MON$TRANSACTIONS table and check out if you find the MON$TRANSACTION_ID for 41467442. Lucky, because I saw occasions where the OAT according to the header page isn't available in the monitoring tables. Perhaps some client (ETL tool?) doesn't behave well from a client transaction management POV. 2): Although you say you aren't in an OLTP pattern here, I guess due to ETL, it isn't a read-only database, right? If so, running the database in no reserve mode isn't a good idea, because, basically you are telling Firebird to not reserve space for back record version on the same data page as the primary record version. This results in more reads from disk, especially in a reporting scenario where you have long-running read-write transactions/queries, where concurrent read/write requests generate a longer back record chain until it can be removed via co-operative GC (the only GC mode in CS). While gfix can be used to remove the no reserve thing, this doesn't change the layout of already allocated data pages. If you have a maintainence window, I would go with a backup/restore cycle to re-build the database with reserve (the default, btw, thus you don't have to provide anything special for that) from scratch. Might be a challenge for a 90GB database and a small maintenance window. A few tricks to shorten the offline window: * Run both, backup and restore through the services API. When using gbak, this can be done via the -service switch. This results in not going through the TCP stack, which can improve performance a lot. * Backup the database with the -g option, because this suppress
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, currently at 90GB. We have sweep disabled, and each night run gbak, gfix --sweep, as well as reindex all tables via a script. Seems like you do correct things, but do you check that sweep is really successful? Look at the transactions' markers log in IBTM (IBSurgeon Transaction Monitor), gathered from Profitmed database (120Gb, 400 clients, 2mln transactions per 12 hours): http://www.ib-aid.com/images/transactions_maintenance.gif You can see gfix -sweep operations 2 times per day (~6am and ~21am), which were successfully performed: there are 2 moments when all transactions markers (Oldest, OAT, OST and NEXT) are equal (Next is +1). This is one of critical things to watch for if you really want to sweep your database. Regards, Alexey Kovyazin IBSurgeon www.ib-aid.com [Non-text portions of this message have been removed]
Re: [firebird-support] Number of instances of firebird
At 05:34 AM 18/09/2012, John Wilk wrote: Hi all I have a client who has software wich runs a test in which the software collects data. What my client would like to do is be able to store each test's data in an independent database located on a network share. Not on a network share...that is not supported by Firebird for read/write databases. Firebird server is a database management system that lives on a host server and manages one or more databases on the same physical host. Clients, such as your data-collecting application, attach to a database across a network, using a network protocol. In theory, there's no reason why each test couldn't have its own database. In practice, why should that be necessary? What is special about the requirements, that sets of results need to be isolated from one another? Does your customer understand the differences between a table and a database? Does [s]he really want to be administering multiple databases unnecessarily? This is a little beyound me and something I had never really thought about if anyone has any info or suggestions where I could look for info. It's not clear whether you are already a database developer yourself. If you haven't worked with client/server systems before, a good place to start would be the Quick Start Guide for the version of Firebird that you are using. You can find it in the \doc\ directory of a standard Firebird server installation; otherwise you can pick it up from the documentation pages at the Firebird website. My books (latest version The Firebird Book Second Edition) devote quite a lot of space to explaining how the various client/server models work. If you feel you need that, visit http://www.ibphoenix.com/products/books/firebird_book and consider buying the Developer DVD, on which it is distributed. ./heLen
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Alexey - Alexey Kovyazin [mailto:a...@ib-aid.com] Seems like you do correct things, but do you check that sweep is really successful? Look at the transactions' markers log in IBTM (IBSurgeon Transaction Monitor), gathered from Profitmed database (120Gb, 400 clients, 2mln transactions per 12 hours): http://www.ib-aid.com/images/transactions_maintenance.gif You can see gfix -sweep operations 2 times per day (~6am and ~21am), which were successfully performed: there are 2 moments when all transactions markers (Oldest, OAT, OST and NEXT) are equal (Next is +1). This is one of critical things to watch for if you really want to sweep your database. Is there any way to tell if the sweep was successful other than all of the markers matching? Is there any way to tell why a sweep would have failed? I'm running the sweep from a batch file, but never thought to check the errorlevel after completion... Thanks, Bob M.. [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Thomas - -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas Steinmaurer 1): The most obvious thing according to the header page is a very large gap between the oldest active transaction and the next transaction. This means, you have a long-running/stuck transaction. If you are lucky, you can go into the MON$TRANSACTIONS table and check out if you find the MON$TRANSACTION_ID for 41467442. Lucky, because I saw occasions where the OAT according to the header page isn't available in the monitoring tables. Perhaps some client (ETL tool?) doesn't behave well from a client transaction management POV. No such luck - 42450558 is the earliest of the 29 records listed. 2): Although you say you aren't in an OLTP pattern here, I guess due to ETL, it isn't a read-only database, right? If so, running the database in no reserve mode isn't a good idea, because, basically you are telling Firebird to not reserve space for back record version on the same data page as the primary record version. This results in more reads from disk, especially in a reporting scenario where you have long-running read-write transactions/queries, where concurrent read/write requests generate a longer back record chain until it can be removed via co-operative GC (the only GC mode in CS). I have definitely never used the no reserve option. I wonder if it was a default on an earlier version of the server that just carried over. I'll use gfix to use reserve to at least deal with those tables that are emptied and overwritten regularly. While gfix can be used to remove the no reserve thing, this doesn't change the layout of already allocated data pages. If you have a maintainence window, I would go with a backup/restore cycle to re-build the database with reserve (the default, btw, thus you don't have to provide anything special for that) from scratch. Might be a challenge for a 90GB database and a small maintenance window. That has been a problem for a very long time. Right now, a full backup/restore cycle is taking more than 24 hours, and at best we only have a 12 hour window at best on a Sunday. Hence the May 2009 creation date of the current DB. A few tricks to shorten the offline window: * Run both, backup and restore through the services API. When using gbak, this can be done via the -service switch. This results in not going through the TCP stack, which can improve performance a lot. That's a good trick, but since we are backing up to a seperate server the gbak -b can't use the service switch. Since we are restoring locally on the second server I could use that switch, but instead we are using the embedded gbak. Using embedded is definitely faster than regular gbak -c, but I'm curious as to whether -service is faster. I would assume that they are probably about the same. * Backup the database with the -g option, because this suppress garbage collection in the source database This is standard practice when planning on replacing the database. * If enough RAM is available, restore the database with a MUCH higher page buffers value as 2048, because this can speed up index creation during a restore a lot. E.g. 10, with a page size of 8K, this means ~800MB RAM for the page cache for this single restore connection only. Use it with caution and don't forget to set it to the original value after the restore!!! Good suggestion, I'm going to try that tonight. * If you have a spare SSD, even if it is only a cheap consumer SSD, make use of it for both, backup and restore. Unfortunately it's a corporate datacenter with fixed configurations, so no goodies like SSD's. 3:) As you are talking about reporting, make use of read-only transactions. Even better would be a combination of read-only transaction in read committed isolation mode, but read committed might be problematic in a reporting scenario, when you need a stable snapshot of the underlaying data for the period of report generation. Very good points! 4:) Keep an eye on the fb_lock_print output to possibly increase the default hash slot value. 5:) Try to run gfix -sweep at a time, when there is zero or close to zero load. Yes, we run it at night just before the backup kicks off. Unfortunately, there is overlap because the sweep usually takes about 2.5 hours. Thanks, Bob M.. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To
