User Account maintanance in SQL [CORE696]

[firebird-automations]

Submitted by: @pcisar

Is related to QA291

Votes: 5

SFID: 807879#⁠
Submitted By: pcisar

I think it a good idea to have the ability in the sql
interface to Create, Edit and Delete User Accounts.

E.G.

Create User "MyUsername" "MyPassword";

Delete User "MyUsername";

Alter User "MyUsername" "MyPassword" "NewPassword";

[firebird-automations]

Modified by: @dyemanov

Fix Version: 3.0 [ 10048 ]

assignee: Dmitry Yemanov [ dimitr ]

SF_ID: 807879 =>

[firebird-automations]

Modified by: @pcisar

assignee: Dmitry Yemanov [ dimitr ] =>

[firebird-automations]

Modified by: @dyemanov

assignee: Alexander Peshkov [ alexpeshkoff ]

[firebird-automations]

Commented by: @AlexPeshkoff

Added SQL operators:

CREATE USER name PASSWORD 'pass' [FIRSTNAME 'text'] [MIDDLENAME 'text'] [LASTNAME 'text'];

ALTER USER name [SET] [PASSWORD 'pass'] [FIRSTNAME 'text'] [MIDDLENAME 'text'] [LASTNAME 'text']; -- at least one of PASSWORD / FIRSTNAME / MIDDLENAME / LASTNAME is required

DROP USER name;

Non-privileged (non-SYSDBA) user can use only:

ALTER USER my_name ....

[firebird-automations]

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

Fix Version: 3.0.0 [ 10048 ] =>

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10720 ] => Firebird [ 15040 ]

[firebird-automations]

Commented by: Vsevolod Migdisov (vmigdisov_gmail.com)

Is it possible to use it in PSQL with parameters?

e.g.

declare variable USER_NAME char(32);
declare variable PASSWD char(32);

for select USER_NAME, PASSWD from MY_USERS
into :USER_NAME, :PASSWD
do begin
CREATE USER :USER_NAME PASSWORD :PASSWD;
end

Thank you!

[firebird-automations]

Commented by: @AlexPeshkoff

No. It was not planned to be used in PSQL, even without parameters (exactly like the rest of DDL).
But if you want/need, you can prepare text string with operator, and use EXECUTE STATEMENT, which can run DDL. EXECUTE STATEMENT overhead can be treated as minor compared with DDL itself.
Remember no to expect users to arrive before whole transaction commit!

[firebird-automations]

Commented by: Vsevolod Migdisov (vmigdisov_gmail.com)

Thank you very much?
Could you please give a short example of how to prepare SQL statement and use EXECUTE STATEMENT in procedure?

[firebird-automations]

Commented by: Vsevolod Migdisov (vmigdisov_gmail.com)

CREATE USER newuser PASSWORD 'masterkey';

ERROR (2.5 Alpha, SYSDBA, IBExpert):
This operation is not defined for system tables.
unsuccessful metadata update.
invalid database handle (no active connection).

[firebird-automations]

Commented by: Pabloj (pabloj_sourceforge)

More details on the case just reported by Vsevolod Migdisov, I'm trying to create an user and get an error, see the log:

C:\Program Files\Firebird\Firebird_2_5\bin>isql -u sysdba -p masterkey localhost
:c:\pippo2.fdb -r sysdba
Database: localhost:c:\pippo2.fdb, User: sysdba, Role: SYSDBA
SQL> create user pippo password 'pippo';
Statement failed, SQLSTATE = 08003
unsuccessful metadata update
-invalid database handle (no active connection)
SQL>

C:\Program Files\Firebird\Firebird_2_5\bin>isql -z
ISQL Version: WI-T2.5.0.20343 Firebird 2.5 Alpha 1
Use CONNECT or CREATE DATABASE to specify a database

SQL> select rdb$get_context('SYSTEM', 'ENGINE_VERSION') from rdb$database;

RDB$GET_CONTEXT

2.5.0

Hope I gave enough details

[firebird-automations]

Commented by: Vsevolod Migdisov (vmigdisov_gmail.com)

Is it possible to add statement "CREATE OR ALTER USER"? because you never know if the user exists.

[firebird-automations]

Commented by: @AlexPeshkoff

CREATE OR ALTER USER? Certainly, possible - but not in 2.5 I think.
Please add it as separate feature request.

[firebird-automations]

Commented by: Vsevolod Migdisov (vmigdisov_gmail.com)

and "DELETE USER" also returns an error if the USER doesn't exist, but you also never know this.