Re: Security Question-Reposted

From: Keith Boulton <boulke_at_globalnet.co.uk>
Date: Mon, 22 Nov 1999 17:36:30 GMT
Message-ID: <38397417.5418001@read.news.globalnet.co.uk>

On Sun, 21 Nov 1999 16:00:00 +0530, Anurag Minocha <anurag_at_synergy-infotech.com> wrote:

>> The application always connects to the same user/schema eg: r2 . I want
>> that users should not be able to connect to r2 schema in any way other
>> than our application even though they know the password. i.e I want to
>> prevent access from sql*plus, crystal reports, etc etc.

You cannot. What is sometimes done to reduce the risk of problems is to grant access to a non-default database role with a password so that the role is enabled by your application e.g.:

SQL> connect internal/kb_at_keith
Connected.

-->> create the role

SQL> create role test identified by xyz;

Role created.

-->> grant to the schema used for login
SQL> grant test to kb;

Grant succeeded.

-->> make a non-default role

SQL> alter user kb default roles all except test;

User altered.

-->> grant access from the table owner to the role
SQL> connect wf/wf_at_keith
Connected.
SQL> grant select on process to test;

Grant succeeded.

-->> connect to the login schema

SQL> connect kb/kb_at_keith
Connected.

-->> show that the tables are not visible
SQL> select * from wf.process;
select * from wf.process

                 *

ERROR at line 1:
ORA-00942: table or view does not exist

->> enable the role
SQL> set role test identified by xyz;

Role set.
-->> show the table is visible

SQL> select * from wf.process;

no rows selected

This prevents accidental errors by the users but does not prevent deliberate damage. The process can be made a little more obscure if required, but it will alway only be "pretend" security. Received on Mon Nov 22 1999 - 11:36:30 CST