User login

Of course forena is not designed to prompt for usernames and passwords from users before connecting, so there will be nothing that you can do here without writing your own custom module code.

Furthermore: I'm not quite sure I understand the question here, so I'll talke a stab at restating them and then answering them.

I want forena to connect do the remote database server with a username and password specified by the user:
To do this, you need to implement hook_forena_repos_alter in order to change the username and password in the repository before implementing your own.

I want to write a query that uses the currently logged in user to select data:
Forena supports the use of the :current_user parameter wich is automatically set to specify the currently logged in user. This parameter can be configured to be prepoulated based on either uid, user name or email address in the data source configuration. This is how I normally deal with accessing data from third party applictions.

Note that you can also create an alternative access function which you can configure in settings.php for your repository or using the forena_repos_alter hook which will test security in any way you want. You can also reference a datablock, which you define that returns all the access rights for a user based in the :current_user parameter. These rights can then be referred to in your data blocks (--ACCESS=right name) instead of the normal drupal rights to impose security.

Hope that gives you some clues.