MySQL - Show Privileges

Following is the syntax to show all the privileges granted to the current user through a JavaScript program −

sql= "SHOW PRIVILEGES;"
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

To show the privileges of the current user, we need to execute the SHOW PRIVILEGES statement using the JDBC executeQuery() function as −

String sql = "SHOW PRIVILEGES";
statement.executeQuery(sql);

Following is the syntax to show all the privileges granted to the current MySQL user through a Python program −

sql = f"SHOW GRANTS FOR '{username_to_show}'@'localhost'";
cursorObj.execute(sql);

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("--------------------------");

  sql = "SHOW PRIVILEGES";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    Privilege: 'Alter',
    Context: 'Tables',
    Comment: 'To alter the table'
  },
.
.
.
  {
    Privilege: 'TELEMETRY_LOG_ADMIN',
    Context: 'Server Admin',
    Comment: ''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowPriv {
	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 = "SHOW PRIVILEGES";
            rs = st.executeQuery(sql);
            System.out.println("All privileges: "); 
            while(rs.next()) {
            	String priv = rs.getNString(1);
            	System.out.println(priv);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Output

The output obtained is as shown below −

All privileges: 
Alter
Alter routine
Create
Create routine
Create role
Create temporary tables
Create view
Create user
Delete
Drop
Drop role
Event
Execute
File
Grant option
Index
Insert
Lock tables
Process
Proxy
References
Reload
Replication client
Replication slave
Select
Show databases
Show view
Shutdown
Super

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)
username_to_show = 'newUser'
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"SHOW GRANTS FOR '{username_to_show}'@'localhost'")
privileges = cursorObj.fetchall()
print(f"Privileges for user '{username_to_show}' are:")
for grant in privileges:
    print(grant[0])
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Privileges for user 'newUser' are:
GRANT USAGE ON *.* TO `newUser`@`localhost`
GRANT SELECT, INSERT, UPDATE ON `your_database`.* TO `newUser`@`localhost`