0

Desire to grant user access restricted to only display one stored procedure in the database AND prevent user from seeing any other tables, views and/or procs.

I have read this post and:

GRANT EXECUTE ON PROCEDURE myDB.spName TO 'TestUser'@'localhost';

works when the user accesses via PhpMyAdmin on the test server but not remotely (odd). When remote they can only "see" the information schema. Do I need to fully qualify the server name instead of 'localhost'?

Also have read the MySQL security docs to no avail.

Lastly, this question posted two years ago seems to describe a similar need but never answered.

[edit to show grants]

Grants for TestUser@localhost
GRANT USAGE ON *.* TO 'TestUser'@'localhost' IDENTIFIED BY PASSWORD '*FC6....'
GRANT EXECUTE ON PROCEDURE `database`.`sp_name` TO 'TestUser'@'localhost'

[SOLUTION from @BK435 below] To give remote user MySQL access to one (or more) stored procs and nothing else you will need their IP address and then:

1.) If MySQL remote security is in use use cPanel->Remote MySQL and "Add an Access Host" with their IP, then execute this command in MySQL (assumes adequate rights) 

GRANT EXECUTE ON PROCEDURE YourDatabase.YourStoredProcedure TO 'TestUser'@'THIER IP ADDRESS';
Community
  • 1
  • 1
TransitDataHead
  • 265
  • 4
  • 12

1 Answers1

0

You have bound TestUser to the localhost that is why he cannot access remotely (from another IP ). If you want Testuser to access remotely you need to specify that IP or just use a wildcard (%) so that he can access it from anywhere (localhost or not).

BK435
  • 3,076
  • 3
  • 19
  • 27
  • Thank you for your reply. To be clear we're not talking about Remote MySQL but this particular user right? This user had already been "white listed" for remote access to MySQL but still couldn't see when remote. If I'm understanding you properly I would need to GRANT EXECUTE....TO 'TestUser'@'IP Address' in addition to white listing their remote access? – TransitDataHead Feb 05 '15 at 19:52
  • can you plz post his "white listed" user privileges? execute command show grants for TestUser – BK435 Feb 05 '15 at 20:00
  • absolutely. The "white list" I'm talking about is via cPanel->RemoteMySQL. Will update post shortly. – TransitDataHead Feb 05 '15 at 20:15
  • tried it with my IP and it works just fine. Edited answer above – TransitDataHead Feb 05 '15 at 20:33