Your cart is currently empty!
Category: mysql
-
Khóa học miễn phí MySQL – Drop Users nhận dự án làm có lương
MySQL – Drop User
Table of content
Dropping users in MySQL will remove a user”s access and permissions on a specific database. This is performed by database administrators to maintain security and control over who can interact with the database system, ensuring that only authorized users can access and manipulate the data.
The MySQL Drop User Statement
You can drop/delete one or more existing users in MySQL using the DROP USER Statement. Once you delete an account, all privileges of it are deleted. To execute this statement, you need to have CREATE USER privilege.
Syntax
Following is the syntax of the DROP USER statement −
DROP USER [IF EXISTS] ''username''@''hostname
Where, user_name is the name of the MySQL user you need to delete.
Example
Suppose, we have created a MySQL user account named ”TestUser” as shown below −
CREATE USER TestUser@localhost IDENTIFIED BY ''password1
Following is the output obtained −
Query OK, 0 rows affected (0.04 sec)
You can verify the list of users using the following query −
SELECT user FROM MySQl.user;
The table will be displayed as shown below −
user TestUser mysql.infoschema mysql.session mysql.sys newUser root sample Now, let us delete the ”TestUser” account created above using the DROP USER statement as shown below −
DROP USER TestUser@localhost;
After executing the above code, we can see the output as shown below −
Query OK, 0 rows affected (0.02 sec)
Verification
Once a table is dropped, if you verify the list of the users as shown below using the SELECT statement, you will find that its name is missing from the list −
SELECT user FROM MySQl.user;
The table obtained is as follows −
user mysql.infoschema mysql.session mysql.sys newUser root sample Removing Multiple Users
You can also delete multiple users at once using the DROP ROLE statement. Roles are used to manage permissions and access control in a database system. By dropping a role, you revoke all privileges associated with that role. −
Example
Let us start by creating two roles ”MyAdmin” and ”MyDeveloper” −
CREATE ROLE ''MyAdmin'', ''MyDeveloper
The output obtained is as follows −
Query OK, 0 rows affected (0.01 sec)
Now, let us remove these roles using the DROP ROLE statement −
DROP ROLE ''MyAdmin'', ''MyDeveloper
This query will effectively delete both roles from the database −
Query OK, 0 rows affected (0.01 sec)
The IF EXISTS clause
If you try to drop a MySQL user that doesn”t exist, an error will be generated. To address this issue, MySQL provides the IF EXISTS clause, which can be used with the DROP USER statement.
Hence, the IF EXISTS clause allows you to drop a user if they exist, and it handles situations where the specified user is not found in the database.
Example
In the below query, we are attempting to drop the ”demo” user. However, it results in an error because the user doesn”t exist in the database −
DROP USER demo@localhost;
The output produced is as shown below −
ERROR 1396 (HY000): Operation DROP USER failed for ''demo''@''localhost''
If you use the IF EXISTS clause along with the DROP USER statement as shown below, the specified user will be dropped and if a user with the given name doesn”t exist, the query will be ignored −
DROP USER IF EXISTS demo;
The output obtained is as follows −
Query OK, 0 rows affected, 1 warning (0.01 sec)
Dropping User Using a Client Program
In this section we are going to see various client programs to drop an existing user from MySQL.
Syntax
Following are the syntaxes to drop a MySQL user in various programming languages −
The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. To drop a user from a MySQL database, we need to execute the DROP USER statement using this function as −
$sql = "DROP USER ''username''@''localhost''"; $mysqli->query($sql);
To drop a user using a NodeJS program, we need to execute the DROP USER statement using the function named query() as −
sql= "DROP USER [IF EXISTS] user_name ..."; con.query(sql, function (err, result) { if (err) throw err; console.log(result); });To drop an user in a MySQL database using Java program, we need to execute the DROP USER statement using the JDBC function named execute() as −
String sql = "DROP USER "USER_NAME''@''LOCALHOST''"; statement.execute(sql);
The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database. To drop a user from a MySQL dataBase, we need to execute the DROP USER statement using this function as −
sql = "DROP USER ''UserName''@''localhost''"; cursorObj.execute(sql);
Example
Following are the client programs to drop an user in MySQL −
$dbhost = ''localhost $dbuser = ''root $dbpass = ''password $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); $sql = "DROP USER ''Revathi''@''localhost''"; if($mysqli->query($sql)){ printf("User dropped successfully...!"); } if($mysqli->error){ printf("Failed..!" , $mysqli->error); } $mysqli->close();Output
The output obtained is as follows −
User dropped successfully...!
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); //Deleting Users sql = "DROP USER TestUser1@localhost;" con.query(sql); sql = "DROP USER TestUser2@localhost;" con.query(sql); //Listing the users after deleting sql = "select user from MySQl.user;" con.query(sql, function(err, result){ console.log("**List of Users after deleting:**") if (err) throw err console.log(result) }) });Output
The output produced is as follows −
Connected! -------------------------- *List of Users after deleting:** [ { user: ''TestUser3'' }, { user: ''TestUser4'' }, { user: ''mysql.infoschema'' }, { user: ''mysql.session'' }, { user: ''mysql.sys'' }, { user: ''root'' }, { user: ''sample'' } ]import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class DropUsers { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "DROP USER ''Vivek''@''localhost''"; st.execute(sql); System.out.println("User ''Vivek'' dropped successfully...!"); }catch(Exception e) { e.printStackTrace(); } } }Output
The output obtained is as shown below −
User ''Vivek'' created successfully...!
import mysql.connector # creating the connection object connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'' ) # Create a cursor object for the connection cursorObj = connection.cursor() cursorObj.execute("DROP USER ''UserNew''@''localhost''") print("User ''UserNew'' is dropped successfully.") cursorObj.close() connection.close()Output
Following is the output of the above code −
User ''UserNew'' is dropped successfully.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí MySQL – Show Users nhận dự án làm có lương
MySQL – Show Users
As you might have already known, MySQL is a multi-user database that allows multiple users to work on it simultaneously. But have you ever wondered who these users might be?
MySQL provides an account to each user that is authenticated with a username and a password. And details of these accounts are stored in the “user” table in the database. This table contains details like username, the host this user is connected from, and other privileges the said user has etc.
The MySQL SHOW Users
MySQL does not provide any direct command to show (list out) all the users. However, the details of these user accounts is stored in the “user” table within the database. Hence, we can use the SELECT statement to list out the contents of this table.
There is no limit for how many users can connect to a MySQL database but the default user is always “root”. And it does not have any password, unless it is set manually.
Syntax
Following is the syntax to show users in a MySQL database −
SELECT * FROM mysql.user;
Example
To see the structure of this “user” table, use the following query with the DESC command −
DESC mysql.user;
Now, in this example, we are listing out all the users in the MySQL database local to a system −
SELECT Host, User, User_attributes, account_locked FROM mysql.user;
Output
The output obtained is as shown below −
| Host | User | User_attr | account_locked |
|---|---|---|---|
| localhost | mysql.infoschema | NULL | Y |
| localhost | mysql.session | NULL | Y |
| localhost | mysql.sys | NULL | Y |
| localhost | root | NULL | N |
The actual user table contains a lot more columns/fields than what is displayed in this chapter. Here, however, only some information is displayed for simplicity.
Note that list of these users are local to a system. Hence, not all systems would give the same output (apart from the default users).
Show Current User
Not only the list of all users, MySQL also has a provision to see the current user. This is done with the help of user() or current_user() functions.
Syntax
Following is the syntax to show the current user −
SELECT user(); or SELECT current_user();
Example
Using the following query, let us display the username of the currently logged in user in MySQL database using the user() function −
SELECT user();
Output
Following is the output obtained −
| user() |
|---|
| root@localhost |
Example
In here, we are using the current_user() function to show the current user −
SELECT current_user();
Output
The output obtained is as follows −
| current_user() |
|---|
| root@localhost |
Show Currently Logged in Users
The difference between current users and currently logged in users is that, current user is the user that is executing the queries; whereas, currently logged in user list includes all the active users that are connected to the MySQL server at the moment.
This information can be extracted from the “information_schema.processlist” table using the SELECT statement.
Example
In the following query, we are retrieving the information of all the currently logged in users −
DESC information_schema.processlist;
Output
Following is the output of the above code −
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | bigint unsigned | NO | |||
| USER | varchar(32) | NO | |||
| HOST | varchar(261) | NO | |||
| DB | varchar(64) | YES | |||
| COMMAND | varchar(16) | NO | |||
| TIME | int | NO | |||
| STATE | varchar(64) | YES | |||
| INFO | varchar(65535) | YES |
Example
In here, we are retrieving information of current users, host, database, and command from the information_schema −
SELECT user, host, db, command FROM information_schema.processlist;
Output
After executing the above code, we get the following output −
| user | host | db | command |
|---|---|---|---|
| root | localhost:49958 | customers | Query |
| event_scheduler | localhost | NULL | Daemon |
Show Users Using a Client Program
We can also display information about the MySQL users using a client program.
Syntax
Following are the syntaxes to display information regarding MySQL users in various programming languages −
To display info regarding user(s) in a MySQL database using a PHP program, we need to execute the SELECT USER statement using the query() function of the PHP mysqli library as −
$sql = "SELECT USER FROM MYSQL.user"; $mysqli->query($sql);
To display the user information We need to execute the SELECT * FROM statement using the query() function of mysql2 library using JavaScript (NodeJS) program as follows −
sql= "SELECT * FROM mysql.user";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
Similarly in Java we can use the JDBC executeQuery() function to execute the SQL query that displays the user info as follows −
String sql = "SELECT USER FROM MYSQL.USER"; statement.executeQuery(sql);
The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database.To show user info in MySQL database, we need to execute the SELECT USER statement using this function as −
sql = "SELECT user, host FROM mysql.user"; cursorObj.execute(sql);
Example
Following are the programs −
$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT USER FROM MYSQL.user";
if($result = $mysqli->query($sql)){
printf("User found successfully...!");
printf("Users list are: ");
while($row = mysqli_fetch_array($result)){
print_r($row);
}
}
if($mysqli->error){
printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows −
User found successfully...!Users list are: Array
(
[0] => Vivek Verma
[USER] => Vivek Verma
)
Array
(
[0] => Revathi
[USER] => Revathi
)
Array
(
[0] => Sarika
[USER] => Sarika
)
Array
(
[0] => mysql.infoschema
[USER] => mysql.infoschema
)
Array
(
[0] => mysql.session
[USER] => mysql.session
)
Array
(
[0] => mysql.sys
[USER] => mysql.sys
)
Array
(
[0] => root
[USER] => root
)
var mysql = require(''mysql2'');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "Nr5a0204@123"
});
//Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
console.log("--------------------------");
//Creating Users
sql = "CREATE USER TestUser1@localhost IDENTIFIED BY ''password1"
con.query(sql);
sql = "CREATE USER TestUser2@localhost IDENTIFIED BY ''password2"
con.query(sql);
sql = "CREATE USER TestUser3@localhost IDENTIFIED BY ''password3"
con.query(sql);
sql = "CREATE USER TestUser4@localhost IDENTIFIED BY ''password4"
con.query(sql);
//Listing the users
sql = "SELECT USER FROM mysql.user;"
con.query(sql, function(err, result){
if (err) throw err
console.log("**List of Users:**")
console.log(result)
});
});
Output
The output produced is as follows −
Connected!
--------------------------
**List of Users:**
[
{ USER: ''TestUser1'' },
{ USER: ''TestUser2'' },
{ USER: ''TestUser3'' },
{ USER: ''TestUser4'' },
{ USER: ''mysql.infoschema'' },
{ USER: ''mysql.session'' },
{ USER: ''mysql.sys'' },
{ USER: ''root'' },
{ USER: ''sample'' }
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowUsers {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String user = "root";
String password = "password";
ResultSet rs;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
//System.out.println("Database connected successfully...!");
String sql = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
st.execute(sql);
System.out.println("User ''Vivek'' created successfully...!");
String sql1 = "SELECT USER FROM MYSQL.user";
rs = st.executeQuery(sql1);
System.out.println("Users: ");
while(rs.next()) {
String users = rs.getNString(1);
System.out.println(users);
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
Output
The output obtained is as shown below −
User ''Vivek'' created successfully...! Users: Sarika Vivek Verma Revathi Sarika Vivek mysql.infoschema mysql.session mysql.sys root
import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
host=''localhost'',
user=''root'',
password=''password'',
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("SELECT user, host FROM mysql.user")
users = cursorObj.fetchall()
print("Existing users are:")
for user, host in users:
print(f"User: {user}, Host: {host}")
cursorObj.close()
connection.close()
Output
Following is the output of the above code −
Existing users are: User: UserNew, Host: localhost User: mysql.infoschema, Host: localhost User: mysql.session, Host: localhost User: mysql.sys, Host: localhost User: newUser, Host: localhost User: root, Host: localhost
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
icon to run the following MySQL code to be executed on the CUSTOMERS table and retrieve the records matching with the given condition.