Scribd
Upload
123 views23 pages

20764C 03-1 PDF

This document provides an overview of authorizing user access to resources in Microsoft SQL Server. It covers topics such as principals and securables, using GRANT, REVOKE, and DENY to manage permissions on database objects, securing tables, views, stored procedures and functions. It also discusses column-level security, row-level security, the WITH GRANT OPTION, and configuring permissions at the schema level. The document includes demonstrations and a lab exercise to apply these authorization techniques.

Uploaded by

Phil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
123 views23 pages

20764C 03-1 PDF

This document provides an overview of authorizing user access to resources in Microsoft SQL Server. It covers topics such as principals and securables, using GRANT, REVOKE, and DENY to manage permissions on database objects, securing tables, views, stored procedures and functions. It also discusses column-level security, row-level security, the WITH GRANT OPTION, and configuring permissions at the schema level. The document includes demonstrations and a lab exercise to apply these authorization techniques.

Uploaded by

Phil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 23

Microsoft SQL Server

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:
• Recall that principals are security identities that can access
securables and perform actions.
• 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;
Database
GO

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


GO Schema
(owner.table)
Principal
gaining
access

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, Name) TO User1;
GO Schema
(owner.table)
Columns in
Salesperson table
Principal
gaining
access
DENY SELECT ON Marketing.Salesperson (Salary, Bonus) TO User2;
GO
Row-Level Security

• 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 What?
• A security predicate determines whether a user executing a query
has access to the row based on the information in that row
• I don’t know why it has to sound so confusing…
WITH GRANT Option

• Use the WITH GRANT OPTION to enable the principal to


grant the same permissions to other users
GRANT UPDATE ON Marketing.Salesperson
TO User1
WITH GRANT OPTION;
GO
• Use CASCADE to revoke or deny these permissions from
the principal and the other users
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

• User Defined Functions require EXECUTE permissions


GRANT EXECUTE ON Products.ChangeProductPrice
TO InternetSales_Manager;
GO
EXECUTE Products.ChangeProductPrice 1,2;
GO

• Table-Valued Functions require SELECT permissions


• Will return a table of results rather than a single value
GRANT EXECUTE ON dbo.FormatPhoneNumber
TO public;
GO
Managing Ownership Chains

Table1 owned
by User2

View1 owned
by User2

User1
granted
Table2 owned
permission to
by User3
both views

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

GRANT EXECUTE ON dbo.FormatPhoneNumber


TO public;
GO
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: 20764C-MIA-SQL
User name: ADVENTUREWORKS\Student
Password: Pa55w.rd

Estimated Time: 45 minutes

You might also like