sqlplus / as sysdba
select grantee , table_name , privilege from dba_tab_privs
where table_name = 'UTL_HTTP' and grantee = 'PUBLIC';
GRANTEE TABLE_NAME PRIVILEGE
---------- ------------------- -----------------
PUBLIC UTL_HTTP EXECUTE
-- By default access on UTL_HTTP is granted to PUBLIC.
-- Revoke from public and grant to specific user who needs it.
revoke execute on utl_http from public;
grant execute on utl_http to scott;
select grantee , table_name , privilege from dba_tab_privs
where table_name = 'UTL_HTTP' and grantee in ('PUBLIC','SCOTT')
GRANTEE TABLE_NAME PRIVILEGE
------------ -------------- ------------------
SCOTT UTL_HTTP EXECUTE
-- Now only SCOTT has execute rights on UTL_HTTP.
SQL> conn scott/tiger
Connected.
create or replace procedure getTitle(pUrl VARCHAR2)
is
vResult CLOB;
begin
vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
dbms_output.put_line(vResult);
end;
/
SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
BEGIN getTitle('http://www.oracleflash.com'); END;
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SCOTT.GETTITLE", line 5
ORA-06512: at line 1
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL() creates a new Access Control List. Following are the parameters that it takes.
acl => Name of the Access Control List. This is a XML file which will be created in /sys/acls directory by default.
Description=> Description of the ACL.
Principal=> Name of the user or role (case sensitive) to whom the permissions are being granted or denied.
is_grant=> TRUE or FALSE, whether to grant access or deny access.
privilege=> connect or resolve (lowercase always). Will the user be able to connect to the network resource or just could resolve the network address.
start_date=> Start date (optional) of the access to the user.
end_date=> End date (optional) of the access to the user.
SQL> conn / as sysdba
Connected.
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'oracleflash.xml',
description => 'Permissions to access http://www.oracleflash.com',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
=================================================================================
Add a privilege to Access Control List
create role oracleflash;
-- A role is created. Now we grant connect to this role on our ACL.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'oracleflash.xml',
principal => 'ORACLEFLASH',
is_grant => TRUE,
privilege => 'connect',
position => null);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
=================================================================================
=================================================================================
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL()
assigns a network host local or remote to an ACL. It takes the following parameters:
acl => Name of the Access Control List.
host => Name of the host.
lower_port => Lower port (optional) from the range of ports allowed on this host.
upper_port => Upper port (optional) from the range of ports allowed on this host
Default for lower and upper port is null, which means all ports can be used on this host. And if you provide a port in lower_port and null in upper_port oracle assumes the upper_port=lower_port.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'oracleflash.xml',
host => '*.oracleflash.com');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'oracleflash.xml',
host => '*.oracle.com');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.oracleflash.com'));
COLUMN_VALUE
------------------------------------
www.oracleflash.com
*.oracleflash.com
*.com
*
SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('192.168.0.132'));
COLUMN_VALUE
------------------------------------
192.168.0.132
192.168.0.*
192.168.*
192.*
*
column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
/sys/acls/oracleflash.xml *.oracle.com
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
ACL PRINCIPAL PRIVILEGE IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml SCOTT connect true
/sys/acls/oracleflash.xml ORACLEFLASH connect true
=================================================================================
=================================================================================
Lets now see if the access is enabled or not.
SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
OracleFlash.com: Oracle Articles, Tutorials, Step by Step Install Guides, Scripts.
PL/SQL procedure successfully completed.
SQL> execute getTitle('http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm');
Configuring Privilege and Role Authorization
PL/SQL procedure successfully completed.
=================================================================================
=================================================================================
Remove a host from Access Control List
Following procedure can be used to remove a host from the ACL.
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
/sys/acls/oracleflash.xml *.oracle.com
BEGIN
DBMS_NETWORK_ACL_ADMIN.unassign_acl (
acl => 'oracleflash.xml',
host => '*.oracle.com');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
=================================================================================
=================================================================================
Delete a privilege from Access Control List
Following procedure can be used to delete a privilege from the ACL.
SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
ACL PRINCIPAL PRIVILEGE IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml SCOTT connect true
/sys/acls/oracleflash.xml ORACLEFLASH connect true
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => 'oracleflash.xml',
principal => 'ORACLEFLASH',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
ACL PRINCIPAL PRIVILEGE IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml SCOTT connect true
=================================================================================
=================================================================================
Drop an Access Control List
Following procedure can be used to drop the ACL.
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => 'oracleflash.xml');
COMMIT;
END;
/
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
no rows selected
=================================================================================
No comments:
Post a Comment