Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-17 16:23, Thomas Steinmaurer wrote: Look here: http://www.firebirdnews.org/?p=5027 Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). But, I can't find the mentioned upgrade sql script whether in 2.5.1 nor in 2.5.2. Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. There's one more thing I've tried. I copied security2.fdb to a new location and connected to it. Then I dumped the contents of RDB$USERS as inserts (via FlameRobin) and tried to insert the users into a fresh security2.fdb taken from a clean 2.5.1 installation. To my surprise, the very first insert failed with string truncation... error. It turned out the encrypted passwords in my old security database were 80 characters long, while the field RDB$PASSWD in the new security2.fdb is varchar(64). What's really surprising is that in my _OLD_ security DB this field is also varchar(64) (according to FlameRobin) and it stores 80-char strings without complaints. So, finding a way to transfer users (with passwords) from an existing security2.fdb to a new one would also be helpful in solving my problem. If anyone has tried that and succeeded, please post some hints. And thank you Thomas for finding this news post - your googling seems better than mine ;) regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-17 16:23, Thomas Steinmaurer wrote: Look here: http://www.firebirdnews.org/?p=5027 Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). But, I can't find the mentioned upgrade sql script whether in 2.5.1 nor in 2.5.2. Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. If you are brave, you could go into firebird-devel with that. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com/
RE: [firebird-support] Can non-sysdba really alter users?
Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). You can pump the data to another database shell without touching or knowing the passwords Alan
Re: [firebird-support] Can non-sysdba really alter users?
Alan, I've already tried that. I described it in my previous post. The problem is the password ciphers seem too long compared to the RDB$PASS data type. I could try to change the type of RDB$PASS, but I don't know if it's safe to mess with the domains in security DB. Tomasz On 2012-09-18 08:50, Alan McDonald wrote: Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). You can pump the data to another database shell without touching or knowing the passwords Alan -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
On Tue, 18 Sep 2012 09:16:30 +0100, Tomasz Tyrakowski t.tyrakow...@sol-system.pl wrote: Thomas, On 2012-09-18 08:47, Thomas Steinmaurer wrote: Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. If you are brave, you could go into firebird-devel with that. ;-) I'll give it a shot (just requested joining firebird-devel). By the way, could you spend half a minute and look into your security2.fdb to check if your varchar(64) RDB$USERS.RDB$PASS field also contains 80-character long ciphers? That's kind of intriguing, isn't it? What is your connection characterset? What happens if you connect using UTF8 or UNICODE_FSS? Mark
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 08:47, Thomas Steinmaurer wrote: Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. If you are brave, you could go into firebird-devel with that. ;-) I'll give it a shot (just requested joining firebird-devel). By the way, could you spend half a minute and look into your security2.fdb to check if your varchar(64) RDB$USERS.RDB$PASS field also contains 80-character long ciphers? That's kind of intriguing, isn't it? 40 bytes in my case. -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 09:39, Mark Rotteveel wrote: could you spend half a minute and look into your security2.fdb to check if your varchar(64) RDB$USERS.RDB$PASS field also contains 80-character long ciphers? That's kind of intriguing, isn't it? What is your connection characterset? What happens if you connect using UTF8 or UNICODE_FSS? In fact it's NONE. Usually I use WIN1250, but when I tried to connect to a copy of security2.fdb with WIN1250, FlameRobin warned me that the database encoding is NONE, so I changed the connection encoding to match it. I've just tried UTF8 and UNICODE_FSS and I can still see 80-characters long ciphers. An example (I split the lines for the readability's sake - in the DB that's the content of the RDB$PASS in a single record): 45645867684b6f324465 3156324a354c4f514f59 71653550417572706147 78704a7569637650383d regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 08:50, Alan McDonald wrote: You can pump the data to another database shell without touching or knowing the passwords Alan I'm not quite sure the data itself is valid (and whether pumping it to another database will fix the problem). I suspect there might be something wrong with the entries of RDB$USER_PRIVILEGES in my security2.fdb. There are some combinations of RDB$USER, RDB$PRIVILEGE and RDB$GRANT_OPTION for RDB$RELATION_NAME='RDB$ADMIN' which may be missing. I'll try to compare the contents of this system table with a valid one (i.e. one which allows privileged users to alter other users) and maybe I'll come up with an idea how to write the upgrade script myself. regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 08:47, Thomas Steinmaurer wrote: If you are brave, you could go into firebird-devel with that. ;-) Just got an answer on firebird-devel. I'll try the solution proposed by Dmitry Yemanov and post a summary in the afternoon. regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
Problem solved, thanks to Thomas Steinmaurer and Dmitry Yemanov. Here's what to do when you experience problems regarding users with elevated privileges not being able to alter other users via SQL. 1. Disconnect all clients from Firebird. 2. Copy security2.fdb to another location. 3. Connect to the copy of security2.fdb. 4. Issue ALTER VIEW USERS (USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PASSWD, PRIVILEGE, COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME, FULL_NAME) AS SELECT RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD, RDB$PRIVILEGE, RDB$COMMENT, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME, COALESCE (RDB$first_name || _UNICODE_FSS ' ', '') || COALESCE (RDB$middle_name || _UNICODE_FSS ' ', '') || COALESCE (RDB$last_name, '') FROM RDB$USERS WHERE CURRENT_USER = 'SYSDBA' OR CURRENT_ROLE = 'RDB$ADMIN' OR CURRENT_USER = RDB$USERS.RDB$USER_NAME; 5. Disconnect from the copied security2.fdb and copy it back to the Firebird folder. Thanks again guys for your help. The cause of all trouble was the lack of the OR CURRENT_ROLE = 'RDB$ADMIN' condition in the original view definition. regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
Thanks for posting the summary, Tomasz. Excellent job of sleuthing! I'm glad you figured it out in the end. Doug C.
[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 ==--__ __--==--__
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/
