MySQL is one of the most commonly used relational database management systems that enable us to store, manipulate, and retrieve data. The following is a list of example commands and confirmation messages: mysql> grant select on dbname.Sameeksha Medewar | 04 Jan, 2023 MySQL Create User The host name must be resolvable by DNS or by the local hosts file.Īt the mysql prompt, type the following command: flush privileges If you know the host name or IP address of the host that the collector is will be installed on, type the following command: grant select on database_name.* to or IP_address' identified by 'password' This command gives the user read-only access to the database from the local host only. If the collector will be installed on the same host as the database, type the following command: grant select on database_name.* to 'read-only_user_name' identified by 'password' To give the user access to the database from any host, type the following command: grant select on database_name.* to identified by 'password' To create a read-only database user account for MySQLĪt a UNIX prompt, run the MySQL command-line program, and log in as an administrator by typing the following command: mysql -u root -pĪt the mysql prompt, do one of the following steps: In this recent question on Database Administrators, the poster wanted the ability for an unprivileged user to modify other users, which of course is not something that can normally be done - a user that can modify other users is, pretty much by definition, not an unprivileged user - however - stored procedures provided a good solution in that case, because they run with the security context of their DEFINER user, allowing anybody with EXECUTE privilege on the procedure to temporarily assume escalated privileges to allow them to do the specific things the procedure accomplishes. Within the body of the procedure, you'd build the GRANT statement with dynamic SQL and/or directly manipulate the grant tables themselves. Of, if you want a single operation to set up and grant the limited set of privileges to users, and perhaps remove any unmerited privileges, that can be done by creating a stored procedure that encapsulates everything that you want to do. | GRANT SELECT, REPLICATION CLIENT ON *.* TO IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' |Ĭhanging 'not_leet' and 'localhost' to match the new user you want to add, along with the password, will result in a reusable GRANT statement to create a new user. If you are doing things manually and looking for an easier way to go about this without needing to remember the exact grant you typically make for a certain class of user, you can look up the statement to regenerate a comparable user's grants, and change it around to create a new user with similar privileges: mysql> SHOW GRANTS FOR Grants for | However, there is no single privilege that grants some subset of other privileges, which is what it sounds like you are asking. GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO. If that's the reading you want to do, you can combine any of those (or any other of the available privileges) in a single GRANT statement. ![]() Note that any or all of these might expose more information than you intend to expose, depending on the nature of the user in question. "Reading" the current replication state is the REPLICATION CLIENT privilege. "Reading" the list of currently-executing queries by other users is the PROCESS privilege. ![]() "Reading" the definition of views is the SHOW VIEW privilege. However, it sounds like you mean an ability to "see" everything, to "look but not touch." So, here are the other kinds of reading that come to mind: ![]() If that's what you mean by "all read" then yes: GRANT SELECT ON *.* TO IDENTIFIED BY 'password' "Reading" from tables and views is the SELECT privilege. ![]() If there is any single privilege that stands for ALL READ operations on database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |