Grant UTL_HTTP permission in PLSQL

问题:

I would like to get HTML content from a certain webpage in my function. I read I can do it with the UTL_HTML package in PLSQL. So I made the following code in the project:

v_webcontent := utl_http.request(v_weblink);

Here the v_webconent and v_weblink are declared earlier. running this in de function gives an PLSQL exception: PLS-00201: identifier 'UTL_HTTP' must be declaredI guess this problem is because the package isn't available (from this link: same error message).

I followed the advice. So I created a new database connection in sql developer as the SYSTEM role (SYS didn't work, it sayd I could only logon using SYSDBA or SYSOPER but both wouldn't take the standard password I created with the database). Then I entered the code in the link above.

GRANT EXECUTE ON SYS.UTL_HTTP TO [database];

The user I created is named 'Database'. It first gave me an error without the [] square brackets. Table or view does not exist so I then put the brackets around it. Now it gives error:

    Error starting at line : 1 in command -
GRANT EXECUTE ON SYS.UTL_HTTP TO [database]
Error report -
SQL Error: ORA-00987: missing or invalid username(s)
00987. 00000 -  "missing or invalid username(s)"
*Cause:    
*Action:

So I have no idea how to fix this. In the link above OP said that he got an other error, so I also checked if I didn't have the same problem. I entered:

SELECT * FROM dba_objects WHERE object_name='UTL_HTTP'

It returned 4 entry's. With owners: SYS, SYS, PUBLIC and APEX_040000.

Can somebody help me? Do I need to logon as SYS and with what passwords?

回答1:

1. Log on as SYS AS SYSDBA.
2. Execute grant execute on sys.utl_http to "Database"; Do not use any square brackets!

That should work.

Piece of advice: Do not name your DB user 'Database'.

---

To reset your SYS password

1. Run cmd.exe as administrator.
2. cd to your ${ORACLE_HOME}/database.
3. Find the PWDsomething.ora file there (where something will be your instance name), copy its name (into clipboard).
4. Run orapwd file=PWDsomething.ora password=SomePasswordOfMine force=y, where PWDsomething.ora will be replaced with the file name from the step 3 and SomePasswordOfMine must be replaced by whatever password you wish to have.

That might work.

回答2:

Since 11g you also have to create an access control list ('ACL') which specifies which users have access to particular domains, it is no longer sufficient to just grant users execute privileges on utl_http!

Something like this should work in 11g(after granting execute privileges on UTL_HTTP to your database user, as specified by the accepted answer) :

SQL> BEGIN
  2  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl=>'mikesacl.xml',
  3  description=>'access control list example',
  4  principal=>'HR',
  5  is_grant=>TRUE,
  6   privilege=>'connect');
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.


    SQL> begin
      2  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
      3  acl=>'mikesacl.xml',host=>'*'); 
      4  commit;

      5  end;
      6  /

PL/SQL procedure successfully completed.

Here is a very helpful link which explains the parameters of the above two functions :

http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html

Good luck!