Home » RDBMS Server » Server Administration » Recovery after DROP USER SYSTEM?
Recovery after DROP USER SYSTEM? [message #56179] Tue, 11 March 2003 04:26 Go to next message
Uwe Küchler
Messages: 3
Registered: March 2003
Junior Member
Hi y'all,

it was a quiet morning today, so I've decided to destroy something beautiful... ;-)
In the FAQ on this site ( http://www.orafaq.org/faqdbase.htm#DEFUSERS ) I read that the user SYSTEM cannot be dropped. This is definitely wrong. "DROP USER system CASCADE;" worked fine on my little 8i production database. Anyone knowing a new job opportunity for me? ;-)))

Seriously, though: If this happens, is there any way of recovering the database with a script (not from a full backup, of course)?

Regards, Uwe.
Re: Recovery after DROP USER SYSTEM? [message #56181 is a reply to message #56179] Tue, 11 March 2003 04:49 Go to previous messageGo to next message
psmyth
Messages: 81
Registered: October 2002
Member
i haven't tried it myself,but I suspect you're in a 'chicken and egg' scenario... you need 'system' to recover the database (from a script/export etc), but you don't have SYSTEM... I'd delegate this one ;-)
Re: Recovery after DROP USER SYSTEM? [message #56183 is a reply to message #56179] Tue, 11 March 2003 06:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Actually,
it is supposed to mean, 
.........DO NOT DROP SYSTEM USER AT ANY SITUATION.......

--1. connect to any other database that is good.
--2. connect as sys
--3. run this sql script as shown
--   it will create a user like an existing user.
--   spool the output to an sql file
--   THEN EDIT THE SQL FILE, TO REMOVE THE UNWANTED ENTRIES.
--   OR JUST USE THE FOLLOWING ENTRIES OF SPOOLED FILE.
--   Better to run this script agains the same version of the database where the user is dropped
tricore@beech_prod > @CR_USER_LIKE
Enter user to model new user to: SYSTEM
Enter new user name: SYSTEM
Enter new user's password: MANAGER
create user SYSTEM identified by MANAGER default tablespace SYSTEM temporary tablespace PRODTEMP pro
grant CONNECT to SYSTEM;
grant SELECT ANY TABLE to SYSTEM;
grant ALTER on SYS.INCEXP to SYSTEM;
grant DELETE on SYS.INCEXP to SYSTEM;
grant INDEX on SYS.INCEXP to SYSTEM;
grant INSERT on SYS.INCEXP to SYSTEM;
grant SELECT on SYS.INCEXP to SYSTEM;
grant UPDATE on SYS.INCEXP to SYSTEM;
grant REFERENCES on SYS.INCEXP to SYSTEM;
grant ALTER on SYS.INCVID to SYSTEM;
grant DELETE on SYS.INCVID to SYSTEM;
grant INDEX on SYS.INCVID to SYSTEM;
grant INSERT on SYS.INCVID to SYSTEM;
grant SELECT on SYS.INCVID to SYSTEM;
grant UPDATE on SYS.INCVID to SYSTEM;
grant REFERENCES on SYS.INCVID to SYSTEM;
grant ALTER on SYS.INCFIL to SYSTEM;
grant DELETE on SYS.INCFIL to SYSTEM;
grant INDEX on SYS.INCFIL to SYSTEM;
grant INSERT on SYS.INCFIL to SYSTEM;
grant SELECT on SYS.INCFIL to SYSTEM;
grant UPDATE on SYS.INCFIL to SYSTEM;
grant REFERENCES on SYS.INCFIL to SYSTEM;
grant EXECUTE on SYS.AQ$_AGENT to SYSTEM;
grant EXECUTE on SYS.AQ$_DEQUEUE_HISTORY to SYSTEM;
grant EXECUTE on SYS.AQ$_SUBSCRIBERS to SYSTEM;
grant EXECUTE on SYS.AQ$_RECIPIENTS to SYSTEM;
grant EXECUTE on SYS.AQ$_HISTORY to SYSTEM;
grant EXECUTE on SYS.DBMS_AQADM to SYSTEM;
grant EXECUTE on SYS.DBMS_AQ_IMPORT_INTERNAL to SYSTEM;
grant EXECUTE on SYS.DBMS_DEFER_IMPORT_INTERNAL to SYSTEM;
alter user SYSTEM default role CONNECT;
tricore@beech_prod > SPOOL OFF

-- 4. NOW CONNECT TO THE TARGET DATABASE WHERE SYSTEM USER IS DROPPED.
--    LOGIN AS SYS AND RUN THE SPOOLED SQL. IT WILL CREATE THE SYSTEM USER.
--
-- 5. This will work fine..if you have not granted any
other special privs to system user.
-- 6. The best method is to 
      take a comple backup of database
      run catalog.sql again..

----------------------------------------------------------------------

this is the script cr_user_like.sql

----------------------------------------------------------------------

C:OracleOra81BIN>cat cr_user_like.sql
set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw     prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by &&psw'||
       ' default tablespace '||default_tablespace||
       ' temporary tablespace '||temporary_tablespace||' profile '||
       profile||';'
from   sys.dba_users
where  username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from   sys.dba_role_privs
where  grantee = upper('&&oldname');

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from   sys.dba_sys_privs
where  grantee = upper('&&oldname');

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from   sys.dba_tab_privs
where  grantee = upper('&&oldname');

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
       '('||column_name||') to &&newname;'
from   sys.dba_col_privs
where  grantee = upper('&&oldname');

-- Set Default Role...
select 'alter user &&newname default role '|| granted_role ||';'
  from sys.dba_role_privs
 where grantee = upper('&&oldname')
   and default_role = 'YES';

Re: Recovery after DROP USER SYSTEM? [message #56185 is a reply to message #56183] Tue, 11 March 2003 07:13 Go to previous messageGo to next message
psmyth
Messages: 81
Registered: October 2002
Member
hey mahesh... that is soooo cool ;-)

nice one!
Re: Recovery after DROP USER SYSTEM? [message #56196 is a reply to message #56183] Wed, 12 March 2003 03:06 Go to previous message
Uwe Küchler
Messages: 3
Registered: March 2003
Junior Member
Nice one, thanks for your efforts. I suppose there's no better chance to recover than that.
Let me add just two more statements to the script:

----------------------------------------------------------------------

-- Find grants on objects to other users
-- Grants on tables/views/procedures
select 'grant '||privilege||' on '||table_name||' to '||grantee||
       decode(GRANTABLE, 'YES', ' WITH GRANT OPTION')||';'
from   sys.dba_tab_privs
where  owner = upper('&oldname');
-- Grants on columns
select 'grant '||privilege||' on '||owner||'.'||table_name||
       '('||column_name||') to '||grantee||
       decode(GRANTABLE, 'YES', ' WITH GRANT OPTION')||';'
from   sys.dba_col_privs
where  owner = upper('&&oldname');


----------------------------------------------------------------------

You could also add the grant option line to the rest of your script.

Kind regards,
Uwe
Previous Topic: ORA-01410:INVALID
Next Topic: Query Hangs Plz. Help
Goto Forum:
  


Current Time: Tue Nov 12 14:09:47 CST 2024