Hi,

We are currently developing client-server applications. I need your
advice concerning any potential problems in the way we are planning to
develop our security.

We are using Sybase 11.9.2 and we are using Delphi for application
development.
All of the tables have "SEL" permission for all users. We want to
restrict user access through ACCESS or other utilities, in order to
prevent any changes in update mode to our tables.

We considered giving update permission of our tables through store
procedures which would handle such functions. However, this could be
rather heavy for Delphi to handle such updates by store procedures,
since Delphi must re-manipulate data in order to update the database.
Furthermore, Delphi offers components which bind directly to the
database.

In order to optimize the application development, we considered building
the security as follows (this is where we need your help) :

We want to give update permission to only one userID, for all tables.
This userID would be used for update operations to all tables. In
addition, we would create all application userID's in the databases, but
with "READ" authority only.
All application userID's would be entered at login and in each
database. So would the userID having table update authority.
Upon entering a database, the application user would be disconnected,
and then would be reconnected using the userID having update authority.
The Delphi application would thus have all update authority for the
tables.

The only problem would be that since all users would use the same userID
for updates, the DBA would have trouble finding which user did which
updates. We considered working around this problem by having the
application register the real username in the HOSTNAME (visible with
sp_who). The DBA would then clearly see who did what.

To help make things clearer, here is a complete example of how it would
work :

Application UserID - rroy (this userID has only READ authority for the
data tables. No update is allowed.)
Update UserID - agent_update (this userID has all permissions : SEL,
UPD, DEL, ADD).

1) UserID rroy connects, and the application checks if he has access to
the database.
2) If access is granted, he is disconnected, then reconnected using the
agent_update userID. The name of the user rroy is passed through the
HOSTNAME parameter.
3) The agent_update user can now update as he wishes.
4) As for the DBA, he can see who did what using the sp_who commande,
using the HOSTNAME parameter to find the name of the real user who
worked on the database server. The locks would be easy to determine.

I hope that I was able to explain the problem clearly.
I also have another question:
- Do we have a limit in Sybase for the number of simultaneous
connections for any one user?

Thank you for your help.

Rémi Roy