Definer u procedurama MySQL-a!

Definer u procedurama MySQL-a! 09.11.2010. u 11:55 · pre 194 meseca #1

Interesuje me da li postoje ograničenja u pokretanju procedure od strane usera koji nisu definer?

Npr. definer procedure je 'root'@'localhost'. Da li će neki drugi user(sa execute privilegijom) moci da poziva proceduru?

Koja je svrha definera, u smislu zaštite?

Unaprijed hvala.

Re: Definer u procedurama MySQL-a! 13.11.2010. u 00:54 · pre 194 meseca #2

Citat:

grant syntax - Stored Routine Privileges:
The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels. Except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines.

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

The permissible priv_type values at the routine level are ALTER ROUTINE, EXECUTE, and GRANT OPTION. CREATE ROUTINE is not a routine-level privilege because you must have this privilege to create a routine in the first place.

MySQL stores routine-level privileges in the mysql.procs_priv table.

Citat:

create procedure syntax - security context:
The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER. The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context.

The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.

Dakle, ako i dalje nije jasno. Da bi osoba X@Y mogla da izvrsi SP mora da ima EXECUTE pravo nad tom SP.
Sama SP se izvrsava sa pravima DEFINERA ili sa pravima X@Y u zavisnosti sta je definisano kao SQL SECURITY vrednost (po defaultu je definer).

Sta to znaci ... ako imamo SP pera() kojoj je definer A@B sa SQL SECURITY DEFINER, kada user C@D izvrsi tu proceduru ta procedura ce se izvrsavati kao A@B, te ako A@B ima pristup tabeli t1 i t2 a nema pristup t3 i t4, dok C@D ima pristup t2 i t3 a nema t1 i t4, kada C@D pozove pera() iz te SP ce moci da se pristupi tabeli t1 iako C@D nema inace pristup tamo ali nece moci da se pristupi tabeli t3 iako C@D ima pristup tamo.

jasno ?

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

1

Re: Definer u procedurama MySQL-a! 15.11.2010. u 13:22 · pre 194 meseca #3

Jeste, jasno mi je! :)

Bunilo me je definisanje definera u invoker security context-u.

Hvala na objašnjenju!

Re: Definer u procedurama MySQL-a! 15.11.2010. u 17:52 · pre 194 meseca #4

nema na cemu, jeste malo "zakukuljeno" ... ali mislim da je to tako definisano standardom... znam da se ovi koji prave ceo taj deo oko sp-a mnogo busaju oko sql standarda (posebno kada im se spomene malo ozbiljnije prosirenje sp sintaxe - pl-sql, perl, php ...)

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Forum kao aplikacija na telefonu

Srodne teme