Probably this might help u to arrive at a solution
rather than giving the actual solution
In certain situations, you may want to log in to
another schema to operate on that user's objects like
granting privileges, but you don't know
the password for that schema and you can't wait until
that user is available to assist you. Consider this
situation. A typical database has the following users:
SYSTEM
SYS
MATERIALS
ACCOUNTS
Assume you don't know the password for both MATERIALS
and ACCOUNTS, but you want to grant privileges on
MATERIALS's objects to the ACCOUNTS schema
immediately. You can't achieve this objective by
logging into SYS or SYSTEM schema. You should have to
log in to that schema for granting privileges to
others. That is, you should have to be the owner to
grant privileges on the objects. At the same time, you
should not disturb the existing password of the
MATERIALS schema. You can perform both the objectives
using the following steps:
1. Connect as SYSTEM or SYS schema and issue the
following sql command:
SQL> select 'alter user '||username||' identified by
values '''||password||''''
from dba_users
where username='MATERIALS';
This will return a statement like the one below
alter user MATERIALS identified by values
'D0DCC7D6877E8507';
Preserve this statement, as it will help you reset the
original (existing) password after finishing the work.
2. Set the new password for the MATERIALS schema by
giving the following sql command:
SQL> alter user materials identified by password;
Here you can set any password temporarily to complete
the objectives.
3. Log in to the MATERIALS schema, grant the privilege
on certain objects to the ACCOUNTS schema and whatever
jobs you want to complete in that schema.
4. Connect as the SYS or SYSTEM schema and execute the
preserved statement.
SQL> alter user MATERIALS identified by values
'D0DCC7D6877E8507';
This will reset the password to the previous one, and
the user's work won't be affected.
Cheers
Fazal
- hemantchitale_at_charteredsemi.com wrote:
>
> As you are talking of Oracle Apps, NONE of the
> suggested solutions :
> 1. Database ON LOGON Trigger
> 2. Lock Database Account
> 3. Change Database Account Password
> 4. Startup Database in Restricted mode
> would work.
>
> The users connect to the Database in the APPS schema
> -- this is the
> "universal"
> schema that Oracle Apps uses. The Patch requires
> APPS so Restricted
> doesn't
> help (unless you grant Restricted to APPS in which
> case all the users can
> logon).
> Ditto about locking, changing password or writing a
> trigger on the APPS
> schema.
>
> What you can do are :
> 1. Shutdown the Apache server for the Self-Service
> Modules
> 2. Shutdown the Forms server for the Forms Module
> 3. Shutdown the Concurrent Managers.
>
> All of the above would affect ALL users.
>
> Alternatively, login to the Application as the
> System Administrator user
> and
> change the Application User Passwords for the users
> whom you want disabled.
> Change the passwords back to a default (WELCOME)
> later.
>
> However, what you SHOULD do, per Oracle Support, is
> 1. Shutdown 2. Shutdown
> 3. Shutdown
> as I have listed above.
> If you are familiar with Oracle Applications
> Patching and are comfortable
> with reading
> the Patch drivers, you can figure out what database
> objects are being
> modified/updated/created
> and what Forms/Reports/HTML etc files are being
> modified/created by the
> patch.
> Then you can take an intelligent decision ....
> should you allow users to
> logon when
> applying the patch ?
>
> Hemant K Chitale
> Principal DBA
> Chartered Semiconductor Manufacturing Ltd
>
>
> "CHAN Chor Ling Catherine (CSC)" <clchan_at_nie.edu.sg>
> 18/02/2002 03:28 PM
> Sent by: root_at_fatcity.com
>
> Please respond to ORACLE-L
>
>
>
> To: Multiple recipients of list
> ORACLE-L <ORACLE-L_at_fatcity.com>
>
> cc: (bcc: CHITALE Hemant
> Krishnarao/IT/CHRT/ST Group)
>
> Subject: Disable certain users from
> login to database while applying
>
> Human
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Hi Gurus,
>
> I need to disable certain users from login to
> database while I apply the
> Human Resources Patches for our Oracle HR 11i
> Applications.
> Which is the best way to do it ? Should I write
> on-logon trigger to disable
> or should I take away their privileges to logon to
> report/forms/sqlplus ?
> What about the rest of the DBA Applications
> administrator ? What do U guys
> do to solve the dead-lock problem (If my users
> access the HR tables while I
> apply the patches, I will encounter dead-lock
> problem) ?
>
> Please advise. Thanks.
>
> Regds,
> New Bee
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: CHAN Chor Ling Catherine (CSC)
> INET: clchan_at_nie.edu.sg
>
> 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_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:
> INET: hemantchitale_at_charteredsemi.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_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).
Abul Fazal
Production Support Services - Quantum Leap
Standard Charted Bank
Singapore
HP : 65-94887900
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Abul Fazal
INET: angmokios_at_yahoo.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_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).
Received on Wed Feb 20 2002 - 04:13:24 CST
