Authorizing Users To Access Resources

Module 3
Authorizing Users to Access

Resources
Module Overview
Authorizing User Access to Objects

Authorizing Users to Execute Code
• Configuring Permissions at the Schema Level
Lesson 1: Authorizing User Access to Objects
What Are Principals?

What Are Securables?
GRANT, REVOKE, DENY
Securing Tables and Views
Column-Level Security
Row-Level Security
WITH GRANT Option
• Demonstration: Authorizing User Access to
Objects
What Are Principals?
• Windows Principals:
• Windows group
• Domain user account
• Local user account
• SQL Server Principals:

• SQL Server login
• Server role
• Database Principals:
• User
• Database role
• Application role
What Are Securables?
• Resources to which SQL Server controls access
• Contained within scopes:

• Server
• Database
• Schema
GRANT, REVOKE, DENY
• GRANT assigns a permission
• DENY explicitly denies a permission:

• Use to deny inherited permissions
• Use only in exceptional circumstances
• REVOKE removes both GRANT and DENY

permissions
Securing Tables and Views
• Several object permissions apply to tables and

views:
• SELECT
• INSERT, UPDATE, DELETE
USE MarketDev;
GO

GRANT SELECT ON OBJECT::Marketing.Salesperson TO
HRApp;
GO

GRANT SELECT ON Marketing.Salesperson TO HRApp;
GO
Column-Level Security
• Can assign permissions at column level

• Can assign permissions for multiple columns in
one GRANT or DENY statement
• Column-level GRANT statements override table-
level DENY statements
GRANT SELECT ON Marketing.Salesperson (SalespersonID)
TO User1;
GO

DENY SELECT ON Marketing.Salesperson
TO User2;
GO
Row-Level Security
• New in SQL Server 2016

• Control access to rows in a table, for example:
• Salesperson accessing customer data in their region
• Employee accessing data relevant to their department
• Advantages:
• Logic held with data—reduces risk of errors and
simplifies security
• Similar to horizontal partitioning or using a
WHERE clause
• Implement by adding a security predicate defined
as an inline table-valued function
WITH GRANT Option
• Use the WITH GRANT OPTION to enable the

principal to grant the same permissions to other
users
• Use CASCADE to revoke or deny these
permissions from the principal and the other
users
GRANT UPDATE ON Marketing.Salesperson
TO User1
WITH GRANT OPTION;
GO

REVOKE UPDATE ON Marketing.Salesperson
FROM User1
CASCADE;
GO
Demonstration: Authorizing User Access to
Objects
In this demonstration, you will see how to view

principals and grant permissions on database
objects
Lesson 2: Authorizing Users to Execute Code
Securing Stored Procedures

Securing User-Defined Functions
Securing Managed Code
Managing Ownership Chains
• Demonstration: Authorizing Users to Execute Code
Securing Stored Procedures
• EXECUTE: enables users to call stored procedures

• ALTER: enables users to modify stored
procedures
• VIEW DEFINITION: enables users to access the
code definition
USE MarketDev;
GO
GRANT EXECUTE ON Reports.GetProductColors

TO User1;
GO
Securing User-Defined Functions
• Scalar UDFs require EXECUTE permissions
• TVFs require SELECT permissions
• CHECK constraints, DEFAULT values, and

computed columns require REFERENCES
permissions
GRANT EXECUTE ON dbo.FormatPhoneNumber

TO public;
GO
Securing Managed Code
• Additional permission requirements above those

required for Transact-SQL code
• Assemblies are registered with one of three

permissions sets:
• SAFE (the default)
• EXTERNAL_ACCESS
• UNSAFE
• EXTERNAL_ACCESS and UNSAFE permission sets

require additional configuration on the database
Managing Ownership Chains
Table1
owned by
View 1 User2
Table 1
View1 owned
by User2
Table 2
User1
granted
View 2 Table2
permission to
owned by
both views
User3
View2 owned
by User2
Demonstration: Authorizing Users to Execute
Code
In this demonstration, you will see how to assign

permissions to execute stored procedures and
functions
Lesson 3: Configuring Permissions at the Schema
Level
Overview of User-Schema Separation

Object Name Resolution
Granting Permissions at Schema Level
• Demonstration: Configuring Permissions at the
Schema Level
Overview of User-Schema Separation
• Containers for database objects
• Listed by querying sys.schemas view
• Users have default schemas
• Built-in schemas:
• dbo and guest
• sys and INFORMATION_SCHEMA
Object Name Resolution
• Multiple objects in different schemas can have

the same name
• SQL Server resolves names by:

• Firstly looking in the user’s default schema
• Then looking in the dbo schema
• Avoid ambiguity by using two-part names

Granting Permissions at Schema Level
• Implicitly applies permissions to all relevant

objects in the schema
• Simplifies permission management
USE MarketDev;
GO
GRANT EXECUTE ON SCHEMA::Marketing TO User1;

GO
GRANT SELECT ON SCHEMA::Marketing TO User1;

GO
Demonstration: Configuring Permissions at the
Schema Level
In this demonstration, you will see how to assign

