Sql server tutorial 06 Managing Database Security - XADAT.COM
post-template-default,single,single-post,postid-15791,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 06 Managing Database Security No ratings yet.

Sql server tutorial 06 Managing Database Security

Key Skills & Concepts

  • Understand the SQL Security Model
  • Create and Delete Roles
  • Grant and Revoke Privileges
  • Grant and Revoke Roles

A critical component of any database is the ability to protect the data from unauthorized
access and malicious attacks. A database must ensure that no unauthorized users can
view or change data that they should not be viewing or changing. At the same time,
authorized users should not be prevented from accessing any information that should be
available to them. The ideal balance is to give every database user exactly the privileges
they need to do their job, nothing more and nothing less. In order to support these
capabilities, SQL defines a security model that allows you to determine which users can
access specific data and what they can do with that data. At the core of this model is the
authorization identifier. An authorization identifier, as you learned in Chapter 2, is an
object in the SQL environment that represents a user or group of users that are granted
specific access privileges to objects and data within the SQL environment. Privileges on
schema objects are granted to authorization identifiers. The type of privilege granted
determines the type of access. In this chapter, we will look at the SQL security model, how
it uses authorization identifiers, and how to set up privileges on objects in your SQL

Understand the SQL Security Model

Authorization identifiers provide the foundation for your database’s security. Access to all
objects is permitted through these identifiers. If the authorization identifier doesn’t have
the appropriate privileges to access a specific object, such as a table, the data within that
table is unavailable to that user. In addition, each authorization identifier can be
configured with different types of privileges. For example, you can permit some
authorization identifiers to view the data within a specific table, while permitting other
authorization identifiers to both view and modify that data.
SQL supports two types of authorization identifiers: user identifiers (or users) and role
names (or roles). A user identifier is an individual security account that can represent an
individual, an application, or a system service (all of which are considered database users).
The SQL standard does not specify how an SQL implementation should create a user
identifier. The identifier might be tied to the operating system on which the relational
database management system (RDBMS) is running, or it might be explicitly created
within the RDBMS environment.
A role name is a defined set of privileges that can be assigned to a user or to another
role. If a role name is granted access to a schema object, then all user identifiers and role

names that have been assigned the specified role name are granted the same access to that
object whenever the role name is the current authorization identifier. For example, in
Figure 6-1 the MRKT_DEPT role name has been assigned to the ACCT_DEPT role name
and to four user identifiers: Ethan, Max, Linda, and Emma. If the MRKT_DEPT role
name is the current authorization identifier and it has been granted access to the
CD_PUBLISHERS table, the ACCT_DEPT role name and all four user identifiers have
access to the CD_PUBLISHERS table. Note that, unlike a user identifier, SQL does
specify how to create a role name, which I discuss in the “Create and Delete Roles”
section later in this chapter.


SQL Sessions

Each SQL session is associated with a user identifier and role name. An SQL session is the
connection between some sort of client application and the database. The session provides
the context in which the authorization identifier executes SQL statements during a single
connection. Throughout this connection, the SQL session maintains its association with a
user identifier/role name pair.
Let’s take a look at Figure 6-2, which shows the user identifier/role name pair
associated with a session. When a session is first established, the user identifier is always
the SQL session user identifier, which is a special type of user identifier that remains
associated with the session throughout the connection. It is up to the SQL implementation
to determine how a specific account becomes the SQL session user identifier, although it
can be an operating system user account or an account specific to the RDBMS. Whatever
method is used to associate an account with the SQL session user identifier, it is this
account that acts as the current user identifier

Figure 6-3 Authorization stack created during an SQL session
In this example, the initial user identifier/role name pair is at the bottom of the stack.
As you would expect, the user identifier is the SQL session user identifier and the role
name is a null value. Access to database objects is based on the privileges granted to the
SQL session user identifier when it is current.
During the session, an embedded SQL statement specifies an authorization identifier of
App_User, which is a user identifier. When the embedded statement is executed,
App_User becomes the current authorization identifier, and access privileges are based on
that account.

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

No Comments

Sorry, the comment form is closed at this time.