Hmm..
While taking away connections from user thru sqlplus would allow application access coz application makes connection using with application user name.
Product_user_profile table is useful only to restrict some scope of sqlplus.
If u still want to keep sqlplus access looks like u have to create a database trigger for restricting external programs.
The following note from Metalink may be helpful.
HTH.
Jyoti
Bookmark
Fixed font
Go to End
Doc ID:
Note:1042517.6
Subject:
HOW TO SET ORACLE PRIVILEGES BASED ON TOOL USED
Type:
PROBLEM
Status:
PUBLISHED
Content Type:
TEXT/PLAIN
Creation Date:
11-FEB-1998
Last Revision Date:
11-FEB-1998 Problem Description:
====================
How do you set Oracle privileges based on the tool being used
in addition to the login ID?
Search Words: security, application
Solution: GENERAL INFORMATION: RESTRICTING ACCESS TO SPECIFIC TOOLS
Solution Description:
=====================
Oracle DBMS does not provide any good way to do this. Security in
Oracle is based on the login ID. This comes with the general
assumption that a particular login can perform certain operations,
regardless of what tool you are using to log in. Oracle7 and Oracle8
have very fine-grained access control mechanisms, but they are both
based on the login ID.
That having been said, there are three general strategies for
restricting access based on the tool being used, not on the login:
- Disable the undesired tool
- Code database triggers that disable DML under certain circumstances
- Code extra data access in the desired tool
Trusted Oracle does not help at all; privileges in TO7 are still
granted on the basis of the login, not the tool.
Solution: USE PRODUCT_USER_PROFILE TABLE TO DISABLE DML FROM SQL*PLUS
Solution Description:
=====================
To disable DML from SQL*Plus, DBAs can use the PRODUCT_USER_PROFILE table.
Example
-------
To disable all inserts by PZOLA, use the following command as SYSTEM:
SQL> insert into product_profile (product, userid, attribute, char_value)
2 Values('SQL*Plus', 'PZOLA', 'INSERT', 'DISABLED');
WARNING! This command is case-sensitive: you *MUST* capitalize it
as shown above.
You can do similar things with UPDATE and DELETE. For details, refer
to Bulletin 98549.947 or Appendix E of the SQL*Plus User's Guide and
Reference.
Disadvantage
------------
This strategy has one problem: it only disables access for SQL*Plus.
It does not keep users from updating database data via Browser,
Microsoft Access, or any other data access product (such as SQL*Less).
To disable DML via Browser, for instance, you would have to configure
that through Browser's access mechanism. Plus, some products (such as
Microsoft Access) do not have *any* way of disabling DML from the
tool.
Additional Information:
=======================
Oracle Documentation:
---------------------
SQL*Plus 3.X User's Guide and Reference
Appendix E, Security
PRODUCT_USER_PROFILE Table
Oracle Support Bulletins:
-------------------------
PRODUCT_USER_PROFILE
Solution: CREATE DATABASE TRIGGERS THAT DISABLE ACCESS TO PROGRAM
Solution Description:
=====================
You can create database triggers that disable access depending on the
program. This works by looking at the 'program' column in the
V$SESSION virtual table. This table is normally not accessible to
users: you need to create a stored procedure to return the value to
the user.
As SYS:
create or replace function sec_progname return varchar2
is
progname V$session.program%type;
begin
select program into progname from V$session
where audsid = userenv('sessionid');
return progname;
end sec_progname;
grant execute on sys.progname to public;
As the owner of the table 'FOO':
create or replace trigger check_dml
before insert or update or delete on foo
declare
insufficient_privileges exception;
pragma exception_init(insufficient_privileges, -1031);
begin
if sys.sec_progname <> 'F45RUN32.EXE' then
raise insufficient_privileges;
end if;
end;
(You can add additional logic to check for the user name:
this code disables DML for everyone, including SYS and SYSTEM!)
Disadvantages
-------------
This approach has two weaknesses. First, it is *far* from completely
foolproof. For example, the end user can simply rename plus33w.exe to
f45run32.exe and then run it. The security system is now completely
compromised.
Second, it requires creating a trigger for every table used by the
application. This can (of course) be automated, but it is still a
pain. It can also become a maintenance problem.
Solution: HARDCODE ROLE PASSWORD INTO FORM
Solution Description:
=====================
You can hardcode a role password in the form.
To do this, create two database roles (e.g. READ_ACC and FULL_ACC).
The READ_ACC role only has the object privileges necessary to read the
tables, while the FULL_ACC role has DML privileges as well. The
READ_ACC role does not have a password, while the FULL_ACC role is
protected by a password -- which the end user does not know. The end
user is granted both roles by the DBA. The form contains code that
enables the FULL_ACC role, probably in a When-New-Form-Instance
trigger.
This is the end result: when the user logs in to the database via
Forms, the FULL_ACC role is enabled, so the user has DML privileges on
the tables; however, logging in any other way gives the user reduced
privileges on those tables.
Example
-------
Using the table FOO owned by SCOTT
and the end user PZOLA:
create role read_acc;
grant select on scott.foo to read_acc;
create role full_acc;
grant update,delete,insert on scott.foo to full_acc;
alter role full_acc identified by asdfg;
grant read_acc, full_acc to pzola;
alter user pzola default role all except full_acc;
In the When-New-Form-Instance trigger:
dbms_session.set_role('connect, read_acc, full_acc identified by asdfg');
Disadvantages
-------------
This solution has a number of problems. Mostly, they revolve around
the fact that if the end user gets the hidden role password, the
security system is completely compromised. While this system can
probably stop the average computer-illiterate operator, a
sophisticated user has at least three different methods to get the
role name and password.
Method 1
--------
Use a hex editor to examine the source code embedded in the .FMX file.
Since the command sent to the database is a literal string, it is
still visible in clear text in the compiled PL/SQL.
Partial workaround #1:
Put the password into a global variable and dynamically create the
SET_ROLE string. This is only a partial workaround, since the
password string is still encoded in the .FMX file, so a persistent
hacker would still find it.
Partial workaround #2:
Put the password into a database table, fetch it from the database,
and use it to dynamically build the SET_ROLE string. This is still
only a partial workaround, as the SELECT statement that fetches the
password is now visible in the .FMX file.
Method 2
--------
Enable SQL Tracing, and examine the trace file. The SET ROLE
statement is plainly visible in the trace file.
Partial Workaround #1:
Keep end users from accessing trace files. This is only a partial
workaround, since users can use the .FMX file to connect to their own
database and get the SET ROLE statement from that connection.
Partial Workaround #2:
Fetch the password from a database table. This is still only a
partial workaround, as the SELECT statement that fetches the role
password is visible in the trace file and can be run against the
production database using SQL*Plus.
Method 3
--------
Run SQL*Net trace. Again, the SET ROLE statement is plainly visible
in the trace file.
Workaround:
Enable Secure Network Services. If configured with mandatory
encryption, the packet dumps will not contain anything meaningful.
Summary: Using this technique is pretty secure, especially if you
- dynamically build the role password string
- base it partially on a global variable and
partially on a value fetched from the database
- turn on Secure Network Services and data encryption
Solution: TAMPER WITH USER'S PASSWORD IN ON-LOGON TRIGGER
Solution Description:
=====================
You can tamper with the user's password in the On-Logon trigger, so
that the password that they type in to Forms Runtime is not the
password that gets sent to the database. This is somewhat tricky: you
need to bypass the On-Logon trigger long enough to get your own logon
screen up, read in the username and password, tamper with the password,
and then call the LOGON built-in with the modified password.
The first block in the form needs to be a modal window with username
and password text items. On the canvas, create a "Connect" button
with the following When-Button-Pressed trigger code:
:global.username := :bk_logon.t_username;
LOGON(:bk_logon.t_username, :bk_logon.t_password, FALSE);
IF NOT FORM_SUCCESS THEN
RAISE FORM_TRIGGER_FAILURE;
END IF;
GO_BLOCK('control_main');
Create the following On-Logon trigger code:
DECLARE
uname VARCHAR2(80);
passwd VARCHAR2(80);
connect_str VARCHAR2(80);
connect_info VARCHAR2(160);
BEGIN
DEFAULT_VALUE(NULL, 'global.username');
IF :global.username IS NULL THEN
NULL;
ELSE
uname := GET_APPLICATION_PROPERTY(USERNAME);
passwd := GET_APPLICATION_PROPERTY(PASSWORD);
connect_str := GET_APPLICATION_PROPERTY(CONNECT_STRING);
--
-- Here's where you tamper with the password!
--
passwd := passwd || 'a';
IF connect_str IS NULL THEN
connect_info := passwd;
ELSE
connect_info := passwd || '@' || connect_str;
END IF;
LOGON(uname, connect_info, FALSE);
IF NOT FORM_SUCCESS THEN
MESSAGE('Cannot log on: invalid username/password', ACKNOWLEDGE);
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
END;
The big advantage of this method is that users cannot log in to the
database at all, except through the Forms application. They know *a*
password, but it is not the password stored in the database. If
regular access is a problem, you can give them a different account
with read-only privileges.
Again, if the user figures out the password-modifying algorithm,
security is completely compromised. The trick is to build your
password-modifying algorithm with PL/SQL built-ins, which is converted
to compiled code and cannot be read in plain text.
In general, this method can be pretty secure. SQL*Net does not
display passwords during a trace, so your users cannot see the
modified password that way. In addition, SQL*Net actually encrypts
the password string before it goes out over the network, so your user
cannot use a network sniffer to see it. If you hide your
password-modifying code well, this method works well.
.
>From: "Fred Smith"
>Reply-To: ORACLE-L@fatcity.com
>To: Multiple recipients of list ORACLE-L
>Subject: Re: HELP - How can I limit what program users use to connect?
>Date: Thu, 23 Aug 2001 09:42:34 -0800
>
>I can't revoke connect permissions. I am simply looking for a way
>to
>prevent users from connecting via TOAD. They need to still be able
>to
>connect via the application and sqlplus.
>Any ideas?
>
>
>>From: "Jyoti N"
>>Reply-To: ORACLE-L@fatcity.com
>>To: Multiple recipients of list ORACLE-L
>>Subject: Re: HELP - How can I limit what program users use to
>>connect?
>>Date: Thu, 23 Aug 2001 09:20:15 -0800
>>
>
>
>_________________________________________________________________
>Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
><< message3.txt >>
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jyoti N
INET: jnair75@hotmail.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@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).
Received on Fri Aug 24 2001 - 11:56:20 CDT
