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