Oracle – How to Prevent Specific Applications from Connecting

ms accessoracle

Is there a way to prevent certain applications (e.g. MS Excel or Access) from connecting to Oracle database?

A bit of context:

My company uses an Oracle database as our live production database. We don't want any Excel or Access files being linked to this database. I don't really care if users use something like Toad to run a few queries by hand but I don't want a bunch of Excel or Access files linked to this database. We replicate this database and all users who need this data should get it from the replicated database, not the live database.

I can of course view the list of sessions and what program was used to connect with this query, but I'm not sure how to prevent the sessions from being opened in the first place:

SELECT SID,SCHEMANAME,OSUSER,TERMINAL,PROGRAM FROM GV$SESSION

The PROGRAM column of this query will tell me if something is opened with Excel or Access.

Best Answer

You could use a logon trigger for that, something along these lines:

create or replace trigger logon_trigger 
  after logon on database
declare
  v_prog varchar2(50);
  v_sid number;
begin 
  v_sid := to_number(sys_context('USERENV', 'SID'));
  select program into v_prog FROM GV$SESSION where sid = v_sid;
  if lower(v_prog) like 'access' then
      raise_application_error(-20000, 'Login not allowed');
  end if;
end;
/

PS. Not tested.

Related Solutions

How to allow an Oracle database to be accessed remotely

Oracle is an enterprise database server so you shouldnt need any strange setup to get it working, just the correct configuration on the DB server or client will be enough.

When you get connection errors, i suggest first ensuring that the client machine can ping the database server where is the name or ip address - try both as it's possible the name may not be configured in the hosts file of the client machine. (Note that ping may be disabled due to security but if you get a reply back, you know the connection is good.A non reply may not mean the network is missing but you can try to ssh or telnet to the dbhost from the client machine as an alternative)

 unix> ping <dbhost>

Once you have confirmed the client machine can see the db server, you can check the configuration on the DB server.

On the DB server, if you connect with the following, you are connecting directly to the database without using TNS.

 unix> sqlplus user/pass

If you connect instead with

 unix> sqlplus user/pass@ORACLE_SID

where ORACLE_SID is the identifier of the database, you will be using TNS, the same as your client machine. If this doesnt work locally, then it's also not going to work on the remote machine.

Check the listener is running

 unix> lsnrctl status

and if it's not, start it as the oracle user

 unix> lsnrctl start

and try again to connect using user/pass@ORACLE_SID

If this connects OK, then check your JDBC configuration to ensure you have specified the connection string correctly.

see http://javaeesupportpatterns.blogspot.de/2011/08/network-adapter-could-not-establish.html for assistance with the correct jdbc connection string.

Ms-access – Normalizing a database without access to source data

Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it. The two fundamental benefits to normalization are:

Data Integrity
Query Flexibility

The simple approach to normalization is to create a table for each person, place, thing, concept, or event. By doing this, you have each characteristic of each person, place, thing, concept, or event of interest to you in one and only one place in the database. The benefit of data integrity is achieved for when you insert and update data. Because you have normalized you don't have redundant copies of the same characteristic in many places across the database, each with a different value, that you have to remember to update and keep in sync. Second, you have only one place in the database to program checks when inserting or updating data to make sure the data is valid. Since your data is coming in from many excel spreadsheets, normalizing your data gives you the opportunity to implement data integrity checks on the load to make sure you analysis is based on accurate data. The benefit of query flexibility is achieved for when you want to read and analyze the data. Because you have normalized the data you can connect up the tables in a flexible way based upon the question about the data you want answered, including only what you need to answer the specific question. Second, this enables the database to return the answers to your questions much faster than if it had to scan through all the data, including the data not relevant to your question, in your un-normalized tables .

Access is a simplified DBMS and does include a basic SQL processor that allows you to write queries and thus take advantage of the benefits of normalized data. If you are eventually going to move to SQL Server, which is a full featured DBMS, then normalizing your data now will ease the transition and let you take advantage of the full capabilities of SQL Server and its very rich implementation of SQL.

As I mentioned in the beginning, to achieve these benefits you must be willing to do the up front programming to translate the data coming in from your various excel dumps and map the rows and columns in those spreadsheets to your normalized tables. This is not a trivial exercise but doable using Access programming. One approach would be to create tables that replicate the data as in the source and load the data into them. These are known as stage tables. Once you have the un-normalized data in Access tables you can then more easily write access code using SQL to extract the data from those stage tables, normalize it, identify data quality issues (say the same characteristic in two different excel dumps that should have the same value but do not), and load it into your normalized tables. This is the common method for normalizing data coming from an un-normalized source very common in subject area based data warehouses.

You will find this additional level of effort to be well worth it though once you have high quality, normalized data in your access database. You report consumers will see that you are a real data professional when you show them examples where data quality was poor and you discovered that fact so it can be corrected in the sources. Likewise, when they ask for a new report that analyzes the data in a very different way, you can rapidly create the new report using SQL to combine the data in the normalized tables in this very different way that wasn't originally anticipated. They will be very impressed that you are able to do this quickly and easily!

I hope this helps explain why normalization would be of benefit to you.

Best Answer

Related Solutions

How to allow an Oracle database to be accessed remotely

Ms-access – Normalizing a database without access to source data

Related Question