The first one allows you to send simple email messages, the second one allows you to communicate with any other computer in the world via http calls, the third allows you to manipulate operating system files and the fourth one allows you to use TCP protocol to communicate with other devices in your network. As you can see these packages are very powerful and can wreck havoc if used by someone who has malicious intent.
As an initial precautionary measure the execute access on these packages must be revoked from public. If someone demands access to these packages then it must be given based on some genuine business reason. This will allow you to have clear knowledge of who has access over any of these packages. Furthermore as an additional precaution, for granular control, you must use the new fine grained access control over your network resources using these packages, which is new in 11g.[title type=”h2″ class=”tfuse”]Fine grained access over UTL_* Packages:[/title]
Even after you have revoked the access to these packages from public, there is still a potential threat from users who have access to these packages. For example, let say that you have granted Scott the access to execute the UTL_HTTP package because he needed to communicate to a particular host, for an application to work properly. There is no guarantee that Scott will not use this privilege to communicate to other hosts outside what is genuinely required. To control this aspect, Oracle has introduced find grained access control, using access control lists.
To implement this,the new DBMS_NETWORK_ACL_ADMIN package is utilized. The process involves certain steps and all this is done using different procedures in this package. In general the entire process involves creating an access control list, adding access control entries and finally assigning hosts to the newly created ACL.
In the following sections we will go through these steps, one by one, and will also talk about how to manage access control lists and accesses granted and denied through them.[title type=”h3″ class=”tfuse”]Creating an Access Control List:[/title]
Oracle uses xml db to store everything related to ACL. Every ACL has a corresponding xml file stored in /sys/acl directory. ACLs can be defined and altered by using the APIs that xml db provides but DBMS_NETWORK_ACL_ADMIN package does the same as well. Also it is much more convenient for DBAs to use this package than he xml db APIs. Take a look at the code below which will create an acl using the create_acl procedure.
2 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
3 acl => ‘dba.xml’,
4 description => ‘Network Access Control for the SYSDBA’,
5 principal => ‘SYSDBA’,
6 is_grant => TRUE,
7 privilege => ‘connect’,
8 start_date => null,
9 end_date => null);
The acl parameter in the procedure defines the name of ACL and hence the name of xml file generated as a result. The parameter value is case sensitive. The description parameter briefly describes the ACL. The principal parameter defines the name of user or role for which this entry is valid for. This must a valid user or role name and it must be provided in caps because it is validated against the database views and they hold these values in caps only. The is_grant parameter defines whether specific privilege listed next should be granted or denied. This is a Boolean parameter and will take either True or False value. The privilege parameter defines the privilege to be granted using this entry.
The privilege parameter can either have value connect or resolve which depends on the type of package for which this is being granted. Connect means that user or role is allowed to connect to some network resource and resolve means that user is only allowed to resolve an internal resource name whose IP is provided. This is also case sensitive value parameter.
The last two parameters are optional and defines the timeline of the particular entry in the list. If you want to specify these two parameters then you must do it according to time-stamp format including the timezone as well.
The above PL/SQL block when run will create your first ACL which you can see in /sys/acl directory. One hint of caution is that everytime there is need to check access from ACL, the xml db is consulted hence the size and number of ACLs in place can affect performance. You should try to share the ACLs as much as possible. Also each individual ACL works best if the number of entries in ACL are less then 16.[title type=”h3″ class=”tfuse”]Add Access Control Entries:[/title]
Once you have created the ACL you can add new entries to it. The add_privilege procedure will allow you to add new entries into an existing ACL. You will use the same procedure for both granting permissions and denying permissions as the is_grant parameter will control that. The below is the code example of how you can add new entries into ACL.
2 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
3 acl => ‘dba.xml’,
4 principal => ‘SCOTT’,
5 is_grant => TRUE,
6 privilege => ‘connect’,
7 start_date => null,
8 end_date => null);
There is no real difference between the create_acl procedure and add_privilege procedure. All the parameters are same and same implications are valid here as well. The only difference is the missing description parameter which is because the ACL has already been created and no description is needed here. Also it is worth mentioning that acl parameter must have a value of an existing acl xml file and remember that name is case sensitive.[title type=”h3″ class=”tfuse”]Assign Hosts:[/title]
Once you have defined the ACL and have also added all the ACEs that you wish to control, you can now finally assign hosts to the particular ACL. In the below code snippet we are allowing http calls to dbaexpert.com website.
2 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
3 acl => ‘dba.xml’,
4 host => ‘dbaexpert.com’,
5 lower_port => 80,
6 upper_port => 443);
As you can see the procedure used for this is assign_acl and it has only four parameters. The first parameter is the name of ACL and the second one is the host name that you want to assign. The lower_port and upper_port parameters define the port ranges which should be used to connect to the host. These parameters are used in cases of extreme security concerns. The default value for both of these two parameters is null. There is another caveat to these parameters and i.e. if you omit value of one parameter, it will take the value from the other one. For example if you don’t provide the value of upper_port and set the lower_port to 80 then the upper_port will also be 80.
One you have assigned the ACL to host you are done with configuration part. You can test the if everything is working by running the following command.
SQL> select utl_http.request(‘http://www.dbaexpert.com’) from dual;
If the above renders an html page then everything is working fine. You can also test by denying a user access to some specific website and running the above query from that user. If you get the below error message then it means that your ACL setup is working fine.
ORA-24247: network access denied by access control list (ACL)
I optionally left the most important part to the very last of configuration so that you don’t forget this. All ACL related operations are transactional and all changes must be committed to take affect.
So after every PL/SQL block above you must add the commit command at the end.[title type=”h3″ class=”tfuse”]Maintain Access Control Lists:[/title]
There are two more procedures which you can use to manage the access control lists. The delete_privilege procedure will remove an access control entry and drop_acl procedure can be used to drop an ACL file if you no longer want to use it. The following code snippet will remove the Scott user’s entry from ACL.
3 (ACL =>’dba.xml’,
4 PRINCIPAL => ‘CKIM’);
The procedure is very simple and straight forward and there are no new parameters worth explaining. The procedure will remove the Scott user’s ACE from ACL.
Similarly if you want to delete an ACL entirely meaning that you want to remove the file from /sys/acl location then you can use the drop_acl procedure following way.
Also there are two data dictionary views which DBAs can use to view information about ACLs and users that have been granted or denied access using those ACLs.
The order of entries within access control list is very important. Oracle reads the ACL from top to bottom. If first entry allows you to have access on a particular network access and second entry denies you from that then you will still be able to access network resources. Similarly if you were denied from network even via a role and were allowed on using network in next entry then you won’t be able to access the network resources.
You can control the position of particular entry using the position parameter of add_privilege procedure. For example the below code snippet will move the privilege being added to number one position in the list.
position => 1,
acl => ‘dba.xml’,
principal => ‘SH’,
is_grant => FALSE,
privilege => ‘connect’,
start_date => null,
end_date => null);
As this is the first entry so any other entry after this for user SH will not be effective This is true if entry is for user himself or if it is for some role of which user is member of.