SQL Server Security

  • Thread starter Thread starter Ed Crowley
  • Start date Start date
E

Ed Crowley

I have a client application written in VB.NET that I need to add security
to. To take a simple example, say I have an Employee table, some stored
procedures to read/write to and from this table and a class which uses these
stored procedures to pass data to the application layer. What I need to be
able to do is restrict what certain users can see and do, for example only
allow people from accounts to see other accounts employees and to only allow
some users read access while others can write.

What is the best way to implement this using good OO design practice? Do I
keep all the security in SQL Server using roles/groups or do I implement
some kind of security class?

I'm really stuck here ... I've never written anything secure in my life!

Thanks in advance.
 
There are several good books that discuss SQL Server security and SQL Server
Magazine is crawling with articles on the subject.
Generally, we setup several roles. I posted this reply the other day that
describes these roles. You're going to want to setup two or three (or more)
roles in SQL Server and if you use SSPI security (most folks recommend this
approach) you'll need to assign people (Windows Logins) to these roles. Yes,
Active Directory also has groups that people can be members of and you can
assign these groups to the roles to save time. Once you have the roles you
can assign permissions to the individual database objects on a case-by-case
basis. However, I would also do some reading or come to one of the upcoming
conferences that discuss this issue in more depth before wading into this.
VSLive in Orlando is next followed by Dev/SQL Server/ASP Connections in Palm
Springs (I'll be there).

<clip>
This highlights one of my new issues that I hope to get addressed in the
next version of VS.NET.
The problem is when you work with VS.NET as a developer, you're lead to the
assumption that the Login credentials you use as a developer are suitable
for the end user to use. We're also lead to believe that the DBA will grant
rights to virtually everything to developers without regard to the integrity
or security of the DBMS. Neither of these assertions are true--especially
not in bigger shops where there are dozens to hundreds of developers and
DBAs all pounding on each other and the DBMS.
In reality there are (at least) four different personas:
1) The SA who has full, unrestricted rights to everything. There should
be only one and the password should be hardened and protected.
2) The DBA(s) who have a subset of the full rights to everything that
pertains to their part of the DB.
3) The developer(s) who have a subset of the rights that pertain to
their application(s). This means developers need access to the tables needed
to construct the SPs (if they are given that responsibility), but perhaps
not DDL permission. That is, they can SELECT and change the data from/in the
tables but they can't change the table schema--that right is reserved for
the SA/DBA. Developers might (just might) be granted access to create
selected stored procedures and views as well as well as execute specific
SPs.
4) The end user who has (very) limited rights to specific SPs and Views,
and (rarely) the specific tables being accessed and then limited rights in
this case.

These "personas" can be setup with Active Directory so you can use SSPI
security to manage their roles. These are mirrored in SQL Server so when the
person logs in their rights are gated by the permissions established for
their role.

Unfortunately, VS.NET does not know how to set rights or permissions at this
point. I expect that will be addressed later but for now, we need to ensure
that we develop with the awareness that the application user must be granted
only those rights that are absolutely necessary to run the program--not
enough to develop it or to make schema changes to the DB or even data
changes to tables/views that are not in the scope of the program's
functionality.

<\clip>

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top