Mysql get list of users9/7/2023 ![]() This cookie is set by GDPR Cookie Consent plugin. These cookies ensure basic functionalities and security features of the website, anonymously. Necessary cookies are absolutely essential for the website to function properly. Then listing the roles becomes much easier. The effect is to grant the account’s privileges and roles to the other user or role.Īnd for better usability, I encourage you to prefix all your roles with ‘ r_‘, so db3_reader becomes r_db3_reader. You can treat a user account like a role and grant that account to another user or a role. Like user accounts, roles can have privileges granted to and revoked from them.Īs has been hinted at earlier for SHOW GRANTS, which displays grants for user accounts or roles, accounts and roles can be used interchangeably. It can become very complicated… the manual answers this question very well:Ī MySQL role is a named collection of privileges. What is a ROLE ?īut the real question, is “ what is a role ?” Because we can also grant a user to another like this: mysql> CREATE USER dbt3_user2 IDENTIFIED BY 'password2' This query might be a good candidate for a new SYS view. On the query above, Active means that there is at least one user having that role assigned. It’s also possible to list a user in it, but this means that you removed the password of a user that you have locked and that the password expired…. Indeed ROLES are locked accounts, without passwords and expired. WHERE account_locked='Y' AND password_expired='Y' AND authentication_string='' It’s possible to achieve this with the following query: mysql> SELECT DISTINCT User 'Role Name', if(from_user is NULL,0, 1) ActiveįROM er LEFT JOIN role_edges ON from_user=user So now the “real problem”, how could we list only the roles and not the users ? Now the user and I will assign it the dbt3_reader role: mysql> CREATE USER dbt3_user1 IDENTIFIED BY 'password' Mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON dbt3.* TO dbt3_writer ![]() Mysql> GRANT SELECT ON dbt3.* to dbt3_reader ![]() As the syntax is very easy, I think this doesn’t require more explanation.įirs the roles, one to read the data and one able to write data: mysql> CREATE ROLE dbt3_reader I will first create two different roles and one user. So let’s have a look at how we could differentiate our roles and list them. In most OpenSource RDBMS, a role is in fact an alias for a user but without a login.Īs I always like to say, if there is not solution, there is no problem, isn’t it ? □ The main point is about making the difference between users and roles. Of course he also made some remarks on things he would have done differently. Giuseppe Maxia is doing a great job testing and promoting them (thank you for that!). I’ve already quoted them before in some posts ( here and here). As you may already know, MySQL 8.0 is coming with SQL Roles. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |