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

User Account maintanance in SQL [CORE696] #1065

Closed
firebird-automations opened this issue Sep 17, 2003 · 16 comments
Closed

User Account maintanance in SQL [CORE696] #1065

firebird-automations opened this issue Sep 17, 2003 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 [ 10048 ]

assignee: Dmitry Yemanov [ dimitr ]

SF_ID: 807879 =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

assignee: Dmitry Yemanov [ dimitr ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA291 [ QA291 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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