Re: Priveleges in stored procedure

Aleksandr Shneyderman wrote: > I have the following problem with stored procedures: when I create > procedure as DBA and grant execute privilege to 'user' then this user > can execute the procedure REGARDLESS whether she has privileges to > tables involved in the stored procedure. On the other hand when the > procedure created by another user ('owner') who also grants execute > privilege to 'user' then 'user' gets error like "No select permission" > while executing the procedure. ('user' does not have select permission > to the tables). > > Can anybody give any clue to the problem? Alex, this is not a problem; it is the documented way stored procedure permissions work. Say user jake writes a procedure, say "del_cust()", which diddles with tables: customer, orders, items, and inventory. Jake has the necessary privileges on all these tables. Now user alex wants to use the procedure delete a customer row. ZZZAPPP!! Alex has no privileges on this procedure. OK, so jake grants execute on del_cust to alex. When alex runs the procedure it is as though, for that moment, jake has granted the delete permissions on those tables to alex. This is what you are expecting, eh? Well, there's a fly in the ointment. A kink in the cable. A lump in the... <SLAP> Thanks, I was getting carried away. ;-) Forgetting SPL for a moment: User jake may have delete privileges on these tables. However, user jake does not have the power to *grant* delete privileges to anyone else ('cause jake is just an ordinary Joe, not a DBA). If jake were to enter the SQL command: grant delete to alex on customer, orders, items, inventory then jake would get an error message relating to privileges because jake does not [automatically] have the power to grant privileges. Back to SPL: When alex executes del_cust, it is as though jake is temporarily granting alex those delete privileges. If jake does not have the privilege: "delete WITH GRANT OPTION", then this temporary grant never happens. Thus, alex gets "No delete permission" or something like it. Bottom line: The owner of the procedure must have the permissions WITH GRANT OPTION before the user of the procedure can really run it. The DBA always has GRANT permissions on all tables. That's why anyone with execute permission on the procedure automatically can diddle any tables. This issue has NOTHING to do with the concept of a DBA procedure. The above is the concise lecture. ;-) -- -- Jake (Never yelled "CROWDED THEATER!" during a fire) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+