DCL——数据控制语言
一、DCL——用户操作
- 用户、授权、撤销权限
1、MySQL用户
1.1 用户名格式
格式: 用户名@客户端地址
localhost:代表只允许本机登录
客户端地址的写法:
1、单个IP martin@10.1.1.1
2、网段 martin@10.1.1.%
3、所有 martin@%
martin@10.1.1.1, martin@10.1.1.10是完全不同的账户
1.2 用户存储的位置
- mysql库中的user表
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> select count(*) as 用户数量 from mysql.user;
+--------------+
| 用户数量 |
+--------------+
| 3 |
+--------------+
1 row in set (0.00 sec)
2、创建用户
2.1 语法
> CREATE USER 用户名@客户端地址 IDENTIFIED BY ‘密码';
2.2 创建允许本地登录的用户
mysql> CREATE USER 'martin'@'localhost' IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host FROM mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| martin | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
[root@localhost ~]# mysql -u martin -pWWW.1.com
mysql> SELECT user();
+------------------+
| user() |
+------------------+
| martin@localhost |
+------------------+
1 row in set (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
2.3 创建允许主机192.168.140.11登录的用户
mysql> CREATE USER 'martin'@'192.168.140.11' IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT user,host FROM mysql.user;
+---------------+----------------+
| user | host |
+---------------+----------------+
| martin | 192.168.140.11 |
| martin | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+----------------+
5 rows in set (0.00 sec)
[root@zabbix_agent ~]# yum install -y mariadb
[root@zabbix_agent ~]# mysql -u martin -pWWW.1.com -h 192.168.140.10
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
在数据库服务器查看连接
mysql> SHOW PROCESSLIST;
+----+--------+----------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+----------------------+------+---------+------+----------+------------------+
| 12 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 13 | martin | 192.168.140.11:33052 | NULL | Sleep | 62 | | NULL |
+----+--------+----------------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# netstat -antp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1088/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1273/master
tcp 0 0 192.168.140.10:22 192.168.140.1:60261 ESTABLISHED 1392/sshd: root@pts
tcp 0 52 192.168.140.10:22 192.168.140.1:62720 ESTABLISHED 1576/sshd: root@pts
tcp6 0 0 :::22 :::* LISTEN 1088/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1273/master
tcp6 0 0 :::3306 :::* LISTEN 1127/mysqld
tcp6 0 0 192.168.140.10:3306 192.168.140.11:33052 ESTABLISHED 1127/mysqld
2.4 创建允许windows连接的用户
mysql> CREATE USER 'martin'@'192.168.140.1' IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host FROM mysql.user;
+---------------+----------------+
| user | host |
+---------------+----------------+
| martin | 192.168.140.1 |
| martin | 192.168.140.11 |
| martin | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+----------------+
6 rows in set (0.00 sec)
数据库图形化工具 Navacit
[root@localhost ~]# netstat -antp | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 1127/mysqld
tcp6 0 0 192.168.140.10:3306 192.168.140.11:33052 ESTABLISHED 1127/mysqld
tcp6 0 0 192.168.140.10:3306 192.168.140.1:62870 ESTABLISHED 1127/mysqld
3、修改用户密码
mysql> ALTER USER 'martin'@'192.168.140.11' IDENTIFIED BY 'WWW.2.com';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
4、删除用户
mysql> DROP USER 'martin'@'192.168.140.11';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
三、DCL——用户授权、撤销权限
1、查看用户权限
MySQL新建用户,默认分配一个USAGE权限,最小权限;只允许查看information_schema只读数据库
mysql> SHOW GRANTS FOR 'martin'@'localhost';
+--------------------------------------------+
| Grants for martin@localhost |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'martin'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)
2、授权
2.1 授权语法
> GRANT 权限,权限,权限 ON 库名.表名 TO 用户名
库名.表名: jiaowu.tutors、jiaowu.*、*.*
权限:
create、drop、alter、insert、update、delete、select
all
- 5.X,用户不存在,会自动创建用户
- 8.X,创建用户、授权必须分开操作
2.2 为用户授权
mysql> GRANT select ON jiaowu.tutors TO 'martin'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'martin'@'localhost';
+-----------------------------------------------------------+
| Grants for martin@localhost |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'martin'@'localhost' |
| GRANT SELECT ON `jiaowu`.`tutors` TO 'martin'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)
3、撤销权限
3.1、语法
> REVOKE 权限,权限 ON 库名.表名 FROM 用户名
mysql> REVOKE select ON jiaowu.tutors FROM 'martin'@"localhost";
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'martin'@"localhost";
+--------------------------------------------+
| Grants for martin@localhost |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'martin'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)
三、LAMP平台应用
1、LAMP平台介绍
1.1 网站类型
-
静态网站
内容(文字、图片、视频、音乐、flash)是固定的
HTML语言, xxxx.html
前端:jquery, js, vue -
动态网站
一段程序代码,根据用户传递的参数不同显示不同的结果
PHP语言, xxxx.php文件
1.2 LAMP平台
1.2.1 作用
- 部署PHP网站
1.2.2 LAMP名称
- Linux + Apache + MySQL + PHP
2、部署LAMP平台
2.1 配置PHP 7.2的软件仓库
[root@localhost ~]# cat /etc/yum.repos.d/php72.repo
[php72]
name=php72
baseurl=ftp://10.11.0.254/software/php72
enabled=1
gpgcheck=0
2.2 安装lamp平台所需的软件
[root@localhost ~]# yum install -y httpd php72w php72w-cli php72w-fpm php72w-common php72w-devel php72w-embedded php72w-gd php72w-mbstring php72w-mysqlnd php72w-opcache php72w-pdo php72w-xml
2.3 启动httpd, MySQL服务
[root@localhost ~]# systemctl enable --now httpd
[root@localhost ~]# netstat -tunlp | grep -E "80|3306"
tcp6 0 0 :::80 :::* LISTEN 1999/httpd
tcp6 0 0 :::3306 :::* LISTEN 1127/mysqld
2.4 验证PHP页面可正常显示
[root@localhost ~]# cat /var/www/html/test1.php
<h1> web static test</h1>
<?php
phpinfo();
?>
2.5 验证PHP可正常连接MySQL
[root@localhost ~]# cat /var/www/html/test2.php
<?php
$link=mysqli_connect("localhost", "root", "WWW.1.com");
if($link)
echo "<h1>ok!!!!!</h1>";
else
echo "<h1>Error!!!!!!</h1>";
mysqli_close();
?>
3、部署Discuz论坛
3.1 创建虚拟主机
[root@localhost ~]# cat /etc/httpd/conf.d/discuz.conf
<VirtualHost *:80>
ServerName dz.linux.com
DocumentRoot /dz
ErrorLog /var/log/httpd/dz_error.log
CustomLog /var/log/httpd/dz_access.log combined
</VirtualHost>
<Directory "/dz">
Require all granted
</Directory>
[root@localhost ~]# mkdir /dz
3.2 部署discuz论坛
[root@localhost ~]# unzip Discuz_X3.5_SC_UTF8_20230726.zip
[root@localhost ~]# cp -ra upload/* /dz
[root@localhost ~]# systemctl restart httpd
3.3 在windows主机添加网站的解析
C:\Windows\System32\drivers\etc\hosts
C:\Users\admin>ping dz.linux.com
正在 Ping dz.linux.com [192.168.140.10] 具有 32 字节的数据:
来自 192.168.140.10 的回复: 字节=32 时间<1ms TTL=64
来自 192.168.140.10 的回复: 字节=32 时间<1ms TTL=64
在浏览器里访问网站 http://dz.linux.com
3.4 在后台数据库创建论坛需要的用户
mysql> CREATE DATABASE discuz CHARSET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER 'dzuser'@'localhost' IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT all ON discuz.* TO 'dzuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)