Hiding SQL database structural design to developpers

[PROPAR]

Hello,

I'd like to hide database design to developpers and allow access to data only by a set of stored procs.

I want to avoid direct "SELECT * FROM" by the apps code.
In fact, I don't care about coders knowing the database structure, only not allow them to code by bypassing my interface encapsulating layer.

How can I do this neatly ?
(I'm using SQL Server 2005)

Thanks

 

[r937]

by granting permission only to the stored procs, and not to the tables

r937.com | rudy.ca

 

[r937]

mike, that's a slippery slope

should an order always belong to a valid customer? yes

how does one enforce this? with a foreign key

are you suggesting moving this from the "data tier" to the "app tier"?

r937.com | rudy.ca

 

[PROPAR]

I can't master the "WITH EXECUTE AS=" instruction.

Hereby is the situation :

I autorized SELECT, INSERT, DELETE... on <dbo.MyTable> for the user <builder>.
And I revoked those same rights for the user <application>.

I autorized execution on the stored proc <ReadMyTable> for <application> and put the "WITH EXECUTE.."
The stored proc read as this :
CREATE PROCEDURE [dbo].[ReadMyTable]
WITH EXECUTE AS 'builder'
AS
BEGIN
SELECT * FROM MyTable
END
GRANT EXECUTE ON [dbo].[ReadMyTable] TO [application]
GO

This doesn't work as I get an error on the SELECT in the execution of <ReadMyTable> on a <application> connection.
What am I doing wrong ?

 

[SQLSister]

Generally the prodution database will have security restricting data access, but I would grant more rights to the developers on the dev database to give them flexibility to find and fix the errors that need to be fixed or at least to report the necessary changes to the dba group. If they know that data access is only allowed through procs on production,they will write all the user code through stored procs. Generally this is a policy thing that devlopers know will be enforcesd and once you get them started doing things this way, they will continue. However, you will still get the idiots who want to write one proc to do everything and use dynamic SQL. Make sure that you have policies in place concerning dynamic SQl (although the restricted rights should take care of this) and the use of cursors as well or you will still get bad code. And let them know why this is a bad thing, most developers I've run into think cursors are a good thing until they get a little more experience on them.

Questions about posting. See faq183-874