Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

no permission for SELECT access to TABLE PLG$SRP in newer snapshot [CORE5841] #6102

Closed
firebird-automations opened this issue Jun 6, 2018 · 26 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Assigned to: @dmitry-starodubov

Is related to CORE5801

Attachments:
CORE5841.png
sec_cleared.fbk
sec_cleared.7z

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 CORE5827

For me, this is a blocking issue.

Commits: 50cd232 0c275c1 9d01fb9

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

from Firebird.conf
ServerMode = Super
WireCrypt = Disabled
UserManager = Srp
AuthClient = Srp, Win_Sspi, Legacy_Auth
AuthServer = Srp, Win_Sspi

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I have tested this with newly created databse (ODS12.2)
same problem

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I can not, it contain reall users and passwords :(
Is something from this database which can help you?

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

Attachment: CORE5841.png [ 13257 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

This can't affect SRP plugin - this is legacy plugin data. BTW, at what side is your old database? :)

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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? :) "

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

Attachment: sec_cleared.fbk [ 13258 ]

Attachment: sec_cleared.7z [ 13259 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

I've meant left/right sides of .png

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Ach :)
on the right i my old db

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Reproduced

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Link: This issue is related to CORE5801 [ CORE5801 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Bug caused by changes in CORE5801

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ] => Dmitriy Starodubov [ dmitriy starodubov ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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
#162

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I see it is now merged.
I tested it and it is working.

I suppose ticket can be closed as fixed.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

Fix Version: 3.0.4 [ 10863 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Deferred

Test Details: Waiting for reply on letter 25.09.2018 12:39 (dimitr, alex)

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Deferred => Done successfully

Test Details: Waiting for reply on letter 25.09.2018 12:39 (dimitr, alex) =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant