📄 ch12.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 12 -- Database Security</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ch11.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch11.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch13.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch13.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 12 -<BR>
Database Security</FONT></H1>
</CENTER>
<P>Today we discuss database security. We specifically look at various SQL statements
and constructs that enable you to administer and effectively manage a relational
database. Like many other topics you have studied thus far, how a database management
system implements security varies widely among products. We focus on the popular
database product Oracle7 to introduce this topic. By the end of the day, you will
understand and be able to do the following:
<UL>
<LI>Create users
<P>
<LI>Change passwords
<P>
<LI>Create roles
<P>
<LI>Use views for security purposes
<P>
<LI>Use synonyms in place of views
</UL>
<H2><FONT COLOR="#000077">Wanted: Database Administrator</FONT></H2>
<P>Security is an often-overlooked aspect of database design. Most computer professionals
enter the computer world with some knowledge of computer programming or hardware,
and they tend to concentrate on those areas. For instance, if your boss asked you
to work on a brand-new project that obviously required some type of relational database
design, what would be your first step? After choosing some type of hardware and software
baseline, you would probably begin by designing the basic database for the project.
This phase would gradually be split up among several people--one of them a graphical
user interface designer, another a low-level component builder. Perhaps you, after
reading this book, might be asked to code the SQL queries to provide the guts of
the application. Along with this task comes the responsibility of actually administering
and maintaining the database.</P>
<P>Many times, little thought or planning goes into the actual production phase of
the application. What happens when many users are allowed to use the application
across a wide area network (WAN)? With today's powerful personal computer software
and with technologies such as Microsoft's Open Database Connectivity (ODBC), any
user with access to your network can find a way to get at your database. (We won't
even bring up the complexities involved when your company decides to hook your LAN
to the Internet or some other wide-ranging computer network!) Are you prepared to
face this situation?</P>
<P>Fortunately for you, software manufacturers provide most of the tools you need
to handle this security problem. Every new release of a network operating system
faces more stringent security requirements than its predecessors. In addition, most
major database vendors build some degree of security into their products, which exists
independently of your operating system or network security. Implementation of these
security features varies widely from product to product.
<H2><FONT COLOR="#000077">Popular Database Products and Security</FONT></H2>
<P>As you know by now, many relational database systems are vying for your business.
Every vendor wants you for short- and long-term reasons. During the development phase
of a project, you might purchase a small number of product licenses for testing,
development, and so forth. However, the total number of licenses required for your
production database can reach the hundreds or even thousands. In addition, when you
decide to use a particular database product, the chances are good that you will stay
with that product for years to come. Here are some points to keep in mind when you
examine these products:
<UL>
<LI>Microsoft FoxPro database management system is a powerful database system that
is used primarily in single-user environments. FoxPro uses a limited subset of SQL.
No security measures are provided with the system. It also uses an Xbase file format,
with each file containing one table. Indexes are stored in separate files.
<P>
<LI>Microsoft Access relational database management system implements more of SQL.
Access is still intended for use on the PC platform, although it does contain a rudimentary
security system. The product enables you to build queries and store them within the
database. In addition, the entire database and all its objects exist within one file.
<P>
<LI>Oracle7 relational database management system supports nearly the full SQL standard.
In addition, Oracle has added its own extension to SQL, called PL*SQL. It contains
full security features, including the capability to create roles and assign permissions
and privileges on objects in the database.
<P>
<LI>Sybase SQL Server is similar in power and features to the Oracle product. SQL
Server also provides a wide range of security features and has its own extensions
to the SQL language, called Transact-SQL.
</UL>
<P>The purpose behind describing these products is to illustrate that not all software
is suitable for every application. If you are in a business environment, your options
may be limited. Factors such as cost and performance are extremely important. However,
without adequate security measures, any savings your database creates can be easily
offset by security problems.
<H2><FONT COLOR="#000077">How Does a Database Become Secure?</FONT></H2>
<P>Up to this point you haven't worried much about the "security" of the
databases you have created. Has it occurred to you that you might not want other
users to come in and tamper with the database information you have so carefully entered?
What would your reaction be if you logged on to the server one morning and discovered
that the database you had slaved over had been dropped (remember how silent the <TT>DROP
DATABASE</TT> command is)? We examine in some detail how one popular database management
system (Personal Oracle7) enables you to set up a secure database. You will be able
to apply most of this information to other database management systems, so make sure
you read this information even if Oracle is not your system of choice.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Keep the following questions in mind
as you plan your security system:
</BLOCKQUOTE>
<UL>
<LI>Who gets the DBA role?
<P>
<LI>How many users will need access to the database?
<P>
<LI>Which users will need which privileges and which roles?
<P>
<LI>How will you remove users who no longer need access to the database?
<HR>
</UL>
<H2><FONT COLOR="#000077">Personal Oracle7 and Security</FONT></H2>
<P>Oracle7 implements security by using three constructs:
<UL>
<LI>Users
<P>
<LI>Roles
<P>
<LI>Privileges
</UL>
<H3><FONT COLOR="#000077">Creating Users</FONT></H3>
<P>Users are account names that are allowed to log on to the Oracle database. The
SQL syntax used to create a new user follows.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile]
</FONT></PRE>
<P>If the <TT>BY password</TT> option is chosen, the system prompts the user to enter
a password each time he or she logs on. As an example, create a username for yourself:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>CREATE USER Bryan IDENTIFIED BY CUTIGER;</B>
User created.
</FONT></PRE>
<P>Each time I log on with my username <TT>Bryan</TT>, I am prompted to enter my
password: <TT>CUTIGER</TT>.</P>
<P>If the <TT>EXTERNALLY</TT> option is chosen, Oracle relies on your computer system
logon name and password. When you log on to your system, you have essentially logged
on to Oracle.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some implementations allow you to
use the external, or operating system, password as a default when using SQL (<TT>IDENTIFIED
</TT>externally). However, we recommend that you force the user to enter a password
by utilizing the <TT>IDENTIFIED BY</TT> clause (<TT>IDENTIFIED BY</TT> password).
<HR>
</BLOCKQUOTE>
<P>As you can see from looking at the rest of the <TT>CREATE USER</TT> syntax, Oracle
also allows you to set up default tablespaces and quotas. You can learn more about
these topics by examining the Oracle documentation.</P>
<P>As with every other <TT>CREATE</TT> command you have learned about in this book,
there is also an <TT>ALTER USER</TT> command. It looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">ALTER USER user
[IDENTIFIED {BY password | EXTERNALLY}]
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile]
[DEFAULT ROLE { role [, role] ...
| ALL [EXCEPT role [, role] ...] | NONE}]
</FONT></PRE>
<P>You can use this command to change all the user's options, including the password
and profile. For example, to change the user Bryan's password, you type this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>ALTER USER Bryan</B>
2 <B>IDENTIFIED BY ROSEBUD;</B>
User altered.
</FONT></PRE>
<P>To change the default tablespace, type this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>ALTER USER RON</B>
2 <B>DEFAULT TABLESPACE USERS;</B>
User altered.
</FONT></PRE>
<P>To remove a user, simply issue the <TT>DROP USER</TT> command, which removes the
user's entry in the system database. Here's the syntax for this command:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DROP USER user_name [CASCADE];
</FONT></PRE>
<P>If the <TT>CASCADE</TT> option is used, all objects owned by username are dropped
along with the user's account. If <TT>CASCADE</TT> is not used and the user denoted
by <TT>user_name</TT> still owns objects, that user is not dropped. This feature
is somewhat confusing, but it is useful if you ever want to drop users.
<H3><FONT COLOR="#000077">Creating Roles</FONT></H3>
<P>A role is a privilege or set of privileges that allows a user to perform certain
functions in the database. To grant a role to a user, use the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">GRANT role TO user [WITH ADMIN OPTION];
</FONT></PRE>
<P>If <TT>WITH ADMIN OPTION</TT> is used, that user can then grant roles to other
users. Isn't power exhilarating?</P>
<P>To remove a role, use the <TT>REVOKE</TT> command:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">REVOKE role FROM user;
</FONT></PRE>
<P>When you log on to the system using the account you created earlier, you have
exhausted the limits of your permissions. You can log on, but that is about all you
can do. Oracle lets you register as one of three roles:
<UL>
<LI>Connect
<P>
<LI>Resource
<P>
<LI>DBA (or database administrator)
</UL>
<P>These three roles have varying degrees of privileges.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you have the appropriate privileges,
you can create your own role, grant privileges to your role, and then grant your
role to a user for further security.
<HR>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -