Oracle 11g and ACL ( access control list )

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