Databases Reference
In-Depth Information
3.4.3
Using RDBMS views for security and access control
Now that you understand the concepts and structure of
RDBMS
s, let's think about how
you might securely add sensitive information. Let's expand the
SALES_ORDER
exam-
ple to allow customers to pay by credit card. Because this information is sensitive, you
need a way to capture and protect this data. Your company security policy may allow
some individuals with appropriate roles in the company to see sales data. Additionally,
you may also have security rules which dictate that only a select few individuals in the
organization are allowed to see a customer's credit card number. One solution would
be to put the numbers in a separate hidden table and perform a join operation to
retrieve the information when required, but
RDBMS
vendors provide an easier solu-
tion by creating a separate
view
of any table or query. An example of this is shown in
figure 3.7.
In this example, users don't access the actual tables. Instead, they see only a report
of information from the table, which excludes any sensitive information that they
don't have access to based on your company security policy. The ability to use dynamic
calculations to create table
views
and grant access to views using roles defined within
an organization is one of the features that make
RDBMS
s flexible.
Many NoSQL systems don't allow you to create multiple views of physical data and
then grant access to these views to users with specific roles. If your requirements
We want to restrict
general access to this
column.
Physical table
The physical table
includes all the
column, including
credit card info.
Only select users
ever see the
physical table.
ORDER_ID
ORDER_DATE
SHIP_STATUS
CARD_INFO
TOTAL
123
2012-07-11
SHIPPED
VISA-1234…
39.45
124
125
2012-07-12
2012-07-13
BACKORDER
SHIPPED
MC-5678…
AMEX-9012…
29.37
42.47
View of table
The view excludes
some fields like credit
card information. All
sales analysts have
access to the views.
ORDER_ID
ORDER_DATE
SHIP_STATUS
TOTAL
123
2012-07-11
SHIPPED
39.45
124
125
2012-07-12
2012-07-13
BACKORDER
SHIPPED
29.37
42.47
Figure 3.7
Data security and access control—how sensitive columns can be
hidden from some users using views. In this example, the physical table that stores
order information contains credit card information that should be restricted from
general users. To protect this information without duplicating the table, RDBMSs
provide a restricted view of the table that excludes this credit card information.
Even if the user has a general reporting tool, they won't be able to view this data
because they haven't been granted permission to view the underlying physical
table, only a view of the table.
