You can create MySQL accounts in two ways:
By using statements intended for creating accounts, such as
CREATE USERorGRANTBy manipulating the MySQL grant tables directly with statements such as
INSERT,UPDATE, orDELETE
The preferred method is to use account-creation statements because they are more concise and less error-prone. CREATE USER and GRANT are described in CREATE USER Syntax, and GRANT Syntax.
Another option for creating accounts is to use one of several available third-party programs that offer capabilities for MySQL account administration. phpMyAdmin is one such program.
The following examples show how to use the mysql client program to set up new users. These examples assume that privileges are set up according to the defaults described in Section 2.3, “Securing the Initial MySQL Accounts”. This means that to make changes, you must connect to the MySQL server as the MySQL root user, and the root account must have the INSERT privilege for the mysql database and the RELOAD administrative privilege.
As noted in the examples where appropriate, some of the statements will fail if you have the server's SQL mode has been set to enable certain restrictions. In particular, strict mode (STRICT_TRANS_TABLES, STRICT_ALL_TABLES) and NO_AUTO_CREATE_USER will prevent the server from accepting some of the statements. Workarounds are indicated for these cases. For more information about SQL modes and their effect on grant table manipulation, see Server SQL Modes, and GRANT Syntax.
First, use the mysql program to connect to the server as the MySQL root user:
shell>mysql --user=root mysql
If you have assigned a password to the root account, you'll also need to supply a --password or -p option for this mysql command and also for those later in this section.
After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
->IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
->IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql>GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql>GRANT USAGE ON *.* TO 'dummy'@'localhost';
The accounts created by these GRANT statements have the following properties:
Two of the accounts have a username of
montyand a password ofsome_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts formontyto be able to connect from anywhere asmonty. Without thelocalhostaccount, the anonymous-user account forlocalhostthat is created by mysql_install_db would take precedence whenmontyconnects from the local host. As a result,montywould be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specificHostcolumn value than the'monty'@'%'account and thus comes earlier in theusertable sort order. (usertable sorting is discussed in Section 3.4, “Access Control, Stage 1: Connection Verification”.)One account has a username of
adminand no password. This account can be used only by connecting from the local host. It is granted theRELOADandPROCESSadministrative privileges. These privileges allow theadminuser to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxxcommands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges later by issuing additionalGRANTstatements.One account has a username of
dummyand no password. This account can be used only by connecting from the local host. No privileges are granted. TheUSAGEprivilege in theGRANTstatement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to'N'. It is assumed that you will grant specific privileges to the account later.The statements that create accounts with no password will fail if the
NO_AUTO_CREATE_USERSQL mode is enabled. To deal with this, use anIDENTIFIED BYclause that specifies a non-empty password.
As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables using FLUSH PRIVILEGES:
shell>mysql --user=root mysql
mysql>INSERT INTO user
->VALUES('localhost','monty',PASSWORD('some_pass'),
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO user
->VALUES('%','monty',PASSWORD('some_pass'),
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
->'','','','',0,0,0,0);
mysql>INSERT INTO user SET Host='localhost',User='admin',
->Reload_priv='Y', Process_priv='Y';
mysql>INSERT INTO user (Host,User,Password)
->VALUES('localhost','dummy','');
mysql>FLUSH PRIVILEGES;
The reason for using FLUSH PRIVILEGES when you create accounts with INSERT is to tell the server to re-read the grant tables. Otherwise, the changes go unnoticed until you restart the server. With GRANT, FLUSH PRIVILEGES is unnecessary.
The reason for using the PASSWORD() function with INSERT is to encrypt the password. The GRANT statement encrypts the password for you, so PASSWORD() is unnecessary.
The 'Y' values enable privileges for the accounts. Depending on your MySQL version, you may have to use a different number of 'Y' values in the first two INSERT statements. For the admin account, you may also employ the more readable extended INSERT syntax using SET.
In the INSERT statement for the dummy account, only the Host, User, and Password columns in the user table row are assigned values. None of the privilege columns are set explicitly, so MySQL assigns them all the default value of 'N'. This is equivalent to what GRANT USAGE does.
If strict SQL mode is enabled, all columns that have no default value must have a value specified. In this case, INSERT statements must explicitly specify values for the ssl_cipher, x509_issuer, and x509_subject columns.
Note that to set up a superuser account, it is necessary only to create a user table entry with the privilege columns set to 'Y'. user table privileges are global, so no entries in any of the other grant tables are needed.
The next examples create three accounts and give them access to specific databases. Each of them has a username of custom and password of obscure.
To create the accounts with GRANT, use the following statements:
shell>mysql --user=root mysql
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON bankaccount.*
->TO 'custom'@'localhost'
->IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON expenses.*
->TO 'custom'@'whitehouse.gov'
->IDENTIFIED BY 'obscure';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->ON customer.*
->TO 'custom'@'server.domain'
->IDENTIFIED BY 'obscure';
The three accounts can be used as follows:
The first account can access the
bankaccountdatabase, but only from the local host.The second account can access the
expensesdatabase, but only from the hostwhitehouse.gov.The third account can access the
customerdatabase, but only from the hostserver.domain.
To set up the custom accounts without GRANT, use INSERT statements as follows to modify the grant tables directly:
shell>mysql --user=root mysql
mysql>INSERT INTO user (Host,User,Password)
->VALUES('localhost','custom',PASSWORD('obscure'));
mysql>INSERT INTO user (Host,User,Password)
->VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql>INSERT INTO user (Host,User,Password)
->VALUES('server.domain','custom',PASSWORD('obscure'));
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('localhost','bankaccount','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('whitehouse.gov','expenses','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>INSERT INTO db
->(Host,Db,User,Select_priv,Insert_priv,
->Update_priv,Delete_priv,Create_priv,Drop_priv)
->VALUES('server.domain','customer','custom',
->'Y','Y','Y','Y','Y','Y');
mysql>FLUSH PRIVILEGES;
The first three INSERT statements add user table entries that allow the user custom to connect from the various hosts with the given password, but grant no global privileges (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual when you modify the grant tables directly, you must tell the server to reload them with FLUSH PRIVILEGES so that the privilege changes take effect.
If you want to give a specific user access from all machines in a given domain (for example, mydomain.com), you can issue a GRANT statement that uses the “%” wildcard character in the host part of the account name:
mysql>GRANT ...
->ON *.*
->TO 'myname'@'%.mydomain.com'
->IDENTIFIED BY 'mypass';
To do the same thing by modifying the grant tables directly, do this:
mysql>INSERT INTO user (Host,User,Password,...)
->VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql>FLUSH PRIVILEGES;
Sebagai informasi tambahan, untuk membangun Radio Internet kita juga bisa menggunakan software lain selain Shoutcast, diantaranya adalah: 
kerenn oom, tapi mikrotik saya koq ga bisa ngelimit BW ping ya , padahal udah di mark di mangle (keliatan ada counting) trus di queue simplenya aku naikin paling atas (number 0) ..tapi koq ga ngaruh yaaa…
apa karena masih versi abal-abal..hehehe
Gue rasa ada trik lain untuk masalah yang sama seperti ini Om…
Qta dapat menggunakan /ip firewall address list untuk mendefinisikan IP-IP address yang akan di EXCLUDE… trus selanjutnya di /ip queue tree itu bisa menentukan bahwa bandwidth limiter hanya akan bekerja jika client menuju jaringan selain jaringan yang IP nya telah diexclude diatas… Kira-kira begitu deh… Ini pernah saya lakukan agar komunikasi VOIP di LAN tetap lancar tanpa terkena aturan bandwidth… Thanx
[Comment ID #37 Will Be Quoted Here]
Terimakasih informasinya mas Ewyn
Apakah di tempat mas Ewyn juga dibuat model hotspot ?
Di hotspot ada mekanisme dinamik yang membuat aturan queue berpindah posisi.
Solusi dipasang di queue tree saya rasa benar juga.
Membuat saya ingat pernah membaca bahwa aturan di queue tree akan diprioritaskan terlebih dahulu daripada queue simple.
mas saya masih baru menggunakan mikrotik. saya mau menanyakan, saya sudah mencoba untuk authenticati buat user yang mau menggunakan hotspot tetapi kendala saya jika sesudah logoff langsung log in lagi dengan user yang sama. saya ingin tampilan untuk log in keluar lagi agar user lain dpt menggunakan komputer yang sama dengan user yang berbeda tetapi kenyataannya mikrotik yang saya setting langsung login lagi dengan user sebelumnya. jadi bagaimana caranya jika sudah log off, tampilan log in dapat keluar lagi untuk user yang lain? terimakasih
[Comment ID #40 Will Be Quoted Here]
Mas Hendrik,
Pastikan mikrotik yang anda pakai adalah lisensi
.
Agar setiap kali user logoff langsung memasukkan menu login lagi.
Anda dapat menghilangkan opsi cookie di profile user hotspot mikrotik.
Yupp. Pada mikrotik yg lama (bajaka) kyknya user hotspot gak bisa automatis di Queue.. Asli donk..
Baru tau ternyata da script yg gituan..
om 3wyn dan om admin, trik yang meng-EXCLUDE menggunakan /ip firewall address list itu mohon untuk bisa dijelaskan. Mohon pencerahannya seperti apa cara ngesetnya termasuk ngeset di queue treenya, karena sy bener2 masih nubie. (Sekalian script yg dimasukkan di user profile hotspotnya).
Sangat ditunggu jawabannya, dan mendesak.
Sebelum dan sesudahnya saya ucapkan banyak terima kasih.
untuk script user hotspot dapat dilihat di gambar yang sudah saya buat.
http://network.web.id/wp-content/uploads/2008/02/profile.PNG
maksud saya, yg diexclude nanti adalah koneksi ke JIX dan IIX, sementara koneksi ke Internasional tetap terkena limit.
Untuk model spt yang disampaikan om 3wyn diatas gimana caranya, termsuk setingan di mangle (marking connection & marking packet), karena menurut pemahaman saya pasti melakukan marking packet untuk trafik2 yg exclude tsb.
Sbg permisalan, saya ingin koneksi ke IX (international) di limit 96kbps dan koneksi ke JIX (jogja internet exchange) dan IIX tidak terkena aturan limit yang dibuat otomatis oleh user manager.
Mohon pencerahannya, sampai saat ini permasalahan saya belum terselesaikan.
Terima kasih sebelum dan sesudahnya.
@andy
Untuk membuat agar yang dilimit hanya internasional.
mas Andy buat dulu mangle seperti yang di tutorial mikrotik.co.id.
Lalu set di scriptnya agar dynamix simple queue mengambil parent dari queue internasional.
Saya sudah mengaplikasikan hal ini di hotspot saya dan berfungsi dengan normal.
terima kasih om,
untuk seting mangle udah clear, yg masih belom saya paham adalah masalah membuat script -> agar dynamix simple queue mengambil parent dari queue internasional.
Itu seperti apa dan bagaimana ya om, bisa minta tolong dijeskan sekalian gak om?
Terima kasih dan mohon maaf sebelumnya, karena saya masih nubie
Kenal mikrotik juga masih baru sekali, belom ada 2 bulan, jadi saya mohon penjelasan yg lebih detail.
Sekali lagi saya sampaikan terima kasih atas bantuannya.
Silaken buka post yang ini
http://human.network.web.id/2008/07/24/hotspot-baypass-iix/
tambahan, apabila diperlukan, bisa saya sampaikan info network di tempat saya. Di sini atau lewat e-mail saya manut saja.
Terima kasih.
Baik, bisa minta disend ke email untuk topologinya ?
redkurawa@yahoo.com