Sstrutt: /* Create Users and Grant/Revoke Privileges */ Updated add new user

2019-03-15T08:58:11Z

Create Users and Grant/Revoke Privileges: Updated add new user

← Older revision		Revision as of 08:58, 15 March 2019
Line 1:		Line 1:
	== Create Users and Grant/Revoke Privileges ==		== Create Users and Grant/Revoke Privileges ==
	~~After making any changes to~~ user ~~privilages,~~ you need to ~~flush them~~ to ~~ensure they're applied...~~		To create a new user you need to specify the username, the location/IP address it will be connecting from (use <code>%</code> for any location), and their password
	<source lang="mysql"> ~~FLUSH PRIVILEGES~~; </source>		<source lang="mysql"> CREATE USER '<user>'@'localhost' identified by '<password>';</source>

			You can then give permissions to that user
			<source lang="mysql"> GRANT ALL ON <dayabase>.* TO '<user>'@'localhost';</source>

	~~To give full privileges~~ (~~including~~ the ~~ability~~ to ~~alter user accounts~~) ~~to a new user coming from any location use;~~		Depending on the version of MySQL running you can do this in one command (note that the <code>WITH GRANT OPTION</code> allows the user to create more users and/or grant privileges to users).
	<source lang="mysql"> GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' WITH GRANT OPTION; </source>		<source lang="mysql"> GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' WITH GRANT OPTION; </source>

Line 17:		Line 20:
	To remove/revoke a privilege from an existing user ''web-user'';		To remove/revoke a privilege from an existing user ''web-user'';
	<source lang="mysql"> REVOKE SELECT ON webdb.* FROM 'web-user'@'localhost';</source>		<source lang="mysql"> REVOKE SELECT ON webdb.* FROM 'web-user'@'localhost';</source>

			After making any changes to user privilages, you need to flush them to ensure they're applied...
			<source lang="mysql"> FLUSH PRIVILEGES; </source>

	== Delete Users ==		== Delete Users ==

Sstrutt: Initial creation - content from MySQL page (now category)

2014-03-16T22:06:59Z

Initial creation - content from MySQL page (now category)

New page

== Create Users and Grant/Revoke Privileges ==
After making any changes to user privilages, you need to flush them to ensure they're applied...
<source lang="mysql"> FLUSH PRIVILEGES; </source>

To give full privileges (including the ability to alter user accounts) to a new user coming from any location use;
<source lang="mysql"> GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' WITH GRANT OPTION; </source>

To give no privileges to a new user coming from a specific host;
<source lang="mysql"> GRANT USAGE on <database>.* to '<user>'@'<host>' identified by '<password>'; </source>

To give an existing user read-only access;
<source lang="mysql"> GRANT SELECT on <database>.* to '<user>'@'<host>';</source>

To give an existing user ''web-user'', read-only access to local database ''webdb'';
<source lang="mysql"> GRANT SELECT ON webdb.* TO 'web-user'@'localhost';</source>

To remove/revoke a privilege from an existing user ''web-user'';
<source lang="mysql"> REVOKE SELECT ON webdb.* FROM 'web-user'@'localhost';</source>

== Delete Users ==
To remove an existing user use the following...
<source lang="mysql"> DROP USER '<user>'@'<host>'; </source>
* You need to specify the full <code>user@host</code> entry (or MySQL assumes the wild-card host <code>%</code>)
* Existing users sessions are not dropped, but will be unable to re-establish

== Display Users ==
To display all configured users;
<source lang="mysql">
SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user;
</source>
Then use the displayed lines to see the detail of each user

== Change User Password ==
<source lang="mysql">
SET PASSWORD FOR 'user'@'%' = PASSWORD('newpass');
</source>

[[Category:MySQL]]

User Management (MySQL) - Revision history

Sstrutt: /* Create Users and Grant/Revoke Privileges */ Updated add new user

Sstrutt: Initial creation - content from MySQL page (now category)