Issue Details (XML | Word | Printable)

Key: CORE-696
Type: New Feature New Feature
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Pavel Cisar
Votes: 5
Watchers: 4
Operations

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

User Account maintanance in SQL

Created: 17/Sep/03 12:00 AM   Updated: 21/Jun/11 08:59 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.5 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

SF_ID: 807879


 Description  « Hide
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";

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alexander Peshkov added a comment - 27/Dec/07 11:06 AM
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 ....

Vsevolod Migdisov added a comment - 09/Jun/08 09:00 AM
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!

Alexander Peshkov added a comment - 09/Jun/08 09:12 AM
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!

Vsevolod Migdisov added a comment - 09/Jun/08 09:24 AM
Thank you very much?
Could you please give a short example of how to prepare SQL statement and use EXECUTE STATEMENT in procedure?

Vsevolod Migdisov added a comment - 15/Jul/08 05:16 AM
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).

Pabloj added a comment - 15/Jul/08 08:45 AM
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

Vsevolod Migdisov added a comment - 03/Sep/08 10:19 AM
Is it possible to add statement "CREATE OR ALTER USER"? because you never know if the user exists.

Alexander Peshkov added a comment - 03/Sep/08 10:23 AM
CREATE OR ALTER USER? Certainly, possible - but not in 2.5 I think.
Please add it as separate feature request.

Vsevolod Migdisov added a comment - 03/Sep/08 10:23 AM
and "DELETE USER" also returns an error if the USER doesn't exist, but you also never know this.