Cara menggunakan grant process privilege mysql

Question

To begin editing privileges in MySQL, you must first login to your server and then connect to the mysql client. Typically you’ll want to connect with root or whichever account is your primary, initial ‘super user’ account that has full access throughout the entire MySQL installation.

Table of Contents Show

Connecting to the MySQL Command-Line Tool
Granting Privileges
Privilege Types
Database-Specific Privileges
Creating Another Super User
Saving Your Changes
Apa yang dimaksud dengan hak akses privileges pada MySQL?
Bagaimana cara membuat hak akses user di MySQL?
Jelaskan hak akses privileges apa sajakah yang bisa diberikan pada user?
Apa yang dimaksud with grant option?

Typically the root user will have been assigned an authentication password when MySQL was installed, but if that is not the case, you should take steps to up your security by adding root passwords as illustrated in the official documentation.

Connecting to the MySQL Command-Line Tool

For this example, we’ll assume root is the primary MySQL account. To begin using the MySQL Command-Line Tool (

$ mysql --user=username

1), connect to your server as the root user, then issue the mysql command:

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 112813
Server version: 5.5.43-0ubuntu0.14.04.1 (Ubuntu)
[...]
mysql>

If successful, you’ll see some output about your MySQL connection and be facing down the mysql prompt.

Note: In the event that you’re unable to connect directly to the server as the root user before connecting to mysql, you can specify the user you wish to connect as by adding the

$ mysql --user=username

7 flag:

$ mysql --user=username

Granting Privileges

Now that you are at the

$ mysql --user=username

1 prompt, you need only issue the

$ mysql --user=username

9 command with the necessary options to apply the appropriate permissions.

Privilege Types

The

$ mysql --user=username

9 command is capable of applying a wide variety of privileges, everything from the ability to

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

1 tables and databases, read or write

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

2, and even

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

3 the server. There are a wide range of flags and options available to the command, so you may wish to familiarize yourself with what

$ mysql --user=username

9 can actually do by browsing through the official documentation.

Database-Specific Privileges

In most cases, you’ll be granting privileges to MySQL users based on the particular

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

5 that account should have access to. It is common practice, for example, for each unique MySQL

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

5 on a server to have its own unique

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

7 associated with it, such that only one single

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

7 has authentication access to one single

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

5 and vice-versa.

To

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

0 privileges to a

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

7, allowing that user full control over a specific

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

5, use the following syntax:

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

With that command, we’ve told MySQL to:

$ mysql --user=username

9 the

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

4 of type

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

5 (thus everything of course). Note: Most modern MySQL installations do not require the optional

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

4 keyword.

These privileges are for

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

7 and it applies to all tables of that database, which is indicated by the

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

8 that follows.

These privileges are assigned to

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

9 when that

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

9 is connected through locally, as specified by

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

1. To specify any valid host, replace

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

2 with

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

3.

Rather than providing all privileges to the entire database, perhaps you want to give the

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

4 user only the ability to read data (

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

5) from the

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

6 table of the

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

7 database. That would be easily accomplished like so:

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

Creating Another Super User

While not particularly secure, in some cases you may wish to create another ‘super user’, that has ALL privileges across ALL databases on the server. That can be performed similar to above, but by replacing the

mysql> GRANT ALL PRIVILEGES ON books.authors  TO 'tolkien'@'localhost';

7 with the wildcard asterisk:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

Now

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';

4 has the same privileges as the default root account, beware!

Saving Your Changes

As a final step following any updates to the user privileges, be sure to save the changes by issuing the

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

1 command from the mysql prompt:

Apa yang dimaksud dengan hak akses privileges pada MySQL?

Secara singkat, maksud User Privilege MySQL adalah hak akses yang diberikan untuk pengguna tertentu agar bisa mengakses database beserta isinya di MySQL. Hak akses ini bersifat custom, artinya masing-masing pengguna bisa punya privilege yang berbeda.

Bagaimana cara membuat hak akses user di MySQL?

Cara Membuat Hak Akses Tertentu untuk User MySQL.

CREATE – memperbolehkan user untuk membuat database atau tabel..

SELECT – memperbolehkan user untuk mengambil data..

INSERT – memperbolehkan user untuk memodifikasi entri yang ada di tabel..

DELETE – memperbolehkan user untuk menghapus entri tabel..

Jelaskan hak akses privileges apa sajakah yang bisa diberikan pada user?

hak_akses adalah privileges yang akan berikan kepada user tersebut. Hak akses disini berisi query yang diperbolehkan, seperti: SELECT, INSERT, UPDATE, DELETE, atau query lainnya. Jika kita ingin memberikan hak penuh untuk semua query dasar tersebut, hak_akses ini bisa diisi dengan ALL.

Apa yang dimaksud with grant option?

GRANT OPTION adalah perintah yang dituliskan jika kita mengizinkan user tersebut bisa memberikan hak akses kepada user lainnya. nama_database adalah nama database yang ingin diberikan hak akses.

kode mysql