THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

Insecure Metadata

I was looking at some metadata access today and this reminded me of when I talked about this topic two years ago at the PASS Summit in Denver.

 

Back in SQL Server 2000 most of the existing metadata was available to members of the public role, that is, to any login that can connect to the SQL Server instance. SQL Server 2005 promises that a user can only see the metadata of an object the user owns or has access to. This was a huge security improvement. However, some items were still left available to any user, which in some cases could be a security concern.

 

Let us test some of it. Create a new login called insecure without granting any permission to it. Connect as that user and see what kind of information from the server you are able to obtain. Try this first

 

select * from sys.databases

 

This new user is able to see the list of all the databases on the server, including the ones he does not have access to. Try

 

select * from sys.configurations

 

This time you can see some configuration information of your instance including some specific security information like if the ‘c2 audit mode’ is enabled.

 

Some other data available to any SQL Server user is information about the location of the files of the current database, linked servers or backup devices. Try these

 

select * from sys.database_files

select * from sys.servers

select * from sys.backup_devices

 

Here I am using catalog views only but the same applies if you use the SQL Server 2000 compatibility views (like sysdatabases). You can also execute some stored procedures like these to get similar information

 

sp_helpdb 'master'

exec sp_configure

exec sp_helpfile

exec sp_helpserver

exec sp_helpdevice

 

Now let us look at the msdb database. By looking at the following system tables (yes, these are called system tables) you can obtain all the information regarding backup and restore operations performed on your instance. You can obtain information like database names, location of databases files, locations of database backup files and names of users who performed these backups.

 

use msdb

go

select * from backupfile

select * from backupmediafamily

select * from backupset

select * from restorefile

select * from restorehistory

 

You can also run some extended stored procedures or functions like the following

 

exec master..xp_msver

select serverproperty('ProductVersion')

select serverproperty('IsIntegratedSecurityOnly')

 

For example, the first two display the version (build) of SQL Server, which can show if the database administrator has installed the latest service pack or security updates. This can reveal some known security vulnerability of your instance.

 

Perhaps you can see now that this information, like security configuration, database names, database file names, location of database backups, user names, etc. could be used by an attacker to get additional permissions or data.

 

Certainly these are the defaults and many of them can be changed to be more secure, but many times this is not practical as some tools and applications depend on these defaults. For example, you can revoke the VIEW ANY DATABASE permission (executed as member of sysadmin) to secure the information displayed on sys.databases.

 

revoke view any database to public

 

But now only database owners can see their databases listed on sys.databases. Of course, this is a problem for a valid user who is not a database owner, who will only see the master and tempdb databases on this list. To test, grant db_datareader permissions to the insecure user to any user database. Test this again connected as the insecure login

 

select * from sys.databases

 

Only the master and tempdb databases are listed this time. One known issue of this configuration is that you can not use ODBC Data Source Administrator to create a new data source to connect to a database you have access to (unless you are the database owner)

 

clip_image002

 

 

Even Management Studio does not list all the databases you have access to (but you can use the USE statement)

 

 

clip_image004

 

 

But what is still ironic is that, even when you can not use ODBC Data Source Administrator or list your databases in Management Studio, you can still use db_name() to list all the database names anyway like in

 

select db_name(5)

 

Finally, note that a command introduced with SQL Server 2005, VIEW DEFINITION, could also help you to have more control on the access to SQL Server metadata.

Published Wednesday, November 18, 2009 2:00 AM by Ben Nevarez
Filed under: ,
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement