SP is now a bridge, bridging the user (our HR employee) and the table ( salary), to which the user has no direct access. Please replace 5.6 with the version you are using.) (For a detailed list and explanation of MySQL privileges, please consult the official documentation. The answer is using a Stored Procedure that returns the required information and grants that employee the EXECUTE privilege. But if he/she can't access the salary table, how can this employee get the aggregation information related to salary? How can we allow the employee to grab that information without compromising the HR policy? In this case, it is obvious that we can't even grant SELECT privilege to this HR employee (which, if we do, means he/she can see the detailed salary of everyone). We know MySQL has a comprehensive privilege control. An HR employee should have the right to grab some figures out of this table: total salary, average salary, etc but this employee should not see the detailed salary of each employee as this information will be too sensitive and should only be available to a few. In a human resource information system (HRIS), it is reasonable to assume that there exists a table holding the salary information of each employee. Last but not least, SP is a fundamental aspect of database security. It increases efficiency to put the data retrieving and processing at the server end instead of writing the same processing logic in a different syntax provided by all these languages/libs, if the data processing logic is so commonly used. Consider how many different languages/libs there are that we can use to deal with the database. SQL is standard and purely 100% platform independent. And we know a compiled program will run faster. This is somehow quite like the interpreted language execution (at the client end) and the compiled language execution (at the database server end). This is quite different from issuing the same query from the client side, where the query will be parsed by database drivers, analyzed and optimized (if possible) every time the query statement is called. It can be pre-compiled and analyzed by the database server. SP is stored and run directly in the MySQL server. So, to save bandwidth and increase robustness, it is sometimes a good idea to have more processing and logic done on the server side (in particular, the MySQL server) and have less data transferred through the network. In a typical environment, these layers will most likely not reside on one single machine, maybe not even in one network, for larger applications.Īlthough network speed has tremendously increased in the past few years, it is still the slowest and most unreliable compared to other ways of transferring the data (CPU cache, memory, hard disk, etc). The database layer, which handles all database queries, including but not limited to a SELECT query, an INSERT statement, etc.The PHP layer, which handles all PHP interpretation, does the application logic and generates the PHP part of response.The web server layer, which handles and dispatches user requests and sends back responses to the client layer.It receives user interactions and presents the data in a UI. The client layer, which is normally a web browser.In a typical PHP database web application, there are four layers: There are at least four advantages I can think of to use an SP in a database application.įirstly, it reduces the network traffic and overhead. That workflow works fine in most cases but there is one important aspect of database programming missing: the Stored Procedure. Most of us are quite familiar with the normal setup to build a database application: creating database, creating tables, set up indexes, CRUD the data, issue queries from the client side and do further processing if necessary. SP are also available in other common database servers (Postgre for example) so what we will discuss here can be applied to those as well. The official MySQL document should always be the place for reference. Note: We are not going to cover the full aspect of the SP here. In this tutorial, we will see how to create an SP in MySQL and execute it in MySQL server and in PHP. and r.Put simply, a Stored Procedure ("SP") is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages. Where r.routine_schema not in ('sys', 'information_schema', Left join information_schema.parameters p P.character_maximum_length as char_length, Query select r.routine_schema as database_name,Ĭase when p.parameter_mode is null and p.data_type is not nullĮlse parameter_mode end as parameter_mode, Query below return all parameters of stored routines (stored functions and stored procedures) and details about it in MySQL database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |