March 1st, 2013, 06:56 AM
Stored Procedure and security
I've done a search but can't find the information I am looking for...
Basically I am in the process of setting up a new DB for my site and am aiming to put the majority of the functionality (well as much as I can ) into stored procedures / functions and triggers and take as much of the processing out of the web interface.
The question i have though is with regards to security specifically around procedures.
I am well versed in Sybase and Oracle and I would like to implement private/ protected procedures so that sensitive tasks such as resetting user passwords can only be called from within another procedure and not externally.
If i can't do this then is there a way of finding out what procedure has called the procedure I want to check (without passing the name in as a IN variable)
Is there a way to find out the name of the current running procedure (again without having to self declare the variable)
I suspect all are not possible as I have not been able to find any supporting documentation regarding this. I suspect I will just have to move these sensitive procedures out and create a batch process or something external to the web interface to limit any potential issues!
Create, Inspire & enjoy
March 1st, 2013, 10:43 AM
Without starting a flame war, may I ask why you like that idea?
Originally Posted by edfollett
Because my opinion (and several of my friends) is the complete opposite since it makes programming/debugging/version control/etc much easier when you have all code in one place.
And since you are not going to have any presentation code in a stored procedure you are bound to have logic in the external language which is why I think the choice is simple.
Usually the only time I use stored procedures is when I'm working on an application that are going to run against a database over a WAN connection and there are procedural processing on a larger amount of data that I can't collapse into a SQL query.
As for your question about locking a stored procedure to only be called from another stored procedure I can't see any solution in MySQL's access control structure.
March 1st, 2013, 11:11 AM
the SQL SECURITY clause allows you to have a function/procedure be executed with elevated privileges. This way you can grant direct execution of your "private" function only to some dummy user and then execute the "public" function with the privileges of that user. I haven't tried it out, but this should work.
But I agree with sr that the whole idea of "I'm gonna put everything into stored procedures" sounds very obscure. Databases aren't made for writing applications. Are you sure you know what you're doing, or could this be an optimization attempt you haven't really though through?
March 1st, 2013, 11:57 AM
Thanks for the replies.
Firstly to clarify I am referring to purely business logic not interface or the such when I say I want to use stored profs as much as pos.
I have opted to go with this approach because I prefer to keep as much of the business logic segregated from the interface as possible. I don't want online SQL selects updates etc within my php code. Also I feel it is more secure.
For me I find this approach for more maintainable. As have all the banks I have worked at!
The segregation means there is little to no reliance on the php code from performing any specific tasks. So if / when I come to upgrade the interface it is a much simpler process.
Of course you will probably have different opinions which I encourage but that is the approach I am comfortable with and feel is the most elegant for my site.
All the best
Create, Inspire & enjoy