Visualizza wikitesto di Elencare Gli Utenti Di Un Server SQL

[[Category:Informatica]]
[[Category:Amminstrazione Server]]
[[Category:sysadmin]]
[[Category:Database]]
Do per scontato che si sia già connessi al Server SQL e che il sistema ci riconosca con i diritti di amministrazione;<br>
passo quindi ad illustrare i comandi necessari per elencare gli utenti riconosciuti.<br>
<br>
Visulaizzare semplicemente tutti gli utenti del server SQL<br>
<div id="atscreen"> 
MariaDB [(none)]> SELECT User FROM mysql.user;<br>
+-------------+<br>
| User &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
+-------------+<br>
| iu6crh &nbsp;&nbsp;&nbsp;&nbsp; |<br>
| skid &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| mariadb.sys |<br>
| mysql &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| phpmyadmin &nbsp;|<br>
| root &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| elettra &nbsp;&nbsp;&nbsp; |<br>
+-------------+<br>
7 rows in set (0.350 sec)<br>
<br>
MariaDB [(none)]>
</div><br>
Potrebbe essere necessario sapere da dove possa connettersi un utente per avere questa informazione cambiamo la query come segue<br>
<div id="atscreen"> 
MariaDB [(none)]> SELECT User, Host FROM mysql.user;<br>
+-------------+-----------+<br>
| User &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Host &nbsp;&nbsp;&nbsp;&nbsp; |<br>
+-------------+-----------+<br>
| iu6crh &nbsp;&nbsp;&nbsp;&nbsp; | % &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| skid &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | % &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| mariadb.sys | localhost |<br>
| mysql &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | localhost |<br>
| phpmyadmin &nbsp;| localhost |<br>
| root &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | localhost |<br>
| elettra &nbsp;&nbsp;&nbsp; | localhost |<br>
+-------------+-----------+<br>
7 rows in set (0.025 sec)<br>
<br>
MariaDB [(none)]><br>
</div><br>
Se necessario si possono ottenere ulteriori dati arricchendo questa query<br>
<div id="atscreen"> 
MariaDB [(none)]> SELECT User, Host, Password, password_expired FROM mysql.user;<br>
+-------------+-----------+-------------------------------------------+------------------+<br>
| User &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Host &nbsp;&nbsp;&nbsp;&nbsp; | Password &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | password_expired |<br>
+-------------+-----------+-------------------------------------------+------------------+<br>
| mariadb.sys | localhost | &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | N &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| root &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | localhost | invalid &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | N &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| mysql &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | localhost | invalid &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | N &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| phpmyadmin &nbsp;| localhost | *7C6C528F6DAFEEEF6ADDF0E1ED1DA44E7F93432A | N &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| skid &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | % &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | *8FEEED1D6AE5240E176DA934D3EDFCFF2AFA47C6 | N &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| iu6crh &nbsp;&nbsp;&nbsp;&nbsp; | % &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | *A4407528FEEAEC6CF6AED1D934D32AE17EDFFF6D | N &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
| elettra &nbsp;&nbsp;&nbsp; | localhost | *ED1D7AE52D9AEEA440E17EDF8F662FFA34D3C6CF | N &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
+-------------+-----------+-------------------------------------------+------------------+<br>
7 rows in set (0.003 sec)<br>
<br>
MariaDB [(none)]> <br>
</div><br>
Una ulteriore necessità potrebbe essere sapere '''QUALE''' utente e "legato" a '''QUALE''' DB<br>
Si puo' derimere questa matassa con un semplicissimo '''<code>SELECT db, host, user FROM mysql.db</code>'''; che c mostrerà riga per riga DB Host di connessione e relativo Utente<br>
<div id="atscreen"> 
MariaDB [(none)]> SELECT db, host, user FROM mysql.db;<br>
+-------------+-----------+---------+<br>
| db &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | host &nbsp;&nbsp;&nbsp;&nbsp; | user &nbsp;&nbsp; |<br>
+-------------+-----------+---------+<br>
| elettrawiki | localhost | elettra |<br>
+-------------+-----------+---------+<br>
1 row in set (0.004 sec)<br>
<br>
MariaDB [(none)]><br>
</div><br>
Questo mi permette di comprendere che lo user '''<code>elettra</code>''' può connettersi '''localmente''' al DB '''<code>elettrawiki</code>'''<br>
<br>
Ed anche questa serie di trick è conclusa