and revoke permissions at schema level
Lab: Authorizing Users to Access Resources
Exercise 1: Granting, Denying, and Revoking

Permissions on Objects
Exercise 2: Granting EXECUTE Permissions on Code
• Exercise 3: Granting Permissions at the Schema
Level
Logon Information
Virtual machine: 20764B-MIA-SQL
User name: ADVENTUREWORKS\Student
Password: Pa$$w0rd
Estimated Time: 45 minutes

Lab Scenario continued
The MIA-SQL SQL Server instance will be used to host

application databases, including the InternetSales
database that contains the following schemas and
database objects:
 Sales schema:
SalesOrderHeader table
SalesOrderDetail table
 Products schema:
o Product table
o ProductSubcategory table
o ProductCategory table
o vProductCatalog view
o ChangeProductPrice stored procedure
 Customers schema:
• Customer table
The InternetSales database must be accessible by the

following users:
• IT support personnel.
• Sales employees in North America, Europe, and Asia.
• Sales managers.
• An e-commerce web application that runs as the
adventureworks\WebApplicationSvc service
account.
The adventureworks.msft domain includes the

following global groups:
 adventureworks\IT_Support: contains all IT support
personnel.
 adventureworks\Sales_Asia: contains all sales
employees in Asia.
 adventureworks\Sales_Europe: contains all sales
employees in Europe.
 adventureworks\Sales_NorthAmerica: contains all sales
employees in North America.
 adventureworks\Sales_Managers: contains all sales
managers.
The domain administrator has created the following

domain local groups, with the members shown:
• ADVENTUREWORKS\Database_Managers:
o ADVENTUREWORKS\IT_Support
• ADVENTUREWORKS\InternetSales_Users:
o ADVENTUREWORKS\Sales_Asia
o ADVENTUREWORKS\Sales_Europe
o ADVENTUREWORKS\Sales_NorthAmerica
 ADVENTUREWORKS\InternetSales_Managers:
o ADVENTUREWORKS\Sales_Managers
A SQL Server administrator has created the

following server logins and database users in the
InternetSales database, mapped to their relevant
Windows accounts:
 Database_Managers
 InternetSales_Users
 InternetSales_Managers
 WebApplicationSvc
The security requirements for the database are:

• The e-commerce application must be able to read data from the
Products.vProductCatalog view.
• The e-commerce application must be able to insert rows into
the Sales.SalesOrderHeader and Sales.SalesOrderDetail
tables.
• All sales employees and managers must be able to read all data
in the Customers table.
• Sales managers must be able to execute the stored
ChangeProductPrice procedure.
• Sales managers must be able to insert and update any data in
the Sales schema.
• All sales employees and managers must be able to read all data
in the Sales schema.
Lab Review
In this lab, you have assigned object-level and

schema-level permissions, and granted EXECUTE
permissions on code.
Module Review and Takeaways
In this module, you have seen how to manage

objects, code, and schema permissions in a
database.

Authorizing Users To Access Resources

Uploaded by

Copyright:

Available Formats

Authorizing Users To Access Resources

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Authorizing Users To Access Resources

Uploaded by

Copyright:

Available Formats

Module 3

Authorizing Users to Access

Authorizing User Access to Objects

What Are Principals?

• SQL Server Principals:

• Resources to which SQL Server controls access

• Contained within scopes:

• GRANT assigns a permission

• DENY explicitly denies a permission:

• REVOKE removes both GRANT and DENY

• Several object permissions apply to tables and

• Can assign permissions at column level

• New in SQL Server 2016

• Use the WITH GRANT OPTION to enable the

In this demonstration, you will see how to view

Securing Stored Procedures

• EXECUTE: enables users to call stored procedures

GRANT EXECUTE ON Reports.GetProductColors

• Scalar UDFs require EXECUTE permissions

• TVFs require SELECT permissions

• CHECK constraints, DEFAULT values, and

GRANT EXECUTE ON dbo.FormatPhoneNumber

• Additional permission requirements above those

• Assemblies are registered with one of three

• EXTERNAL_ACCESS and UNSAFE permission sets

In this demonstration, you will see how to assign

Overview of User-Schema Separation

• Containers for database objects

• Listed by querying sys.schemas view

• Users have default schemas

• Multiple objects in different schemas can have

• SQL Server resolves names by:

• Avoid ambiguity by using two-part names

• Implicitly applies permissions to all relevant

• Simplifies permission management

GRANT EXECUTE ON SCHEMA::Marketing TO User1;

GRANT SELECT ON SCHEMA::Marketing TO User1;

In this demonstration, you will see how to assign

Exercise 1: Granting, Denying, and Revoking

Estimated Time: 45 minutes

The MIA-SQL SQL Server instance will be used to host

The InternetSales database must be accessible by the

The adventureworks.msft domain includes the

The domain administrator has created the following

A SQL Server administrator has created the

The security requirements for the database are:

In this lab, you have assigned object-level and

In this module, you have seen how to manage

You might also like