Granting access to another database schema

There would be situations where you need to grant access to another schema in the database to manage or read from a table/view/trigger/index/synonym/sequence, etc.

The first rule to note is that whoever creates a table is the owner of that database object. For instance, if a UserA creates a table, UserA is the owner of that object. Thus, UserB cannot access the object except granted access on it or if such user has DBA privilege.

Type of access on database objects

These are the types of privileges you can grant a database schema on any database object. For instance, UserA can grant all these privileges to UserB (with admin option).

ALTER  
DELETE
INDEX
INSERT
UPDATE
SELECT
REFERENCES
ON COMMIT REFRESH
QUERY REWRITE
DEBUG
FLASHBACK

You can also grant privilege WITH GRANT OPTION. With grant option empowers the user to also transfer the privilege or grant same privilege to another user in the database. 

Where to confirm ownership

To check ownership of database objects, you need to query data dictionary views that store information about database objects. The key one are:

DBA_TABLES
DBA_VIEWS
DBA_SYNONYMS
ALL_TABLES
ALL_VIEWS
ALL_SYNONYMS
USER_TABLES
USER_VIEWS
USER_SYNONYMS

All objects starting with 'DBA_' are all objects in the database. Objects starting with 'ALL_' are objects a particular schema has privilege on while those starting with 'USER_' are those the particular user owns.

How to grant access

In order to allow read only access on tables, issue this command:
 

SQL> grant select on  pharmacist.license to  nurse;

Granting access to all database objects for another schema

Yes, it is possible to grant access to all tables owned by a certain user to another database schema. You can run a script to pull all the tables and grant the privilege immediately. Use the script below and adjust the schema name as appropriate.

Generating a script to grant readonly access to another database schema 

set heading off
set pagesize 1000
set feedback off

spool grant_select_table.sql
select 'GRANT SELECT ON '||table_name||' TO NURSE;' from user_tables;
spool off

spool grant_select_views.sql
select 'GRANT SELECT ON '||view_name||' TO NURSE;' from user_views;
spool off

select view_name from dba_views where owner='PHARMACIST';

