Dropping users and roles
Bob, May 22, 2007 - 11:11 am UTC
Hi Tom,
I have read the Oracle 9i SQL documentation about dropping users - using the cascade option. I am in the process of creating a repository for Oracle Designer in which I have created a "designer" user. The "designer" user has to have CREATE ROLE Privilege. In the Oracle 9i SQL documentation - it states that roles will not be dropped after issuing DROP USER CASCADE.
How do I tell which roles were created in the first place by the tool - I could look in DBA_ROLES and drop them - but is there a table which ties it back to the user, AFTER it has been dropped.
May 22, 2007 - 7:02 pm UTC
roles are globally owned, by sys, not by "anything"
they are not tied to a user, a schema, a tool. much like public synonyms, rollback segments and some other things.
eg: a user mary is created by bob. drop user bob cascade will NOT drop mary as well.
a role mary is created by bob......
Dropping users
Bob, May 23, 2007 - 4:45 am UTC
Thanks Tom. OK it looks like I have to see what roles currently exist. Run the repository install and see what new roles have been created and note them.
drop own objects
A reader, May 23, 2007 - 4:46 am UTC
Sorry to ask one question not exactly related to this article, I have one requirement that one user shouldn't have any privileges to create or drop any objects, but I also need this user's schema has its own objects like table,index,... etc. I removed all the priveleges except the privilege of "create session" and "unlimited resource", so I can create its objects by another users like system or esle(for example: create table test1.t1 (a number);), but I just cann't block the user drop his own objects! How can I get it? Thanks in advance!
May 23, 2007 - 7:06 am UTC
well, this always surprises me - that it comes up over and over....
the right way: create the objects in some other schema, grant the right privileges on those objects to this schema, there is NO NEED for this schema to own *anything*. Use private synonyms or alter session set current_schema if you don't want to have the username.
You could use a database ddl trigger to fail a 'drop' on this schema, but that would be a convoluted "magic happens here" method, one I would not necessarily suggest.
wes, May 24, 2007 - 12:30 am UTC
Thanks for Tom's quick response, actualy I had thought about the way you proposed, but I have some problems with this way. If I create the objects in some other schema, I will have to maintain a lot of synonyms over and over due to applications's new deployments again and again, it would be annoyed. You can say why don't you create an procedure and make a regular job to do this things, yes, It can help, but I had another big problem, how can I get access to some tables which are located in other databases by some private database links, It seems that synonyms don't work at this situation.
May 26, 2007 - 10:41 am UTC
you don't need synonyms
alter session set current_schema = foobar;
select * from t
will be read as select * from foobar.t;
synonyms do work in that other situation, you'd have to be more clear about what you perceive was wrong with them.
Drop and recreate user - possible script
Paul, May 27, 2007 - 3:25 am UTC
Hello Tom,
If I want to drop and recreate a user (typically for import purposes) I use the following script. Do I cover all relevant user-charactistics in here?
DUPLUSER.SQL
REM Call @DUPLUSER SCOTT
store set plusenv.tmp REPLACE
set head off
set trimout on
set trimspool on
set feed off
set term off
set lines 200
set verify off
set pages 1000
define me = &1
spool DuplUser_&&me..sql
REM ### create user
select 'create user ' || upper('&&me') ||
' identified by values ' || chr(39) || password || chr(39) ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ' account unlock; '
from dba_users
where username = upper('&&me');
REM ### Tablespace quotas
select 'alter user ' || upper('&&me') || ' quota ' ||
decode(max_bytes,-1,'unlimited', max_bytes/1024 || 'K') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = upper('&&me');
REM ### System privileges
select 'grant ' || privilege || ' to ' ||
upper('&&me') || decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from dba_sys_privs
where grantee=upper('&&me');
REM ### Tab privileges
select 'grant ' || privilege || ' on ' || owner ||'.'|| table_name ||
' to ' || upper('&&me') ||
decode(grantable,'YES', ' WITH GRANT OPTION;', ';')
from dba_tab_privs
where grantee=upper('&&me');
REM ### Role privileges
select 'grant ' || granted_role || ' to ' || upper('&&me') ||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee=upper('&&me');
spool off
@plusenv.tmp
host del plusenv.tmp
May 27, 2007 - 9:18 am UTC
seems easier to drop their schema objects and keep them.
doesn't it? that way, you won't miss anything - especially into the future when other things are added.
I guess it's more a matter of taste...
Paul, May 27, 2007 - 6:04 pm UTC
I guess it's more a matter of taste, drop user or clean all objects. I use it like this:
@DuplUser SCOTT --> to create my script;
Drop user Scott cascade;
@DuplUser_SCOTT --> this script re-creates the user
My question was more: is my list of grants complete if I use the script "DuplUser" I mentioned in my post above? I use it -a lot actually- and never found problems with it. It would be a pitty if I found out the hard way that I missed something... :-(
May 30, 2007 - 9:23 am UTC
It could be missing stuff, that is why I would suggest the other route.
There are hard ways to do things and there are easy ways.
To me, the easy way to "clean out a schema, but preserve all grants, roles, whatever outside the schema" is to clean out the schema.
Not to drop and recreate it - hoping we got everything.
I just did not expend the brain cells necessary to verify that in 10.2.0.3 and probably before - you got them all - because it could change in 10.2.0.4 for example.
You are missing many things on the user - see the create user command. I use OS authenticate users many times, you would not be able to do that. You miss password expiration, profiles. Maybe you don't care, maybe you will sometime in the future.
Dropping a user taking lot of time
shailu, July 17, 2007 - 12:18 pm UTC
Hi Tom,
I have a user as sysadm and it contains around 16,000 objects.Size of this user is around 6GB. We are using 8.1.6 in my company.
OS detail
$ uname -a
SunOS nyuxfatst01 5.8 Generic_117350-46 sun4u sparc SUNW,Sun-Fire-V440
This particular user is taking around 2 hrs to get dropped.
I am using following command to drop it..
drop user sysadm cascade;
Can you please give me your thoughts on this
July 17, 2007 - 1:12 pm UTC
sounds about right to me.
given that dropping a schema is a once in a lifetime thing (eg: you do not drop them in general), optimizing a schema drop to be super fast is not something really being researched....
dropping user taking time
shailu, July 17, 2007 - 2:54 pm UTC
Hello Tom,
Thank you very much for the quick response. I have found dropped all the objects first for that particular user.After that i dropped the user and it was very fast ....only 15 mins
does it mean dropping objects first made this process faster.
Thanks
July 17, 2007 - 4:18 pm UTC
it depends, before segments can be dropped they have to be checkpointed - could be that you did it once when the cache was full of stuff that needed to be written - later, that was not true and it was faster. could be "lots of things"
bottom line - why would you do this more than once.
Checkpointing before drop
Michel Cadot, July 18, 2007 - 12:17 am UTC
before segments can be dropped they have to be checkpointed
This one (and the same for truncate) always surprised me.
Why cache blocks are not just discarded (flagged as free)?
Why must they be written on disk?
What can be the reason?
Regards
Michel
July 18, 2007 - 10:09 am UTC
recovery purposes - in the event of a failure.