Re: some question on response

On 15 Aug 2006 17:11:01 -0500, mistonl_at_mail.com (Mistton) wrote:

>In article <joe4e2dovdig3pqdi16hdsp29ltvo5cgt5_at_4ax.com>, sybrandb_at_hccnet.verwijderdit.nl wrote:
>>On 15 Aug 2006 15:54:02 -0500, mistonl_at_mail.com (Mistton) wrote:
>
>>It is not really possible to restrict. However with 8i and higher you
>>can create triggers on DDL statement (in order to raise an error in
>>your case)
>>Actually I would implement the idea of your DBA
>>- lockdown the schema owner
>>- create a new owner with select, insert, update, delete on the
>>original tables
>>- create synonyms for every table, view, function, procedure, package
>>
>>- No need to change your code.
>>
>>In the former solution you end up with a kludgy system.
>>The second (and older) alternative is transparent and more easy to
>>implement.
>>
>Thanks for responding!! i know your work and it is an honor to have your
>input!!!
>
>A bit confused
>
>with your solution do we need to do a ex/imp? is that what is meant by create
>new user?
>
>What do you mean by lockdown schema owner?
>
>our goal is that users would continue to user STEPH accounts with same
>password,etc and no need to change app code, not sure how this would work.

No, no exp/imp needed.

What one would do:

the existing STEPH remains.
You CREATE an user called STEPH_USER (or whatever). STEPH grants you SELECT, INSERT, UPDATE, DELETE on every table, view STEPH grants EXECUTE on every procedure, function, package.

You connect as STEPH_USER.

You issue CREATE SYNONYM <original_table_name> FOR STEPH.<original table_name> for *every* table, view, procedure, function, package.

Now you can
select * from <original_table_name>
just like before under STEPH_USER.

Now you LOCK the STEPH account, and throw away the key.

If you would need to change anything in the origingal STEPH account (by change I mean 'ALTER TABLE' or something like that), either you need to UNLOCK the account, or a DBA need to do it on STEPH's behalf.

Hope this helps and makes it clear.

--
Sybrand Bakker, Senior Oracle DBA