Category Archives: Database

How to resolve ORA-24247: network access denied by access control list (ACL)

http://dbtricks.com/?p=159

If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error:

ORA-24247: network access denied by access control list (ACL)

The cause according to oracle is that

“No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.”

This error is common after an upgrade to Oracle 11. before oracle 11, using network resources via packages like utl_tcp, utl_smtp, utl_mail, utl_http, and utl_inaddr exposed the database to a serious security threat because once the user is granted with permission to use those packages there was no other limitation to connect to any computer.

Since Oracle 11, oracle introduced a fine grained access to network services using access control lists (ACL).

This new feature gave the DBA a better control on which user can connect to which computer

In order to solve ORA-24247 you will need to:

1) Create an acl (if it is not already created)

2) Add privileges to the user using the network resources

3) Assign the acl to a specific address

1) run the following query to check if an ACL exists

SELECT *FROM dba_network_acls;

If the computer you are trying to connect to is not listed under host, you will need to create an acl:
begin

dbms_network_acl_admin.create_acl (
acl => ‘http_permissions.xml’, — or any other name
description => ‘HTTP Access’,
principal => ‘SCOTT’, — the user name trying to access the network resource
is_grant => TRUE,
privilege => ‘connect’,
start_date => null,
end_date => null
);
end;
/
commit;

This will create the acl and grant SCOTT the connect privilege.

2) IF the acl exists run the following query to verify the user is granted with the appropriate privilege
SELECT *FROM dba_network_acl_privileges
where principal='SCOTT';

In order to use UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL the user will need the connect privilege
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => ‘SCOTT’,
is_grant => true,
privilege => ‘connect’);
end;
/
commit;

If you need to resolve a host name from a host IP you will need the resolve grant as well.
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => ‘SCOTT’,
is_grant => true,
privilege => ‘resolve’);
end;
/
commit;

3) The final step is to assign the acl to a specific target
BEGIN
dbms_network_acl_admin.assign_acl (
acl => ‘http_permissions.xml’,
host => ‘NETWORK ADDRESS’, /*can be computer name or IP , wildcards are accepted as well for example – ‘*.us.oracle.com’*/
lower_port => 80,
upper_port => 80
);
END;

It is important to note that only one ACL can be assigned to any host computer. If you assign a new acl to a target the old acl gets unassigned.

However, the old acl is not dropped. So, this could cause confusion because even if the acl was already assigned, it is possible that a new assignment overrode it.

Advertisements

ORA-12154 while connecting oracle database with OracleClient and Oracle 10g Client from vs2008 web project in Win7/2008 x64

When you opening connection to Oracle database in web project of Visual Studio 2008, you always get below error:
ORA-12154: TNS:could not resolve the connect identifier specified

But your tnsnames.ora is correct, sqlplus or other projects (like console/win form) can connect successfully.

Solution:
Upgrade Oracle 10g Client to 10.2.0.3 or upper to fix this strange bug.

Oracle: get tablespace/data file space usage information

whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;

set trimout off
set trimspool on
set pagesize 1000
set linesize 135

col file_id format 999
col file_name format a40
col tablespace_name format a15
col size_mb format 9,999,999
col maxsize_mb format 9,999,999
col ae format a3
col free_mb format 9,999,999
col free_pct format 999

spool space.status

SELECT   a.file_id, a.file_name, a.tablespace_name, TRUNC (a.BYTES / 1024 / 1024) size_mb,
         TRUNC (a.MAXSIZE / 1024 / 1024) maxsize_mb, a.autoextensible ae,
         TRUNC (  DECODE (a.autoextensible,
                          'YES', a.MAXSIZE - a.BYTES + NVL (b.free, 0),
                          'NO', NVL (b.free, 0)
                         )
                / 1024
                / 1024
               ) free_mb,
         TRUNC (DECODE (a.autoextensible,
                        'YES', (a.MAXSIZE - a.BYTES + NVL (b.free, 0))
                         / a.MAXSIZE
                         * 100,
                        'NO', NVL (b.free, 0) / a.MAXSIZE * 100
                       )
               ) free_pct
    FROM (SELECT   file_id, file_name, tablespace_name, autoextensible, BYTES,
                   DECODE (autoextensible, 'YES', maxbytes, BYTES) MAXSIZE
              FROM dba_data_files) a,
         (SELECT   file_id, tablespace_name, SUM (BYTES) free
              FROM dba_free_space
          GROUP BY file_id, tablespace_name) b
   WHERE a.file_id = b.file_id(+) AND a.tablespace_name = b.tablespace_name(+)
         AND a.autoextensible = 'NO'
ORDER BY a.file_name
/

spool off

set trimout on
exit;

Oracle: move table, index or blob from one tablespace to another

Use below sql to generate the alter commands.
v_owner: owner of the object
v_source_tablespace: current tablespace
v_target_tablespace: the “move to” tablespace

select decode( segment_type, 'TABLE', 
                       segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'alter ' || segment_type || ' ' || owner || '.' || decode( segment_type, 'TABLE', segment_name, '"' || segment_name || '"' ) ||  
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) || --chr(10) ||
      ' tablespace ' || :v_target_tablespace || --chr(10) ||
      --' storage ( initial ' || initial_extent || 
      --' next ' || next_extent || --chr(10) ||
      --' minextents ' || min_extents || 
      --' maxextents ' || max_extents || --chr(10) ||
      --' pctincrease ' || pct_increase || 
      --' freelists ' || freelists || 
      --')' || 
        ';'
  from (select segment_name, segment_type, owner from dba_segments where owner=:v_owner and tablespace_name=:v_source_tablespace), 
       (select table_name, index_name from dba_indexes where owner=:v_owner and tablespace_name=:v_source_tablespace)
 where segment_type in ( 'TABLE', 'INDEX' )
   and segment_name = index_name (+)
union 
select table_name, 3, 
    'alter table ' || owner || '.' || table_name || 
    ' move lob(' || column_name || 
    ') store as (tablespace ' || :v_target_tablespace || '_BLOB);'
    from dba_lobs where owner=:v_owner and tablespace_name=:v_source_tablespace
order by 1, 2
/