hi everybody,

ive a problem here about database login system.

up to now, we had a server service (a tcp listener) and client
application was asking for a username and password over tcp
(encyripted with a rc4 algorithm). if login info was accepted, server
was sending a connectiong string (and some other information about
user). so the client application can login into the database with "sa"
user. so all the users logging into sql with "sa" user. user
information was kept out of sql database. all the user rights was
sended from server app to the client.

now im planning to change this structure.

first of all we may have to get rid of that server service. client
should directly login into sqlserver. so users information and user
rights will be kept in sqlserver.

what would your advice be for this structure?

i created a "dbo.user_login(username, password)" function. and created
two users "admin" and "loginguest" in sqlserver. "admin" user can see
and do all jobs in sqlserver. but "loginguest" cannot see tables,
views, procedures etc. "loginguest" can just call "dbo.user_login"
function (even cannot redesign the
function) if username and password information provided is accepted by
the function (matching a user in the "appusers" table) it returns
admin user password and some user information to the caller.

shortly, application will open a session with loginguest, get password
for admin, close session and the real user will login into sqlserver
with admin user.

or wat about creating an sqlserver user for all real users?

wat other techniques have u designed for this purpose?

thanks

Ender Karada? (enkaradag (AT) gmail (DOT) com) writes:
Is this a two-tier or three-tier application? That is, is there a middle-
tier running on a separate server, or are users connecting directly
from their workstations?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

our application is a two tier application between fat clients and a
completely dummy database server (wat i mean is; all calculations and
requirements are done on client machine, server just saves the given
data and queries it) but i want to redesign all the application and
database with the best approach (with a "+1" tier maybe).

for example while saving a product card, a product may not be saved
with a null product_name field. in our database product_name is not a
required field or a trigger does not check whether data is valid or
not. before going to server, application checks whether the
product_name textbox is filled or not. (this looks bad, but to admit,
may be good for performance) but i want to change this structure, im
planning to do all these kind of calculations and controls with table
triggers and returning the corresponding message to the user.

shortly, (by the way sorry for long descriptions i made) i want to
desing an application that is "living" on the database.

for the login structure, would it be possible to change the sqlserver
user, without dropping the session?

or wat other methods would u advice for a windows application login?

Regards

Ender Karada? (enkaradag (AT) gmail (DOT) com) writes:
I guess the reason for this complicated arrangement is that you don't
want users to access the database directly from outside the application.

This is not really achievable with a two tier-application. Anything
the application can do, the user can do outside the application.
You can employ various tricks, but it's only security by obscurity.
It may be enough to keep the users who are too smart for their own
good out, but not enough to keep the dedicated malicious user out.

The best you can do, save re-architecturing the application, is to
put it on a terminal server, and set up the terminal server so that
when user log in they directly come to the application, and so that
they cannot leave the application. Then you can do one of two things:

1) Application uses a proxy login, and users have no logins of their
own.
2) Application logs in each user with their own login, but the network
is setup so that users cannot reach SQL Server from their own
machines.

There are application roles that were introduced to address this
situation, but again, on a two-tier application, they are not safe,
because the user can always retrieve the password on his own.

As for you endeavour to move the business logic from the application
to the database, this is definitely a correct step to take, but it
will certainly take some time to realise.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx