Re: Is it possible to stop odbc connections to an oracle database

You can, but it is pretty easy to by pass for anyone with either insomnia or a *real* desire to get in. See below (custom trigger code not shown)...

C:\>sp

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 16:19:45 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> column osuser format a20
SQL> column program format a30
SQL> column db_schema format a20
SQL> set lines 85
SQL> select * from allowed_user_access;

OSUSER               PROGRAM                        DB_SCHEMA
-------------------- ------------------------------
--------------------
*                    sqlplus.exe                    *

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

C:\>sqlplus scott/tiger_at_test

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 16:22:49 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

C:\>sp

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 16:23:02 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> delete from allowed_user_access;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

C:\>sqlplus scott/tiger_at_test

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 16:23:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-20999: ACCESS DENIED.
ORA-06512: at line 33

C:\>sp

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 16:23:20 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> insert into allowed_user_access values('*','sqlplu.exe','*');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

C:\>sqlplus scott/tiger_at_test

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 16:23:40 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-20999: ACCESS DENIED.
ORA-06512: at line 33

C:\>which sqlplus
c:\oracle92\bin/sqlplus.exe

C:\>mv c:\oracle92\bin/sqlplus.exe c:\oracle92\bin/sqlplu.exe

C:\>sqlplu scott/tiger_at_test

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 16:24:08 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL>

Regards,

Steve Received on Tue Jan 24 2006 - 14:27:14 CST