Transcript Chapter 9- security

Chapter 9
Security
Endpoints
 A SQL Server endpoint is the point of entering into SQL Server.
 It is implemented as a database object that defines the ways and
means in which SQL Server may communicate over the network.
 An endpoint has a transport, (either HTTP or TCP).
 You also specify a 'payload', which is one of TSQL, Service_Broker,
Database_Mirroring, or SOAP.
 SOAP must use HTTP, and the others must use TCP.
 The endpoints, other than TSQL, have a number of special-purpose
properties that define the way that the service using them
communicates, and operates.
 Endpoints can be created and managed and dropped with CREATE
ENDPOINT, ALTER ENDPOINT and DROP ENDPOINT statements.
More on endpoints
 CREATE ENDPOINT [MyFirstUserConnection]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1680, LISTENER_IP =114.221.1.1)
FOR TSQL() ;
 Permission to connect to the default System endpoints is
implicitly granted to users when logins are created.
 To see the list of endpoints
• SELECT * FROM sys.endpoints
Endpoint categories
sys.endpoints
All endpoints and all generic properties
sys.database_mirroring_endpoints
The Database Mirroring endpoints
sys.service_broker_endpoints
The Service Broker endpoints
sys.soap_endpoints
HTTP endpoints that carry a SOAP-type
payload
sys.endpoint_webmethods
SOAP methods defined on endpoints
sys.tcp_endpoints
All TCP endpoints and properties
sys.http_endpoints
All http endpoints and HTTP properties
Securables
 Any object to which SQL Server regulates access
is called a securable
 You assign permissions on securables for users
 Securables fall under one of three scopes
• Server
• Database
• Schema
 schema objects may represent a human user's conceptual
model of the knowledge captured in an enterprise database.
Thus defined, there can be many schema that can be
associated with one physical database.
Securables

•
•
•
•
•
•
Securable scope: Server
• Endpoint
• Login
• Database

Securable scope: Database
•
•
•
•
•
•
•
•
User
Role
Application role
Assembly
Message Type
Route
Service
Remote Service Binding

Fulltext Catalog
Certificate
Asymmetric Key
Symmetric Key
Contract
Schema
Securable scope: Schema
•
•
•
•
securables:
Type
XML Schema Collection
Object
Principals (Database Engine)
 Principals are entities that can request SQL Server
resources. Like other components of the SQL Server
authorization model
 Windows-level principals
• Windows Domain Login
• Windows Local Login
 SQL Server-level principal
• SQL Server Login
 Database-level principals
• Database User
• Database Role
• Application Role
Principals
Server
Server
Server
Server
Database
Database
Database
Database
Database
Database
Database
SQL Server login
SQL Server login mapped to Windows login
SQL Server login mapped to certificate
SQL Server login mapped to asymmetric key
Database users
Database users mapped to Windows login
Database users mapped to certificate
Database users mapped to asymmetric key
Database roles
Application roles
Database users with no login
Note that server logins are mapped to server securables, while database
login are mapped to database objects
Schemas
 The first layer of grouping database object
for security related management
 A database object is referred in four levels:
Server.database.sechema.object
 Schemas are containers to organize objects
to simplify granting permissions as opposed
to the earlier notion of owner.
SQL Server & Database Objects
 Server logins (both Windows and Server)
can have permissions to the followings
• Servers, Endpoints, and Logins
 A database object can have permissions to
Table
View
Function
Procedure
Queue
Type
Rule
Default
Synonym
Aggregate
......
Role based Access
 Same ideas as Groups in Active Directory
 Build In Server Roles
• Table 9.2 page 356
 Build in Database Roles
• Table 9.3 page 357
Build in Server Roles

Sysadmin
•

Serveradmin
•

can create and make changes to databases
Diskadmin
•

can end (kill) a process, good for developers to be in
Dbcreator
•

manage security issues such as creating and deleting logins, reading
the audit logs, and granting users permission to create databases
Processadmin
•

can install replication and manage extended stored procedures
Securityadmin
•
•

can set serverwide configuration options, such as how much memory SQL Server
can use or how much information to send over the network in a single frame, or
even shut down the server.
Setupadmin
•

to perform any task in SQL Server, DBA only
manage files on disk. They perform actions such as mirroring databases and adding
backup devices
Bulkadmin
•
can execute the BULK INSERT statement
built-in database roles
 These are built-in to each database:
•
•
•
•
•
•
•
•
•
•
Db_owner
Db_accessadmin
Db_datareader
Db_datawriter
Db_ddladmin
Db_securityadmin
Db_backupoperator
Db_denydatareader
Db_denydatawriter
Public
Application Roles
 This is a special role that must be activated
using a password
 It is designed to force users to use a specific
application to access data
 The activation code can be hardwired into
your custom applications
 It does not contain any users
Ownership Chains
 When a user queries a view, or executes a stored
procedure, SQL Server checks the owner of the
underlying object
 If the owners are the same, only permissions on
the queried object are verified
 If they are different, permissions on both objects
are verified and honored with the lesser of the two
• This is called a broken ownership chain