小白求助
为啥建立角色后，对其授予了权限，但转接给用户，无法在用户上实现权限，语句如下：create role Classmates;
Query OK, 0 rows affected (0.07 sec)
grant all privileges on Students to Classmates;
Query OK, 0 rows affected (0.01 sec)
grant selecton SC to Classmates;
Query OK, 0 rows affected (0.02 sec)
grant Classmates to Cjh,Fxk,Rjh;
Query OK, 0 rows affected (0.02 sec)
登入用户Rjh后
select * from SC;
ERROR 1142 (42000): SELECT command denied to user 'Rjh'@'localhost' for table 'sc'
mysql> select * from Students;
+-----------+-------+------+------------+------------+----------------------+------+
| Sno | Sname | Sex| Birthday | Enrollyear | Speciality | Dno|
+-----------+-------+------+------------+------------+----------------------+------+
| 202200304 | Cjk | 1 | 2004-02-28 | 2022 | Computer Techonology | CT |
| 202200305 | Cwl | 0 | 2004-03-18 | 2022 | Computer Techonology | CT |
| 202200306 | Cjt | 1 | 2004-02-15 | 2022 | Computer Techonology | CT |
| 202201001 | Fxk | 1 | 2004-02-28 | 2022 | math | MA |
| 202201002 | Swt | 0 | 2004-06-01 | 2022 | math | MA |
| 202201003 | Rjh | 1 | 2004-05-22 | 2022 | math | MA |
| 202201007 | Cjh | 1 | 2004-02-01 | 2022 | math | MA |
只有查询Students可以实现（因为前面用root身份进行了授权），想查询SC表却无法做到，求助具体原因，感谢
先执行 show grants for Classmates 以及 show grants for Rjh 确认最终授权结果。
另外，grant xx for Rjh 这种写法，等同于授权给 Rjh@%，但我看你后面执行select的账号是 Rjh@localhost，这二者不等价。 yejr 发表于 2024-5-17 12:25
先执行以及 show grants for Rjh 确认最终授权结果。
另外，grant xx for Rjh 这种写法，等 ...
mysql> show grants for Classmates;
+---------------------------------------------------------------+
| Grants for Classmates@% |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `Classmates`@`%` |
| GRANT SELECT ON `test`.`sc` TO `Classmates`@`%` |
| GRANT ALL PRIVILEGES ON `test`.`students` TO `Classmates`@`%` |
+---------------------------------------------------------------+
3 rows in set (0.06 sec)
mysql> show grants for Rjh;
+--------------------------------------------------------------------------+
| Grants for Rjh@% |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `Rjh`@`%` |
| GRANT USAGE ON `test`.`sc` TO `Rjh`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `test`.`students` TO `Rjh`@`%` WITH GRANT OPTION |
| GRANT `Classmates`@`%` TO `Rjh`@`%` |
+--------------------------------------------------------------------------+
查询结果如上，并未显示出具有对SC表的任何权限，但有角色与用户之间授予权限，想问一下这里没有实现角色权限转移实是因为MySQL中角色这块的实际应用不对吗？
yejr 发表于 2024-5-17 12:25
先执行以及确认最终授权结果。
另外，grant xx for Rjh 这种写法，等同于授权给 Rjh@%，但我看你后面 ...
另外，grant xx for Rjh 这种写法，等同于授权给 Rjh@%，但我看你后面执行select的账号是 Rjh@localhost，这二者不等价。
====
看我这里的提醒，意思是不要用 Rjh@localhost 方式登入，改成 tcp/ip + 端口方式登入，例如
mysql -h 127.0.0.1 -u Rjh -p x -P 3306
yejr 发表于 2024-5-17 15:57
另外，grant xx for Rjh 这种写法，等同于授权给 Rjh@%，但我看你后面执行select的账号是 Rjh@localhost ...
# /usr/local/GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64-minimal/bin/mysql -h 127.0.0.1 -u Rjh -pCjh200421@ -P 3306
mysql: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.32-25 GreatSQL, Release 25, Revision db07cc5cb73
Copyright (c) 2021-2023 GreatDB Software Co., Ltd
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from SC;
ERROR 1142 (42000): SELECT command denied to user 'Rjh'@'localhost' for table 'sc'
mysql> select user();
+---------------+
| user() |
+---------------+
| Rjh@localhost |
+---------------+
1 row in set (0.01 sec)
采用这种登入方式，但用户还是没变，这个是为啥呀？:'(
万里旅人 发表于 2024-5-17 18:38
# /usr/local/GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64-minimal/bin/mysql -h 127. ...
grant Classmates to 'Rjh'@'localhost'这样授权，看看行不行。 grant all privileges on Students to Classmates;这个语句授权有问题吧，ON后面就是数据库名.表名吧 万里旅人 发表于 2024-5-17 18:38
# /usr/local/GreatSQL-8.0.32-25-Linux-glibc2.17-x86_64-minimal/bin/mysql -h 127. ...
感觉你的操作还是哪里有问题，你看看我的就正常
# mysql -h127.0.0.1 -uyejr -pyejr -P3306
mysql: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 1990455
Server version: 8.0.32-25 GreatSQL, Release 25, Revision 79f57097e3f
Copyright (c) 2021-2023 GreatDB Software Co., Ltd
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
greatsql> show processlist;
+---------+------+-----------------+------+---------+------+-------+------------------+---------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+---------+------+-----------------+------+---------+------+-------+------------------+---------+-----------+---------------+
| 1990455 | yejr | 127.0.0.1:57108 | NULL | Query | 0 | init| show processlist | 0 | 0 | 0 |
+---------+------+-----------------+------+---------+------+-------+------------------+---------+-----------+---------------+
1 row in set (0.00 sec)
greatsql> select user();
+----------------+
| user() |
+----------------+
| yejr@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)
15167759230 发表于 2024-5-17 20:33
grant Classmates to 'Rjh'@'localhost'这样授权，看看行不行。
还是不行，ID查不到 15167759230 发表于 2024-5-17 20:48
grant all privileges on Students to Classmates;这个语句授权有问题吧，ON后面就是数据库名.表名吧 ...
结果是一样的，我简写了，刚才试了一下，还是不行，应该还是地址与授权问题
页:
[1]