Issue Details (XML | Word | Printable)

Key: CORE-5841
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitriy Starodubov
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

no permission for SELECT access to TABLE PLG$SRP in newer snapshot

Created: 06/Jun/18 06:51 AM   Updated: 25/Sep/18 03:47 PM
Component/s: Engine
Affects Version/s: 3.0.4
Fix Version/s: 3.0.4, 4.0 Beta 1

File Attachments: 1. File sec_cleared.7z (49 kB)
2. File sec_cleared.fbk (9 kB)

Image Attachments:

1. CORE5841.png
(57 kB)
Environment: WI-V3.0.4.32985 Firebird 3.0

QA Status: Done successfully


 Description  « Hide
Something was broken recently.

Below sql is working under
WI-V3.0.4.32954 Firebird 3.0

but cause an error under
WI-V3.0.4.32972 Firebird 3.0

and current snapshot
WI-V3.0.4.32985 Firebird 3.0
------------------------------------------------------------------------------

find/display record error

no permission for SELECT access to TABLE PLG$SRP.

------------------------------------------------------------------------------
SELECT
U.SEC$USER_NAME
, (SELECT UA.SEC$VALUE FROM SEC$USER_ATTRIBUTES UA WHERE UA.SEC$USER_NAME=U.SEC$USER_NAME AND UA.SEC$KEY='DYR_ID') AS GID
, (SELECT UA.SEC$VALUE FROM SEC$USER_ATTRIBUTES UA WHERE UA.SEC$USER_NAME=U.SEC$USER_NAME AND UA.SEC$KEY='PRAC_ID') AS UID
FROM
SEC$USERS U
WHERE
U.SEC$USER_NAME=CURRENT_USER

-----------------------------------------------------------------------------

this core can be releated to CORE-5827

For me, this is a blocking issue.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alexander Peshkov added a comment - 06/Jun/18 08:15 AM
Could not reproduce (on a fresh B3_0_Release build):

First of all create some user:

./isql -user sysdba -z employee
ISQL Version: LI-V3.0.4.32985-dev Firebird 3.0
Server version:
LI-V3.0.4.32985-dev Firebird 3.0
Database: employee, User: SYSDBA
SQL> show users;
Statement failed, SQLSTATE = 28000
find/display record error
-Install incomplete, please read the Compatibility chapter in the release notes for this version
                         (i.e. it's clean install)
Command error: show users
SQL> create user sysdba password 'masterkey';
SQL> create user a12 password 'a12';

Now I start firebird server and try with it:

./isql -user a12 -pas a12 -z localhost:employee
ISQL Version: LI-V3.0.4.32985-dev Firebird 3.0
Server version:
LI-V3.0.4.32985-dev Firebird 3.0
LI-V3.0.4.32985-dev Firebird 3.0/tcp (alex-Vostro-15-3568)/P15:C
LI-V3.0.4.32985-dev Firebird 3.0/tcp (alex-Vostro-15-3568)/P15:C
Database: localhost:employee, User: A12
SQL> SELECT
CON> U.SEC$USER_NAME
CON> , (SELECT UA.SEC$VALUE FROM SEC$USER_ATTRIBUTES UA WHERE UA.SEC$USER_NAME=U.SEC$USER_NAME AND UA.SEC$KEY='DYR_ID') AS GID
CON> , (SELECT UA.SEC$VALUE FROM SEC$USER_ATTRIBUTES UA WHERE UA.SEC$USER_NAME=U.SEC$USER_NAME AND UA.SEC$KEY='PRAC_ID') AS UID
CON> FROM
CON> SEC$USERS U
CON> WHERE
CON> U.SEC$USER_NAME=CURRENT_USER ;

SEC$USER_NAME GID UID
=============================== =============================================================================== ===============================================================================
A12 <null> <null>

Probably I'm missing something but to fix a bug we need a way to reproduce.

Karol Bieniaszewski added a comment - 06/Jun/18 09:35 AM
i got this error also for

SELECT
U.SEC$USER_NAME
FROM
SEC$USERS U
WHERE
U.SEC$USER_NAME=CURRENT_USER

------------------------------------------------------
by flamerobin:
-----------------------------------------------------
Message: isc_dsql_fetch failed.

SQL Message : -901
Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements

Engine Code : 336723996
Engine Message :
find/display record error
no permission for SELECT access to TABLE PLG$SRP

------------------------------------------------------

by isql

------------------------------------------------------

SEC$USER_NAME
===============================
Statement failed, SQLSTATE = 28000
find/display record error
-no permission for SELECT access to TABLE PLG$SRP

------------------------------------------------------------------------------------------

more details about settings
my database is ODS: 12.2 - mayby this is the reason?
i use charset: WIN1250
it is on Windows7

if i revert fb to previous WI-V3.0.4.32954 Firebird 3.0 with same database it is working ok

Karol Bieniaszewski added a comment - 06/Jun/18 09:40 AM
from Firebird.conf
ServerMode = Super
WireCrypt = Disabled
UserManager = Srp
AuthClient = Srp, Win_Sspi, Legacy_Auth
AuthServer = Srp, Win_Sspi

Karol Bieniaszewski added a comment - 06/Jun/18 09:42 AM
I have tested this with newly created databse (ODS12.2)
same problem

Alexander Peshkov added a comment - 06/Jun/18 10:04 AM
Karol, I do not want to say that there is no issue - moreover, there were some commits related with access checks, i.e. I expect that there can be an issue. But currently I can't reproduce it. Can you attach here problematic security database? I'm almost sure that the problem is not with target db but with security one.

Karol Bieniaszewski added a comment - 06/Jun/18 10:37 AM
I can not, it contain reall users and passwords :(
Is something from this database which can help you?

Alexander Peshkov added a comment - 06/Jun/18 11:00 AM - edited
I.e. you were testing not with new fresh security database?
Please first of all repeat your test with fresh install.
At the same time please send here output of 'show grants;' command _for security database_ in isql.

Karol Bieniaszewski added a comment - 06/Jun/18 11:40 AM - edited
With fresh security database it work ok
i have compared privileges and the only difference is the grantor "GRANTED BY FBADMIN"
look at attached picture

Alexander Peshkov added a comment - 06/Jun/18 12:32 PM
This can't affect SRP plugin - this is legacy plugin data. BTW, at what side is your old database? :)

Alexander Peshkov added a comment - 06/Jun/18 01:18 PM
Sorry, but looks like I can't add something else w/o problematic DB.
Make a copy of it, make all logins l1,l2,l3 or something else in this way and reset passwords to 123.

Karol Bieniaszewski added a comment - 07/Jun/18 06:16 AM - edited
i have attached backup of cleared security database and compressed database itself
there are users

SYSDBA password 'sysdba'
KBIENIASZEWSKI password 'kbieniaszewski'

P.S. what do you mean "BTW, at what side is your old database? :) "

Alexander Peshkov added a comment - 07/Jun/18 01:21 PM
I've meant left/right sides of .png

Karol Bieniaszewski added a comment - 07/Jun/18 01:27 PM
Ach :)
on the right i my old db

Alexander Peshkov added a comment - 07/Jun/18 01:32 PM
Reproduced

Alexander Peshkov added a comment - 07/Jun/18 05:51 PM - edited
Bug caused by changes in CORE-5801

The simplest way to reproduce:

isql -user KBIENIASZEWSKI sec_cleared.FDB
SQL> select * from plg$srp_view;

Will show access error which is invalid here.

Karol Bieniaszewski added a comment - 19/Jul/18 01:11 PM - edited
Hi, is there some timeframe for fixing this?
We are interested in recent snapshots but this issue prevent us from testing.

I see that the patch was created but not commited to the branch
https://github.com/FirebirdSQL/firebird/pull/162

Karol Bieniaszewski added a comment - 24/Sep/18 07:10 AM
I see it is now merged.
I tested it and it is working.

I suppose ticket can be closed as fixed.