Transcript Slide 1

Chapter 10 Overview
 Implement Microsoft Windows
Authentication Mode and Mixed Mode
 Assign login accounts to database user
accounts and roles
 Assign permissions to user accounts and
roles
 Manage security within SQL Server.
Authentication Processing
 When a user connects to SQL Server, the
client opens a trusted connection to SQL
Server, which passes the user’s Windows
2000 security credentials to SQL Server
 If SQL Server finds the user’s Windows
2000 user account or group account in the
list of SQL Server login accounts in the
sysxlogins system table, it accepts the
connection.
Windows Authentication Mode
Advantages
 Provides more features, such as secure
validation and encryption of passwords,
auditing, password expiration, minimum
password length, and account lockout
after an invalid password.
 Enables you to add groups of users to
SQL Server by adding a single login
account.
 Enables users to access SQL Server
quickly, without having to remember
another login account and password.
Mixed Mode Authentication Advantages
Mixed Mode, and the SQL Server
Authentication mechanism in particular,
enables non-Windows 2000 clients,
Internet clients, and mixed client groups to
connect to SQL Server.
Create a Login Account
 Can create login accounts from existing
Windows 2000 users and groups, or can
create new SQL Server login accounts.
 Login accounts are stored in the
master..sysxlogins system table.
 System or security administrators can
execute stored procedures to allow or
disallow a Windows 2000 user or group
account to connect to SQL Server
Create a Login Account
System Stored Procedure
Description
sp_grantlogin
Adds a login account
entry for a Windows
2000 user or SQL Server
group
sp_revokelogin
Removes the login
account entries for a
windows 2000 user or
group from SQL Server
sp_denylogin
Prevents a Windows
2000 user or group from
connecting to SQL
Server
Create a Login Account
SQL Server login accounts and passwords
can contain up to 128 characters, including
letters, symbols, and digits. However, login
accounts cannot:
• contain a backslash character,
• be a reserved login account – for example,
sa.
• be null or an empty string (“ ”)
Default Login Accounts
• BUILTIN\Administrators is provided as the
default login account for all Windows 2000
administrators. It has all rights on the SQL
Server and in all databases.
• System administrator (sa) is a special login
account that has all rights on the SQL Server
and in all databases. It is provided for
backward compatibility and should not be
used routinely. This account is only enabled
when SQL Server is using Mixed Mode
Authentication.
Assign Logins to User Accounts
 Add User Accounts using:
 SQL Server Enterprise Manager
 sp_grantdbaaccess
 Dbo User Account:
The sa login and members of the System
Administrators (sysadmin) role are
mapped to a special user account inside all
databases called dbo. Any object that a
system administrator creates automatically
belongs to dbo. The dbo user is a default
account and cannot be deleted.
Assign Logins (ctd)
 Guest User Account
The guest user account allows logins without
user accounts to access a database. Login
accounts assume the identity of the guest user
when both of the following conditions are met:
 The login account has access to SQL
Server but does not have access to the
database through its own user account.
 The database contains a guest user
account.
Roles
Roles provide a means of assembling users into
a single unit to which permissions can be
applied.
Roles include:
•
Fixed Server Roles
•
Fixed Database Roles
•
User-defined Database Roles
Fixed Server Role Privileges
(Groupings of administrative privileges at the server level)
Role
Privileges
Sysadmin
Can perform any activity in SQL Server
Serveradmin
Can set serverwide configuration options, shut
down the server
Setupadmin
Can manage linked servers and startup
procedures
Securityadmin Can manage logins and CREATE DATABASE
permissions, also read error logs and change
passwords
Processadmin Can manage processes running in SQL Server
Dbcreator
Can create, alter, and drop databases
Diskadmin
Can manage disk files
Bulkadmin
Can execute BULK INSERT Statements
Fixed Database Role Privileges
(Groupings of administrative privileges at the database level)
Role
Privileges
db_owner
Has all permissions in the database
db_accessadmin
db_securityadmin
Can add or remove user IDs
Can manage all permissions, object ownerships,
roles, and role memberships
db_ddladmin
Can issue ALL DDL, but cannot issue GRANT,
REVOKE, or DENY statements.
db_backupoperator Can issue DBCC, CHECKPOINT, and BACKUP
statements
db_datareader
Can select all data from any user table in the
database
db_datawriter
Can modify any data from any user table in the
database
db_denydatareader Cannot select any data from any user table in the
database
db_denydatawriter
Cannot modify any data from any user table in
the database
User-defined Database Role
Creating a user-defined database role enables
you to create a group of users with a set of
common permissions.
Add a Role:
• When a group of users need to perform
the same activities in SQL Server
• If you do not have permissions to
manage Windows 2000 accounts
Permissions
Permissions specify which database objects
users are authorized to use and what the
users can do with those objects.
Types of Permissions
1. Statement Permissions
• Activities that involve creating a database
or items in a database require a class of
permissions called statement permissions.
• Statement Permissions give users the
privilege of issuing certain Transact-SQL
statements including: Create Database,
Create Table, Create view, Create
Procedure, Create Rule, Create Default,
Create function, Backup Database,
Backup Log
Types of Permissions
2. Object Permissions:
Activities that involve working with data or
executing procedures require a class of
permissions known as object permissions:
• Table and view Permissions: Select,
Insert, Update Table/view, Delete,
References
• Column Permissions: Select, Update
column, and references
• Stored Procedure Permissions: Exec
Types of Permissions
3. Predefined Permissions:
Only members of fixed roles or owners of
database objects can perform certain
activities. Permissions to perform these
activities are called predefined or implicit
permissions
• Fixed Role Permissions: Fixed roles have
implicit administrative permissions.
• Object owner Permissions: Object owners
also have implied permissions that allow
them to perform all activities with objects that
they own.
Command Line Interface (CLI)
Security can also be managed from the
command line interface (CLI)
CLI – Create Logins
• Create a SQL Server Login
sp_addlogin ‘MrsDeveloperSQL’, ‘password’
• Create a Windows account Login
sp_grantlogin ‘SENECADOM\MrsDboNT’
CLI – Work with Database Users
 Map Login Accounts to a User account
USE pubs2
Go
EXEC sp_grantdbaccess ‘SENECADOM\MrsDboNT’,
‘MrsDboNT’
Go
CLI – Work with Database Users
Command
Result
sp_helplogins
Displays information about a login
sp_droplogin
Removes a login
sp_revokelogin
Removes a login
sp_denylogin
Denies a login
sp_password
Changes a login’s password
sp_defaultdb
Sets a login’s default database
sp_validatelogins Shows “orphans” in the database.
These are logins that exist in SQL
Server but no longer exist as
Windows accounts
CLI – Work with Roles
 Add SQL 2000 user to the sysadmin group
sp_addsrvrolemember ‘SuperUser’, ‘sysadmin’
 Add a new database role of developers
sp_addrole ‘Developers’
CLI – Work with Roles
Command
Result
sp_droprole
Removes a role
sp_helprole
Displays information about
a role
sp_helpdbfixedrole Displays privileges the
fixed roles have
CLI – Work with Permissions
 Grant Select access to a table
Grant Select
On authors
To MrsDeveloperSQL
GO
 Deny Insert access to a table
Deny Insert
On authors
To MrsDeveloperSQL
GO
• Revoke Select permissions from a table
Revoke Select
On authors
From MrsDeveloperSQL
GO