[oracle@ ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 27 14:06:36 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @grant_select_table.sql
SQL> @grant_select_view.sql

connect NURSE/****@patients

SQL> select count(*) from pharmacist.license;
COUNT(*)
----------
     10512

SQL> select count(*) from pharmacist.note;
COUNT(*)
----------
     25828

After running the script, Nurse can now select on pharmacist's tables and views. 

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

Error likes "*Cause: The UTL_HTTP package failed to execute the HTTP request" are very common with Oracle 11g and older versions of Oracle database. The problem is that PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR cannot be executed by the current user. 

Applications rely on database schemas to execute functions. For example, using internet maps to determine location in an application requires an external connection to another webpage. This is why 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. 

This is how the process works: 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. 

In Oracle 11.2.0.3.6, this problem was solved. Prior releases of Oracle requires XML DB to be installed and Configured a network Access Control Lists (ACLs) in the database before these packages can work.

What is an ACL? An ACL is a network access control list that enables a DBA control access to external connection through the host. Using an ACL is common in production databases when issues with Network Access like ORA-24247 arises.

Environment
Linux 5.4
Oracle 11.2.0.3.0 (Production)

Cause
Privilege to UTL_http  should have been granted to specific user or schema that will be using the external connection. Though this can be given through patches/updates, it is highly recommended to reduce host vulnerability to manually assign privilege through ACL.
If schema has access to objects after Oracle patch was installed, this is/could be a problem when installed on the production server because accounts that should not have access to this and other objects will have access to things  they are not supposed to.

Solution
Verify that the Port is open
You need to verify that your port is open and can connect externally and also download webpages. Use these commands:

login as user "Oracle" (OS environment)

wget http://maps.google.com/
or
wget -r -l 0 http://maps.google.com/

If you get a response, then your port is open and ready for connection.

Create the user requiring access

Run these commands to create the users needed:

create tablespace patients 

datafile '/home/oracle/app/oracle/oradata/patients/datafile/patients01.dbf' size 50m reuse autoextend on maxsize unlimited;

create smallfile temporary tablespace patientstemp tempfile '/home/oracle/app/oracle/oradata/patients/datafile/patientstemp01.dbf' size 5m autoextend on next 2048k maxsize 1024m extent management local uniform size 1m;

create user patients profile "default" identified by "patients"
default tablespace patients
temporary tablespace patientstemp
quota unlimited on patients account unlock;

grant create session to patients;
grant connect to patients;
grant create trigger to patients;
grant create view to patients;
grant create procedure to patients;
grant create sequence to patients;
grant create table to patients;
grant create synonym to patients;

Run script to create ACL
The script will first drop any existing ACL before creating a new one. This is necessary to reduce or eliminate conflicts.

begin
        DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'utl_http.xml');
end;
/
commit;
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'utl_http.xml',
                                    description => 'geo code ACL',
                                    principal   => 'PATIENTS',
                                    is_grant    => true,
                                    privilege   => 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'utl_http.xml',
                                       principal => 'PATIENTS',
                                       is_grant  => true,
                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'utl_http.xml',
                                    host => 'your_ip_address');
END;
/
COMMIT;

Grant access to user
as user "SYS" run this command

grant execute on utl_http to PATIENTS;

grant execute on DBMS_NETWORK_ACL_ADMIN to PATIENTS;

Assign ACL to networks (in this order)

SQL> BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'utl_http.xml', host => 'maps.google.com');
  END;
   /

SQL> BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'utl_http.xml', host => 'local.yahooapis.com');
 END;
  /

Verify the ACL

SELECT acl from dba_network_acl_privileges;
/sys/acls/utl_http.xml

SELECT host, lower_port, upper_port, acl FROM   dba_network_acls;

Desc   dba_network_acls;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HOST                                      NOT NULL VARCHAR2(1000)
 LOWER_PORT                       NUMBER(5)
 UPPER_PORT                         NUMBER(5)
 ACL                                         VARCHAR2(4000)
 ACLID                                     NOT NULL RAW(16)

Verify permission
Run this script as user "SYS" to verify if the permission to connect externally has been granted to the user - patients:

SELECT acl, principal, privilege, is_grant, 
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date FROM   dba_network_acl_privileges;

SQL> SELECT DECODE(
  2  DBMS_NETWORK_ACL_ADMIN.check_privilege('/sys/acls/utl_http.xml', 'PATIENTS', 'connect'),
  3  1, 'GRANTED', 0, 'DENIED', NULL) privilege
  4  from dual;
PRIVILE
-------
GRANTED


Check that it can connect
Run this script as user "SYS" to verify if the permission to connect externally has been granted to the user - patients:

SQL> DECLARE
  2  l_url            VARCHAR2(50) := 'http://maps.google.com';
  3  l_http_request   UTL_HTTP.req;
  4   l_http_response  UTL_HTTP.resp;
  5   BEGIN
  6  l_http_request  := UTL_HTTP.begin_request(l_url);
  7  l_http_response := UTL_HTTP.get_response(l_http_request);
  8  UTL_HTTP.end_response(l_http_response);
  9   END;
10  /

I hope this helps. Please drop your comments or/and questions.

Pix credit: Oracle, NetworkDigest

Database auto-start and Stop

Hi folks, I am going to demonstrate in this post the process of writing and launching a script to automatically start and stop a database and listener on Oracle. I am working in this environment:

Oracle 11.2.0.1.0
Linux 5.4
instance name PATIENTS

Purpose: creating an auto-start script on PATIENTS database

Log in as Oracle

vi start_PATIENTS

add this line:

#!/bin/sh

ORACLE_SID=PATIENTS

export ORACLE_SID

PATH=$ORACLE_HOME/bin:$PATH

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH

echo "Starting Database $ORACLE_SID"

sqlplus /nolog >/dev/null <

     connect / as sysdba

     startup;

     exit

EOF

if [ $? != 0 ] ; then

 echo "Database $ORACLE_SID did not start!"

else

 echo "Database $ORACLE_SID started!"

fi

vi stop_PATIENTS

add this line:

#!/bin/sh

ORACLE_SID=PATIENTS

export ORACLE_SID

PATH=$ORACLE_HOME/bin:$PATH

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH

echo "Stopping Database $ORACLE_SID"

sqlplus /nolog >/dev/null <

     connect / as sysdba

     shutdown immediate;

     exit

EOF

if [ $? != 0 ] ; then

 echo "Database $ORACLE_SID did not stop!"

else

 echo "Database $ORACLE_SID stopped!"

fi

vi start_listener

add this line:

#!/bin/sh

echo "Starting listener"

lsnrctl start > /dev/null

if [ $? != 0 ] ; then

  echo "listener did not start"

else

  echo "listener started!"

fi

vi stop_listener

add this line:

#!/bin/sh

echo "Stopping listener"

lsnrctl stop > /dev/null

if [ $? != 0 ] ; then

  echo "listener did not stop"

else

  echo "listener stopped!"

fi

change file to executable

[oracle@localhost ~]$ chmod +x start_PATIENTS
[oracle@localhost ~]$ chmod +x stop_PATIENTS
[oracle@localhost ~]$ chmod +x start_listener
[oracle@localhost ~]$ chmod +x stop_listener

launching the script:

[oracle@localhost ~]$ ./start_listener
Starting listener
listener started!

[oracle@localhost ~]$ ./start_PATIENTS
Starting Database PATIENTS
Database PATIENTS started!

[oracle@localhost ~]$ ./stop_PATIENTS
Stopping Database PATIENTS
Database PATIENTS stopped!

[oracle@localhost ~]$ ./stop_listener
Stopping listener
listener stopped!


launching the script:

Log in as Oracle (SSH or Putty)

From /home/oracle, launch the files.

Start database in this order….

Launch start_listener
Launch start_PATIENTS

Stop database this way….

Launch stop_PATIENTS
Launch stop_listener


You should get this message after launching the script…

[oracle@localhost ~]$ ./start_listener
Starting listener
listener started!

[oracle@localhost ~]$ ./start_PATIENTS
Starting Database PATIENTS
Database PATIENTS started!

[oracle@localhost ~]$ ./stop_PATIENTS
Stopping Database PATIENTS
Database PATIENTS stopped!

[oracle@localhost ~]$ ./stop_listener
Stopping listener
listener stopped!

Thanks for reading.

RMAN-06169: could not read file header for datafile 7 error reason 9

Error
While attempting to run a backup of the database, we got an error identifying a problem with a datafile. In this post, I will discuss the cause of the problem and how the error was resolved.

rman target sys/****@PUBLIC

Starting backup at 01-APR-13
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 7 error reason 9
RMAN-06169: could not read file header for datafile 7 error reason 9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 04/01/2013 15:54:32
RMAN-06056: could not access datafile 7

cause
There is a problem with the datafile 7. It could be due to corruption, or any other problem with the datafile. Oracle will not be able to back up the database except this datafile is restored. Alternative, as a temporary solution, you can skip the datafile in your backup. If the datafile is skipped, RMAN will be able to back up the database but it is not advisable.


solution:

Check the status of the datafile
sqlplus sys/***@PUBLIC

SQL> select file#, status from v$datafile where file# = 7;
FILE# STATUS
---------- -------
7 RECOVER

SQL> select file#, status, enabled from v$datafile where file# = 7;
FILE# STATUS ENABLED
---------- ------- ----------
7 RECOVER READ WRITE
exit;

Restore the datafile from backup
A copy of the datafile in backup can be used to restore the datafile. Simply log back into rman and issue this command:
RMAN> restore datafile 7;
datafile restored

NOTE: You do not need to put your database in mount mode to do this.

Recover the datafile
Recovering a datafile after restore brings it up to date. At this stage, archivelogs and incremental backups will be applied to bring the datafile to the point it was before the problem.

RMAN> recover datafile 7;
starting media recovery
database recovered


For more details on RMAN recovery and restore process, check out Oracle documentations at RMAN

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

In this post I want to talk about a common error with Oracle 11g, especially 11.2.0.1.0. We were getting this error on all versions of our application after upgrading from 10.2 to 11.2 oracle database on Linux 5.4 64 bit.

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)

Details

This problem is bug 13375884 associated with 11g databases but corrected in latest patch sets. Don't panic! Just follow instructions.

cause

The problem is common to Oracle 11g because prior to 11gR1, you can run PL/SQL code making calls to Oracle Supplied Packages without error. These packages include:

•UTL_TCP
•UTL_HTTP
•UTL_SMTP
•UTL_MAIL

However, after upgrading to a 11g version, this error comes because of inaccessible external network privilege for users calling the packages.

In 11gR1 the Oracle Database enhanced the level of security when users attempt to access External Network Services by implementing Access Control Lists (ACL) using the new DBMS_NETWORK_ACL_ADMIN package. The PL/SQL packages listed above were affected. For more information on this change to Oracle Database Security please review Oracle documentation.


see Oracle documentation on Fine Grain access control at ACL.

Temporary fix

To resolve this issue temporarily, run this script to grant privilege to the user so that the user can access external network service.

BEGIN

-- Only uncomment the following line if ACL "network_services.xml" has already been created
--DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'network_services.xml',
description => 'NETWORK ACL',
principal => 'QUEEN',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'network_services.xml',
principal => 'QUEEN',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'network_services.xml',
host => '*');

COMMIT;

END;


permanent fix

Apply patch 11.2.0.3.0 or later to permanently resolve this network issue.

1. Download the patchset from Oracle Metalink. The detail is "Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER"

2. unzip the software

3. run Installer

4. Run DBUA to configure database to higher version

More on this later.