Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• 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
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • 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 Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • 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
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

What Are Privileges?

Privileges are authority levels used to access the database itself, access objects within the database, manipulate data in the database, and perform various administrative functions within the database. Privileges are issued via the GRANT command and are taken away via the REVOKE command.

Just because a user can connect to a database does not mean that the user can access data within a database. Access to data within the database is handled through these privileges. There are two types of privileges:

1. System privileges
2. Object privileges

System Privileges

System privileges are those that allow database users to perform administrative actions within the database, such as creating a database, dropping a database, creating user accounts, dropping users, dropping and altering database objects, altering the state of objects, altering the state of the database, and other actions that could result in serious repercussions if not carefully used.

System privileges vary greatly among the different relational database vendors, so you must check your particular implementation for all the available system privileges and their correct usage.

The following are some common system privileges in Sybase:

• CREATE DATABASE
• CREATE DEFAULT
• CREATE PROCEDURE
• CREATE RULE
• CREATE VIEW
• DUMP DATABASE
• DUMP TRANSACTION
• EXECUTE

The following are some common system privileges in Oracle:

• CREATE TABLE
• CREATE ANY TABLE
• ALTER ANY TABLE
• DROP TABLE
• CREATE USER
• DROP USER
• ALTER USER
• ALTER DATABASE
• ALTER SYSTEM
• BACKUP ANY TABLE
• SELECT ANY TABLE

The following are some common global (system) privileges in MySQL:

• CREATE
• DROP
• GRANT
• REFERENCES
• FILE
• PROCESS
• RELOAD
• SHUTDOWN

Object Privileges

Object privileges are authority levels on objects, meaning you must have been granted the appropriate privileges to perform certain operations on database objects. For example, to select data from another user's table, the user must first grant you access to do so. Object privileges are granted to users in the database by the object's owner. Remember that this owner is also called the schema owner.

The ANSI standard for privileges includes the following object privileges:

• USAGE Authorizes usage of a specific domain
• SELECT Allows access to a specific table
• INSERT(column_name) Allows data insertion to a specific column of a specified table
• INSERT Allows insertion of data into all columns of a specific table
• UPDATE(column_name) Allows a specific column of a specified table to be updated
• UPDATE Allows all columns of a specified table to be updated
• REFERENCES(column_name) Allows a reference to a specified column of a specified table in integrity constraints; this privilege is required for all integrity constraints
• REFERENCES Allows references to all columns of a specified table

Most implementations of SQL adhere to the standard list of object privileges for controlling access to database objects.

These object-level privileges are those privileges that should be used to grant and restrict access to objects in a schema. These privileges can be used to protect objects in one schema from database users that have access to another schema in the same database.

There are a variety of object privileges available among different implementations not listed in this section. The ability to delete data from another user's object is another common object privilege available in many implementations. Be sure to check your implementation documentation for all the available object-level privileges.

Who Grants and Revokes Privileges?

The DBA is usually the one who issues the GRANT and REVOKE commands, although a security administrator, if one exists, may have the authority to do so. The authority on what to GRANT or REVOKE would come from management and would hopefully be in writing.

The owner of an object must grant privileges to other users in the database on the object. Even the DBA cannot grant database users privileges on objects that do not belong to the DBA, although there are ways to work around that.

Share This