Protect Stored Procedure

Question

I would like to know if there's a way to conceal the text of a stored procedure from certain users.

I'm working with MySQL 5.1.48 and Net Connector 6.2.3 with Visual Studio 2008 SP1.

I have two users in MySQL, one is root (with all privileges for administration porpuses) and the other one is admin (with select, update, insert, delete, create_temp_table, lock_table and execute privileges in a specific schema).

All the stored procedures have been created by the root user, so the admin user can only execute them without knowing how they work or reading their content.

Everything was working fine until we started creating DataSets and using WebService, for this the admin user needed the select privilege in the column proc from the table mysql. The problem is that with this privilege, admin can see all the stored procedures including their content.

Please let me know if there's a way to protect the SP's, I don't know if this is fixed in the latest version of MySQL and it's .Net connector.

Answer 1

Normally when using MySQL .NET connector in conjunction with stored procedures you have to grant your "application user" select permissions on mysql.proc otherwise the connector can't see the sproc you're trying to call through it.

So usually you have the following two grants for your app user:

grant execute on foo_db.* to foo_dbo@localhost identified by 'pass';
grant select on mysql.proc to foo_dbo@localhost;

As you correctly pointed out this is a bit of a security issue but I do know one way around it which might freak you out but, it works abosultely fine and is actually as performant if not more performant than the standard approach (think - less overhead).

So after revoking grant select on mysql.proc and flushing privileges...

string sqlCmd = string.Format("call list_users({0})", userType);

MySqlConnection cn = new MySqlConnection();
cn.ConnectionString = "Server=....";

adr = new MySqlDataAdapter(sqlCmd, cn);
adr.SelectCommand.CommandType = CommandType.Text; // change from sproc to CommandType.Text
dt = new DataTable();
adr.Fill(dt); //opens and closes the DB connection automatically !!

Hope this helps :)

EDIT: as my answer was causing some confusion I thought I'd add a complete walk through of the problem as I see it...

Create demo database

Just a quick demo database (demo_db) with two users demo_dbo and demo_usr.

demo_dbo is granted full permissions on demo_db - they are the database owner (dbo) and can do as they wish inside this database e.g. create, drop, create procedure, truncate etc...

demo_usr is our application user - they have only been granted execute permimissions so all they can do is call stored procs;

call list_users();

So I login as root and run this script demo_db.sql

drop database if exists demo_db;

create database demo_db
character set latin1
default character set latin1
collate latin1_swedish_ci
default collate latin1_swedish_ci;

revoke select on mysql.* from demo_dbo@localhost;
revoke all privileges on demo_db.* from demo_dbo@localhost;
revoke grant option on *.* from demo_dbo@localhost;
drop user demo_dbo@localhost;

revoke select on mysql.* from demo_usr@localhost;
revoke all privileges on demo_db.* from demo_usr@localhost;
revoke grant option on *.* from demo_usr@localhost;
drop user demo_usr@localhost;

grant all on demo_db.* to demo_dbo@localhost identified by 'pass';
grant select on mysql.* to demo_dbo@localhost;

grant execute on demo_db.* to demo_usr@localhost identified by 'pass';

flush privileges;

select host, user from mysql.user;

Create the demo_db schema

OK now I have a database to play in so next I login as demo_dbo and start to create my schema objects: tables, triggers, sprocs, views etc... (don't forget demo_dbo is the owner and can do as they like inside demo_db)

So I login as demo_dbo and run this script demo_dbo.sql

use demo_db;

drop table if exists users;
create table users(
 user_id int unsigned not null auto_increment primary key
)
engine=innodb;

insert into users values (null),(null),(null),(null),(null),(null);

drop procedure if exists list_users;

delimiter #

create procedure list_users()
begin
    select * from users order by user_id;
end #

delimiter ; 

Ok that's the demo_db schema created (1 table and 1 sproc) so I'd better do some testing while I'm still logged in as demo_dbo.

select from table - PASS

select * from users;
user_id
=======
1
2
3
4
5
6

Call stored procedure - PASS

call list_users();
user_id
=======
1
2
3
4
5
6

Show create procedure - PASS

show create procedure list_users;

Procedure   sql_mode    Create Procedure    character_set_client    collation_connection    Database Collation
=========   ========    ================    ====================    ====================    ==================
list_users      CREATE DEFINER=`demo_dbo`@`localhost` PROCEDURE `list_users`()
begin
    select * from users order by user_id;
end utf8    utf8_general_ci latin1_swedish_ci

Ok that all looks great demo_dbo (owner) can do all the things they should be able to do inside of demo_db so now we'd better test what our demo_usr can get up to.

I login as demo_usr and run the demo_usr.sql script:

show tables - FAIL

show tables;

Tables_in_demo_db
=================
NULL

Not much to see - how disappointing for demo_usr that they can't even see what tables are in the database.

select from user table - FAIL

select * from users;
SELECT command denied to user 'demo_usr'@'localhost' for table 'users'

Ok as expected - they can't access tables directly so I guess the only way they can get data is through my stored procdure API.

call stored procedure - PASS

call list_users();
user_id
=======
1
2
3
4
5
6

Finally a result but we'd better see what information about this sproc the demo_usr can see so...

show create procedure - FAIL

show create procedure list_users;
Procedure   sql_mode    Create Procedure    character_set_client    collation_connection    Database Collation
=========   ========    ================    ====================    ====================    ==================
list_users          utf8    utf8_general_ci latin1_swedish_ci

The demo_usr can see the stored procdure exists (well they can call it after all) but can't see the body.

Demo Application

So I knock up this quick and dirty C# app that uses MySQL .NET connector (which is great BTW) and attempt to call the list_users() stored procedure and populate a datatable as the application user demo_usr.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;

namespace demo_db
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection cn = new MySqlConnection("Server=127.0.0.1;Uid=demo_usr;Pwd=pass;Database=demo_db;");
            MySqlDataAdapter adr = null;
            try
            {
                DataTable dt = new DataTable();
                adr = new MySqlDataAdapter("call list_users", cn);
                adr.SelectCommand.CommandType = CommandType.StoredProcedure;
                adr.Fill(dt); //opens and closes the DB connection automatically !!
                foreach (DataRow dr in dt.Rows)
                    Console.WriteLine(string.Format("user_id = {0}", dr.Field<uint>("user_id").ToString()));
            }
            catch(Exception ex)
            {
                Console.WriteLine(string.Format("oops - {0}", ex.Message));            
            }
            finally
            {
                adr.Dispose();
                cn.Dispose();
            }
            Console.WriteLine("\nPress any key to quit.");
            Console.ReadKey();
        }
    }
}

Ok this fails - why ?? Here's the exception message:

oops - SELECT command denied to user 'demo_usr'@'localhost' for table 'proc'

Remember we only granted execute permissions to demo_usr for demo_db - nothing else !!

Now we can fix this by granting them select perms on mysql.proc but if we do that they will be able to see the stored procedure body - which we want to avoid.

So how do we get around this ? Well we can login into mysql console as demo_usr and call a stored procedure from the command line so could perhaps do the same in the app code ?

mysql> use demo_db;
Database changed
mysql> call list_users();
+---------+
| user_id |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
+---------+

So changing the line:

 adr.SelectCommand.CommandType = CommandType.StoredProcedure;

to

adr.SelectCommand.CommandType = CommandType.Text;

we now get...

user_id = 1
user_id = 2
user_id = 3
user_id = 4
user_id = 5
user_id = 6

Press any key to quit.

Great - it works.

Hope this helps

Rgds f00

Answer 2

You can restrict premissions to your stored procedures using GRANT.

Perhaps you can use somethng like -

GRANT EXECUTE ON PROCEDURE db.storedproc TO 'username'@'somehost';

You can find the complete syntax for GRANT here

Edit -

GRANT is to provide permissions and REVOKE is to do the opposite of it. You can find more information about REVOKE here.

You can try using -

FLUSH PRIVILEGES; 

This reloads the privileges from the grant tables in the mysql database.

If the user already has execute permissions and you want to revoke all the permissions for the user then you can use -

REVOKE ALL PRIVILEGES ON db.* FROM username@somehost

To revoke a user's execute grant on a specific stored procedure, you can use

REVOKE EXECUTE ON PROCEDURE db.storedproc FROM username;  

Always make sure that you don't grant global privileges unnecessarily.