Saturday, July 14, 2007

Oracle 11g and ACL ( access control list )

Virag Sharma virag123@gmail.com
If your application code ( package , procedure , function ) uses utl_tcp , utl_smtp etc and you upgraded database to oracle 11g , then that code will not work even you gave execute permission on utl_tcp etc to user

You need execute addition steps and add user to ACL ( access control list )

How to check XML DB installed or not

.

SQL> select COMP_NAME, status from dba_registry;

COMP_NAME STATUS
-------------------------------------------- --------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Real Application Clusters VALID
Oracle XML Database VALID

...


How to install XML DB
If above query not show XML DB component then run following sql to install XML DB


sqlplus "/ as sysdba"
SQL>@?/rdbms/admin/catqm.sql

How to add user to ACL
Following example show , how add scott user to ACL

SQL> exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('MY.xml',
'Comments ..', 'SCOTT', TRUE, 'connect');

SQL>exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('MY.xml','*.abc.com');
SQL> Commit;

Note :- Don't forget to commit


As per Oracle 11g Upgrade manual

Configure Fine-Grained Access to External Network Services

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE

acl_path VARCHAR2(4000);

BEGIN

SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'host_name' AND lower_port
IS
NULL AND upper_port IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,
'user_name','connect') IS NULL THEN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
'user_name', TRUE, 'connect');

END IF;

EXCEPTION

WHEN no_data_found THEN

DBMS_

NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml',

'ACL description', 'user_name', TRUE, 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');

END;

COMMIT;



UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR PL/SQL Packages

A new security measure is introduced in this release for the following network-related
PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR.
The invoker of those packages needs additional privileges to connect to an external
host or to resolve the name or the IP address of a host. The packages check the
invoker for the necessary privileges only when the calls are made at runtime and
raises an exception if the invoker lacks the privileges. This new security measure is
implemented by XML DB's access control list (ACL) mechanism and, therefore,
requires XML DB to be installed in order to use those packages.

Each external host that a database user wants to connect to or to resolve the name
or IP address for from the database is restricted by an access control list (ACL).
To grant a user the privileges for the host, the database administrator should create
an ACL, add the privileges to the ACL for the user, assign the ACL to the host
and commit the changes using the DBMS_NETWORK_ACL_ADMIN PL/SQL package.
For example, to give the users SCOTT and ADAMS the permission to connect
to www.oracle.com via HTTP (namely to connect to TCP/IP port 80), the
database administrator should execute the following:

SQL> REM Creates a new ACL and adds SCOTT the privilege 
SQL> REM to the ACL to make TCP connections
SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('acl_for_oracle.xml', -
> 'ACL for www.oracle.com', 'SCOTT', TRUE, 'connect')
 
SQL> REM Adds ADAMS the privilege to the ACL to make TCP connections also
SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('acl_for_oracle.xml', -
> 'ADAMS', TRUE, 'connect')
 
SQL> REM Assigns the new ACL to www.oracle.com for TCP/IP port 80 (HTTP)
SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('acl_for_oracle.xml', -
> 'www.oracle.com', 80)
 
SQL> REM Commits to make the ACL take effect
SQL> COMMIT
 

The invoker of the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL packages needs the 'connect' privilege to make TCP/IP, HTTP, or SMTP connections to the external host. The invoker of the UTL_INADDR package needs the 'resolve' privilege to resolve the name or the IP address of the external host. Note that those privileges are not granted through the GRANT SQL statement but through the DBMS_NETWORK_ACL_ADMIN package.

The current ACL assignment to external hosts and the privileges currently defined
in the ACLs are shown through the system catalog views DBA_NETWORK_ACLS
and DBA_NETWORK_ACL_PRIVILEGES.

The invokers of other database components that use those PL/SQL packages to
perform network operations from the database, which are XML DB's
HttpUriType, Oracle Multimedia (formerly interMedia), and Spatial, are
subject to the same network permission check and need the same privileges.




TAG=> Oracle 11g New Features, Oracle 11g tips and tricks, Oracle Database 11g New Features for DBA, Oracle Database 11g New Features

1 comment:

  1. hello, this is very good information for me i am looking for it i have search many websites,but know i get the actual information thanks.Kashmir,glory of India, is poetry of nature. The beauty of Kashmir has always attracted travelers and emperors alike to appreciate the enchanting wonder that Kashmir is. A Kashmir Tour Packages is an experience of a lifetime, unforgettable in every way. The Kashmir holidays experience is no less splendid than a most beautiful dream, the kind of which we see with our eyes open. From the quiet sunset on Dal Lake in Srinagar to the thrilling rush of white water rafting adventure sports of Gulmarg, Kashmir has a plethora of unforgettable experiences to offer to the traveler. In fact, the beauty of Kashmir can be summarized into three distinct categories, the foothill plains of Jammu, the beautiful lakes and valleys of Kashmir (Gulmarg, Sonmarg, Pahalgam, Srinagar etc) and the magnificent mountains of Ladakh and Leh. This is the reason we offer tour packages encompassing these three distinct regions of Kashmir. Kashmir is also a holy land with pilgrims visiting the Amaranth Siva-Linga made of ice and also the Vaishno Devi temple pilgrimage. Therefore, if you are a pilgrim to Kashmir, you should choose the tour package that takes you on your pilgrimage.

    ReplyDelete

Google