保护存储过程 [英] Protect Stored Procedure
问题描述
我想知道是否有办法对某些用户隐藏存储过程的文本.
我正在使用带有 Visual Studio 2008 SP1 的 MySQL 5.1.48 和 Net Connector 6.2.3.
我在 MySQL 中有两个用户，一个是 root(具有管理 porpuses 的所有权限)，另一个是 admin(具有选择、更新、插入、删除、create_temp_table、lock_table 和特定架构中的执行权限).
所有的存储过程都是由 root 用户创建的，所以 admin 用户只能在不知道它们如何工作或阅读它们的内容的情况下执行它们.
一切正常，直到我们开始创建数据集和使用 WebService，为此管理员用户需要 select 权限在表 mysql 的 proc 列中.问题是，有了这个权限，管理员可以看到所有的存储过程，包括它们的内容.
请告诉我是否有办法保护 SP，我不知道这是否在最新版本的 MySQL 中修复，它是 .Net 连接器.
通常，将 MySQL .NET 连接器与存储过程结合使用时，您必须授予应用程序用户"对 mysql.proc 的选择权限，否则连接器无法查看您试图通过它调用的 sproc.
因此，您的应用用户通常会获得以下两项授权:
grant execute on foo_db.* to foo_dbo@localhost 由'pass'标识；将 mysql.proc 上的选择授予 foo_dbo@localhost；正如您正确指出的那样，这是一个安全问题，但我确实知道一种解决方法，这可能会让您感到害怕，但是，它完全可以正常工作，并且实际上与标准方法一样有效(想想- 更少的开销).
因此在撤销 mysql.proc 上的授予选择并刷新权限后...
string sqlCmd = string.Format("call list_users({0})", userType);MySqlConnection cn = new MySqlConnection();cn.ConnectionString = "服务器=....";adr = new MySqlDataAdapter(sqlCmd, cn);adr.SelectCommand.CommandType = CommandType.Text;//从 sproc 更改为 CommandType.Textdt = 新数据表();adr.Fill(dt);//自动打开和关闭数据库连接！！希望这有帮助:)
编辑:由于我的回答引起了一些混乱，我想我会在我看到的问题中添加一个完整的演练......
创建演示数据库
只是一个包含两个用户 demo_dbo 和 demo_usr 的快速演示数据库 (demo_db).
demo_dbo 被授予对 demo_db 的完全权限 - 他们是数据库所有者 (dbo) 并且可以在此数据库中为所欲为，例如创建、删除、创建过程、截断等...
demo_usr 是我们的应用程序用户 - 他们只被授予执行权限，所以他们所能做的就是调用存储过程；
调用 list_users();所以我以 root 身份登录并运行此脚本 demo_db.sql
删除数据库如果存在 demo_db;创建数据库 demo_db字符集 latin1默认字符集 latin1整理 latin1_swedish_ci默认整理 latin1_swedish_ci；从 demo_dbo@localhost 撤销对 mysql.* 的选择；从 demo_dbo@localhost 撤销所有对 demo_db.* 的权限；从 demo_dbo@localhost 撤销 *.* 上的授予选项；删除用户 demo_dbo@localhost;从 demo_usr@localhost 撤销对 mysql.* 的选择；从 demo_usr@localhost 撤销所有对 demo_db.* 的权限；从 demo_usr@localhost 撤销 *.* 上的授权选项；删除用户 demo_usr@localhost;将 demo_db.* 上的所有内容授予由pass"标识的 demo_dbo@localhost；将 mysql.* 上的选择授予 demo_dbo@localhost;授权在 demo_db.* 上执行给由pass"标识的 demo_usr@localhost；刷新权限；从 mysql.user 中选择主机、用户；创建 demo_db 架构
好的，现在我有一个数据库可以使用，所以接下来我以 demo_dbo 身份登录并开始创建我的模式对象:表、触发器、sproc、视图等......(不要忘记 demo_dbo 是所有者，可以这样做他们喜欢在demo_db里面)
所以我以 demo_dbo 的身份登录并运行这个脚本 demo_dbo.sql
使用demo_db；如果存在用户，则删除表；创建表用户(user_id int unsigned not null auto_increment 主键)引擎=innodb；插入用户值 (null),(null),(null),(null),(null),(null);如果存在 list_users，则删除程序；分隔符#创建过程 list_users()开始select * from users order by user_id;结尾 #分隔符；好的，这是创建的 demo_db 模式(1 个表和 1 个 sproc)，所以我最好在我仍然以 demo_dbo 身份登录的情况下进行一些测试.
从表中选择 - 通过
select * from users;用户身份========123456调用存储过程 - PASS
调用 list_users();用户身份========123456显示创建过程 - PASS
show 创建过程 list_users;过程 sql_mode 创建过程 character_set_client collation_connection 数据库整理==============================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================list_users CREATE DEFINER=`demo_dbo`@`localhost` PROCEDURE `list_users`()开始select * from users order by user_id;结束 utf8 utf8_general_ci latin1_swedish_ci好的，一切看起来都很棒demo_dbo(所有者)可以在 demo_db 中完成他们应该可以做的所有事情，所以现在我们最好测试一下我们的 demo_usr 可以做些什么.>
我以 demo_usr 身份登录并运行 demo_usr.sql 脚本:
显示表格 - 失败
显示表格；Tables_in_demo_db==================空值没什么可看的 - demo_usr 甚至无法看到数据库中的表是多么令人失望.
从用户表中选择 - 失败
select * from users;SELECT 命令拒绝用户 'demo_usr'@'localhost' 用于表 'users'正如预期的那样 - 他们无法直接访问表，所以我猜他们获取数据的唯一方法是通过我的存储过程 API.
调用存储过程 - PASS
调用 list_users();用户身份========123456终于有结果了，不过我们最好看看 demo_usr 能看到关于这个 sproc 的哪些信息，所以...
显示创建过程 - 失败
show 创建过程 list_users;过程 sql_mode 创建过程 character_set_client collation_connection 数据库整理==========================================================================================================================================================================================================================================================list_users utf8 utf8_general_ci latin1_swedish_cidemo_usr 可以看到存储过程存在(他们毕竟可以调用它)但看不到正文.
演示应用
所以我关闭了这个快速而肮脏的 C# 应用程序，它使用 MySQL .NET 连接器(顺便说一句，很棒)并尝试调用 list_users() 存储过程并以应用程序用户的身份填充数据表demo_usr.
使用系统；使用 System.Collections.Generic;使用 System.Linq；使用 System.Text;使用 MySql.Data;使用 MySql.Data.MySqlClient;使用 System.Data;命名空间 demo_db{课程计划{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;尝试{数据表 dt = 新数据表();adr = new MySqlDataAdapter("call list_users", cn);adr.SelectCommand.CommandType = CommandType.StoredProcedure;adr.Fill(dt);//自动打开和关闭数据库连接！！foreach(dt.Rows 中的 DataRow dr)Console.WriteLine(string.Format("user_id = {0}", dr.Field("user_id").ToString()));}捕获(异常前){Console.WriteLine(string.Format("oops - {0}", ex.Message));}最后{adr.Dispose();cn.Dispose();}Console.WriteLine("\n按任意键退出.");Console.ReadKey();}}} 好的，这失败了 - 为什么?这是异常消息:
oops - SELECT 命令拒绝用户 'demo_usr'@'localhost' 用于表 'proc'请记住，我们只为 demo_db 授予了 demo_usr 的执行权限 - 没有别的！！
现在我们可以通过在 mysql.proc 上授予他们选择权限来解决这个问题，但如果我们这样做，他们将能够看到存储过程体——这是我们想要避免的.
那么我们如何解决这个问题?好吧，我们可以以 demo_usr 身份登录到 mysql 控制台并从命令行调用存储过程，那么也许可以在应用程序代码中执行相同的操作?
mysql>使用 demo_db;数据库已更改mysql>调用 list_users();+---------+|用户 ID |+---------+|1 ||2 ||3 ||4 ||5 ||6 |+---------+所以改变行:
adr.SelectCommand.CommandType = CommandType.StoredProcedure;到
adr.SelectCommand.CommandType = CommandType.Text;我们现在得到...
user_id = 1用户 ID = 2用户 ID = 3用户 ID = 4用户 ID = 5用户 ID = 6按任意键退出.太好了 - 它有效.
希望能帮到你
Rgds f00
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.
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
这篇关于保护存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
