Page 24 - Open Soource Technologies 304.indd

Unit 2: Working with My SQL

• CREATE. This privilege gives the user the right to create new tables in the specified Notes
database with the CREATE TABLE statement.
• ALTER. This privilege gives the user the right to alter all tables of the specified database
with the ALTER TABLE statement.

• DROP. This privilege gives the user the right to remove all tables of the specified database.
• INDEX. This privilege gives the user the right to define and remove indexes on all tables
of the specified database.

• CREATE TEMPORARY TABLES. This privilege gives the user the right to create temporary
tables in the specified database.

• CREATE VIEW. This privilege gives the user the right to create new views in the specified
database with the CREATE VIEW statement.
• CREATE ROUTINE. This privilege gives the user the right to create new stored procedures
and functions for the specified database.

• ALTER ROUTINE. This privilege gives the user the right to update and remove existing
stored procedures and functions of the specified database.

• EXECUTE ROUTINE. This privilege gives the user the right to invoke existing stored
procedures and functions of the specified database.

• LOCK TABLES. This privilege gives the user the right to block existing tables of the specified
database.

• ALL or ALL PRIVILEGES. This privilege is a shortened form for all the privileges just
named.
2.1 Privilege System in SQL

The My SQL privilege system is always on. The first time you try to connect, and for each
subsequent action, My SQL checks the following three things:
Where you are accessing from (your host).

Who you say you are (your username and password).
What you’re allowed to do (your command privileges).

All this information is stored in the database called My SQL, which is automatically created
when My SQL is installed. There are several tables in the My SQL database:

columns_priv Defines user privileges for specific fields within a table.
db Defines the permissions for all databases on the server.

func Defines user-created functions.
host Defines the acceptable hosts that can connect to a specific database.

tables_priv Defines user privileges for specific tables within a database.
user Defines the command privileges for a specific user.

LOVELY PROFESSIONAL UNIVERSITY 19