tag:blogger.com,1999:blog-7849367040589270673.post7428665138033618874..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Tightening security in your PL/SQL code with 12c new features, part 1Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-7849367040589270673.post-65779920162117245342017-03-24T09:17:15.912-07:002017-03-24T09:17:15.912-07:00Thanks, Iudith. You are correct. The revoke statem...Thanks, Iudith. You are correct. The revoke statement should have included ANY or used the FROM syntax. I will correct that. And your explanation about the exception is also spot on. Thanks!Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-55458243894564177332017-03-22T15:22:00.690-07:002017-03-22T15:22:00.690-07:00Hello Steven,
Just a short remark regarding the R...Hello Steven,<br /><br />Just a short remark regarding the REVOKE statement.<br /><br />Shouldn't it look like the following:<br /><br />REVOKE INHERIT PRIVILEGES ON USER powerful_boss FROM lowly_worker <br />/<br /><br /><br />or, alternatively, if the system privilege INHERIT ANY PRIVILEGES were used, then <br /><br />REVOKE INHERIT ANY PRIVILEGES FROM lowly_worker <br />/<br /><br /><br />This feature is rather weird ... in the sense that normally the owner of an object is the one who decides what others can do with that object,<br />and not the opposite.<br /><br />Revoking the privilege from the procedure owner by the invoking user means that the invoking user will in fact prevent himself from running the procedure ... not only this procedure, but any other procedure owned by that same owner ...<br /><br />This in fact means that, if you want to create really powerful invoker-rights routines, those have to be created always by a "very trusted" owner.<br /><br />Regarding the exception ORA-06598, it is raised by the mere attempt to execute the procedure by user powerful_boss, and not by the procedure execution itself, so, if you want to handle that exception in the code shown above, <br />this should be done in the anonymous block that called SHOW_TODOS.<br /><br /><br />And ... yes, all what you said about the boss above is true in real life, I do confirm it !<br /><br />And, if code review will be done before deploying it to production, this will surely NOT be done by the boss :)<br /><br />Cheers & Best Regards,<br />Iudith<br />iudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.com