![]() ![]() 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 you made a mistake at some point you can undo all the steps above by executing the following commands, taking the precaution of replacing localhost with ‘%’ if you also changed it in the previous commands: DROP USER DATABASE mydb įinally, here is a very simple and small Linux script in Bash that will help you to do all this in a much faster and direct way.If there is any single privilege that stands for ALL READ operations on database. Verify your new user has the right permissions mysql> SHOW GRANTS FOR Grants for | To be effective the new assigned permissions you must finish with the following command: mysql> FLUSH PRIVILEGES 6. MySQL 8 and higher versions: mysql> GRANT ALL ON `mydb`.* TO in the previous command, if you want the user to work with the database from any location you will have to replace localhost with ‘%’. MySQL 5.7 and earlier versions: mysql> GRANT ALL privileges ON `mydb`.* TO IDENTIFIED BY 'mypassword' Grant all privileges to a user on a specific database In MySQL 8 or higher we will not add the IDENTIFIED BY ‘mipassword’ part. To allow access to MySQL server from any other computer on the network: mysql> GRANT USAGE ON *.* TO IDENTIFIED BY 'mypassword' ![]() Only allow access from localhost (this is the most secure and common configuration you will use for a web application): mysql> GRANT USAGE ON *.* TO IDENTIFIED BY 'mypassword' ![]() Grant permissions to access and use the MySQL server User creation mysql> CREATE USER IDENTIFIED BY 'mypassword' 3. 1. Database creation mysql> CREATE DATABASE `mydb` 2. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |