Page 26 - Open Soource Technologies 304.indd

Unit 2: Working with My SQL

| 1 | blah | Notes
| 2 | blah blah |
+——+——————+

2 rows in set (0.0 sec)
My SQL> insert into test_table values (‘’, ‘my text’);

Error 1044: Access denied for user: ‘joe@localhost’ (Using password: YES)
Action-based permissions are common in applications with several levels of administration. For
example, if you have created an application containing personal financial data, you might grant
only SELECT privileges to entry-level staff members, but INSERT and DELETE privileges to
executive-level staff with security clearances.

2.2 Working with User Privileges

In most cases when you are accessing My SQL through an Internet service provider, you will
have only one user and one database available to you. By default, that one user will have access
to all tables in that database and will be allowed to perform all commands. In this case, the
responsibility is yours as the developer to create a secure application through your programming.
If you are the administrator of your own server or have the ability to add as many databases
and users as you want, as well as modify the access privileges of your users, these next few
sections will take you through the processes of doing so.

2.2.1 Adding Users Through My SQL
Administering your server through a third-party application might afford you a simple method
for adding users, using a wizard-like process or a graphical interface. However, adding users
through the My SQL monitor is not difficult, especially if you understand the security checkpoints
used by My SQL, which you just learned.
The simplest method for adding new users is the GRANT command. By connecting to My SQL
as the root user, you can issue one command to set up a new user. The other method is to issue
INSERT statements into all the relevant tables in the My SQL database, which requires you to
know all the fields in the tables used to store permissions. This method works just as well but
is more complicated than the simple GRANT command.
The simple syntax of the GRANT command is:
GRANT privileges.
ON databasename.tablename.

TO username@host.
IDENTIFIED BY “password”;
The privileges you can grant are:

ALL Gives the user all the following privileges.
ALTER User can alter (modify) tables, columns, and indexes.
CREATE User can create databases and tables.

DELETE User can delete records from tables.
DROP User can drop (delete) tables and databases.

LOVELY PROFESSIONAL UNIVERSITY 21