#1
  1. guru
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2004
    Location
    SW1A London UK
    Posts
    608
    Rep Power
    0

    Stored Procedure and security


    Hi all,

    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)

    OR

    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!

    Ed
    Create, Inspire & enjoy
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by edfollett
    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.
    Without starting a flame war, may I ask why you like that idea?
    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.
    /Stefan
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1045
    Hi,

    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?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. guru
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2004
    Location
    SW1A London UK
    Posts
    608
    Rep Power
    0
    Hey all

    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
    Ed
    Create, Inspire & enjoy

IMN logo majestic logo threadwatch logo seochat tools logo