Updated:<\/b> March 2014<\/p>\nPreamble<\/h2>\n
This workbench shows, as simply as possible, how to tighten security a great deal in a SQL Server database by implementing a schema-based security system. SQL Server Security is sometimes rather a blind spot tapplication developers. This has been widely illustrated by the success of very simple attacks on database-driven websites, which would have been prevented by even moderate security measures.<\/p>\n
In this workbench we will show how to do simple schema-based security and show how it can, to a great extent, prevent SQL Injection. We’ll also show how, with careless code within a stored procedure, you can still accidentally introduce a vulnerability.<\/p>\n
It is impossible, purely from the database layer of an application alone, to prevent SQL injection attacks happening, but you can, with care, render them harmless. If you are accustomed to give your application logins DBO rights in your database, then it is time to tighten security.<\/p>\n
We’ll illustrate schema-based security by creating a simple database, creating an example of all the common database objects<\/p>\n
Preventing any damage through SQL Injection is a relatively simple matter. You should prevent any direct access to the base tables by the application. All database access should be through a set of stored procedures, views and functions. This can make things trickier for programmers in the short-term, but the result will be a secure database, and one that is easier to refactor. The neatest way of doing this is by Schema-based security. In previous versions of SQL Server, it had to be done by assigning permissions to the application-interface explicitly, but now you can do this just by placing the routines (aka Modules) that make up the application interface into a schema that is made for the purpose.<\/p>\n
Firstly create a database called For this part of the exercise, log in as database owner and execute the following section. This builds a couple of base tables and puts some pretty sensitive information in one of them that you wouldn’t want anyone but the sysadmin to see. Then we’ll create various modules that will each access the data and extract just safe parts of it. We’ll create a chain to show ownership chaining. We’ll also show how to use dynamic SQL safely, and how to do it recklessly and stupidly in a way that allows access to the base tables even when you have no direct permissions to Delete the table we create if it exists, so we can do it over and again.<\/p>\n The And we’ll now add some data to it.<\/p>\n Now we’ll add an address table in dbo schema. First we delete it if it exists, so we can do this test over and again.<\/p>\n Create the address table in SQL Injection usually comes through the failure of the application programmer to filter the input from the user to ‘escape’ or otherwise change SQL string-delimiters in input from the user, or pass numbers unvalidated. So, when a user types in Instead of using parameters, the progreammer does it simply by concatenating the components of the SQL String together. If so then it is the work of a moment for a hacker to type the following into the postcode field of the application.<\/p>\n Instead of:<\/p>\n He types …<\/p>\n … which is then automatically converted into:<\/p>\n With obvious results, and no apparent error.<\/p>\n Let’s simulate this entirely in SQL just to show you how easily this happens. Firstly we’ll put in innocent parameters and then some malicious ones.<\/p>\n Now we are going to show how you can avoid this sort of thing happenening, and a lot more besides, but implementing schema-based security. Firstly, we’ll add a schema, put some objects in it, and show how one can prevent sensitive data being accessed from the table.<\/p>\n In our database we create a database user. We will log in as And in reality, there will be a lot of them so we’d want to create a database role that we will assign to our schema by default, with Now, the most important component, is a schema that acts as an application interface.<\/p>\n Now we create a stored procedure in the Now it is likely that we will want to allow the application access to a view of the customer information without the sensitive information. We’ll also create a procedure in the And now we do a stored procedure in the And now we do a second stored procedure in the And, we do a function the And a view that encapsulates a function just to test out a short chain:<\/p>\n Now we create a function that is vulnerable to SQL Injection.<\/p>\n Note that our postcode should not be 80 characters. 16 chars internationally is safe. Obviously, in reality, you’d always validate all your parameters.<\/p>\n To inject, try…<\/p>\n … but you could do it by injecting in any parameter – if you have the patience.<\/p>\n But it is very rare that you’d need to do this. Dynamic pivots, maybe, but not for the general run.<\/p>\n Now the test database is constructed, we can use it to try out different security models.<\/p>\n You have two approaches to testing various ideas out. You can use two query windows or just one.<\/p>\n For the two-window approach…<\/strong><\/p>\n Firstly, open up a new window in SSMS or Query Manager, but using the To execute this following code, you must be logged in as You can try out the following \u2013 paste it all into the new window. Don’t execute it in this window!<\/p>\n For the single-query window approach use this….<\/strong><\/p>\n We’ll use a loginless user to test out security. These login-less users are strange characters that exist as automata either to provide a convenient owner of a schema and objects used in any given application in parts of a database, break or create chains, or to mark modules with Here is a simple example.<\/p>\n And here is the snag. We are still vulnerable to SQL Injection at the database level if we try to assemble dynamic SQL rather than use parameters properly.<\/p>\n Ownership-chaining bypasses permission checks entirely, and even takes precedence over So let’s demonstrate this by creating a schema.<\/p>\n Robyn Page and Phil Factor present practical T-SQL techniques for controlling access to sensitive information within the database, and preventing malicious SQL injection attacks.…<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4698,4168,4662,4699,4700,4619,4179,4150,4697,4151,4696,4183,4252,4460],"coauthors":[6813,6814],"class_list":["post-234","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-access-control","tag-database","tag-database-roles","tag-denydatareader","tag-denydatawriter","tag-security","tag-source-control","tag-sql","tag-sql-injection","tag-sql-server","tag-sql-server-security","tag-t-sql","tag-t-sql-programming","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/234","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=234"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/234\/revisions"}],"predecessor-version":[{"id":77225,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/234\/revisions\/77225"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=234"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}SecurityWorkbench<\/code> for the workbench just to try things out. Then…<\/p>\n use SecurityWorkbench;\r\n<\/pre>\n
SELECT<\/code> from it.<\/p>\nBuilding the database<\/h2>\n
if exists (Select * from information_schema.tables\r\n where TABLE_NAME like 'Customer'\r\n and TABLE_SCHEMA= 'dbo')\r\n drop table Customer; \r\n <\/pre>\n
customer<\/code> table in the dbo<\/code> schema. In reality, we probably wouldn’t do this, of course but it serves for a demo.<\/p>\n CREATE TABLE dbo.Customer(\r\n Customer_ID int IDENTITY NOT NULL PRIMARY KEY,\r\n Firstname varchar(50) NULL,\r\n Surname varchar(50) NOT NULL,\r\n Password varchar(50) NULL,\r\n [User_ID] varchar(20) NOT NULL,\r\n CreditCardNo char(16) NULL,\r\n SortCode varchar(20) NULL,\r\n AccountNo varchar(20) NULL,\r\n InsertionDate datetime NOT NULL default GETDATE()\r\n ) ON [PRIMARY];\r\n go\r\n<\/pre>\n
--pop some spoof data into the table....\r\n INSERT INTO dbo.Customer\r\n (Firstname, Surname, USER_ID, Password,\r\n Creditcardno, SortCode, AccountNo)\r\n SELECT 'Joe', 'McTavish','Foo','plasticShoe',\r\n '7666923165777980','23-45-67','040592739';\r\n INSERT INTO Customer\r\n (Firstname, Surname, USER_ID, Password,\r\n Creditcardno, SortCode, AccountNo)\r\n SELECT 'Lars', 'Porsenna','Abe','ninegods',\r\n '5960711184930897','76-54-23','014354678';\r\n INSERT INTO Customer\r\n (Firstname, Surname, USER_ID, Password,\r\n Creditcardno, SortCode, AccountNo)\r\n SELECT 'Abou', 'Ben-Adam','Tribe','increase',\r\n '9807493817364950','08-48-37','003948673';\r\n INSERT INTO Customer\r\n (Firstname, Surname, USER_ID, Password,\r\n Creditcardno, SortCode, AccountNo)\r\n SELECT 'Phil', 'Factor','jig','flutersball',\r\n '7666923165777980','22-45-44','020594835';\r\n Go\r\n <\/pre>\n
if exists (Select * from information_schema.tables\r\n where TABLE_NAME like 'Address'\r\n and TABLE_SCHEMA= 'dbo')\r\n drop table dbo.Address; \r\n go \r\n <\/pre>\n
dbo<\/code> schema.<\/p>\n Create table dbo.Address (\r\n Address_ID int identity NOT NULL PRIMARY KEY,\r\n FirstLineAddress varchar(50) NOT NULL,\r\n SecondLineAddress varchar(50) NULL,\r\n Town varchar(50) NOT NULL,\r\n County varchar(50) NOT NULL,\r\n PostCode Varchar(12) NOT NULL);\r\n Go \r\n <\/pre>\n
SQL Injection<\/h2>\n
CM5 4RS<\/code> for his postcode, then the application programmer might take it into his or her head to construct, on the fly, the SQL string…<\/p>\n \r\n INSERT INTO address (FirstLineAddress, SecondLineAddress, Town, County, PostCode)\r\n VALUES ('The acacias','Holly Avenue', 'Good Easter', 'Chelmsford' ,'CM5 4RS');\r\n <\/pre>\nCM5 4RS<\/pre>\n
CM5 4RS') Select * from customer select ('<\/pre>\n INSERT INTO address (FirstLineAddress, SecondLineAddress, Town, County, PostCode)\r\n VALUES ('The acacias','Holly Avenue', 'Good Easter', 'Chelmsford' ,'CM5 4RS') Select * from customer select ('');\r\n <\/pre>\n Go\r\n Declare @FirstLineAddress varchar(50), @SecondLineAddress varchar(50),@Town varchar(50),@County varchar(50), @Postcode varchar(12)\r\n\r\n --we put in a legitimate postcode\r\n Select @FirstLineAddress='The Hall', @SecondLineAddress='Factor Road', @Town='Little Snoring',@County='Suffolk', @PostCode ='C09 5RT'\r\n\r\n --this code is soooo Baaaad\r\n Execute ('INSERT INTO address (FirstLineAddress, SecondLineAddress, Town, County, PostCode)\r\n VALUES ('''+@FirstLineAddress+''','''+@SecondLineAddress+''','''+\r\n @Town+''','''+@County+''','''+@Postcode+''')');\r\n --seems OK, the row got inserted and ...\r\n -- (1 row(s) affected) \r\n \r\n go\r\n Declare @FirstLineAddress varchar(50), @SecondLineAddress varchar(50),@Town varchar(50),@County varchar(50), @Postcode varchar(80)\r\n\r\n --we put in a SQL Injection postcode\r\n Select @FirstLineAddress='The Hall', @SecondLineAddress='Factor Road',\r\n @Town='Little Snoring',@County='Suffolk', @PostCode ='C09 5RT'') Select * from customer select (''';\r\n\r\n --this is going to end in tears!\r\n Execute ( 'INSERT INTO address (FirstLineAddress, SecondLineAddress, Town, County, PostCode)\r\n VALUES ('''+@FirstLineAddress+''','''+@SecondLineAddress+''','''+\r\n @Town+''','''+@County+''','''+@Postcode+''')');\r\n --ouch! All the passwords and credit card numbers\r\n <\/pre>\nWebsiteUser<\/code> with what we hope will be the ability to access just the data that he or she is entitled to but no more. (it is worth doing a windows user as well, to test that too.).<\/p>\n \r\n if not exists (select name from sys.syslogins where name like 'Workbench') \r\n Raiserror ('you''ll need a login called ''Workbench'' for this test',16,1)\r\n if exists (select name from sys.sysusers where name like 'WebsiteUser') drop user WebsiteUser\r\n go\r\n CREATE USER [WebsiteUser] FOR LOGIN [Workbench] WITH DEFAULT_SCHEMA=[WidgetShopSite];\r\n --CREATE USER [Tom] FOR LOGIN [MyDomain\\Tom] WITH DEFAULT_SCHEMA=[WidgetShopSite]\r\n GO\r\n <\/pre>\nSELECT<\/code> and EXECUTE<\/code> permissions.<\/p>\n IF DATABASE_PRINCIPAL_ID('AllWebsiteUsers') IS not NULL drop role AllWebsiteUsers\r\n go\r\n CREATE ROLE [AllWebsiteUsers];\r\n go\r\n <\/pre>\n IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'WidgetShopSite')\r\n BEGIN\r\n -- The schema must be run in its own batch!\r\n EXEC( 'CREATE SCHEMA WidgetShopSite' );\r\n END\r\n go\r\n -- add role member WebsiteUser to the role\r\n EXEC sp_addrolemember N'AllWebsiteUsers', N'WebsiteUser'\r\n --Now we allow our role to execute or select anything in the schema\r\n GRANT EXECUTE ON SCHEMA::[WidgetShopSite] TO [AllWebsiteUsers];\r\n GRANT SELECT ON SCHEMA::[WidgetShopSite] TO [AllWebsiteUsers];\r\n GO\r\n <\/pre>\n
WidgetShopSite<\/code> schema that checks the application’s User ID and password for their website users, so it is unnecessary to expose the information outside the database. We wont give it any permission to the table but because of ownership chaining, the code can access the table. The user has no permission to alter the procedure so it is safe.<\/p>\n \r\n if exists (Select * from information_schema.routines\r\n where ROUTINE_NAME like 'spLogMeIn'\r\n and ROUTINE_SCHEMA= 'WidgetShopSite')\r\n drop PROCEDURE WidgetShopSite.spLogMeIn; \r\n go -----\r\n CREATE PROCEDURE WidgetShopSite.spLogMeIn\r\n @User_ID VARCHAR(50),\r\n @Password VARCHAR(50),\r\n @Success INT output\r\n AS\r\n BEGIN\r\n SET NOCOUNT ON;\r\n SELECT @success = CASE WHEN EXISTS (SELECT 1 FROM dbo.customer\r\n WHERE [user_ID] =@User_ID AND Password=@password\r\n ) THEN -1 ELSE 0 END;\r\n END;\r\n <\/pre>\n
\n Here is a simple view in the WidgetShopSite <\/code>schema to illustrate such a view….<\/p>\n Go\r\n if exists (Select * from information_schema.tables\r\n where TABLE_NAME like 'vCustomer'\r\n and TABLE_SCHEMA= 'WidgetShopSite'\r\n and TABLE_TYPE='VIEW')\r\n drop VIEW WidgetShopSite.vCustomer; \r\n go\r\n CREATE VIEW WidgetShopSite.vCustomer\r\n AS\r\n SELECT Customer_ID, Firstname, Surname, USER_ID, InsertionDate\r\n FROM dbo.Customer\r\n WITH CHECK OPTION --prevent untoward changes via an update\r\n \r\n GO\r\n <\/pre>\n
WidgetShopSite<\/code> schema that returns a table that would provide a result. This will just serve to prove to you that it works.<\/p>\n if exists (Select * from information_schema.routines\r\n where ROUTINE_NAME like 'Customer'\r\n and ROUTINE_SCHEMA= 'WidgetShopSite')\r\n drop PROCEDURE WidgetShopSite.Customer; \r\n go -----\r\n CREATE PROCEDURE WidgetShopSite.Customer\r\n AS\r\n SELECT Customer_ID, Firstname, Surname, USER_ID, InsertionDate\r\n FROM dbo.Customer;\r\n \r\n GO\r\n <\/pre>\n
WidgetShopSite<\/code> schema which uses ‘Dynamic’ SQL. We’ll do it to show that this won’t access the table even though the role we’ve created has permission to execute this.<\/p>\n if exists (Select * from information_schema.routines\r\n where ROUTINE_NAME like 'CustomerWithDynamicSQL'\r\n and ROUTINE_SCHEMA= 'WidgetShopSite')\r\n drop PROCEDURE WidgetShopSite.CustomerWithDynamicSQL; \r\n go\r\n CREATE PROCEDURE WidgetShopSite.CustomerWithDynamicSQL\r\n AS\r\n EXECUTE ('SELECT Customer_ID, Firstname, Surname, User_ID,\r\n InsertionDate FROM dbo.Customer');\r\n GO\r\n <\/pre>\nWidgetShopSite<\/code> schema which uses ‘Dynamic’ SQL. This time, we do it to show that this will successfully access the table even though the role we’ve created has permission to execute this. This may look dangerous, but the attacker can’t alter the code and there are no parameters anyway that he could use. Yes, it is safe.<\/p>\n if exists (Select * from information_schema.routines\r\n where ROUTINE_NAME like 'CustomerWithDynamicSQLAsOwner'\r\n and ROUTINE_SCHEMA= 'WidgetShopSite')\r\n drop PROCEDURE WidgetShopSite.CustomerWithDynamicSQLAsOwner; \r\n go -----\r\n CREATE PROCEDURE WidgetShopSite.CustomerWithDynamicSQLAsOwner\r\n with execute as owner --don't use if there is any possibility of injection\r\n AS\r\n EXECUTE ('SELECT Customer_ID, Firstname, Surname, User_ID,\r\n InsertionDate FROM dbo.Customer');\r\n go\r\n <\/pre>\nWidgetShopSite<\/code> schema just to see if that provides the right access successfully.<\/p>\n \r\n if exists (Select * from information_schema.routines\r\n where ROUTINE_NAME like 'TheCustomer'\r\n and ROUTINE_SCHEMA= 'WidgetShopSite'\r\n and ROUTINE_TYPE='FUNCTION')\r\n drop function WidgetShopSite.TheCustomer; \r\n go -----\r\n \r\n CREATE FUNCTION WidgetShopSite.TheCustomer\r\n (\r\n )\r\n RETURNS\r\n @Results TABLE\r\n (\r\n [Customer_ID] [int] ,\r\n [Firstname] [varchar](50),\r\n [Surname] [varchar](50),\r\n [User_ID] [varchar](20),\r\n [InsertionDate] [datetime]\r\n )\r\n AS\r\n BEGIN\r\n INSERT INTO @Results\r\n (Customer_ID, Firstname,Surname,[User_ID],InsertionDate)\r\n SELECT Customer_ID, Firstname,Surname,[User_ID],InsertionDate\r\n FROM dbo.customer;\r\n RETURN;\r\n END;\r\n GO\r\n <\/pre>\n
if exists (Select * from information_schema.tables\r\n where TABLE_NAME like 'vCustomerViaFunction'\r\n and TABLE_SCHEMA= 'WidgetShopSite'\r\n and TABLE_TYPE='VIEW')\r\n drop VIEW WidgetShopSite.vCustomerViaFunction; \r\n go\r\n \r\n CREATE VIEW WidgetShopSite.vCustomerViaFunction\r\n AS\r\n SELECT Customer_ID, Firstname, Surname, USER_ID, InsertionDate\r\n FROM WidgetShopSite.TheCustomer();\r\n \r\n GO\r\n <\/pre>\n
if exists (Select * from information_schema.routines\r\n where ROUTINE_NAME like 'vulnerableCode_DontUse'\r\n and ROUTINE_SCHEMA= 'WidgetShopSite'\r\n and ROUTINE_TYPE='PROCEDURE')\r\n drop procedure WidgetShopSite.vulnerableCode_DontUse; \r\n go -----\r\n Create procedure WidgetShopSite.vulnerableCode_DontUse\r\n @FirstLineAddress varchar(50), @SecondLineAddress varchar(50),@Town varchar(50),@County varchar(50), @Postcode varchar(80)\r\n <\/pre>\n
Execute WidgetShopSite.vulnerableCode_DontUse @FirstLineAddress='The Hall', @SecondLineAddress='Factor Road',\r\n @Town='Little Snoring',@County='Suffolk', @PostCode ='C09 5RT'') Select * from customer select ('''\r\n <\/pre>\n with execute as owner\r\n Execute ( 'INSERT INTO address (FirstLineAddress, SecondLineAddress, Town, County, PostCode)\r\n VALUES ('''+@FirstLineAddress+''','''+@SecondLineAddress+''','''+\r\n @Town+''','''+@County+''','''+@Postcode+''')');\r\n \r\n go\r\n \r\n if exists (Select * from information_schema.routines\r\n where ROUTINE_NAME like 'SaferDynamicSQL'\r\n and ROUTINE_SCHEMA= 'WidgetShopSite'\r\n and ROUTINE_TYPE='PROCEDURE')\r\n drop procedure WidgetShopSite.SaferDynamicSQL; \r\n go \r\n Create procedure WidgetShopSite.SaferDynamicSQL\r\n @FirstLineAddress varchar(50), @SecondLineAddress varchar(50),@Town varchar(50),@County varchar(50), @Postcode varchar(16)\r\n<\/pre>\n \r\n Execute WidgetShopSite.SaferDynamicSQL @FirstLineAddress='The Hall', @SecondLineAddress='Factor Road',\r\n @Town='Little Snoring',@County='Suffolk', @PostCode ='C09 5RT'') Select * from customer select ('''\r\n \r\n <\/pre>\n with execute as owner\r\n as\r\n execute sp_ExecuteSQL N'INSERT INTO address (FirstLineAddress, SecondLineAddress, Town, County, PostCode)\r\n VALUES (@FLAddress,@SLAddress,@TheTown,@TheCounty,@ThePostcode)',\r\n N'@FLAddress varchar(50),@SLAddress varchar(50),@TheTown varchar(50),@TheCounty varchar(50),@ThePostcode varchar(80)',\r\n @FLAddress=@FirstLineAddress, @SLAddress=@SecondLineAddress, @TheTown=@town, @TheCounty=@county, @ThePostcode=postcode;\r\n go\r\n <\/pre>\n
Trying out the Test harness<\/h2>\n
workbench<\/code> login ID and mypassword<\/code> password (right-click, press ‘connection…’ in the pop-up and then ‘Change Connection …’).<\/p>\nWorkBench<\/code> (password: mypassword<\/code>). Don’t execute it whilst logged in as DBO!<\/p>\nEXECUTE AS<\/code> and granting only permissions to these principals.<\/p>\n create user fred without login\r\n go\r\n execute as user = 'fred'\r\n go\r\n select '\"I knew about loginless users, but have never needed them,\" said ' + user_name() + ' pensively. I did feel as if i was missing something in my life.'\r\n go\r\n revert\r\n go\r\n CREATE USER [The_Proletariat] WITHOUT LOGIN WITH DEFAULT_SCHEMA= WidgetShopSite;\r\n EXEC sp_addrolemember N'AllWebsiteUsers', N'The_Proletariat';\r\n -- Step 3 : Checking access to Tables views etc.\r\n SELECT schema_name(schema_id)+'.'+name, Type_desc, type\r\n FROM sys.objects\r\n where schema_name(schema_id) <>'sys';\r\n -- Step 4 : Changing the execution context\r\n EXECUTE AS USER = 'The_Proletariat';\r\n GO\r\n Select Schema_Name(); \/* should be 'WidgetShopSite' if you are 'The_Proletariat' or 'dbo' if you are still there as DBO. To switch to 'The_Proletariat' execute EXECUTE AS USER = 'The_Proletariat'; and to go back to being DBO then execute REVERT *\/\r\n \r\n USE SecurityWorkbench;\r\n -- can we use a view?\r\n SELECT * FROM vcustomer ;\r\n \r\n -- what about executing a stored procedure that returns the (censored)\r\n -- data from the table\r\n EXECUTE Customer;\r\n \r\n \/*can we access a view that accesses a function in the schema tha then access the table *\/\r\n Select * from vCustomerViaFunction --slides down the object chain\r\n \r\n -- can we access a table function?\r\n SELECT * FROM WidgetShopSite.TheCustomer() WHERE surname LIKE 'factor';\r\n \r\n -- or use a view containing a table function?\r\n SELECT * FROM vCustomerViaFunction;\r\n \r\n -- and lastly, can we, without having any table access, check a\r\n -- User_ID and password\r\n DECLARE @success INT\r\n EXECUTE spLogMeIn @user_id='jig',@password='flutersball',\r\n @success=@success out --fixed 9\/3\/2007\r\n SELECT @success\r\n \r\n -- Can you use a stored procedure that contains dynamic SQL. If not,\r\n -- then why not (The answer is in the SQL Server Security Cribsheet)\r\n EXECUTE CustomerWithDynamicSQL;\r\n \/* nope\r\n Msg 229, Level 14, State 5, Line 1\r\n The SELECT permission was denied on the object 'Customer', database 'SecurityWorkbench', schema 'dbo'.\r\n *\/\r\n EXECUTE CustomerWithDynamicSQLAsOwner;\r\n<\/pre>\n
Execute WidgetShopSite.vulnerableCode_DontUse @FirstLineAddress='The Hall', @SecondLineAddress='Factor Road',\r\n @Town='Little Snoring',@County='Suffolk', @PostCode ='C09 5RT'') Select * from customer select ('''\r\n \r\n \r\n -- can we access a table directly?\r\n SELECT * FROM customer;\r\n \/* Nope\r\n Msg 229, Level 14, State 5, Line 1\r\n The SELECT permission was denied on the object 'Customer', database 'SecurityWorkbench', schema 'dbo'.\r\n *\/\r\n \r\n -- or insert into a view?\r\n INSERT INTO vcustomer (firstname, surname,[user_ID]) \r\n SELECT 'Akund','Swat','Who' ;\r\n \/* Nope. Good\r\n Msg 229, Level 14, State 5, Line 1\r\n The INSERT permission was denied on the object 'vCustomer', database 'SecurityWorkbench', schema 'WidgetShopSite'.\r\n \r\n *\/\r\n -- or indulge in wickedness?\r\n master..xp_cmdshell 'Dir c:\\';\r\n --The EXECUTE permission was denied on the object 'xp_cmdshell',\r\n DROP TABLE dbo.customer;\r\n --Cannot drop the table 'Customer', because it does not exist or you do not have permission.\r\n DELETE FROM dbo.customer;\r\n --The DELETE permission was denied on the object 'Customer', database 'SecurityWorkbench', schema 'dbo'.\r\n SELECT * FROM information_schema.tables\r\n EXECUTE sp_help; --you should only see the objects you have permissions for\r\n EXECUTE sp_who ; --you should only see yourself\r\n KILL 51; \/* should be Msg 6102, Level 14, State 1, Line 1 User does not have permission to use the KILL statement.*\/\r\n REVERT;\r\n <\/pre>\nDENY ACCESS<\/code> for all links but the first. If you wish to deny a principal usage of a chain, then you must DENY ACCESS<\/code> at the start of the chain.<\/p>\n create schema fred\r\n go\r\n\r\n -- and a sample table with data.\r\n\r\n create table fred.tableName\r\n (\r\n value varchar(20)\r\n )\r\n insert into fred.tableName values ('fred')\r\n go\r\n\r\n --we now create a stored procedure in the directory\r\n create procedure fred.tablenameQuery\r\n as\r\n select *\r\n from fred.tablename\r\n go\r\n\r\n --and we create a login-less user to test with\r\n create user fred without login with default_schema = fred\r\n go\r\n\r\n --we will prevent our user from direct access to the table\r\n deny select on fred.tableName to fred\r\n\r\n -- ... but allow Fred to access the stored procedure that accesses the table\r\n grant execute on fred.tablenameQuery to fred\r\n go\r\n\r\n --for the demo, we take on Fred's persona\r\n execute as user = 'fred'\r\n go\r\n select * from fred.tablename\r\n \/* \r\n Msg 229, Level 14, State 5, Line 1\r\n The SELECT permission was denied on the object 'tableName', database 'SecurityWorkbench', schema 'fred'.\r\n *\/\r\n go\r\n\r\n -- but we try using the stored procedure which just accesses the table denied to Fred\r\n exec fred.tablenameQuery;\r\n \/*\r\n\r\n ---and it works! Fred can get to that table\r\n value\r\n --------------------\r\n fred\r\n *\/\r\n go\r\n revert\r\n go\r\n\r\n \/* and tear-down this experiment. *\/\r\n drop procedure fred.tablenameQuery\r\n drop table fred.tableName\r\n drop user fred\r\n drop schema fred\r\n <\/pre>\n","protected":false},"excerpt":{"rendered":"