Transcript login

Chapter Overview
•
Understanding the Authentication Process
•
Understanding the Authorization Process
• Creating and Managing Logins
1
Authentication Process
2
Authentication Method
Security Issues
•
Unencrypted user names and passwords on
the wire
• Password policies
•
Lockout policies
3
Net-Library Issues
•
The client and Microsoft SQL Server must support a
matching set of Net-Libraries.
•
Default client Net-Libraries are TCP/IP Sockets and
Named Pipes, and connections are attempted in that
order.
•
Use the Client Network Utility to modify client NetLibraries.
•
Only Named Pipes and Multiprotocol Net-Libraries
require an existing authenticated connection.
4
SQL Server Authentication
Modes
5
Security Account
Delegation
•
SQL Server can impersonate an authenticated user to
access resources outside of SQL Server.
•
All participating servers must be running Windows
2000 and have security account delegation enabled.
•
The SQL Server service account must be trusted for
delegation.
•
The user requesting delegation must be trusted for
delegation.
•
The SQL Server instance must have a Service Principal
Name (SPN).
6
Server Roles Grant ServerWide Permissions
sysadmin
serveradmin
setupadmin
securityadmin
processadmin
dbcreator
diskadmin
bulkadmin
7
Database-Specific
Permissions
Database owner
DBO role
User
Guest user
Public role
Fixed database role
User-defined database role
Statement permissions
Object permissions
Application role
8
Fixed Database Roles
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatawriter
db_denydatareader
9
Login Accounts Using
Create Login Wizard
•
Specify the authentication type for the login.
•
You can grant or deny SQL Server access to a
Windows user or group.
•
Define a server role, if any.
•
Specify the databases to which the login will be
a permitted user.
10
Login Accounts Using
Enterprise Manager Directly
11
User-Defined Database
Roles
•
Create the user-defined database role within a
specific database.
• Add users within the database to that role.
•
A user-defined database role has no permissions
assigned to it automatically.
12
Windows Login Accounts
Using Transact-SQL
•
sp_grantlogin 'login'
•
sp_revokelogin 'login'
• sp_denylogin 'login'
•
sp_defaultdb 'login', 'database'
•
sp_defaultlanguage 'login', 'language'
13
SQL Server Login Accounts
Using Transact-SQL
•
sp_addlogin 'login', ['password', 'database',
'language', 'sid', 'encryption_option']
• sp_droplogin 'login'
•
sp_password 'old_password', 'new_password',
'login'
• sp_defaultdb 'login', 'database'
•
sp_defaultlanguage 'login', 'language'
14
Server Roles and Database
Access Using Transact-SQL
•
sp_addsrvrolemember 'login', 'role'
•
sp_dropsrvrolemember 'login', 'role'
• sp_grantdbaccess 'login', 'name_in_db'
•
sp_revokedbaccess 'name'
15
Database Roles Using
Transact-SQL
•
sp_changedbowner 'login', 'remap_alias_flag'
•
sp_addrolemember 'role', 'security_account'
• sp_droprolemember 'role', 'security_account'
•
sp_addrole 'role', 'owner'
•
sp_droprole 'role'
16
Viewing Access Information
Using Enterprise Manager
•
View on a user-by-user basis in the Logins
container within the Security container.
• View server role membership in the Server Roles
container within the Security container.
•
View database access on a per-user basis in the
Users container in each database.
•
View database role membership in the Roles
container in each database.
17
Viewing Access Information
Using Transact-SQL
•
sp_helplogins ['login']
•
sp_helpsrvrolemember ['role']
• sp_helpuser ['security_account']
•
sp_helprolemember ['role']
•
sp_helpntgroup ['name']
18
Chapter Summary
•
Authentication is required for access, and authorization
is required to perform specific actions.
•
SQL Server supports Windows and SQL Server
authentication.
•
SQL Server can run in Windows Authentication Mode or
Mixed Mode.
•
Server roles grant server-wide administration rights,
and database roles grant database-specific rights.
•
View access information on a user basis or a role basis.
19