SPは直接テーブルに権限を付与したくない場合やその他制限をかける場合など
の他パフォーマンスを考慮したりする場合に利用される。直接テーブルにSELECT権限を
付与せずともSPに対するEXEC権限のみで必要なデータを参照出来る。


mysql> delimiter //
mysql> CREATE PROCEDURE SP_TABLE101()
-> BEGIN
-> SELECT title,body FROM TABLE101;
-> END
-> //
Query OK, 0 rows affected (0.14 sec)

SQLクエリーの最後は「;」を付けます、ストアドプロシージャを記述する場合、
スクリプト中のセンテンスの終了にも「;」を使用するため、区別のために
デリミタ(SQLクエリーの区切り文字)を「//」のようにして変更します。
デリミタは、「|」、「/」などで問題ありません。

sp


mysql> delimiter /
mysql> CREATE PROCEDURE SP_TABLE101_2(OUT PARAMETER1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO PARAMETER1 FROM TABLE101;
-> END
-> /
Query OK, 0 rows affected (0.00 sec)

ここでは、SELECT * INTOクエリーを使用しています。
MySQLはSELECT …… INTO TABLEクエリーに対応しておらず、
INSERT INTO …… TABLEクエリーを代わりに使用しますが、
格納先が変数の場合はSELECT …… INTO 変数クエリーが使用可能。

sp2

——————–サーバーに格納されているSP確認——————–
mysql> SHOW PROCEDURE STATUS;
show_procedure


mysql> select SPECIFIC_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA,SECURITY_TYPE from information_schema.ROUTINES;
+---------------+--------------+----------------+---------------+
| SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA | SECURITY_TYPE |
+---------------+--------------+----------------+---------------+
| SP_TABLE101 | PROCEDURE | DB01 | DEFINER |
| SP_TABLE101_2 | PROCEDURE | DB01 | DEFINER |
+---------------+--------------+----------------+---------------+
2 rows in set (0.00 sec)

sp3

SQL SECURITY特徴はルーチンを生成させるユーザあるいはそれを呼び出すユーザの許可
を使って、ルーチンが実行されるべきか否かを明示するために使うことができます。
そのデフォルトはDEFINERです。この特徴はSQL:2003の新機能です。その生成者や利用者は、
ルーチンが属するデータベースにアクセスできる許可を取得していなければなりません。
ルーチンを実行することができるEXECUTE権限を持つ必要があります。
この権限を持たなければいけないユーザは、SQL SECURITY機能を設定する方法によって、
規定者か利用者のいずれかになります。

オプションのDEFINER節はSQL SECURITY DEFINER特徴を有するルーチンに対して、
実行中にアクセス権限をチェックする時使用すべきMySQLアカウントを特定します。
DEFINER節はMySQL 5.1.8.で追加されました。


17.2.1. CREATE PROCEDUREおよびCREATE FUNCTION 構文

Post Navigation