Stored procedure ignores DENY INSERT
-
Here's a weird one. SQL 2k5, SP1. I have a SQL login that will be used to write stored procedures for reports. We're giving it perms in a user database to Select & Execute procs. But we want to make sure no data can be written to the database using this login, so we're denying Insert and setting db_denydatawriter.
If you look at the below code, it'll prevent me, as the user in question, from doing a direct insert. However, when I write a stored procedure to do the insertion for me, then execute the proc as said user, I'm able to insert. And I don't want to insert.
What am I missing? Can someone point me in the correct direction? Code is as follows:
use master;Gocreate login BrandieTest2 with Password = 'MyPassword';use MyDB1;GOCreate User BrandieTest2 For LOGIN BrandieTest2 With default_Schema = dbo;GOexec Sp_addrolemember db_datareader, 'BrandieTest2';exec Sp_addrolemember db_denydatawriter, 'BrandieTest2';GRANT ALTER ON SCHEMA::dbo TO BrandieTest2--Required for Create Procedure PermissionsGRANT CREATE PROCEDURE TO BrandieTest2GRANT SELECT, EXECUTE ON SCHEMA::dbo TO BrandieTest2Deny INSERT ON SCHEMA::dbo TO BrandieTest2Deny Insert to BrandieTest2GOuse tempdb;GOCreate User BrandieTest2 For LOGIN BrandieTest2 With default_Schema = dbo;GOGrant Create Table to BrandieTest2;use MyDB1;goExecute as User = 'BrandieTest2';Select 1 as FirstNum,5 as SecondNum, 'This is a test Temp Table' as MyTextinto dbo.#MyTemp;Insert into dbo.#MyTemp (FirstNum, SecondNum, MyText)Select 7,9,'Just another test';Select * from dbo.#MyTemp;Drop Table dbo.#MyTemp;Revert;Execute as User = 'BrandieTest2';GODrop Procedure uspTestingInserts;GOCreate Procedure uspTestingInsertsASInsert into dbo.MyLookupTable (Lookcode, LookType, LookDesc)Select 'ABCDE', 'Status','Brandie is testing again and again';GOExec uspTestingInsertsSelect * from dbo.MyLookupTable where LookType = 'Status';Revert; -
If the sproc and underlying table are owned by the same user(schema) such as dbo then the assumption is that you should be able to perform the functions that the sproc specifies.
This will be true unless the sproc uses dynamic SQL (at least it used to).
What all this means is that when the sproc runs SQL sees that the sproc and the table are both owned by DBO and so the individual users security is not checked..
CEWII
-
Oh, now I haven't tested other schemas here. And in the database we'll be doing this in, the procs will be a different schema from the tables.
Thanks. I'll check that out. But if anyone has any ideas on how to make the Deny Insert work for the same schema, I'd appreciate that too as I'll need it further down the road.
-
Denying insert and datawriter doesn't override the permission on the proc. If you grant execute rights on the proc, the assumption by the security engine is that you are granting the right to execute the whole proc.
A very common practice is to deny all rights in a database except the right to execute certain procs, then have those procs do all the DML actions. That would be impossible if deny datawriter overrode the execution of the proc.
If you really don't want a user to be able to insert data through a proc that inserts data, deny permissions on the proc. Or rewrite the proc, I guess.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Nope. That did NOT work. I even did a DENY INSERT on schema dbo and had the user's default schema as MySchema and had the proc saved in the schema MySchema and the user was still able to insert into a dbo table.
Bleargh.
-
Brandie Tarvin (12/2/2009)
Nope. That did NOT work. I even did a DENY INSERT on schema dbo and had the user's default schema as MySchema and had the proc saved in the schema MySchema and the user was still able to insert into a dbo table.Bleargh.
See my other reply above. Execute permission on the proc allows the user to execute the proc, including all actions the proc is designed to take. If that includes DML, then it allows it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (12/2/2009)
A very common practice is to deny all rights in a database except the right to execute certain procs, then have those procs do all the DML actions. That would be impossible if deny datawriter overrode the execution of the proc.If you really don't want a user to be able to insert data through a proc that inserts data, deny permissions on the proc. Or rewrite the proc, I guess.
The problem is, these procs are supposed to be SELECT procs that are used for reports. So if I deny execute on the proc, than the user can't execute the proc to get the report data back.
We have a new team of report writers. Their job is to create reports using stored procedures (the whole security thing). In order to keep them from "using their powers for evil"--by sneaking Inserts and Updates in their procs and causing bad data to get into our databases--we need to find a way to ensure the procs won't actually insert data into our tables.
Any thoughts?
-
I have to agree with the others, if you don't want the user to insert data, don't give them execute rights to the procedures that insert data into the tables.
-
I can't think of anyway to do it in the same schema short of building the logic into your sproc. The engine won't do it, the security model prevents that. And G is correct, it is very common to remove all permissions and then grant access to sprocs to provide data access.
I am a little leary of putting the sprocs in different schema from the tables they use. My main reason is tied to not wanting to grant any more permissions than are absolutely required..
CEWII
-
GSquared (12/2/2009)
Brandie Tarvin (12/2/2009)
Nope. That did NOT work. I even did a DENY INSERT on schema dbo and had the user's default schema as MySchema and had the proc saved in the schema MySchema and the user was still able to insert into a dbo table.Bleargh.
See my other reply above. Execute permission on the proc allows the user to execute the proc, including all actions the proc is designed to take. If that includes DML, then it allows it.
My understanding is that as soon as you allow someone access to create objects in your database you are opening a massive security hole. Your report writers could for example: [Edit] Do bad things that I won't explain [/Edit]
I think that one of the safest ways to operate is to not allow these report writers to create new objects and have them send them to you for approval / execution. Let them debug / create whatever they want in a development database that they have all kinds of rights on, and just give them extremely locked down rights on the real DB. This also has the advantage of you seeing the all code that goes into your server so that you don't glance at it 6 months down the road and find nested cursors everywhere.
-
Hrm, and now that I think about it, if I am right, I'm gonna go edit that back out of my post, don't want to give anyone any ideas :-D.
-
Brandie Tarvin (12/2/2009)
GSquared (12/2/2009)
A very common practice is to deny all rights in a database except the right to execute certain procs, then have those procs do all the DML actions. That would be impossible if deny datawriter overrode the execution of the proc.If you really don't want a user to be able to insert data through a proc that inserts data, deny permissions on the proc. Or rewrite the proc, I guess.
The problem is, these procs are supposed to be SELECT procs that are used for reports. So if I deny execute on the proc, than the user can't execute the proc to get the report data back.
We have a new team of report writers. Their job is to create reports using stored procedures (the whole security thing). In order to keep them from "using their powers for evil"--by sneaking Inserts and Updates in their procs and causing bad data to get into our databases--we need to find a way to ensure the procs won't actually insert data into our tables.
Any thoughts?
The usual solution to that is don't grant them any permissions at all in the production database. They write the procs in a test/dev environment, and then you review the procs and pass them on to production if they are following standards.
That keeps it secure, and also allows for quality reviews on the code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
"using their powers for evil".. That makes me grin..
CEWII
-
Actually, we don't allow anybody to write procs in production. We have a Dev environment, Test environment and QC environment to go through before things reach Production. Unfortunately, we're all human and don't always catch the things we're supposed to. @=)
Thanks, everyone. Someone else actually wanted this capability and I needed the double-check to prove that I wasn't crazy (which I am, but let's not get into that now). Now I can go back and say "It can't be done" and know that it just isn't a matter of something I missed.
-
Edited out the example of said evil. I don't think it is commonly known and I don't want to help it become so.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply