At 09:23 AM 15/03/2010, johnehgrim wrote:

>Need to force a login so can GRANT needed privleges to various users.
>Current data path: UserInterface(dBase)<-->BDE<-->FirebirdODBC<-->fbclient...FbServer<-->FBdatabase.

Using the BDE in your Firebird client layering for client/server is a *very* bad idea. What is the programming environment?

>Have been working on 'front-end' and am now near to using FB for 'live' data and thus need to start setting grants etc. from within FB.

"Grants, etc." is SQL permissions. These are created on objects in the database itself, and may be created only by the SYSDBA or the user that owns the objects.

>After much experimenting only way I found to 'force' a login is setting a user name in FB-ODBC setup with no password. That triggers a login.

Logging-in happens at the SERVER level, not the database. If a user gets past the server security, that user can try to read data from any database - as long as that user has the required SQL permissions in the database.

>BUT, get all kinds of error messages back such as 'no such index' etc.

That sort of message does not come from Firebird.

>If the user/password in FB-ODBC is left blank everything passes back and forth (which is how I've been using it and now want to restrict).

Probably until now you have hard-coded the SYSDBA credentials in the application, or in the BDE/ODBC layering. Only you can know what you did, we can't guess.

>However, if I manually enter user/password in FB-ODBC everything acts exactly as expected (for example: user TESTUSER password dummy with select permissions but no insert, update, drop. Firebird sends back exact error message expected when the dBase front end attempts the insert,etc.

If TESTUSER does not have the required permissions on an object, the application will receive a permissions error.

>
>So.... how can I force a login without firebird generating incorrect errors (such as no index when I know there are - in fact some of the code in the dBase front end, for example, queries RDB$INDICES to retrieve available indices so user can reset row viewing order - this works fine except when I force the login at the FB-ODBC layer.

This "index" error isn't coming from Firebird. If the BDE layer is querying RDB$INDICES to create some structure in application storage, that would be expected to work, since this is a system table. Currently, any logged-in user can query the system tables, even if that user has no object permissions.

>Can't be manually changing logins in FB-ODBC setup all the time.

Of course not. But don't hard-code the login credentials. Provide the means in your application to accept login credentials and *don't* tell users the SYSDBA password.

Read up on SQL Roles: these give you the means to define sets of permissions for a role. Then, to assign those permissions to users, you only have to assign that role to each user. Then, the ROLE will need to be included in the login credentials, along with the user name and password.

>Without the permission setting ability from within Firebird security is an issue.

You can *only* set permissions "from within Firebird* and the authority to do so is as described above.

> Certainly don't want to have to use the dBase front end to control access to the front end - sorta defeats part of the purpose of client/server. So where as I going wrong here?

Perhaps you need a better understanding of the client/server architecture and also understand how the two levels of security (server and database) relate.

>Any help would be most appreciated - am getting close to using live data, finally, if I can get this figured out.
>
>PS is there an on login event where I can put a trigger (such as on insert is used to trigger generator (autoincrement behavior) - don't need that right now, but will when I eventually add subset viewing control from within Firebird.

Not in a login event (which happens at the server). Autoincrement behaviour for table inserts is defined in the trigger definitions for each table, by the owner of the object, or the SYSDBA.

./heLen