- Sams Teach Yourself SQL in 24 Hours, Third Edition
- Table of Contents
- Copyright
- About the Authors
- Acknowledgments
- Tell Us What You Think!
- Introduction
- Part I: A SQL Concepts Overview
- Hour 1. Welcome to the World of SQL
- SQL Definition and History
- SQL Sessions
- Types of SQL Commands
- An Introduction to the Database Used in This Book
- Summary
- Q&A
- Workshop
- Part II: Building Your Database
- Hour 2. Defining Data Structures
- What Is Data?
- Basic Data Types
- Summary
- Q&A
- Workshop
- Hour 3. Managing Database Objects
- What Are Database Objects?
- What Is a Schema?
- A Table: The Primary Storage for Data
- Integrity Constraints
- Summary
- Q&A
- Workshop
- Hour 4. The Normalization Process
- Normalizing a Database
- Summary
- Q&A
- Workshop
- Hour 5. Manipulating Data
- Overview of Data Manipulation
- Populating Tables with New Data
- Updating Existing Data
- Deleting Data from Tables
- Summary
- Q&A
- Workshop
- Hour 6. Managing Database Transactions
- What Is a Transaction?
- What Is Transactional Control?
- Transactional Control and Database Performance
- Summary
- Q&A
- Workshop
- Part III: Getting Effective Results from Queries
- Hour 7. Introduction to the Database Query
- What Is a Query?
- Introduction to the <tt>SELECT</tt> Statement
- Examples of Simple Queries
- Summary
- Q&A
- Workshop
- Hour 8. Using Operators to Categorize Data
- What Is an Operator in SQL?
- Comparison Operators
- Logical Operators
- Conjunctive Operators
- Negating Conditions with the <tt>NOT</tt> Operator
- Arithmetic Operators
- Summary
- Q&A
- Workshop
- Hour 9. Summarizing Data Results from a Query
- What Are Aggregate Functions?
- Summary
- Q&A
- Workshop
- Hour 10. Sorting and Grouping Data
- Why Group Data?
- The <tt>GROUP BY</tt> Clause
- <tt>GROUP BY</tt> Versus <tt>ORDER BY</tt>
- The <tt>HAVING</tt> Clause
- Summary
- Q&A
- Workshop
- Hour 11. Restructuring the Appearance of Data
- The Concepts of ANSI Character Functions
- Various Common Character Functions
- Miscellaneous Character Functions
- Mathematical Functions
- Conversion Functions
- The Concept of Combining Character Functions
- Summary
- Q&A
- Workshop
- Hour 12. Understanding Dates and Times
- How Is a Date Stored?
- Date Functions
- Date Conversions
- Summary
- Q&A
- Workshop
- Part IV: Building Sophisticated Database Queries
- Hour 13. Joining Tables in Queries
- Selecting Data from Multiple Tables
- Types of Joins
- Join Considerations
- Summary
- Q&A
- Workshop
- Hour 14. Using Subqueries to Define Unknown Data
- What Is a Subquery?
- Embedding a Subquery Within a Subquery
- Summary
- Q&A
- Workshop
- Hour 15. Combining Multiple Queries into One
- Single Queries Versus Compound Queries
- Why Would I Ever Want to Use a Compound Query?
- Compound Query Operators
- Using an <tt>ORDER BY</tt> with a Compound Query
- Using <tt>GROUP BY</tt> with a Compound Query
- Retrieving Accurate Data
- Summary
- Workshop
- Q&A
- Part V: SQL Performance Tuning
- Hour 16. Using Indexes to Improve Performance
- What Is an Index?
- How Do Indexes Work?
- The <tt>CREATE INDEX</tt> Command
- Types of Indexes
- When Should Indexes Be Considered?
- When Should Indexes Be Avoided?
- Summary
- Q&A
- Workshop
- Hour 17. Improving Database Performance
- What Is SQL Statement Tuning?
- Database Tuning Versus SQL Tuning
- Formatting Your SQL Statement
- Full Table Scans
- Other Performance Considerations
- Performance Tools
- Summary
- Q&A
- Workshop
- Part VI: Using SQL to Manage Users and Security
- Hour 18. Managing Database Users
- Users Are the Reason
- The Management Process
- Tools Utilized by Database Users
- Summary
- Q&A
- Workshop
- Hour 19. Managing Database Security
- What Is Database Security?
- How Does Security Differ from User Management?
- What Are Privileges?
- Controlling User Access
- Controlling Privileges Through Roles
- Summary
- Q&A
- Workshop
- Part VII: Summarized Data Structures
- Hour 20. Creating and Using Views and Synonyms
- What Is a View?
- Creating Views
- Dropping a View
- What Is a Synonym?
- Summary
- Q&A
- Workshop
- Hour 21. Working with the System Catalog
- What Is the System Catalog?
- How Is the System Catalog Created?
- What Is Contained in the System Catalog?
- Examples of System Catalog Tables by Implementation
- Querying the System Catalog
- Updating System Catalog Objects
- Summary
- Q&A
- Workshop
- Part VIII: Applying SQL Fundamentals in Today's World
- Hour 22. Advanced SQL Topics
- Advanced Topics
- Cursors
- Stored Procedures and Functions
- Triggers
- Dynamic SQL
- Call-Level Interface
- Using SQL to Generate SQL
- Direct Versus Embedded SQL
- Summary
- Q&A
- Workshop
- Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
- SQL and the Enterprise
- Accessing a Remote Database
- Accessing a Remote Database Through a Web Interface
- SQL and the Internet
- SQL and the Intranet
- Summary
- Q&A
- Workshop
- Hour 24. Extensions to Standard SQL
- Various Implementations
- Examples of Extensions from Some Implementations
- Interactive SQL Statements
- Summary
- Q&A
- Workshop
- Part IX: Appendixes
- Appendix A. Common SQL Commands
- SQL Statements
- SQL Clauses
- Appendix B. Using MySQL for Exercises
- Windows Installation Instructions
- Linux Installation Instructions
- Appendix C. Answers to Quizzes and Exercises
- Hour 1, "Welcome to the World of SQL"
- Hour 2, "Defining Data Structures"
- Hour 3, "Managing Database Objects"
- Hour 4, "The Normalization Process"
- Hour 5, "Manipulating Data"
- Hour 6, "Managing Database Transactions"
- Hour 7, "Introduction to the Database Query"
- Hour 8, "Using Operators to Categorize Data"
- Hour 9, "Summarizing Data Results from a Query"
- Hour 10, "Sorting and Grouping Data"
- Hour 11, "Restructuring the Appearance of Data"
- Hour 12, "Understanding Dates and Time"
- Hour 13, "Joining Tables in Queries"
- Hour 14, "Using Subqueries to Define Unknown Data"
- Hour 15, "Combining Multiple Queries into One"
- Hour 16, "Using Indexes to Improve Performance"
- Hour 17, "Improving Database Performance"
- Hour 18, "Managing Database Users"
- Hour 19, "Managing Database Security"
- Hour 20, "Creating and Using Views and Synonyms"
- Hour 21, "Working with the System Catalog"
- Hour 22, "Advanced SQL Topics"
- Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet"
- Hour 24, "Extensions to Standard SQL"
- Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
- <tt>EMPLOYEE_TBL</tt>
- <tt>EMPLOYEE_PAY_TBL</tt>
- <tt>CUSTOMER_TBL</tt>
- <tt>ORDERS_TBL</tt>
- <tt>PRODUCTS_TBL</tt>
- Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
- <tt>INSERT</tt> Statements
- Appendix F. Glossary
- Appendix G. Bonus Exercises
Recommended Book
Recommended Book
Recommended Book
The Management Process
A stable user management system is mandatory for data security in any database system. The user management system starts with the new user's immediate supervisor, who should initiate the access request, and then go through the company's approval authorities, at which time the request, if accepted by management, is routed to the security officer or database administrator, who takes action. A good notification process is necessary; the supervisor and the user must be notified that the user account has been created and that access to the database has been granted. The user account password should only be given to the user, who should immediately change the password upon initial login to the database.
Creating Users
The creation of database users involves the use of SQL commands within the database. There is no one standard command for creating database users in SQL; each implementation has a method for doing so. Some implementations have similar commands, while others vary in syntax. The basic concept is the same, regardless of the implementation. There are several GUI, Graphical User Interface, tools on the market that can be used for user management.
When the database administrator or assigned security officer receives a user account request, the request should be analyzed for the necessary information. The information should include your particular company's requirements for establishing a user ID.
Some items that should be included are Social Security number, full name, address, phone number, office or department name, assigned database, and sometimes, a suggested user ID.
Syntactical examples of creating users compared between two different implementations are shown in the following sections.
Creating Users in Oracle
Steps for creating a user account in an Oracle database:
- Create the database user account with default settings.
- Grant appropriate privileges to the user account.
The following is the syntax for creating a user:
CREATE USER USER_ID IDENTIFIED BY [PASSWORD | EXTERNALLY ] [ DEFAULT TABLESPACE TABLESPACE_NAME ] [ TEMPORARY TABLESPACE TABLESPACE_NAME ] [ QUOTA (INTEGER (K | M) | UNLIMITED) ON TABLESPACE_NAME ] [ PROFILE PROFILE_TYPE ] [PASSWORD EXPIRE |ACCOUNT [LOCK | UNLOCK]
|
If you are not using Oracle, do not overly concern yourself with some of the options in this syntax. A tablespace is a logical area that houses database objects, such as tables and indexes, that is managed by the DBA. The DEFAULT TABLESPACE is the tablespace in which objects created by the particular user reside. The TEMPORARY TABLESPACE is the tablespace used for sort operations (table joins, ORDER BY, GROUP BY) from queries executed by the user. The QUOTA is the space limit placed on a particular tablespace to which the user has access. PROFILE is a particular database profile that has been assigned to the user. |
The following is the syntax for granting privileges to the user account:
The GRANT statement can grant one or more privileges to one or more users in the same statement. The privilege(s) can also be granted to a role, which in turn can be granted to a user(s).
In MySQL, the GRANT command can be used to grant users on the local computer to the current database. For example:
GRANT USAGE ON *.* TO USER@LOCALHOST IDENTIFIED BY 'PASSWORD';
Additional privileges can be granted to a user as follows:
GRANT SELECT ON TABLENAME TO USER@LOCALHOST;
For the most part, multi-user setup and access for MySQL is only required in multi-user environments.
Creating Users in Sybase and Microsoft SQL Server
The steps for creating a user account in a Sybase and Microsoft SQL Server database follow:
- Create the database user account for SQL Server and assign a password and a default database for the user.
- Add the user to the appropriate database(s).
- Grant appropriate privileges to the user account.
The following is the syntax for creating the user account:
The following is the syntax for adding the user to a database:
SP_ADDUSER USER_ID [, NAME_IN_DB [, GRPNAME ] ]
The following is the syntax for granting privileges to the user account:
CREATE SCHEMA
Schemas are created via the CREATE SCHEMA statement.
The following is the syntax:
The following is an example:
CREATE SCHEMA USER1 CREATE TABLE TBL1 (COLUMN1 DATATYPE [NOT NULL], COLUMN2 DATATYPE [NOT NULL]...) CREATE TABLE TBL2 (COLUMN1 DATATYPE [NOT NULL], COLUMN2 DATATYPE [NOT NULL]...) GRANT SELECT ON TBL1 TO USER2 GRANT SELECT ON TBL2 TO USER2 [ OTHER DDL COMMANDS ... ]
The following is the application of the CREATE SCHEMA command in one implementation:
CREATE SCHEMA AUTHORIZATION USER1 CREATE TABLE EMP (ID NUMBER NOT NULL, NAME VARCHAR2(10) NOT NULL) CREATE TABLE CUST (ID NUMBER NOT NULL, NAME VARCHAR2(10) NOT NULL) GRANT SELECT ON TBL1 TO USER2 GRANT SELECT ON TBL2 TO USER2;
Schema created.
The AUTHORIZATION keyword is added to the CREATE SCHEMA command. This example was performed in an Oracle database. This goes to show you, as you have also seen in this book's previous examples, that vendors' syntax for commands often varies in their implementations.
Dropping a Schema
A schema can be removed from the database using the DROP SCHEMA statement. There are two options that must be considered when dropping a schema. First, the RESTRICT option. If RESTRICT is specified, an error occurs if objects currently exist in the schema. The second option is CASCADE. The CASCADE option must be used if any objects currently exist in the schema. Remember that when you drop a schema, you also drop all database objects associated with that schema.
The syntax is as follows:
Altering Users
A very important part of managing users is the ability to alter a user's attributes after user creation. Life for the database administrator would be a lot simpler if personnel with user accounts were never promoted, never left the company, or if the addition of new employees was minimized. In the real world, high personnel turnover, as well as users' responsibilities, are a reality and a significant factor in user management. Nearly everyone changes jobs or job duties, therefore, user privileges in a database must be adjusted to fit a user's needs.
The following is one implementation's example of altering the current state of a user.
For Oracle:
ALTER USER USER_ID [ IDENTIFIED BY PASSWORD | EXTERNALLY |GLOBALLY AS 'CN=USER'] [ DEFAULT TABLESPACE TABLESPACE_NAME ] [ TEMPORARY TABLESPACE TABLESPACE_NAME ] [ QUOTA INTEGER K|M |UNLIMITED ON TABLESPACE_NAME ] [ PROFILE PROFILE_NAME ] [ PASSWORD EXPIRE] [ ACCOUNT [LOCK |UNLOCK]] [ DEFAULT ROLE ROLE1 [, ROLE2 ] | ALL [ EXCEPT ROLE1 [, ROLE2 | NONE ] ]
Many of the user's attributes can be altered in this syntax. Unfortunately, not all implementations provide a simple command that allows the manipulation of database users. Some implementations also provide GUI tools that allow users to be created, modified, and removed.
User Sessions
A user database session is the time that begins at database login and ends when a user logs out. During the time a user is logged in to the database (a user session), the user can perform various actions that have been granted, such as queries and transactions.
An SQL session is initiated when a user connects from the client to the server using the CONNECT statement. Upon the establishment of the connection and the initiation of the session, any number of transactions can be started and performed until the connection is disconnected; at that time, the database user session terminates.
Users can explicitly connect and disconnect from the database, starting and terminating SQL sessions, using commands such as the following:
CONNECT TO DEFAULT | STRING1 [ AS STRING2 ] [ USER STRING3 ] DISCONNECT DEFAULT | CURRENT | ALL | STRING SET CONNECTION DEFAULT | STRING
User sessions can be—and often are—monitored by the database administrator or other personnel having interest in user activities. A user session is associated with a particular user account when a user is monitored. A database user session is ultimately represented as a process on the host operating system.
Removing User Access
Removing a user from the database or disallowing a user's access can easily be accomplished through a couple of simple commands. Once again, however, variations among different implementations are numerous, so you must check your particular implementation for the syntax or tools used to accomplish user removal or access revocation.
Methods for removing user database access:
- Change the user's password.
- Drop the user account from the database.
- Revoke appropriate previously granted privileges from the user.
The DROP command can be used in some implementations to drop a user from the database:
The REVOKE command is the counterpart of the GRANT command in many implementations, allowing privileges that have been granted to a user to be revoked. An example syntax for this command in some implementations follows: