Monday, March 22, 2010

Size of MySQL Database and More

Get the size of the xyz database

du -ch data/xyz/* | grep total
808K

Create a User and Grant Him Privileges 

mysql> use xyz; 
Database changed

mysql> show tables;                                                                                                                                                                                                             
+------------------+
| Tables_in_xyz    |
+------------------+
| actor            | 
+------------------+
1 row in set (0.00 sec)

mysql> create user 'clerk'@'localhost' identified by 'aPassword';
Query OK, 0 rows affected (0.01 sec)

mysql> grant delete,insert,select,update on xyz.actor to 'clerk'@'localhost'; 
Query OK, 0 rows affected (0.00 sec)

Alternatively, Grant All 

mysql> grant all on xyz.* to ’clerk’@'localhost';

Change the User's Password and the Necessary Subsequent Step

mysql> update mysql.user set password = Password('aPassword') where user = 'clerk'@'localhost'; 
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

Alternative for Setting Password

mysql> set password for 'clerk'@'localhost' = Password('aPassword');

Verify the User Exists 

mysql> select user, host from mysql.user;

Verify Privileges Granted a User 

mysql> select * from information_schema.table_privileges where GRANTEE = '''clerk''@''localhost''';
+-----------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE               | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------+---------------+--------------+------------+----------------+--------------+
| 'manager'@'localhost' | NULL          | xyz          | staff      | SELECT         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | actor_info | SELECT         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | actor_info | INSERT         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | actor_info | UPDATE         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | film_list  | SELECT         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | actor      | SELECT         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | actor      | INSERT         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | actor      | UPDATE         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | actor      | DELETE         | NO           | 
| 'manager'@'localhost' | NULL          | xyz          | payment    | SELECT         | NO           | 
+-----------------------+---------------+--------------+------------+----------------+--------------+
10 rows in set (0.00 sec)

Column Level Security

mysql> revoke all on xyz.staff from 'manager'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select(staff_id, first_name, last_name, address_id, picture, email, 
store_id, active, username, last_update) on xyz.staff to 'manager'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Quickest Way To Run a MySQL Command

mysql -t --user=ghelton --password=aPassword -e "select * from classicmodels.Orders;"


at