Interbase Question - Adding Users

Hi Folks,

I need a working solution here, because I have been spending WEEKS on this and have not had any luck.

I need to be able to add, delete and modify users in an interbase DB - regardless of where it is located (remote or local).  I don't care how it is does (SQL or direct APIs - although I dont think you can do it using SQL).

I have downloaded a miriad of source code and nothing has worked.  I've spent ages modifying code which doesnt look like it could ever have worked.  This is really frustrating the hell out of me because it doesnt seem like it should be that hard.

In D5 Enterprise, there is a component called IBServices.  This component is documented as having a UserListCount property (according to the help file), but it doesnt seem to be in the actual component.  This is a core part of what I am trying to do.  If anyone can solve this, they've solved my entire problem.

I'll offer 200 points now, but will give out upto another 300 if the answer works perfectly for me.  As I said, I'm desperate for a solution now....

Cheers!


Stu.

PS.  Just to clarify, I do need to be able to do this from within the program I am writing.  It has to be either Delphi code (D5) or native SQL.
LVL 6
Stuart_JohnsonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
TOndrejConnect With a Mentor Commented:
Are you using Interbase 6?
The various IBServices only work with Interbase 6 (or later)...

In that case you can use TIBSecurityService component from IBServices unit.
The component may not be registered in the component palette; you can either register it in a design package or create and use it in code, as shown in the help example 'AddUser, DeleteUser Example' in ibx.hlp.

There is an update for Delphi 5 IBX: http://www.borland.com/devsupport/interbase/ibx_updates.html
I believe it will work without problems under Delphi 6, too.

If you're using older version of Interbase, you can still use old security APIs isc_add_user, isc_delete_user which are now deprecated (see Interbase "API Guide" for details):

uses
  IBHeader, IBExternals, IBErrorCodes, IB, IBIntf;

procedure TForm1.ButtonAddUserClick(Sender: TObject);
var
  isc_res: ISC_STATUS;
  UserSecData: TUserSecData;
begin
  CheckIBLoaded;
  FillChar(UserSecData, SizeOf(TUserSecData), 0);
  with UserSecData do
  begin
    sec_flags := sec_first_name_spec or sec_middle_name_spec or sec_last_name_spec or
      sec_dba_user_name_spec or sec_dba_password_spec;
    protocol := sec_protocol_local;
    user_name := PChar(EditUserName.Text);
    password := PChar(EditPassword.Text);
    first_name := PChar(EditFirstName.Text);
    middle_name := PChar(EditMiddleName.Text);
    last_name := PChar(EditLastName.Text);
    dba_user_name := 'SYSDBA';
    dba_password := 'masterkey';
  end;

  isc_res := isc_add_user(StatusVector, @UserSecData);
  if (StatusVector^ = 1) and (isc_res > 0) and
    not CheckStatusVector([isc_usrname_too_long, isc_password_too_long, isc_usrname_required,
      isc_password_required, isc_bad_protocol, isc_dup_usrname_found, isc_usrname_not_found,
      isc_error_adding_sec_record, isc_error_deleting_sec_record, isc_error_modifying_sec_record,
      isc_error_updating_sec_db]) then
      IBDatabaseError;
end;

procedure TForm1.ButtonDeleteUserClick(Sender: TObject);
var
  isc_res: ISC_STATUS;
  UserSecData: TUserSecData;
begin
  CheckIBLoaded;
  FillChar(UserSecData, SizeOf(TUserSecData), 0);
  with UserSecData do
  begin
    sec_flags := sec_dba_user_name_spec or sec_dba_password_spec;
    protocol := sec_protocol_local;
    user_name := PChar(EditUserName.Text);
    dba_user_name := 'SYSDBA';
    dba_password := 'masterkey';
  end;

  isc_res := isc_delete_user(StatusVector, @UserSecData);
  if (StatusVector^ = 1) and (isc_res > 0) and
    not CheckStatusVector([isc_usrname_too_long, isc_password_too_long, isc_usrname_required,
      isc_password_required, isc_bad_protocol, isc_dup_usrname_found, isc_usrname_not_found,
      isc_error_adding_sec_record, isc_error_deleting_sec_record, isc_error_modifying_sec_record,
      isc_error_updating_sec_db]) then
      IBDatabaseError;
end;

This example uses Local Interbase. I've just tested it on Local IB 6 on NT 4 and it kind of works, the only problem is that errors are not properly reported when the API call fails (tried adding an existing user but no exception was raised). The behaviour might be different on older version of Interbase, or maybe I'm doing something wrong... I'm not sure.

Hope this helps a little
TOndrej
0
 
kretzschmarCommented:
from the interbase tutorial

Creating a new user

The rest of this tutorial assumes that you are user TUTOR and that your password is tutor4ib. In this
next exercise, you create user TUTOR.

In the previous section, you opened Server Manager and connected to a server as an existing user.
Now you create a new user, TUTOR. Note InterBase ships by default with one user, SYSDBA, defined.

1. In Server Manager, choose Tasks | User Security to display the InterBase Security dialog or click
the User Security button.

2. In the InterBase Security dialog, click the Add User button to display the User
Configuration dialog.

3. Type TUTOR in the User Name field and tutor4ib in the Password and Confirm Password
fields.

4. Click OK to close the User Configuration dialog. Click OK to close the InterBase Security
dialog.

You have now created a user on the server you?re logged into. Users are defined server-wide and
can connect to any database that resides on that server. Tables within these databases have
additional security, however. Being able to connect to a database won?t do you much good if you
don?t have privileges on any of its tables.

5. Now choose File | Exit. Choose Yes when InterBase asks you if you want to log out from all
servers and exit Server Manager.


meikl ;-)
0
 
Stuart_JohnsonAuthor Commented:
Hi Meikl,

I am assuming this is an extra program and not Delphi source?  I guess I should have stipulated that I actually wanted to be able to do it from within my program.

Am I reading your comment incorrectly?

Thanks,

Stu.
0
 
ITugayCommented:
Hi Stuart.

Hello Ondrej :-)

Stuart, if you just need to add/delete/modify user to the server then it's not so difficult. Here is the way to do using BDE

Create an alias to user's database (ISC4.GDB)  on your server. Usually location looks something like this:
"servername:C:\Program Files\Borland\Interbase\Isc4.gdb".

Connect to database as admin (SYSDBA).

There is "USERS" table. To add user you just need to specify USER_NAME and PASSWD fields for new record. The main problem is that password should be encrypted. I have a procedure to encrypt password, but it's big enought to public here.

Once you added user you can modify rest of fields.

-----
Igor


0
 
Stuart_JohnsonAuthor Commented:
Hi TOndrej,

The IBX Update fixed the problems I was having!  Thanks very much for you help.

Thanks to everyone else too for your input.

Cheers,

Stu
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.