Home » RDBMS Server » Security » How to rename a user
How to rename a user [message #463073] Tue, 29 June 2010 11:17 Go to next message
Riyasbasha
Messages: 25
Registered: March 2010
Junior Member
Hi DBA'S

Can any one help me how to rename a user in oracle.
Re: How to rename a user [message #463074 is a reply to message #463073] Tue, 29 June 2010 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
>Can any one help me how to rename a user in oracle.
can NOT be done
Re: How to rename a user [message #463076 is a reply to message #463074] Tue, 29 June 2010 11:28 Go to previous messageGo to next message
cookiemonster
Messages: 13866
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can of course create a new user, export the contents of the old users schema, import that to the new user and then drop the old user.
Re: How to rename a user [message #463084 is a reply to message #463076] Tue, 29 June 2010 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66936
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about the privileges of the user, synonyms on its objects, privileges on its objects...

Regards
Michel
Re: How to rename a user [message #463085 is a reply to message #463084] Tue, 29 June 2010 11:52 Go to previous messageGo to next message
cookiemonster
Messages: 13866
Registered: September 2008
Location: Rainy Manchester
Senior Member
OK I over-simplified.
Re: How to rename a user [message #463086 is a reply to message #463073] Tue, 29 June 2010 11:57 Go to previous messageGo to next message
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
Member
dbms_metadata
-get_ddl
-get_granted_ddl

takes some effort, but you can recreate the user with all permission and all objects.

[Updated on: Tue, 29 June 2010 11:58]

Report message to a moderator

Re: How to rename a user [message #463088 is a reply to message #463086] Tue, 29 June 2010 12:01 Go to previous messageGo to next message
cookiemonster
Messages: 13866
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does that cover synonyms?

Though to be honest you should have scripts for all this stuff in source control - in which case modifying said scripts for the new user shouldn't be all that difficult.
If you don't have the scripts in source control then maybe you should create them.
Re: How to rename a user [message #463093 is a reply to message #463088] Tue, 29 June 2010 12:10 Go to previous messageGo to next message
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
Member
Yep.

create table t1 as (select user c1 from dual);
Table created.

create synonym s1 for t1;
Synonym created.

select * from t1;
T1
----------
TESTUSER

select * from s1;
S1
----------
TESTUSER

select dbms_metadata.get_ddl('SYNONYM','S1') myDDL from dual;
DDL
----------------------------------------------------
CREATE OR REPLACE SYNONYM "TESTUSER"."S1" FOR "TESTUSER"."T1"

Re: How to rename a user [message #463095 is a reply to message #463093] Tue, 29 June 2010 12:13 Go to previous messageGo to next message
cookiemonster
Messages: 13866
Registered: September 2008
Location: Rainy Manchester
Senior Member
Normally synonyms belong to a different user to the table so that doesn't prove anything.
Re: How to rename a user [message #463097 is a reply to message #463095] Tue, 29 June 2010 12:18 Go to previous messageGo to next message
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
Member
you can run the query as sys and specify the user.

select dbms_metadata.get_ddl('SYNONYM','S1','TESTUSER') myDDL from dual;


If you have to, you can query the data dictionary to find the synonym owner and object owner.

Like I said, it takes some effort, but it can be done.

[Updated on: Tue, 29 June 2010 12:24]

Report message to a moderator

Re: How to rename a user [message #463105 is a reply to message #463095] Tue, 29 June 2010 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66936
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Tue, 29 June 2010 19:13
Normally synonyms belong to a different user to the table so that doesn't prove anything.

Without speaking about PUBLIC synonym.

And so on... Yes, some effort...

Regards
Michel

Re: How to rename a user [message #463108 is a reply to message #463105] Tue, 29 June 2010 12:51 Go to previous messageGo to next message
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
Member
to beat a dead horse:
select 
 'select dbms_metadata.get_ddl('SYNONYM','','''||SYNONYM_NAME||''','''||owner||''') from dual;' GETMYDDL 
from dba_synonyms where table_owner='TESTUSER';



Will create select statements to get ALL synonyms, including public, for supplied user.

Where there is a will, there is a way. Even if it goes beyond the worth of the level of effort.
Re: How to rename a user [message #464738 is a reply to message #463108] Fri, 09 July 2010 02:34 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am stabbing in the dark here. That said, I did a simple GOOGLE and found these links on how to "clone a schema".

Burleson (not everyone likes this guy but you can't google Oracle stuff without hitting one of his pages) Note that he claims not to have ever used the script so who knows if it actually works, and also note that the date is 2007 so it might be too old anyway depending upon your version. Be careful with this one. Heck be carefull with all of these.

one of our own

A little commentary on the REMAP_SCHEMA option of IMPDB Note in particular the "might not do everything right" commentary; like the "won't find and fix references to embedded schema names in code". Jeesh... what good is it then I ask you. The one thing we really need and it won't do it for us. Then again, anyone who is hard coding schema names in their code deserves these kinds of problems. There are few reasons to hardcode a schema name in plsql and scripts. I have only really needed it for one thing in my career, for building historical aware applications. I use schema management as one of the linch-pins of my historical implementation strategy so when inside an app I want to view a current and historical perspective at the same time, I hard code the historical schema name. However, I have never used REMAP_SCHEMA in an effort to clone a schema so don't call me, I'll call you.

Cloning a schema, aka copying a user completely and catching everything was never easy.

Kevin

[Updated on: Fri, 09 July 2010 02:49]

Report message to a moderator

Previous Topic: protect from unauthorise user
Next Topic: Data Security During network
Goto Forum:
  


Current Time: Tue Apr 07 12:42:28 CDT 2020