• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1691
  • Last Modified:

restrict oracle database schema access for developers

hi,
i have one in-house ERP developed and developers are working on it. In fact for developers i have given another server where they can develop, test application etc

but they are using Toad  and they are able to connect live database from Toad which I don't want to do it

How to restrict developer's for accessing live databae, please advise
0
Jinesh Kumar Kochath
Asked:
Jinesh Kumar Kochath
  • 5
  • 3
  • 2
  • +2
1 Solution
 
OP_ZaharinCommented:
- its already a good approach to have a different server and database for developer to work on. using toad might enable them to see only the database schema on development server unless they have all the details to connect to prod (username, password, instance, etc)
- do not share/reveal any documentation that contain details on prod database instance, schema, user etc.
- do not use standard/known password that they can guess on the prod server. use different password on development server than the prod.
- tnsname. create the tnsnames for their oracle client on their pc that contain only for the development server.
- restrict access to the prod server especially on remote desktop access and shared folder. they might be able to copy the tnsnames from the server
- make sure you (or other dba who have access to prod) are not sharing the c or d drive because they might be able to copy your tnsnames.
- if they need to access to view the prod data, create views to the actual tables. create a new user schema on prod with previleage only to those views and create a dblink between dev and prod database using that new user.
0
 
slightwv (䄆 Netminder) Commented:
Sorry to repeat one above but it is important:  Do not give the developers production database passwords!

You can also restrict the ip addresses that can connect to the production database.

You can configure the sqlnet.ora file or create a system logon trigger.
0
 
schwertnerCommented:
One way is to create a trigger that will prevent logging from machines that are outside a given list of machines or IPs
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
schwertner,

I believe I had already covered system logon triggers in my post above.
0
 
Jinesh Kumar KochathSr. IT Manager (Middle East & South East Asia)Author Commented:
preventing developers via login to specific IP's is an great thing
can you plesae help me on this
0
 
slightwv (䄆 Netminder) Commented:
Check the online docs for your specific version because they change the exact file and parameters a little between versions but check EXCLUDED_NODES and INVITED_NODES

For 10gR2:
http://www.stanford.edu/dept/itss/docs/oracle/10gR2/network.102/b14213/sqlnet.htm

TCP.EXCLUDED_NODES

Purpose

Use the parameter TCP.EXCLUDED_NODES to specify which clients are denied access to the database.

Syntax

TCP.EXCLUDED_NODES=(hostname | ip_address, hostname | ip_address, ...)

Example

TCP.EXCLUDED_NODES=(finance.us.acme.com, mktg.us.acme.com, 144.25.5.25)

TCP.INVITED_NODES

Purpose

Use the parameter TCP.INVITED_NODES to specify which clients are allowed access to the database. This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.

Syntax

TCP.INVITED_NODES=(hostname | ip_address, hostname | ip_address, ...)



Possibly TCP.VALIDNODE_CHECKING
0
 
schwertnerCommented:
CREATE OR REPLACE TRIGGER logonauditing BEFORE LOGON ON database
DECLARE
  machinename VARCHAR2(64);
  osuserid    VARCHAR2(30);
  v_sid       NUMBER(10);
  v_serial    NUMBER(10);
  v_ip        VARCHAR2(20);
   
  CURSOR c1 IS
    SELECT sid, serial#, osuser, machine, sys_context('userenv','ip_address')
      FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
  OPEN c1;
  FETCH c1 INTO v_sid, v_serial, osuserid, machinename, v_ip;
  INSERT INTO ICW_logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
      user, osuserid, machinename, v_ip);
 
  CLOSE c1;
END;
/

Instead the INSERT statement you can put an IF statement like this one:

  IF    INSTR(lower(machinename),'bg\shvertner',1) <> 0
     OR INSTR(lower(machinename),'bg\fed',1) <> 0
     OR INSTR(lower(machinename),'bg\plamen',1) <> 0
     OR INSTR(lower(machinename),'developbeta',1) <> 0
     OR INSTR(lower(machinename),'oralin1',1) <> 0
     OR machinename IS NULL
THEN
       null;
  ELSE
     RAISE_APPLICATION_ERROR (num => -20000,msg => message);
  END IF;

Of course replace 'machinename' with  'c1.machinename'

But be very catious because by software mistake you can block logon off all users (including SYS and SYSTEM) to the DB.
0
 
slightwv (䄆 Netminder) Commented:
>>be very catious because by software mistake you can block logon off

Exactly.  So why use a logon trigger for this when SQLNet has this functionality built in?
0
 
schwertnerCommented:
Because this trigger gives the possibility to track all attempts to login and which are rejected.
Additionally this trigger offers the possibility to use not only machine names, but also many other attributes of the client.
Read only all possible parameters of the function  sys_context('userenv','ip_address').

But in contrast to other expert I will not claim that the proposed solution is the best one. It is up the DBA the decide what is the best option.

My regards to everybody!
0
 
Jinesh Kumar KochathSr. IT Manager (Middle East & South East Asia)Author Commented:
hi,
please give me time till tomorrow, so that I can check it and let you know our updates
0
 
martin_seaCommented:
DO NOTE TO TEST ALL IN A TEST DATABASE FIRST. ITS ALSO BETTER TO GO WITH A.) THAN B.) OPTION

A.) restricting through sqlnet.ora on connection.
For Oracle 9i/10g, the valid node checking lines are added to the $ORACLE_HOME/network/admin/sqlnet.ora file.

tcp.validnode_checking = yes
tcp.excluded_nodes=(dbclient132,DBCLIENT333)                                 # tcp.excluded_nodes=( x.x.x.x | name, x.x.x.x | name)


dbclient132,DBCLIENT333 CANNOT ACCESS THE DATABASE  OTHERS CAN ip CAN

The Listener must be stopped and started for valid node checking to become active.

B.) restricting through trigger for all TOAD check  "AWARE its applying for all users"  (HAVE NOT TESTED THE BELOW SO PLEASE TEST IF USING IN QA)

you could create a logon trigger to check this. and ENABLE/DISABLE this trigger during peak hours

check with module FOR TOAD

create trigger NOTOAD after logon on database begin
if (sys_context('userenv','module')='TOAD' ) then
raise_application_error(-20001,'Please no toad yet, try again later');
end;
/

CHECK WITH MODULE AND USERNAME

create trigger NOTOAD after logon on database begin
if (sys_context('userenv','module')='TOAD' AND sys_context('userenv','USERNAME')='YOUR_USER_NAME' ) then
raise_application_error(-20001,'Please no toad FROM YOUR USERNAME, try again later');
end;
/


check with IP and module

create trigger NOTOAD after logon on database begin
if (sys_context('userenv','module')='TOAD' and SYS_CONTEXT('USERENV','IP_ADDRESS')='YOUR_IP' ) then
raise_application_error(-20001,'Please no toad FROM YOUR IP, try again later');
end;
/

But be very catious because by software mistake you can block logon off all users (including SYS and SYSTEM) to the DB.

HOPE IT HELP's but again repeating test in test database first.
0
 
slightwv (䄆 Netminder) Commented:
martin_sea,

I believe the sqlnet parameters and logon trigger have already been covered.  I realize you are probably just trying to expand on them further and provide additional information but please try to not duplicate previous answers.

For example:  Even though Toad was mentioned, the logon trigger has a few holes:  If I wanted to get in and they blocked Toad, I would just use sqlplus or some other app.

I realize you have stressed 'TEST FIRST' but I feel it is better to leave specific apps out of it.  Especially since you admit:  "HAVE NOT TESTED".
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now