1 / 7

SQL Server 2008 – Security, Security, Security ….

SQL Server 2008 – Security, Security, Security …. Learningcomputer.com. Layers of Security. Security is a huge topic in any database platform. Many layers: Physical Security – Limiting access to the server and network (firewall). Remote Access to the server

ciqala
Download Presentation

SQL Server 2008 – Security, Security, Security ….

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2008 – Security, Security, Security…. Learningcomputer.com

  2. Layers of Security • Security is a huge topic in any database platform. Many layers: • Physical Security – Limiting access to the server and network (firewall). Remote Access to the server • OS Security – Using a secure OS with all the service packs • Surface Area – Beginning with SQL 2005, a lot of services are tuned off by default • For SQL service, using domain account with low privilges • File/Folder security especially program, data and backup files • Have a written security policy in place

  3. Security terminology • Principal – It is an entity that can request SQL Server resources. It can be at the server or database level • Role – A role in SQL Server is analogous to group in Windows. It is a collection of principals for easy management • Schema – It is a container of database objects like tables, views etc. Object are owned by schema and not users • Securables – Are the resources that can be accessed. Have a scope at the server, database or schema scope e.g. table • Certificates and Keys - Ways to use encryption

  4. A picture is worth a 1000 words … • I am using a picture from the MSDN website on SQL Server security concepts • Address is below • http://msdn.microsoft.com/en-us/library/bb545450.aspx

  5. More on Principals • Principal is an entity that can request SQL Server resources. It can be at the server or database level • Server level can have two levels – Windows or SQL Server • Windows can be local login or domain login • SQL Server can be SQL login • Best Practice is to use Window level principals only • Database-level principals • Database User • Database Role • Application Role

  6. Roles in SQL Server 2008 • A role in SQL Server is analogous to group in Windows. • It is a collection of principals for easy management • Two types of roles: Server and database roles • Server Roles • Server-level roles are also named fixed server roles because you cannot create new server-level roles. More in the demo • Database Roles • Makes it easier to manage the permissions in your databases • Two types: fixed database roles that are predefined in the database and flexible database roles that you can create. • DEMO

  7. Some important Transact SQL commands • Catalog views • sys.server_principals - Contains a row for every server-level principal. • sys.database_principals - Returns a row for each principal in a database • sys.database_role_members - Returns one row for each member of each database role. • Other TSQL • CREATE LOGIN • CREATE ROLE • CREATE SCHEMA • GRANT, DENY, REVOKE

More Related