Re: Windows NT Security
From: Uri Dimant (urid_at_iscar.co.il)
Date: 05/05/04
- Next message: Sohail: "Re: Is This Possible?"
- Previous message: Uri Dimant: "Re: Queries timing out...how to update query timeout value?"
- In reply to: vishal subramaniam: "RE: Windows NT Security"
- Next in thread: B1j: "RE: Windows NT Security"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 5 May 2004 08:12:27 +0200
Vishal
As far as OP wants to use dynamic sql inside SP, so then he will not be able
to run the SP.
"vishal subramaniam" <vishalsu@microsoft.com> wrote in message
news:r8mgCdlMEHA.3464@cpmsftngxa10.phx.gbl...
>
> 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.
>
- Next message: Sohail: "Re: Is This Possible?"
- Previous message: Uri Dimant: "Re: Queries timing out...how to update query timeout value?"
- In reply to: vishal subramaniam: "RE: Windows NT Security"
- Next in thread: B1j: "RE: Windows NT Security"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
