Security through stored procedures

  • Thread starter Thread starter Rob Nicholson
  • Start date Start date
R

Rob Nicholson

I've just started reading "Microsoft ADO.NET" by David Sheppa. One of the
lines in the overview caught my eye:

"Because many database administrators allow users to modify the contents of
a database only through stored procedures, many developers cannot submit
updates through the ADO Recordset object".

This is new to me - does it mean that you could revoke UPDATE access to a
database table but allow EXEC access and that the code inside the sproc
carries out the update?

I rather like the idea of this. I've always had a problem with the conflict
between security and open access to a database. For example, our users have
UPDATE access to a table as they obviously have to write updates back.
However, this means that anyone with a little knowledge could use ODBC to
connect to the database and write whatever they want to the database table.
Which from a security/audit point of view isn't good. I was thinking about
adding an encryption code to each record so that whilst we couldn't stop
users changing data directly, we could at least detect when it had happened.

Another option was to carry out all updates via a service which had
different security rights but that's getting very messy, an probably very
inefficient.

Regards, Rob.
 
"Rob Nicholson" <rob.nicholson@NOSPAM_informed-direct.com>
wrote in message
I've just started reading "Microsoft ADO.NET" by David Sheppa. One of the
lines in the overview caught my eye:

"Because many database administrators allow users to modify the contents of
a database only through stored procedures, many developers cannot submit
updates through the ADO Recordset object".

This is new to me - does it mean that you could revoke UPDATE access to a
database table but allow EXEC access and that the code inside the sproc
carries out the update?

I rather like the idea of this. I've always had a problem with the conflict
between security and open access to a database. For example, our users have
UPDATE access to a table as they obviously have to write updates back.
However, this means that anyone with a little knowledge could use ODBC to
connect to the database and write whatever they want to the database table.
Which from a security/audit point of view isn't good. I was thinking about
adding an encryption code to each record so that whilst we couldn't stop
users changing data directly, we could at least detect
Click to expand...
when it had happened.

With SQL Server 2000 (I don't know about earlier versions),
you can do that. You can grant and revoke access rights for
each table and stored procedure for each user.

So you can forbid your users to access the table directly
and give them access to your stored procedures instead.

Markus
 
With SQL Server 2000 (I don't know about earlier versions),
you can do that. You can grant and revoke access rights for
each table and stored procedure for each user.
Click to expand...

So can SQL 7.
So you can forbid your users to access the table directly
and give them access to your stored procedures instead.
Click to expand...

So this means that an sproc runs with all access rights on a table no matter
what rights are assigned for the user against the table? Even when run using
NT authentication?

Cheers, Rob.
 
All versions of SQL Server have supported this feature--since the dawn of
time (when I taught SQL Server classes).

This approach has become an industry-standard way to protect the underlying
data. Yes, stored procedures can be granted execute permission to a specific
user, group or role while at the same time denying access to the root
tables. Most DBAs deny access to all of the root tables for updating, but
sometimes grant read-only access to lookup tables. However, if the query is
complex, a stored procedure (or View) is usually created to fetch the data
and access to the root table is denied. DBAs can also restrict access right
down to the column so some columns are available for reading, but not
writing. DBAs can also build protection mechanisms around Views using
similar mechanisms to restrict access. For example, a view could expose
columns from several tables leaving out those that you don't want to show to
chosen logins.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
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