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

Error "no permission for DELETE access to TABLE PLG$SRP_VIEW" when <admin-1> tries to drop <admin-2> via ES and <admin-2> already successfully dropped some 3rd (non privileged) user in the same execute block [CORE4770] #5069

Closed
firebird-automations opened this issue Apr 23, 2015 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Consider following script:

set list on;

show version;
select mon$user,mon$remote\_protocol,mon$auth\_method from mon$attachments where mon$attachment\_id=current\_connection;
commit;

\-\-set list off;
\-\-set echo on;

create or alter view v\_users as
select
     u\.sec$user\_name usr\_name
    ,sec$plugin sec\_plugin
    ,u\.sec$admin is\_sec\_admin
    ,rp\.rdb$relation\_name granted\_role
    ,rp\.rdb$privilege granted\_privilege
from rdb$database
left join sec$users u on u\.sec$user\_name in \( upper\('boss'\), upper\('acnt'\), upper\('mgr1'\), upper\('mgr2'\)\)
left join rdb$user\_privileges rp on
    u\.sec$user\_name = rp\.rdb$user
    and rp\.rdb$relation\_name = upper\('rdb$admin'\)
    and rp\.rdb$object\_type = 13
    and rp\.rdb$user\_type = 8
;
commit;

set width usr\_name 10;
set width sec\_plugin 10;
set width granted\_role 12;


drop user boss;
drop user acnt;
drop user mgr1;
drop user mgr2;
commit;
create user boss password '123' grant admin role
;
grant rdb$admin to boss;
commit;

create user acnt password '456' grant admin role
;
grant rdb$admin to acnt;
commit;

create user mgr1 password '789';
create user mgr2 password '890';
commit;


set count on;
select \* from v\_users;
set count off;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- DROP ALL TOGETHER IN ONE EXECUTE BLOCK \-\-\-\-\-\-\-\-\-\-\-\-\-\-
set term ^;
execute block as
begin

    execute statement 'drop user mgr1'                \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \[ 1 \]
    as user 'ACNT' password '456' role 'RDB$ADMIN'
    ;

    execute statement 'drop user mgr2'                \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \[ 2 \]
    as user 'BOSS' password '123' role 'RDB$ADMIN' 
    ;

    execute statement 'drop user acnt'                  \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \[ 3 \]
    \-\-as user 'BOSS' password '123' role 'RDB$ADMIN'    \-\- <<<<<  :::::::   NB  :::::::: <<<<<
    ;
end
^
set term ;^
commit;

drop user boss; \-\-\- \[ 4 \]
commit;

set count on;
select \* from v\_users;
set count off;

===

When the statement marked as '[ 3 ]' is executed from SYSDBA (i.e. as it shown, with COMMENTED "--as user 'BOSS' . . .") then script works fine.

However, if line "--as user 'BOSS' . . ." in the statement [ 3 ] will be UNcommented:

    execute statement 'drop user acnt'                  \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- \[ 3 \]
    as user 'BOSS' password '123' role 'RDB$ADMIN'    
    ;

-- then strange things appear:
1) execute block fails with message:

Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW

2) statement "drop user boss; --- [ 4 ]" (that is OUTSIDE execute block and should be issued by SYSDBA!) also FAILS with the same error;
3) statement "select * from v_users;" also FAILS with this error.

Union of STDOUT and STDERR in this case:

=======
ISQL Version: WI-T3.0.0.31807 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31807 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31807 Firebird 3.0 Beta 2/tcp (balaha)/P13:C"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31807 Firebird 3.0 Beta 2/tcp (balaha)/P13:C"
on disk structure version 12.0

MON$USER SYSDBA
MON$REMOTE_PROTOCOL TCPv4
MON$AUTH_METHOD Srp

Statement failed, SQLSTATE = HY000
record not found for user: BOSS
After line 29 in file es-drop-user.sql
Statement failed, SQLSTATE = HY000
record not found for user: ACNT
After line 32 in file es-drop-user.sql
Statement failed, SQLSTATE = HY000
record not found for user: MGR1
After line 33 in file es-drop-user.sql
Statement failed, SQLSTATE = HY000
record not found for user: MGR2
After line 34 in file es-drop-user.sql

USR_NAME BOSS
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M

USR_NAME ACNT
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M

USR_NAME MGR1
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>

USR_NAME MGR2
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>

Records affected: 4
Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW
After line 74 in file es-drop-user.sql
Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW
After line 77 in file es-drop-user.sql

USR_NAME BOSS
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M

USR_NAME ACNT
SEC_PLUGIN Srp
IS_SEC_ADMIN <true>
GRANTED_ROLE RDB$ADMIN
GRANTED_PRIVILEGE M

USR_NAME MGR1
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>

USR_NAME MGR2
SEC_PLUGIN Srp
IS_SEC_ADMIN <false>
GRANTED_ROLE <null>
GRANTED_PRIVILEGE <null>

Records affected: 4
Statement failed, SQLSTATE = 28000
delete record error
-no permission for DELETE access to TABLE PLG$SRP_VIEW
After line 82 in file es-drop-user.sql

PS. If statement "[ 1 ]" will be commented then EB works fine again - i.e. this error is somehow related to the fact that 'ACNT' could successfuly do at least one action against users list (he drops non-dba user mgr1).

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

PPS.
I still don't understand why 'GRANT RDB$AMIN' is mandatory for <admin_1> can successfully issue DROP USER <someone> if this <admin_1> was created like this: CREATE USER <admin_1> GRANT ADMIN ROLE.

If "GRANT ADMIN ROLE" clause does not that <admin_1> already CAN manage list of users than for what it is needed ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Use of common transactions (default mode for EXECUTE STATEMENT) may produce side effects for operators causing DFW - first of all for DDL operators.
Users management of just one sample of it, inconsistencies (from the first look) are possible for all types of DDL when mix of EXECUTE STATEMENT with different common transactions is executed. The best way to execute DDL statements with different current user and role is running them with autonomous transaction. I.e. using autonomous transactions is provided sample makes statements execute as expected.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

2 participants