MySQL/Uživatelská rozhraní/CLI/mysql_setpermission

mysql_setpermission je řádková utilita, která usnadňuje správu uživatelských práv databázového systému MySQL.

Příklad

Způsob užití si ukážeme na příkladu:

• Spuštění utility (jako root) - po zadání hesla nás utilita přivítá nápovědou:

$ mysql_setpermission -u root

Password for user root to connect to MySQL: **********

######################################################################
## Welcome to the permission setter 1.4 for MySQL.
## made by Luuk de Boer
######################################################################
What would you like to do:
  1. Set password for an existing user.
  2. Create a database + user privilege for that database
     and host combination (user can only do SELECT)
  3. Create/append user privilege for an existing database
     and host combination (user can only do SELECT)
  4. Create/append broader user privileges for an existing
     database and host combination
     (user can do SELECT,INSERT,UPDATE,DELETE)
  5. Create/append quite extended user privileges for an
     existing database and host combination (user can do
     SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,
     LOCK TABLES,CREATE TEMPORARY TABLES)
  6. Create/append full privileges for an existing database
     and host combination (user has FULL privilege)
  7. Remove all privileges for for an existing database and
     host combination.
     (user will have all permission fields set to N)
  0. exit this program

Předpokládáme, že chceme vytvořit účet adam, kterému chceme přidělit přístupová práva z lokálního stroje: možnost vytváření vlastních databází a manipulace s nimi, zvolíme tedy volbu 5; následuje dialog:

Make your choice [1,2,3,4,5,6,7,0]: 5

Which database from existing databases would you like to select: 
You can choose from: 
  - information_schema 
  - mysql 
  - performance_schema 
  - test 
Which database will it be (case sensitive). Type * for any: 
*
OK, the user entry will NOT be limited to any database
What username is to be created: adam
Username = adam
Would you like to set a password for petr [y/n]: y
What password do you want to specify for adam: 
Type the password again: 
We now need to know from what host(s) the user will connect.
Keep in mind that % means 'from any host' ...
The host please: localhost
Would you like to add another host [yes/no]: no
Okay we keep it with this ...
The following host(s) will be used: localhost.
######################################################################

That was it ... here is an overview of what you gave to me:
The database name       : *
The username            : adam
The host(s)             : localhost
######################################################################

Are you pretty sure you would like to implement this [yes/no]: yes
Okay ... let's go then ...

Everything is inserted and mysql privileges have been reloaded.

Nyní můžeme vyzkoušet, jak nový účet funguje; zalogování:

$ mysql -uadam -p
Enter password: *********
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.5.35-0ubuntu0.13.10.1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Vytvoření a smazání vlastní databáze:

mysql> CREATE DATABASE moje_database;
Query OK, 1 row affected (0.00 sec)

mysql> USE moje_database;
Database changed

mysql> CREATE TABLE nejaka_tabulka (jmeno VARCHAR(10), telefon INTEGER(20), PRIMARY KEY(jmeno));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO nejaka_tabulka VALUES ("adam", 123456789);
Query OK, 1 row affected (0.04 sec)

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_moje_database |
+-------------------------+
| nejaka_tabulka          |
+-------------------------+
1 row in set (0.01 sec)                                     
                                                        
mysql> SELECT * FROM nejaka_tabulka;
+-------+-----------+
| jmeno | telefon   |
+-------+-----------+
| adam  | 123456789 |
+-------+-----------+
1 row in set (0.01 sec)

mysql> DROP TABLE nejaka_tabulka;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM nejaka_tabulka;
ERROR 1146 (42S02): Table 'moje_database.nejaka_tabulka' doesn't exist

mysql> DROP DATABASE moje_database;
Query OK, 0 rows affected (0.00 sec)

mysql> QUIT;
Bye