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

CREATE [OR ALTER] USER statement with GRANT ADMIN ROLE: TAGS is allowed to be specified only in the end of statement in such case (discrepancy with documentation) [CORE6183] #6428

Closed
firebird-automations opened this issue Nov 3, 2019 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

According to doc, CREATE USER statement can contain GRANT ADMIN ROLE, and the syntax is:

1) in FB 2.5.x ( https://firebirdsql.org/refdocs/langrefupd25-security-sql-user-mgmt.html ):

CREATE USER username PASSWORD 'password'
[FIRSTNAME 'firstname']
[MIDDLENAME 'middlename']
[LASTNAME 'lastname']
[GRANT ADMIN ROLE]
-------------------------
2) in FB 3.x ( https://firebirdsql.org/file/documentation/reference_manuals/firebird-language-reference-30-rus.pdf (NOTE: this doc is in RUSSIAN)), page 518:
CREATE USER username PASSWORD 'password'
[FIRSTNAME 'firstname']
[MIDDLENAME 'middlename']
[LASTNAME 'lastname']
[ACTIVE | INACTIVE]
[USING PLUGIN pluginname] ----------------------------- 1
[TAGS (<tag>[, <tag>[, <tag>...]] )] ------------------ 2
[GRANT ADMIN ROLE] ------------------------------------ 3
;

/*
Reference to FB 2.5 here is only for those who can not read doc in russian (unfort., I could not find similar to "firebird-language-reference-30-rus.pdf" in english; may be it was not (yet) translated ?)
*/

So, we can specify all three clauses in FB 3.x: 'USING PLUGIN', 'TAGS', and 'GRANT ADMIN ROLE'
Unfortunately, current FB releases force to specify these clauses in fixed positions; furthermore, statement will be considered as valid only when positions violate the order that is shown above.
It seems to me that it is "TAG( ... )" clause that is the reason of this problem.

Please look:

C:\FB\30SS>isql /:e30
Database: /:e30, User: SYSDBA
SQL> create user tmp20191103_150701 password '123' using plugin Srp grant admin role; -- NO tags here ==> no problem
SQL> drop user tmp20191103_150701 using plugin Srp;

#⁠#⁠#⁠ Examples WITH TAGS(....) clause #⁠#⁠#⁠

SQL> create user tmp20191103_150701 password '123' tags ( foo = 'bar' ) using plugin Srp grant admin role;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 68
-using
SQL> rollback;

SQL> create user tmp20191103_150701 password '123' using plugin Srp tags ( foo = 'bar' ) grant admin role;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 85
-grant
SQL> rollback;

SQL> create user tmp20191103_150701 password '123' using plugin Srp grant admin role tags ( foo = 'bar' );
SQL> drop user tmp20191103_150701 using plugin Srp;

SQL> create user tmp20191103_150701 password '123' grant admin role using plugin Srp tags ( foo = 'bar' );
SQL> drop user tmp20191103_150701 using plugin Srp;

So, currently valid order of clauses is:
1) USING PLUGIN <...>; 2) GRANT ADMIN ROLE; 3) TAGS( ... )
== or ==
1) GRANT ADMIN ROLE; 2) USING PLUGIN <...>; 3) TAGS( ... )

-- i.e. TAGS must be specified at the end of the whole statement.
The same in CREATE OR ALTER user (rather than just "CREATE').

Checked on: WI-T4.0.0.1639 Firebird 4.0 Beta 1 ; WI-V3.0.5.33183 Firebird 3.0

PS.
I've set priority to "minor" because workaround does exist, see examples above.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

That should go to the doc bugs - it's explicitly defined in README.user_management that TAGS clause goes after all other options.

PS. yes, it's missing ADMIN ROLE option, but what about TAGS location - it's rather clear

@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 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

I'm fixed it in firebird-language-reference-30-rus.pdf (and 4.0 too)

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