Magic said...Seems a good idea however it all falls flat because the same user can still do the following in SQLPlus:
select utl_http.request('http://www.oracle.com/') from dual;
or any other website or host as long as they have grant execute to utl_http.
Myself I think this is a huge security flaw but then again it is just my opinion.
Thanks for the well laid out example by the way, appreciated.
Philip Howe said...Tim,
When I issue the create_acl, I get:
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 226
ORA-06512: at line 2
I have even dropped and recreated the synonym.
The action of the error can be seen here:
Since you've already recreated the synonym, I can only image there is something wrong with your installation. Check the package exists, is valid and you have permission to use it. If all that is OK, then I guess you need to contact Oracle support.
Dmitry said...Hi Tim,
So far you've been re-printing Oracle documentation very successfully, but let me ask you this: How is actually to disable ACL completely in 11g? Have you though of that?
Tim... said...Dmitry: Insulting someone then asking them a question is not really the best way to get a favorable response.
Reagrding, "re-printing Oracle documentation". Look at the size of the Oracle documentation and look at my article. I've written a quick how-to article summarizing the points I find important. I don't see how this can possibly be called reprinting the Oracle documentation. If that were the case I would suggest that every other website and blog about Oracle could be accused of the same thing. It's utter nonsense.
As for disabling the feature, why on earth would I do that? That would open up a whole bunch of potential security problems.
If your box absolutely must be able to access anything, then set up an ACL using wildcards so it can access any server.
Tushar said...Tim, till 10g we had no issues with UTL_ packages being accessible then why all of a sudden disabling ACL will become a huge security issue? More than 90% of databases are still not having this feature so I would really look for having any option to disable ACL if it is creating many issues.
The risk is dependent on they environment you work in. Some people have very locked down environments with nothing accessing the DB other than the app servers. In this type of closed network the ACL adds little in the way of security. If on the other hand the DB can access lots of other servers, or even the internet, then that is a real security issue.
You can always set up an ACL to allow access to all networks (*.*.*.*), but I would suggest you take more care than this. For most people, security only becomes an issue when something goes wrong, and then it can be too late.
ACL is introduced from 11g, so earlier versions
will work fine. But in 11g , select utl_http.request('http://www.oracle.com/') from dual;
will not work without ACL.
Please clarify if my understanding is correct.
Ismail said...Thanks for your help ,
i have experienced error while i was trying to create package utl_http , thanks to Allah i found that was missing to grant the user connection:
grant execute on utl_http to scott;
Thanks to damien Antipa
Read the article and you will see it already includes the execute grant on the UTL_HTTP package already. It is done in the testing section.
Toby said...Thanks for the article, It helped me resolve the issue in 11G.
John O'Toole said...Great article as always Tim. This really helped me understand ACL.
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!