Home » RDBMS Server » Server Administration » create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 (Oracle Apex, Ver:3.2.1.​00.10, Windows)
create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #559923] Sat, 07 July 2012 13:16 Go to next message
Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
The requirement is as follows:

Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.

Could you please let me know you the steps in detail.
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #559935 is a reply to message #559923] Sat, 07 July 2012 17:14 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't think that it is an Apex problem. First you need to find out whether it is possible in the first place (i.e. connect to one database and create user in another one). I don't know whether you can do that or not; all users I have ever created or dropped belonged to "my" database, not a remote one. However, I'd put my bet on NO, you can't do that - I believe that you need to be connected to a database in which you want to create a user and - obviously - have required privileges.

Therefore, I'll move this question into the Server Administration forum. Let's hope that someone will be able to assist. Once you know the steps (if they exist), it shouldn't be a problem to implement it in Apex.
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #559936 is a reply to message #559935] Sat, 07 July 2012 18:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database
within this other Oracle DB, there needs to be a procedure that is owned by a suitably privileged owner
which can issue EXECUTE IMMEDIATE CREATE USER statement.
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #559939 is a reply to message #559935] Sun, 08 July 2012 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However, I'd put my bet on NO, you can't do that - I believe that you need to be connected to a database in which you want to create a user and - obviously - have required privileges.


It is possible to do it but it is definitively not wise to do it. To give high privileges through a database link is never a good decision.
THe best way is indeed to directly connect to the remote database.

Regards
Michel
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560106 is a reply to message #559939] Tue, 10 July 2012 10:24 Go to previous messageGo to next message
Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
Hi Michel,

As far as my knowledge goes, DB links can be used to access existing remote objects only and not for DDLs remotely.
I didn't understand your point "To give high privileges through a database link is never a good decision." Could you please elaborate.

Thanks and Regards,
Sangeetha.
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560107 is a reply to message #559935] Tue, 10 July 2012 10:36 Go to previous messageGo to next message
Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
As of now I am following these steps.
1. Create procedure in the remote database with create/drop user statement.
2. Create procedure in the local DB which calls the above procedure through DB link.
3. Call the local procedure in Apex application.

But, the problem is - this process is not dynamic, i.e., if there are more databases added - then I need to manually do the above steps. I am thinking of making these steps dynamic (just with botton click in Apex application).
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560108 is a reply to message #560107] Tue, 10 July 2012 10:46 Go to previous messageGo to next message
gazzag
Messages: 1116
Registered: November 2010
Location: Bedwas, UK
Senior Member
It sounds like you're treating Oracle databases like SQL Server ones. How often do you add a database?
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560109 is a reply to message #560108] Tue, 10 July 2012 10:56 Go to previous messageGo to next message
Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
Not very often. But, whenever added can't we make the above steps dynamic?
I see lots of dependencies (on the local admin who has created Apex application) in the above steps as we need to interact with multiple databases in this manner. So, I need to create the remote procedure in all databases manually.
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560110 is a reply to message #560109] Tue, 10 July 2012 11:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So, I need to create the remote procedure in all databases manually.
I suspect that you'll waste more time trying to automate than doing it manually.
For discussion sake let us assume you proceed to automate.
1) how to automagically know or determine when new DB has come into being?
2) how to automagically know or determine upon which system this new DB resides?
3) how to automagically obtain correct username & password to access remote OS?
4) how to automagically know or determine new instance SID?
5) how to automagically obtain suitably privileged schema name & password to create needed procedure?

Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560118 is a reply to message #560106] Tue, 10 July 2012 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sangeethahg wrote on Tue, 10 July 2012 17:24
Hi Michel,

As far as my knowledge goes, DB links can be used to access existing remote objects only and not for DDLs remotely


Your knowledge is not up to date.

Quote:
I didn't understand your point "To give high privileges through a database link is never a good decision." Could you please elaborate.


To be able to create a user into a remote database means you have DBA privilege on remote database means the local DBA can't manage what you do through your database link and if your database is hacked the remote is then too.

Regards
Michel

Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560298 is a reply to message #560110] Wed, 11 July 2012 22:19 Go to previous messageGo to next message
Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
> For discussion sake let us assume you proceed to automate.
> 1) how to automagically know or determine when new DB has come into being?
-- The user will be able to enter the new DB name through the Apex application. Which can be stored in a table and can be used to create DB links through a local procedure.
> 2) how to automagically know or determine upon which system this new DB resides?
-- I think, this is not required.
> 3) how to automagically obtain correct username & password to access remote OS?
-- I don't think, we need OS information for this. Do we? If so what for?
> 4) how to automagically know or determine new instance SID?
-- In our case the DB name (entered by the user) itself is the SID.
> 5) how to automagically obtain suitably privileged schema name & password to create needed procedure?
-- Let us consider that I have sys access to the remote DB.

With the above given information, is there any way I can automate the process?
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560300 is a reply to message #560298] Wed, 11 July 2012 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>With the above given information, is there any way I can automate the process?
I doubt you could do it.
I know I could do it it, but I would be wise enough to not waste my time attempting to do so.

Take careful notes when you successfully manually complete the necessary tasks for existing DBs.
Then just write some wrapper code around all the SQL that you manually issued.
Re: create/dro​p user/schem​a in another Oracle database through Oracle Apex Ver:3.2.1.​00.10 [message #560768 is a reply to message #560300] Mon, 16 July 2012 22:26 Go to previous message
Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
Thank you all for all your valuable inputs on this topic.
Previous Topic: “ORA-01102 Cannot mount database in Exclusive mode”
Next Topic: enable row movement
Goto Forum:
  


Current Time: Tue Mar 19 04:13:27 CDT 2024