Sunday, October 5, 2014

2 SQL Server 2014 Security enhancement

Here are my notes for security enhancement in the SQL Server 2014.

New privilege

  • CONNECT ANY DATABASE:  User can connect to all the databases. This is same as to add the user to all the database but with one simple grant instead of adding user multiple times.
  • SELECT ALL USER SECURABLES: User can select all the objects including  the objects in the system databases. This is same as to grant db_reader to the user on all the database but with one simple grant instead of adding user multiple times
  • IMPERSONATE ANY LOGIN
USE [master]
GO
CREATE LOGIN [rob] WITH PASSWORD=N'Pass@word', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
grant CONNECT ANY DATABASE to rob
GO
grant SELECT ALL USER SECURABLES to rob
go
grant IMPERSONATE ANY LOGIN to rob
go
select * from sys.fn_my_permissions(NULL,NULL)

image

Below is what it is looks like when user only have CONNECT ANY DATABASE privilege

image

Below is what it is looks like when user have CONNECT ANY DATABASE and SELECT ALL USER SECURABLES privilege . If user does not have CONNECT ANY DATABASE  but only SELECT ALL USER SECURABLES, user is still not able to query the objects because it can not change the context to the specific database.

image

One existing permission CONTROL SERVER, if we grant this permission to the user, user would have ‘IMPERSONATE ANY LOGIN’, which means even user is not sysadmin, but they can impersonate as sysadmin and add themselves as sysadmin. The solution for this prior 2014 is we have to explicitly deny any impersonate when new sysadmin is added.

DENY IMPERSONATE ON LOGIN::sa TO [rob]

Obviously , this is not idea.  From SQL 2014 onward, we can just

DENY IMPERSONATE ANY LOGIN to rob

Server role

USE [master]
GO
CREATE SERVER ROLE [SQLMON_ROLE]
GO
GRANT ALTER TRACE TO  [SQLMON_ROLE]
GO
GRANT VIEW ANY DATABASE TO  [SQLMON_ROLE]
GO
GRANT VIEW SERVER STATE TO  [SQLMON_ROLE]
GO

image

Reference

2 comments:

  1. Thanks For sharing this Superb article.I use this Article to show my assignment in college.it is useful For me Great Work. www

    ReplyDelete
  2. Hi! Thanks for the great information you havr provided! You have touched on crucuial points! website

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates