KEMBAR78
Studi Kasus Grant | PDF | My Sql | Databases
Scribd
Upload
39 views10 pages

Studi Kasus Grant

This document discusses implementing grants and revokes in MariaDB. It shows how to create databases, tables, users, and grant various permissions to users. Several users are created and granted different permissions, and their ability to perform various actions like SELECT, INSERT, UPDATE and DELETE is then tested by logging in as each user.

Uploaded by

Hartini Damanik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
39 views10 pages

Studi Kasus Grant

This document discusses implementing grants and revokes in MariaDB. It shows how to create databases, tables, users, and grant various permissions to users. Several users are created and granted different permissions, and their ability to perform various actions like SELECT, INSERT, UPDATE and DELETE is then tested by logging in as each user.

Uploaded by

Hartini Damanik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 10

IMPLEMENTASI GRANT - REVOKE

C:\xampp\mysql\bin>mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> create database dbstikomtb;


Query OK, 1 row affected (0.004 sec)

MariaDB [(none)]> use dbstikomtb;


Database changed

MariaDB [dbstikomtb]> create table prodi_mi(


-> nim char(9) primary key,
-> nama char(50),
-> umur int,
-> tempat_lahir char(50),
-> ipk decimal(3,2)
-> );
Query OK, 0 rows affected (0.031 sec)

MariaDB [dbstikomtb]> create table prodi_ka(


-> nim char(9) primary key,
-> nama char(50),
-> umur int,
-> tempat_lahir char(50),
-> ipk decimal(3,2)
-> );
Query OK, 0 rows affected (0.013 sec)
MariaDB [dbstikomtb]> create table prodi_si(
-> nim char(9) primary key,
-> nama char(50),
-> umur int,
-> tempat_lahir char(50),
-> ipk decimal(3,2)
-> );
Query OK, 0 rows affected (0.019 sec)

MariaDB [dbstikomtb]> create table prodi_ti(


-> nim char(9) primary key,
-> nama char(50),
-> umur int,
-> tempat_lahir char(50),
-> ipk decimal(3,2)
-> );
Query OK, 0 rows affected (0.016 sec)

MariaDB [dbstikomtb]> insert into prodi_mi


-> values('9901','andi Surya',23,'Jakarta',2.70),
-> ('9902','Santi Syahnum',21,'Malang',3.20),
-> ('9903','Neli Situmorang',22,'Medan',3.20);
Query OK, 3 rows affected (0.018 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [dbstikomtb]> insert into prodi_ka


-> values('2001','yogi pratama',23,'Jakarta',2.70),
-> ('2002','susi susanti',21,'Malang',3.20),
-> ('2003','meri simanjuntak',22,'Medan',3.20);
Query OK, 3 rows affected (0.010 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [dbstikomtb]> insert into prodi_si


-> values('2101','safrizal ridho',23,'Jakarta',2.70),
-> ('2102','zahwa sarah',21,'Malang',3.20),
-> ('2103','maria simanurung',22,'Medan',3.20);
Query OK, 3 rows affected (0.010 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [dbstikomtb]> insert into prodi_ti
-> values('2201','Ari wibowo',23,'Jakarta',2.70),
-> ('2202','roswin husaini',21,'Malang',3.20),
-> ('2203','didik arianto',22,'palembang',3.20);
Query OK, 3 rows affected (0.009 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [dbstikomtb]> select * from prodi_mi;


+------+-----------------+------+--------------+------+
| nim | nama | umur | tempat_lahir | ipk |
+------+-----------------+------+--------------+------+
| 9901 | andi Surya | 23 | Jakarta | 2.70 |
| 9902 | Santi Syahnum | 21 | Malang | 3.20 |
| 9903 | Neli Situmorang | 22 | Medan | 3.20 |
+------+-----------------+------+--------------+------+
3 rows in set (0.010 sec)

MariaDB [dbstikomtb]> select * from prodi_ka;


+------+------------------+------+--------------+------+
| nim | nama | umur | tempat_lahir | ipk |
+------+------------------+------+--------------+------+
| 2001 | yogi pratama | 23 | Jakarta | 2.70 |
| 2002 | susi susanti | 21 | Malang | 3.20 |
| 2003 | meri simanjuntak | 22 | Medan | 3.20 |
+------+------------------+------+--------------+------+
3 rows in set (0.001 sec)

MariaDB [dbstikomtb]> select * from prodi_si;


+------+------------------+------+--------------+------+
| nim | nama | umur | tempat_lahir | ipk |
+------+------------------+------+--------------+------+
| 2101 | safrizal ridho | 23 | Jakarta | 2.70 |
| 2102 | zahwa sarah | 21 | Malang | 3.20 |
| 2103 | maria simanurung | 22 | Medan | 3.20 |
+------+------------------+------+--------------+------+
3 rows in set (0.001 sec)
MariaDB [dbstikomtb]> select * from prodi_ti;
+------+----------------+------+--------------+------+
| nim | nama | umur | tempat_lahir | ipk |
+------+----------------+------+--------------+------+
| 2201 | Ari wibowo | 23 | Jakarta | 2.70 |
| 2202 | roswin husaini | 21 | Malang | 3.20 |
| 2203 | didik arianto | 22 | palembang | 3.20 |
+------+----------------+------+--------------+------+
3 rows in set (0.001 sec)

MEMBUAT USERNAME DAN PASSWORD


MariaDB [dbstikomtb]> create user 'mhs_mi'@'localhost' IDENTIFIED BY 'mhs123';
Query OK, 0 rows affected (0.015 sec)

MariaDB [dbstikomtb]> create user 'operator_mi'@'localhost' IDENTIFIED BY 'op1234';


Query OK, 0 rows affected (0.008 sec)

MariaDB [dbstikomtb]> create user 'admin_mi'@'localhost' IDENTIFIED BY 'admin123';


Query OK, 0 rows affected (0.003 sec)

MariaDB [dbstikomtb]> create user 'staf_mi'@'localhost' IDENTIFIED BY 'staf123';


Query OK, 0 rows affected (0.008 sec)

MEMBERIKAN PERINTAH GRANT

MariaDB [dbstikomtb]> GRANT SELECT ON dbstikomtb.prodi_mi to 'mhs_mi'@'localhost';


Query OK, 0 rows affected (0.009 sec)

MariaDB [dbstikomtb]> GRANT ALL ON dbstikomtb.prodi_mi to 'admin_mi'@'localhost';


Query OK, 0 rows affected (0.008 sec)

MariaDB [dbstikomtb]> GRANT INSERT,UPDATE,DELETE ON dbstikomtb.prodi_mi to 'operator_mi'@'localhost';


Query OK, 0 rows affected (0.008 sec)
MariaDB [dbstikomtb]> GRANT INSERT,SELECT ON dbstikomtb.prodi_mi to 'staf_mi'@'localhost';
Query OK, 0 rows affected (0.008 sec)

MariaDB [dbstikomtb]> EXIT


Bye

mysql -u mhs_mi -p
C:\xampp\mysql\bin>
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> select * from prodi_mi;


ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use dbstikomtb;
Database changed
MariaDB [dbstikomtb]> select * from prodi_mi;
+------+-----------------+------+--------------+------+
| nim | nama | umur | tempat_lahir | ipk |
+------+-----------------+------+--------------+------+
| 9901 | andi Surya | 23 | Jakarta | 2.70 |
| 9902 | Santi Syahnum | 21 | Malang | 3.20 |
| 9903 | Neli Situmorang | 22 | Medan | 3.20 |
+------+-----------------+------+--------------+------+
3 rows in set (0.001 sec)

MariaDB [dbstikomtb]> insert into prodi_mi


-> values('9904','suyanti',22,'siantar',3.40);
ERROR 1142 (42000): INSERT command denied to user 'mhs_mi'@'localhost' for table `dbstikomtb`.`prodi_mi`
MariaDB [dbstikomtb]> select * from prodi_ka;
ERROR 1142 (42000): SELECT command denied to user 'mhs_mi'@'localhost' for table `dbstikomtb`.`prodi_ka`
MariaDB [dbstikomtb]> DELETE * FROM PRODI_MI;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB
server version for the right syntax to use near '* FROM PRODI_MI' at line 1
MariaDB [dbstikomtb]> DELETE FROM PRODI_MI;
ERROR 1142 (42000): DELETE command denied to user 'mhs_mi'@'localhost' for table `dbstikomtb`.`prodi_mi`
MariaDB [dbstikomtb]> EXIT
Bye

C:\xampp\mysql\bin>mysql -u operator_mi -p
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> use dbstikomtb;


Database changed
MariaDB [dbstikomtb]> insert into prodi_mi
-> values('9904','suyanti',22,'siantar',3.40);
Query OK, 1 row affected (0.008 sec)

MariaDB [dbstikomtb]> select * from prodi_mi;


ERROR 1142 (42000): SELECT command denied to user 'operator_mi'@'localhost' for table
`dbstikomtb`.`prodi_mi`
MariaDB [dbstikomtb]> update prodi_mi set nama='Aisyah' where nim='9904';
ERROR 1143 (42000): SELECT command denied to user 'operator_mi'@'localhost' for column 'nim' in table
'prodi_mi'
MariaDB [dbstikomtb]> update prodi_mi set prodi_mi.nama='Aisyah' where prodi_mi.nim='9904';
ERROR 1143 (42000): SELECT command denied to user 'operator_mi'@'localhost' for column 'nim' in table
'prodi_mi'
MariaDB [dbstikomtb]> EXIT
Bye
C:\xampp\mysql\bin>mysql -u operator_mi -p
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> update prodi_mi set nama='Aisyah' where nim='9904';


ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use dbstikomtb;
Database changed
MariaDB [dbstikomtb]> update prodi_mi set nama='Aisyah' where nim='9904';
ERROR 1143 (42000): SELECT command denied to user 'operator_mi'@'localhost' for column 'nim' in table
'prodi_mi'
MariaDB [dbstikomtb]> delete from prodi_mi where nim='9904';
ERROR 1143 (42000): SELECT command denied to user 'operator_mi'@'localhost' for column 'nim' in table
'prodi_mi'
MariaDB [dbstikomtb]> delete from prodi_mi where prodi_mi.nim='9904';
ERROR 1143 (42000): SELECT command denied to user 'operator_mi'@'localhost' for column 'nim' in table
'prodi_mi'
MariaDB [dbstikomtb]> exit
Bye

C:\xampp\mysql\bin>mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use dbstikomtb;
Database changed
MariaDB [dbstikomtb]> select * from prodi_mi;
+------+-----------------+------+--------------+------+
| nim | nama | umur | tempat_lahir | ipk |
+------+-----------------+------+--------------+------+
| 9901 | andi Surya | 23 | Jakarta | 2.70 |
| 9902 | Santi Syahnum | 21 | Malang | 3.20 |
| 9903 | Neli Situmorang | 22 | Medan | 3.20 |
| 9904 | suyanti | 22 | siantar | 3.40 |
+------+-----------------+------+--------------+------+
4 rows in set (0.001 sec)

MariaDB [dbstikomtb]> GRANT UPDATE ON dbstikomtb.prodi_mi to 'operator_mi'@'localhost';


Query OK, 0 rows affected (0.008 sec)

MariaDB [dbstikomtb]> exit


Bye

C:\xampp\mysql\bin>mysql -u operator_mi -p
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> use dbstikotb;


ERROR 1044 (42000): Access denied for user 'operator_mi'@'localhost' to database 'dbstikotb'
MariaDB [(none)]> use dbstikomtb;
Database changed
MariaDB [dbstikomtb]> update prodi_mi
-> set nama='Aisyah'
-> where nim='9904';
ERROR 1143 (42000): SELECT command denied to user 'operator_mi'@'localhost' for column 'nim' in table
'prodi_mi'
MariaDB [dbstikomtb]> update prodi_mi
-> set nama='Aisyah' ;
Query OK, 4 rows affected (0.010 sec)
Rows matched: 4 Changed: 4 Warnings: 0

UNTUK GRANT UPDATE MENGGUNAKAN WHERE KITA HARUS MEMBUATT VIEW

C:\xampp\mysql\bin>mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> create view vmahasiswa AS


-> select nim,nama from prodi_mi
-> where nim between '9901' and '9903';
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use dbstikomtb;
Database changed
MariaDB [dbstikomtb]> create view vmahasiswa AS
-> select nim,nama from prodi_mi
-> where nim between '9901' and '9903';
Query OK, 0 rows affected (0.016 sec)

MariaDB [dbstikomtb]> GRANT UPDATE (nim,nama) ON vmahasiswa to 'operator_mi'@'localhost';


Query OK, 0 rows affected (0.021 sec)

MariaDB [dbstikomtb]> exit


Bye

C:\xampp\mysql\bin>mysql -u operator_mi -p
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> use dbstikomtb;


Database changed
MariaDB [dbstikomtb]> update vmahasiswa
-> set nama='Alif' where nim='9901';
Query OK, 1 row affected (0.013 sec)
Rows matched: 1 Changed: 1 Warnings: 0

You might also like