[Oracle-l] Restrict certain database access using 3rd party tools.

Paquette stephane

at Oct 3, 2002 at 4:08 pm

In homemade applications, by default users have a role
with read only, in the applications we change the
default role that allows insert, update, delete.

I've not tested this scenario but how about if, in a
database logon trigger, you check the
v$process.program field then depending of that value
you may be able to change the user default's role.

Should work on 8i using dedicated connection.

Rick_Cale_at_teamhealth.com a écrit : > Hi All,

We have users that have OPS$ accounts that have full
DML privs when they
run forms application via citrix. Currently they do
not have sqlplus,etc. There is a requirement that
some can have
sqlplus,toad,etc. I know you can set up security
for sqlplus,etc
using product_user_profile but is there a way to
allow only SELECT when
using a 3rd party tool such as TOAD.

Thanks
Rick

--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).

Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com

Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Jamadagni, Rajendra

at Oct 3, 2002 at 4:18 pm

⇧

From the TOAD help file ...

Although TOAD is intended as a developer's tool, TOAD can be made read-only
via the two license files that come with TOAD, READONLY.LIC and
FULLTOAD.LIC.

TOAD.EXE only reads TOAD.LIC to determine if it is full TOAD or read-only.
The license file contains a setting for read-only database access. The
network administrator can copy READONLY.LIC over the TOAD.LIC on an
individual workstation to make TOAD read-only at that workstation.
Remember, the TOAD.LIC file must be in the TOAD folder.

Quest Software

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Thursday, October 03, 2002 10:33 AM
To: Multiple recipients of list ORACLE-L

Hi All,

We have users that have OPS$ accounts that have full DML privs when they
run forms application via citrix. Currently they do
not have sqlplus,etc. There is a requirement that some can have
sqlplus,toad,etc. I know you can set up security for sqlplus,etc
using product_user_profile but is there a way to allow only SELECT when
using a 3rd party tool such as TOAD.

Thanks
Rick

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

text/plain attachment: ESPN_Disclaimer.txt

reply | permalink

Kevin Lange

at Oct 3, 2002 at 4:38 pm

⇧

Except for the fact that they could always change the program name that they
are running to match what you need. Then that security is bypassed.

-----Original Message-----
Sent: Thursday, October 03, 2002 11:08 AM
To: Multiple recipients of list ORACLE-L

In homemade applications, by default users have a role
with read only, in the applications we change the
default role that allows insert, update, delete.

I've not tested this scenario but how about if, in a
database logon trigger, you check the
v$process.program field then depending of that value
you may be able to change the user default's role.

Should work on 8i using dedicated connection.

Rick_Cale_at_teamhealth.com a écrit : > Hi All,

We have users that have OPS$ accounts that have full
DML privs when they
run forms application via citrix. Currently they do
not have sqlplus,etc. There is a requirement that
some can have
sqlplus,toad,etc. I know you can set up security
for sqlplus,etc
using product_user_profile but is there a way to
allow only SELECT when
using a 3rd party tool such as TOAD.

Thanks
Rick

--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).

Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com

Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
INET: kgel_at_ppoone.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Mercadante, Thomas F

at Oct 3, 2002 at 5:48 pm

⇧

Rick,

Can you change the forms application?
If so, then a really simple way of doing this is to grant insert, update and
delete access to the tables to an Oracle role.

When the form starts, enable that role to grant access to the tables. By
default, the role would not be enabled for the user.

You could even extend this idea by having a password required on the role,
and getting that password inside the form. that way, a sqlplus user could
not enable the role.

the other ideas restricting access by program name do not work because you
do not have control of the PC desktop.

Another thing I've seen done is to establish "shadow accounts". this idea
involves a person having an OPS account with read-only access to the db
tables. the user also has another oracle account that has total access to
all tables. but the user doesn't even know this account exists. again, the
forms application is run, connecting via the OPS account. the first thing
the form does is to query a lookup table, finding the OPS account and the
shadow account/password, and re-connects to the database using this account.

this is the best idea I have found for protecting the database.

hope these help.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Thursday, October 03, 2002 10:33 AM
To: Multiple recipients of list ORACLE-L

Hi All,

We have users that have OPS$ accounts that have full DML privs when they
run forms application via citrix. Currently they do
not have sqlplus,etc. There is a requirement that some can have
sqlplus,toad,etc. I know you can set up security for sqlplus,etc
using product_user_profile but is there a way to allow only SELECT when
using a 3rd party tool such as TOAD.

Thanks
Rick

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Paquette stephane

at Oct 3, 2002 at 6:08 pm

⇧

Oups ! you're right.

Kevin Lange a écrit : > Except
for the fact that they could always change

the program name that they
are running to match what you need. Then that
security is bypassed.

-----Original Message-----
Sent: Thursday, October 03, 2002 11:08 AM
To: Multiple recipients of list ORACLE-L

In homemade applications, by default users have a
role
with read only, in the applications we change the
default role that allows insert, update, delete.

I've not tested this scenario but how about if, in a
database logon trigger, you check the
v$process.program field then depending of that value
you may be able to change the user default's role.

Should work on 8i using dedicated connection.

--- Rick_Cale_at_teamhealth.com a écrit : > Hi All,

We have users that have OPS$ accounts that have full
DML privs when they
run forms application via citrix. Currently they do
not have sqlplus,etc. There is a requirement that
some can have
sqlplus,toad,etc. I know you can set up security
for sqlplus,etc
using product_user_profile but is there a way to
allow only SELECT when
using a 3rd party tool such as TOAD.

Thanks
Rick

--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L

(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).

=====
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com

Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et
en français !
Yahoo! Mail : http://fr.mail.yahoo.com
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Kevin Lange
INET: kgel_at_ppoone.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).

Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com

Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Jacques Kilchoer

at Oct 3, 2002 at 6:53 pm

⇧

Stupid DBA trick #32, or how to drive your DBA colleague wild on April
Fool's day:
go over to her machine, and change the name of the SQL*Plus executable
(%ORACLE_HOME%\bin\sqlplusw.exe, plus80w.exe or whatever it is) by
surrounding it with parentheses, e.g. "(sqlplusw).exe" and change the
shortcuts to point to that program. SQL*Net will NOT be happy.

-----Original Message-----
From: Kevin Lange

Except for the fact that they could always change the program
name that they
are running to match what you need. Then that security is bypassed.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Jacques Kilchoer

at Oct 3, 2002 at 6:53 pm

⇧

P.S. IIRC this will happen with any client program using SQL*Net:
e.g. change "My_program.exe" to "(My_program).exe" and SQL*Net will be
unable to establish a connection.

-----Original Message-----
From: Jacques Kilchoer

Stupid DBA trick #32, or how to drive your DBA colleague wild
on April Fool's day:
go over to her machine, and change the name of the SQL*Plus
executable (%ORACLE_HOME%\bin\sqlplusw.exe, plus80w.exe or
whatever it is) by surrounding it with parentheses, e.g.
"(sqlplusw).exe" and change the shortcuts to point to that
program. SQL*Net will NOT be happy.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Jared.Still_at_radisys.com

at Oct 3, 2002 at 9:11 pm

⇧

Dang, and my intern just left last week!

Jacques Kilchoer
Sent by: root_at_fatcity.com
10/03/2002 11:53 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L
cc:
Subject: RE: Restrict certain database access using 3rd party tools.

P.S. IIRC this will happen with any client program using SQL*Net:
e.g. change "My_program.exe" to "(My_program).exe" and SQL*Net will be
unable to establish a connection.

-----Original Message-----
From: Jacques Kilchoer

Stupid DBA trick #32, or how to drive your DBA colleague wild
on April Fool's day:
go over to her machine, and change the name of the SQL*Plus
executable (%ORACLE_HOME%\bin\sqlplusw.exe, plus80w.exe or
whatever it is) by surrounding it with parentheses, e.g.
"(sqlplusw).exe" and change the shortcuts to point to that
program. SQL*Net will NOT be happy.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Yechiel Adar

at Oct 8, 2002 at 5:53 pm

⇧

Just deny login if your trigger does not know the program.

Check the archives for example scripts for login triggers.

Yechiel Adar
Mehish

----- Original Message -----
To: Multiple recipients of list ORACLE-L
Sent: Thursday, October 03, 2002 8:08 PM

Oups ! you're right.
--- Kevin Lange a écrit : > Except
for the fact that they could always change

the program name that they
are running to match what you need. Then that
security is bypassed.

-----Original Message-----
Sent: Thursday, October 03, 2002 11:08 AM
To: Multiple recipients of list ORACLE-L

In homemade applications, by default users have a
role
with read only, in the applications we change the
default role that allows insert, update, delete.

I've not tested this scenario but how about if, in a
database logon trigger, you check the
v$process.program field then depending of that value
you may be able to change the user default's role.

Should work on 8i using dedicated connection.

--- Rick_Cale_at_teamhealth.com a écrit : > Hi All,

We have users that have OPS$ accounts that have full
DML privs when they
run forms application via citrix. Currently they do
not have sqlplus,etc. There is a requirement that
some can have
sqlplus,toad,etc. I know you can set up security
for sqlplus,etc
using product_user_profile but is there a way to
allow only SELECT when
using a 3rd party tool such as TOAD.

Thanks
Rick

--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services

---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L

(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).

=====
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com

___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et
en français !
Yahoo! Mail : http://fr.mail.yahoo.com
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L

(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Kevin Lange
INET: kgel_at_ppoone.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L

(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).

>

=====
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Yechiel Adar
INET: adar76_at_inter.net.il

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Mercadante, Thomas F

at Oct 8, 2002 at 6:28 pm

⇧

Yechiel,

this is a never-ending chase. security by attempting to control what
program is to be used is useless. it will always be broken - or the DBA
will always be stuck monitoring what is being used.

security based on Oracle Roles is the only good answer.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, October 08, 2002 1:54 PM
To: Multiple recipients of list ORACLE-L

Just deny login if your trigger does not know the program.

Check the archives for example scripts for login triggers.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L
Sent: Thursday, October 03, 2002 8:08 PM