Database Reference
In-Depth Information
A USER can be assigned to one or more ROLEs (or USER GROUPs), and a ROLE can
have one or more USERs. An OBJECT is an element of a database, such as a table, view, or
stored procedure. PERMISSION is an association entity among USER, ROLE, and OBJECT.
Hence, the relationships from USER to PERMISSION, ROLE to PERMISSION, and OBJECT to
PERMISSION are all 1:N, O-M.
Permissions can be managed using SQL Data Control Language (DCL) statements:
The SQL GRANT statement is used to assign permissions to users and groups, so
that the users or groups can perform various operations on the data in the database.
The SQL REVOKE statement is used to take existing permissions away from users
and groups.
While these statements can be used in SQL scripts and with SQL command line utilities, we will
find it much easier to use the GUI DBMS administration utilities provided for use with each of
the major DBMS products and will illustrate how to use these utilities for SQL Server 2012 in
Chapter 10A, Oracle Database 11 g Release 2 in Chapter 10B, and for MySQL 5.6 in Chapter 10C.
When a user signs on to the database, the DBMS limits the user's actions to the permis-
sions defined for that user and to the permissions for roles to which that user has been as-
signed. Determining whether someone actually is who they claim to be is a difficult task, in
general. All commercial DBMS products use some version of user name and password verifica-
tion, even though such security is readily circumvented if users are careless with their identities.
Users can enter their user name (also called the login name ) and password, or, in some
applications, the user name and password is entered on the user's behalf. For example, the
Windows user name and password can be passed directly to the DBMS. In other cases, an ap-
plication program provides the user name and password. Internet applications usually define
a group such as “Unknown Public” and assign anonymous users to that group when they sign
on. In this way, companies, such as Dell, need not enter every potential customer into their
security system by user name and password.
SQL Server 2012, Oracle Database 11 g Release 2, and MySQL 5.6 security systems are
variations of the model in Figure 9-15. You will learn about them in Chapters 10A, 10B, and
10C, respectively.
DBMS Security Guidelines
Guidelines for improving security in database systems are listed in Figure 9-16. First, the DBMS
must always be run behind a firewall. However, the DBA should plan security with the assump-
tion that the firewall has been breached. The DBMS, the database, and all applications should
be secure even if the firewall fails.
DBMS vendors, including IBM, Oracle, and Microsoft, are constantly adding product
features to improve security and reduce vulnerability. Consequently, organizations using
DBMS products should continually check the vendors' Web sites for service packs and fixes;
any service packs or fixes that involve security features, functions, and processing should be
installed as soon as possible.
The installation of new service packs and fixes is not quite as simple as described
here. The installation of a service pack or fix can break some applications, particularly some
licensed software that requires specific service packs and fixes to be installed (or not installed). It
may be necessary to delay installation of DBMS service packs until vendors of licensed software
have upgraded their products to work with the new versions. Sometimes just the possibility that
a licensed application might fail after a DBMS service pack or fix is applied is sufficient reason to
delay the fix. However, the DBMS is still vulnerable during this period. Pick your regret!
Additionally, database features and functions that are not required by the applications
should be removed or disabled from the DBMS. For example, if TCP/IP is used to connect
to the DBMS, other communications protocols should be removed. This action reduces the
pathways by which unauthorized activity can reach the DBMS. Further, all DBMS products are
installed with system-stored procedures that provide services such as starting a command file,
modifying the system registry, initiating e-mail, and the like. Any of these stored procedures
that are not needed should be removed. If all users are known to the DBMS, default logins
and guest user accounts should be removed as well. Finally, unless otherwise required, users
 
Search WWH ::




Custom Search