Thursday, March 1, 2012

MS SQL: Hiding sys and INFORMATION_SCHEMA Objects

As of SQL 2005, Microsoft created a super-secret hidden database called mssqlsystemresource.  The master database is now really just a front for this database.  So in reality, these objects do not exist in master and as a user, you cannot access the mssqlsystemresource database.

However, there is a work around.  First a disclaimer, middleware such as ODBC rely on access to sub set of these tables, so once you restrict them from the user, you are also restricting them from ODBC (and other middleware).

Here is a simple example:

USE master

USE Junk
CREATE USER SecuredAccount FOR LOGIN SecuredAccount
-- Restrict the user from any DDL
EXEC sp_addrolemember N'db_datareader', N'SecuredAccount'
EXEC sp_addrolemember N'db_datawriter', N'SecuredAccount'
-- ----------------------+
USE master
CREATE USER SecuredAccount FOR LOGIN SecuredAccount
EXEC sp_addrolemember N'db_denydatawriter', N'SecuredAccount'
EXEC sp_addrolemember N'db_denydatareader', N'SecuredAccount'
--  Each individual object in the sys schema needs to be restricted
--  But this can be scripted, so its not too bad.
USE Junk
-- Tables and views can be restricted at the local database
DENY SELECT ON sys.all_objects TO SecuredAccountGO
USE master
-- Stored Procedures need to be blocked in the master database
DENY EXECUTE ON sys.sp_tables TO SecuredAccount
And as a test, created a simple table called and inserted four rows, and then log in as SecuredAccount and execute:
SELECT * FROM sys.all_objects;
EXECUTE sys.sp_tables
The results should be:
-- The first select returns the 4 rows correctly
(4 row(s) affected)
-- Everything else fails. Note the database name in the error messages.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'TABLES', database 'mssqlsystemresource', schema 'INFORMATION_SCHEMA'.
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'all_objects', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_tables, Line 1
The EXECUTE permission was denied on the object 'sp_tables', database 'mssqlsystemresource', schema 'sys'.

1 comment:

  1. dafabet | Sports Betting » Sportsbook in Israel
    bet365 Sportsbook is your starvegad new-found favorite among new 다파벳 and seasoned bettors in the Israelian sports 퍼스트 카지노 market. The sports betting market is thriving in