Transcript ON table|view

Today’s Objectives
 Chapters 10 and 11
 Security in SQL Server
– Manage server logins and database
users.
– Manage server-level, database-level,
and application access security.
– Develop a security plan, and determine
security requirements.
Authentication - Server level
 Authentication gives you access to SQL
Server by using a login
 Windows authentication – Access is
established through a trusted connection for
a Windows, Windows NT domain, or Active
Directory domain user or group
 Mixed mode (Windows authentication and
SQL Server authentication) – Access is
established either through Windows
authentication or through the use of a SQL
Server login name and password
 Comparison on Table 10-1
Authentication process
 Windows authentication:
1. The client opens a trusted connection to
the server.
2. Windows user and group account
information is passed to SQL Server.
3. This is preferred way of authentication
– The user and group accounts are
checked against the server’s login
accounts in the sysxlogins table.
Authentication process
 SQL Server authentication
– The client opens a non-trusted
connection.
1. A SQL Server login and password are
passed to the server.
2. The server verifies the login name and
password against the sysxlogins table.
Configuring authentication - EM
Creating a new login
 Enterprise Manager
– Create Login Wizard
– EM Console
 Transact SQL
– System stored procedures
– Tables 10-5 and 10-6
 Once the login is created, it has to be
mapped to a SQL Server database user
account
 The db user can be different than the login,
however it is not recommended
New login - Under Security
Windows login sp’s - Table 10-5
 Sp_grantlogin – create login account
sp_grantlogin 'computer_or_domain\user_or_group'
 Sp_denylogin – prevent user or group
from logging in
sp_denylogin 'domain_or_machine\user_or_group'
 Sp_revokelogin – revokes login
account information
sp_revokelogin 'domain_or_machine\user_or_group'
 Sp_defaultdb – identify default
database
sp_defaultdb 'login', 'database’
New SQL Server login
SQL Server login sp’s - Table 10-6
 Sp_addlogin – create a SQL Server
login
sp_addlogin 'login' [, 'password']
[, 'database'] [, 'language'] [, 'sid']
[, 'encryption_option']
 Sp_droplogin – delete SQL Server
login
sp_droplogin 'login’
 Sp_password – set/change login
password
sp_password ['old_password' ,] 'new_password'
[, 'login_name']
Database Access - same as db user
Dropping logins
 You cannot remove sa (the system
administrator default login).
 You cannot remove any login that
owns jobs in the msdb database.
 You cannot remove any login that is in
use and connected to SQL Server.
 You cannot remove any login that
owns an existing database.
SQL Server Roles
 Roles are analogous to Groups in NT/2000
 Roles contain either logins or db users
 Roles are a powerful tool that allow you to
collect users into a single unit against which
you can apply permissions.
 Permissions granted to, denied to, or
revoked from a role also apply to any
members of the role
 Types of SQL Server Roles: Fixed Server
Roles, Fixed Database Roles, User-defined
Roles and Application Roles
Fixed Server Roles
 Sysadmin – Gods of SQL Server
– SA login is member of this role
 Serveradmin – server options like
starting/stopping server, memory settings
 Setupadmin – manager linked servers
 Securityadmin – manager security
 Processadmin – can kill processes
 Dbcreator – can create databases
 Diskadmin – backing up and restoring
databases
 Bulkadmin - can execute Bulk Insert
statements
 Table 10-2 has more details
Server Roles - EM under Security
Server role properties - Under Security
Server Roles - Using T-SQL
 Sp_addsrvrolemember – add a login
to a server role
sp_addsrvrolemember 'login', 'role'
 Sp_dropsrvrolemember – drop a login
from a server role
sp_dropsrvrolemember 'login', 'role’
 Table 10-7 has more details
Fixed Database Roles
 Db_owner – Gods of databases
– dbo (database owner) is member of this role
– SA login is mapped to the dbo user
 Db_accessadmin – control the access to
database
 Db_datareader – can read data from tables
 Db_datawriter – can add and delete data
 Db_ddladmin – can modify database
objects
 Db_securityadmin – change db permissions
 Db_backupoperator – backup database
 Db_denydatareader – cannot read data
 Db_denydatawriter – cannot change data
 Table 10-4 has more details
Database role properties under DB folder
Database Roles - Using T-SQL
 Sp_addrolemember – add members to
a database role
sp_addrolemember 'role', 'member'
 Sp_droprolemember – drop a user
from a database role
sp_droprolemember 'role', 'member’
 More in Table 10-9
Public role
 Public role members can:
– Every database user is member of this
role and cannot be dropped from it
– Execute T-SQL statements that do not
require permissions
– Execute system-stored procedures that
retrieve information from databases to
which the user has access
– Access a database through the guest
account if one exists - The guest db
account lets a new login access a
database without being a user of that db
– Good security to remove guest account
In class Exercises
 Make sure you have SSEMDB
database (instructions on Page 171)
 Pages 334-335
 Pages 339-341(steps 4 - 37 only)
 Pages 345-346 (steps 4 - 10 only)
 Pages 351- 353
 Review the exercises with me to get
the credit
Permissions
 Once the login has access to SQL
Server, still needs database
permissions
 Roles have predefined permissions
 Two types
 Statement Permissions
 Object Permissions
 You can GRANT, DENY or REVOKE
permissions
Statement Permissions - Database level









BACKUP DATABASE
BACKUP LOG
CREATE DATABASE
CREATE DEFAULT
CREATE INDEX
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
More in Table 11-1
Object permissions - Obbjects level
 SELECT – supported for table and view
 INSERT – supported for table and view
 UPDATE – supported for table, view, and
column
 DELETE – supported for table and view
 REFERENCES – supported for table, view
 EXECUTE – supported for stored
procedure, extended stored procedure
 More in Table 11-2
GRANT
 GRANT – When a user is granted a
permission, the user is allowed to
perform that action. Marked by check
 Syntax is
GRANT [ALL PRIVILEGES] | permission [, n]
[(column [, n])] ON table|view
| ON table|view [(column [, n)]
| ON stored_proc|extended_stored_proc
| ON user_defined_function
TO account [, n]
[WITH GRANT OPTION]
[AS group|role]
DENY
 DENY – A user cannot perform an
action that has been denied either
explicitly or through role membership.
Marked by X
 Syntax is
DENY ALL [PRIVILEGES] | permission [, n]
[(column [, n])] ON table|view
| ON table|view [(column [, n)]
| ON stored_proc|extended_stored_proc
| ON user_defined_function
TO account [, n]
[CASCADE]
REVOKE
 REVOKE – A revoked permission is a
neutral action. Marked by nothing
 Syntax
REVOKE ALL [PRIVILEGES] | permission [, n]
[(column [, n])] ON table|view
| ON table|view [(column [, n)]
| ON stored_proc|extended_stored_proc
| ON user_defined_function
TO | FROM account [, n]
[CASCADE]
AS group|role
Application roles
 Application roles have no members.
 An application role is activated when a
user runs the application that the role
is defined for.
 User account and role permissions are
ignored in the context of the
application.
 A password is required for application
role activation.
Defining application roles
 Sp_addapprole – create application
roles
sp_addapprole 'role', 'password'
 Sp_approlepassword – change
application role password
sp_approlepassword 'role', 'new_password'
 Sp_dropapprole – drop an application
role
sp_dropapprole 'role'
New application role
More Exercises





Pages 360
Pages 365-368
Pages 374-378
Pages 382 (optional)
Review the exercises with me to get
the credit
 Next week - Chapters 12 and 13
Authentication strategy
 Use Windows authentication if:
– You do not want to allow any connections other
than trusted connections.
– You want to manage account security and
passwords through Windows or as domain
accounts.
 Use Mixed mode if:
– You need to support non-Windows clients.
– You need to support clients that are not domain
members.
– You need to support Internet access.
– You want to manage SQL Server security
separately from Windows or domain security.
Windows login failures
 Wrong network libraries loaded – Only
Named Pipes, TCP/IP Sockets, and
Multiprotocol support trusted
connections.
 No login name – You must create a
login name for the user or for a group
the user belongs to.
 Login denied – If login is denied for a
user or a group the user belongs to,
the user will not be able to connect to
the server.
SQL Server login failures
 Windows authentication mode – If the
server is configured to support
Windows authentication only, SQL
Server logins are not permitted.
 Incorrect password – If a user forgets
his/her password, a system
administrator will have to change the
password to a new, known value.
 Login deleted – Another possible
cause is that the login name has been
deleted.
Database Access (Authorization)
 Determine if you need the guest account
 Assign access rights need by all users to
public role
 If the user will administer the database,
make them a member of db_owner
 Use the database roles whenever possible
 Use a user defined role if existing roles do
not satisfy the security needs
 Use views and stored procedures to limit
access to database tables
Database permission notes
 If a login name has not been mapped
to a database user, access is through
the Guest account only (if any).
 All database users are members of the
public role.
 Access permissions are cumulative of
all role memberships and explicit
assignments.
 Denied access takes precedence over
granted access.