user mgmt and SQL Server Security pt 1

________ provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server

credentials

All logins have default what?

database and language

database users have principals at what level?

database level

database level principals

database user fixed database role user defined database role application tole

Access to database resources require additional permission via a ______ user

database user (logins ≠ database users)

Having a user account does not mean you can access a particular table for what users?

database users

WINDOWS GROUP:

All members of the group granted or denied access

Logins or users can be grouped into a "_____" and then permissions assigned to the it

role

what are similar to groups in Windows?

roles

To display all the users in a database

select * from sys.database_principals

Display server Principals

select * from sys.server_principals order by type_desc

Display server Principals

select left(name,25) name, type, type_desc from sys.server_principals AS log WHERE (log.type in ('U', 'G', 'S', 'R')) order by 3,1

Display the current login, database user, db names

select suser_name() as login , user_name() as usr , db_name() as db

A database user can be associated with what?

single login or a group

mixed mode access open to all:

unix, mac, and novell users

for database users, a ____ account for a login is not created by default

user

Application roles

Manage database access for an application

________ _______ keeps logins, and permissions are granted to logins for resources at the server level

Master database

From SQL Server _____, user-defined server roles can be created, and server-level permissions can be allocated

2012

User-defined server, database roles

Create a custom set of permissions

database users DEMO

Create a login roberts in SS • Do nothing. Then, login as roberts • Check out suser_sname(), db_name(), user_name() • Can you access DBTest? • Set the default db (DBTest) and default language • Log in as roberts. Do you have any error message? • Why did it happen? The user mapping for roberts is not done yet • Do user mapping • Can you access DBTest? • Can you read tables in DBTest? SELECT * FROM dbo.cust • Check db_datareader. Try SELECT • INSERT INTO dbo.login VALUES ('USER200','cucumber');

user defined roles

Create custom database roles • Make the more granular set of permissions

all logins have ______ and ______ accounts

Credentials and proxy accounts

guest

Disabled by default; If enabled, any login can use the database without a user account

fixed database roles

Fixed permissions at the database level • Assigned to database users

Fixed-server and fixed-database roles

Installed by default and have fixed permissions

INFORMATION_SCHEMA

Internal usage

sys

Internal usage

______ ______build on credentials

Proxy accounts

principals: each has a ___

SID

SQL Server level principals

SQL Server login Fixed server role user-defined server role

all logins have _____ and _______

Securables Status

all logins have _____ ______ role

Server-level role: • Applied to the SQL Server instance

dbo

The sysadmin fixed server is mapped to the special user dbo in each database. Any object created by any member of the sysadmin fixed server role belongs to dbo automatically

scenario: Michael is a member of a Windows group named Marketing that has been granted access to SQL Server via a Windows group account in SQL Server. Michael should not have access to SQL Server, but he needs the permissions afforded the Marketing group on other servers. How can you remedy this?

Windows • Go to Windows and create a group Marketing • Create a Window user Michael and put the login in Marketing group SQL Server • Add Marketing group to SS Windows • Log back in as Michael • The Window users in the Marketing group can log in to SS without having SS login SQL Server • Add Michael login and deny acces

mixed mode

allows windows authentication, SQL Server authentication

principals has permission granted to

an individual login or a role that the login belongs to

who is the database owner?

dbo

built in database users?

dbo, guest, Information_schema, sys

Default language:

display error messages and functions

principals

entities that can request SQL Server resources - resources

for database users, the _____ name and the database username are usually the same

login

Each principal has an associated ______

login (Windows accounts, Windows groups, or SQL Server logins)

Sql server principals

logins, server roles

Principals include ______ of all types and ____

logins; roles

all logins have user ____

mapping to map logins to database users

windows authentication steps

user logs in; performes a DNS lookup to lovate a key distribution center (KDC) user computer logs into domain KDC issues ticket granting ticket (security token) to user users computer sends the token to SQL server if windows SID is the same as that in sys.server_principals, they are allowed access

SQL Server Authentication steps mixed mode

user logs into network user connects to SQL Server using a separate ID/pwd —> called non trusted connection bc SQL Server doesn't use OS authentication SQL Server compares the creds with the ones on the sys.sql_logins table

database principals

users, database roles

windows level principals

windows group windows domain login windows local login

principals: each scoped at the ...?

windows, server, or database level

fixed server role: setupadmin

• Can install replication and manage extended stored procedures •Assistant DBAs

The public role (in Server Roles)

• Every SQL Server login belongs to this role • The login inherits the permissions granted to the public on that object if nothing has been granted

The public role (in Database Roles)

• Every database has this role • All database users and roles inherit permissions granted to the public

SQL Server three types of roles:

• Fixed-server and fixed-database roles • User-defined server, database roles • Application Roles

fixed server roles

• Permissions for these roles are related to server-level securables • Logins, not database users, are assigned to these roles • A single login can be assigned to multiple fixed-server roles • Permissions for these roles are cascaded to the database level

Logins at the SQL Server instance

• WINDOWSLOGIN • WINDOWS GROUP • SQL LOGIN • SERVER_ROLE

fixed server role: sysadmin

•Can conduct any task in SQL Server •DBAs

fixed server role: dbcreator

•Can create and make changes to databases •Assistant DBAs as well as developers •Don't use it (members can DROP any database on the server)

fixed server role: bulkadmin

•Can execute the BULK INSERT statement (importing data into databases from text files) •Assistant DBAs

fixed server role: diskadmin

•Can manage files on disk (mirroring, adding backup devices) •Assistant DBAs

fixed server role: securityadmin

•Can manage security issues (logins creation and deletion, audit logs reading, grant permission to create databases) •Assistant DBAs

fixed server role: server admin

•Change serverwide configuration options (e.g., memory usage limit) •Assistant DBAs

fixed server role: processadmin

•Kill a process •Assistant DBAs and developers