RE: Windows NT Security

From: vishal subramaniam (vishalsu_at_microsoft.com)
Date: 05/05/04 

Date: Wed, 05 May 2004 04:12:41 GMT

vishalsu@online.microsoft.com

RESOLUTION
============

Stored Procedures offer an opportunity to improve your database security.
By denying read (SELECT) and write (INSERT, UPDATE, and DELETE) access to
all database tables and providing only EXECUTE privileges to your stored
procedures, you can ensure that your data can be accessed in a much more
controlled, restricted medium. Ad hoc SQL no longer will be able to access
your data. All data access must pass through stored procedures and be
subject to any validation, security, logging, etc. that you care to
implement at this layer.

Here's an example of how to do it.

1. Make a new login - in my case I'm using MyLogin. Check off the database
that you want to allow this login access to . Then check off public for the
database role.

2. By default, this login will not have permission to Insert, Select,
Update or Delete records in any tables. Your data can now be accessed only
via stored procedures that you have granted Execute permissions on. Test
your new login in Query Analyzer by trying to select on a table in the
database after you have logged in with the new username and password. This
will generate an error as expected.

SELECT * FROM sysobjects

Results …

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'suppliers', database 'Northwind', owner
'dbo'.

3 . Grant permission to Execute all stored procedures for this new login.
Create the Grant statements with the following SQL statement, copy the
results, and run them in Query Analyzer while logged in as SA (not as the
new login). Note: this SQL assumes you use some other prefix than "sp" to
name your own stored procedures. SP is reserved for System Stored
Procedures.

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with
the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

Results …

GRANT EXECUTE ON usp_ins_AUDIT_FIELD TO MyLogin
GRANT EXECUTE ON usp_del_TRANSACT TO MyLogin
GRANT EXECUTE ON usp_upd_AUDIT_FIELD TO MyLogin
GRANT EXECUTE ON usp_ins_TRANSACT TO MyLogin
GRANT EXECUTE ON usp_del_AUDIT_LOG TO MyLogin

4. As you add new procedures re-run the Grant scripts to make sure the new
login has Execute permissions on ALL stored procedures.

5. To help automate this process you can run the following stored
procedure. This will automatically add Execute permissions to all
non-system/VSS procedures in your database. You can tweak this code to
process only a subset of your stored procedures.

CREATE PROCEDURE prc_gen_CreateGrants AS

/* ------------------------------------------------------------
PROCEDURE: prc_gen_CreateGrants

DESCRIPTION: Grants Execute permissions on all procs in database
for Login MyLogin

AUTHOR: Brian Lockwood 3/15/00 5:38:48 PM
------------------------------------------------------------ */

DECLARE @ExecSQL varchar(100)

DECLARE curGrants CURSOR FOR

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with
the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

OPEN curGrants

FETCH NEXT FROM curGrants

INTO @ExecSQL

WHILE @@FETCH_STATUS = 0

BEGIN -- this will loop thru all your own procs and grant Execute
privileges on each one

Exec(@ExecSQL)
IF @@ERROR <> 0
BEGIN
RETURN 1 -- return 1 if there is an error
END

Print @ExecSQL

FETCH NEXT FROM curGrants INTO @ExecSQL

END

CLOSE curGrants
DEALLOCATE curGrants

LINKS
=====
Link for asp code with sql 2k that does the same is on:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/SecNetHT03.asp
============================================================================
=
 This posting is provided "AS IS" with no warranties, and confers no
rights.

Relevant Pages

  • Connecting a user from a backup database to a new login
    ... In SQL Server 2000, I loaded a backup file into a new local database. ... The database has an existing user which owns several stored procedures, ... I want to create a login of the same ...
    (microsoft.public.sqlserver.security)
  • Re: Security Using Access with asp.net
    ... I'm not sure, because the stored procedures used are already created, and I ... I suppose if you wanted to decompile the dlls for the Login ... saved in a SQL Express 2008 database. ... Why I ask, is because where I have my website, I am offered SQL server ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Adding Permissions
    ... every database and grant it rights to execute all stored procedures. ... Director of Text Mining and Database Strategy ... not using sa as the login. ...
    (microsoft.public.sqlserver.security)
  • Re: system stored procedures
    ... The stored procedures sp_MSdbuserpriv and sp_MSuseraccess ... legitimate system stored procedures. ... >standard login, give that login access to the user ... >database and also default the login to the user database. ...
    (microsoft.public.sqlserver.security)
  • Re: permissions required for executing CDOSys stored procedures
    ... it is possible to GRANT EXECUTE ON sp_OACreate TO ... I'm doing some analysis on the database applications in my organisation ... Apparently only members of the sysadmin role can execute ... these stored procedures, however, the sql login for this application is ...
    (microsoft.public.sqlserver.security)