?? 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="../buttonart/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/ch11.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch13/ch13.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/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 statementsand constructs that enable you to administer and effectively manage a relationaldatabase. Like many other topics you have studied thus far, how a database managementsystem implements security varies widely among products. We focus on the populardatabase product Oracle7 to introduce this topic. By the end of the day, you willunderstand 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 professionalsenter 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 youto work on a brand-new project that obviously required some type of relational databasedesign, what would be your first step? After choosing some type of hardware and softwarebaseline, 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 graphicaluser interface designer, another a low-level component builder. Perhaps you, afterreading this book, might be asked to code the SQL queries to provide the guts ofthe application. Along with this task comes the responsibility of actually administeringand maintaining the database.</P><P>Many times, little thought or planning goes into the actual production phase ofthe application. What happens when many users are allowed to use the applicationacross a wide area network (WAN)? With today's powerful personal computer softwareand with technologies such as Microsoft's Open Database Connectivity (ODBC), anyuser with access to your network can find a way to get at your database. (We won'teven bring up the complexities involved when your company decides to hook your LANto the Internet or some other wide-ranging computer network!) Are you prepared toface this situation?</P><P>Fortunately for you, software manufacturers provide most of the tools you needto handle this security problem. Every new release of a network operating systemfaces more stringent security requirements than its predecessors. In addition, mostmajor database vendors build some degree of security into their products, which existsindependently of your operating system or network security. Implementation of thesesecurity 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 phaseof 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 yourproduction database can reach the hundreds or even thousands. In addition, when youdecide to use a particular database product, the chances are good that you will staywith that product for years to come. Here are some points to keep in mind when youexamine 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 softwareis suitable for every application. If you are in a business environment, your optionsmay be limited. Factors such as cost and performance are extremely important. However,without adequate security measures, any savings your database creates can be easilyoffset 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 thedatabases you have created. Has it occurred to you that you might not want otherusers 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 discoveredthat the database you had slaved over had been dropped (remember how silent the <TT>DROPDATABASE</TT> command is)? We examine in some detail how one popular database managementsystem (Personal Oracle7) enables you to set up a secure database. You will be ableto apply most of this information to other database management systems, so make sureyou 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. TheSQL syntax used to create a new user follows.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">CREATE USER userIDENTIFIED {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 entera 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 mypassword: <TT>CUTIGER</TT>.</P><P>If the <TT>EXTERNALLY</TT> option is chosen, Oracle relies on your computer systemlogon name and password. When you log on to your system, you have essentially loggedon 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, Oraclealso allows you to set up default tablespaces and quotas. You can learn more aboutthese 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 passwordand 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 theuser'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 droppedalong with the user's account. If <TT>CASCADE</TT> is not used and the user denotedby <TT>user_name</TT> still owns objects, that user is not dropped. This featureis 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 certainfunctions 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 otherusers. 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 haveexhausted the limits of your permissions. You can log on, but that is about all youcan 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></BLOCKQUOTE><H4><FONT COLOR="#000077">The Connect Role</FONT></H4><P>The Connect role can be thought of as the entry-level role. A user who has beengranted Connect role access can be granted various privileges that allow him or herto do something with a database.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>GRANT CONNECT TO Bryan;</B>Grant succeeded.</FONT></PRE><P>The Connect role enables the user to select, insert, update, and delete recordsfrom tables belonging to other users (after the appropriate permissions have beengranted). The user can also create tables, views, sequences, clusters, and synonyms.<H4><FONT COLOR="#000077">The Resource Role</FONT></H4><P>The Resource role gives the user more access to Oracle databases. In additionto the permissions that can be granted to the Connect role, Resource roles can alsobe granted permission to create procedures, triggers, and indexes.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>GRANT RESOURCE TO Bryan;</B>Grant succeeded.</FONT></PRE><H4><FONT COLOR="#000077">The DBA Role</FONT></H4><P>The DBA role includes all privileges. Users with this role are able to do essentiallyanything they want to the database system. You should keep the number of users withthis role to a minimum to ensure system integrity.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL><B> GRANT DBA TO Bryan;</B>Grant succeeded.</FONT></PRE><P>After the three preceding steps, user Bryan was granted the Connect, Resource,and DBA roles. This is somewhat redundant because the DBA role encompasses the othertwo roles, so you can drop them now:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>REVOKE CONNECT FROM Bryan;</B>Revoke succeeded.SQL> <B>REVOKE RESOURCE FROM Bryan;</B>Revoke succeeded.</FONT></PRE><P>Bryan can do everything he needs to do with the DBA role.<H3><FONT COLOR="#000077">User Privileges</FONT></H3><P>After you decide which roles to grant your users, your next step is deciding whichpermissions these users will have on database objects. (Oracle7 calls these permissionsprivileges.) The types of privileges vary, depending on what role you have been granted.If you actually create an object, you can grant privileges on that object to otherusers as long as their role permits access to that privilege. Oracle defines twotypes of privileges that can be granted to users: system privileges and object privileges.(See Tables 12.1 and 12.2.)</P><P>System privileges apply systemwide. The syntax used to grant a system privilegeis as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">GRANT system_privilege TO {user_name | role | PUBLIC}[WITH ADMIN OPTION];</FONT></PRE><P><TT>WITH ADMIN OPTION</TT> enables the grantee to grant this privilege to someoneelse.<H3><FONT COLOR="#000077">User Access to Views</FONT></H3><P>The following command permits all users of the system to have <TT>CREATE VIEW</TT>access within their own schema.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>GRANT CREATE VIEW</B> 2 <B>TO PUBLIC;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">Grant succeeded.</FONT></PRE><H5>ANALYSIS:</H5><P>The <TT>public</TT> keyword means that everyone has <TT>CREATE VIEW</TT> privileges.Obviously, these system privileges enable the grantee to have a lot of access tonearly all the system settings. System privileges should be granted only to specialusers or to users who have a need to use these privileges. Table 12.1 shows the systemprivileges you will find in the help files included with Personal Oracle7.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Use caution when granting privileges to <TT>public</TT>. Granting <TT>public</TT> gives all users with access to the database privileges you may not want them to have. <HR></BLOCKQUOTE>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -