Attach remote DB file: authorization and permissions

Hi All,
I'm development a system, and I have doubts about login (authorization and
permissions)
This system need be very secure.
The systems have clients that connect in a web service and this web service
connects in SQL Server.
My doubt is:
What is the best:
Logins here is: Name and password(hash) and what each user can do.
Store logins in one table in my database and always WebServise use SA
account to manipulate data base?
Use SQL logins for each users?
Use Windows logins for each users?
ThanksFirst of all, abstraction is the easiest to manage and administrate. In all
regards, use Windows Authentication whenever possible. If this is public
facing, in all likelihood, you will not have Windows Domain Accounts
available for use.
In this case, and in cases where the applicaiton needs to control the
security of the users (that is, keep explicit user identification and
permissions within the application database), it would be better to use a
single Windows Authenticated account to manipulate database calls.
Also, you should not have the Web Services directly manipulate the database.
In stead, you should have application layer services resident on another
server from you Web Server and have the web call these services. The
application tier should then use this single application, Windows
Authenticated account control the connections and calls to the Data Services
tier.
The interfaces exposed from the DBMS should all be done through stored
procedures and ad-hoc requests for reporting and administrative services
should be exposed through Views. No direct base table access should be
granted outside of the development/application support staff, and even then,
restricted to DML activities alone. The DBA should be the only user
authorized to make system level changes, including DDL, and only through a
formalized Change Control process.
Sincerely,
Anthony Thomas
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:O3KN$thaFHA.3048@.TK2MSFTNGP12.phx.gbl...
Hi All,
I'm development a system, and I have doubts about login (authorization and
permissions)
This system need be very secure.
The systems have clients that connect in a web service and this web service
connects in SQL Server.
My doubt is:
What is the best:
Logins here is: Name and password(hash) and what each user can do.
Store logins in one table in my database and always WebServise use SA
account to manipulate data base?
Use SQL logins for each users?
Use Windows logins for each users?
Thanks|||This is a multi-part message in MIME format.
--090003030806010808040200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The most secure option is to force all users to have their own login and
make them all trusted logins. Of course, there are typically practical
problems with that approach from web apps as the user that hits the web
server needs to be authenticated against AD for the trusted SQL login
from the web server to work properly and for externally accessible web
apps authenticating the users against AD is often not possible. But
purely from a SQL perspective trusted logins are the most secure method
of connecting.
Storing passwords in your DB is never a good idea if solid security is
what you're aiming for. Storing localised application permissions in
some user table in your DB is fine as long as you have the table locked
down so if a user hacks in somehow they can't "tweak" the permissions
table to change their level of permissions within the app. As far as
SQL permissions go, each different "role" within your app (eg. clerk,
manager, administrator, etc.) should have a corresponding role within
the DB (see sp_addrole and sp_addrolemember in BOL) with the necessary
SQL permissions assigned to the DB roles.
It's best not to use the 'sa' login for anything. In fact, if you have
Windows Authentication only then you cannot login with 'sa' because it's
a SQL login (not a trusted login).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
ReTF wrote:
>Hi All,
>
>I'm development a system, and I have doubts about login (authorization and
>permissions)
>This system need be very secure.
>
>The systems have clients that connect in a web service and this web service
>connects in SQL Server.
>
>My doubt is:
>
>What is the best:
>
>Logins here is: Name and password(hash) and what each user can do.
>
>Store logins in one table in my database and always WebServise use SA
>account to manipulate data base?
>
>Use SQL logins for each users?
>
>Use Windows logins for each users?
>
>Thanks
>
>
--090003030806010808040200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The most secure option is to force all users to have their own
login and make them all trusted logins. Of course, there are typically
practical problems with that approach from web apps as the user that
hits the web server needs to be authenticated against AD for the
trusted SQL login from the web server to work properly and for
externally accessible web apps authenticating the users against AD is
often not possible. But purely from a SQL perspective trusted logins
are the most secure method of connecting. 
 
Storing passwords in your DB is never a good idea if solid security is
what you're aiming for. Storing localised application permissions in
some user table in your DB is fine as long as you have the table locked
down so if a user hacks in somehow they can't "tweak" the permissions
table to change their level of permissions within the app. As far as
SQL permissions go, each different "role" within your app (eg. clerk,
manager, administrator, etc.) should have a corresponding role within
the DB (see sp_addrole and sp_addrolemember in BOL) with the necessary
SQL permissions assigned to the DB roles. 
 
It's best not to use the 'sa' login for anything. In fact, if you have
Windows Authentication only then you cannot login with 'sa' because
it's a SQL login (not a trusted login). 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
ReTF wrote:
<blockquote cite="midO3KN$thaFHA.3048@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">Hi All,
I'm development a system, and I have doubts about login (authorization and
permissions)
This system need be very secure.
The systems have clients that connect in a web service and this web service
connects in SQL Server.
My doubt is:
What is the best:
Logins here is: Name and password(hash) and what each user can do.
Store logins in one table in my database and always WebServise use SA
account to manipulate data base?
Use SQL logins for each users?
Use Windows logins for each users?
Thanks
</pre>
</blockquote>
</body>
</html>
--090003030806010808040200--

Sunday, March 25, 2012

authorization and permissions

No comments:

Post a Comment

Attach remote DB file

Blog Archive

About Me