Home » RDBMS Server » Server Administration » Get ALL user definitions (10g, sles 9)
Get ALL user definitions [message #405055] Tue, 26 May 2009 03:38 Go to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Is there an easy way to get the full DDL for all existing users on a database (including their passwords) ?

I need to restore a production db over a test database, but need to keep the Test users and their privileges and passwords

.... so basically do the restore, and then restore all the users as they were before the restore.


Dirk
Re: Get ALL user definitions [message #405061 is a reply to message #405055] Tue, 26 May 2009 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only easy way is export/import.

Regards
Michel
Re: Get ALL user definitions [message #405079 is a reply to message #405055] Tue, 26 May 2009 04:38 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Thank you Michel.

Is it possible to restore the database, and then to drop the users but not the objects belonging to them, and then to recreate the previous (old) users (as they were on the test database before the restore) ?

I am just worried about dependancies (that I might not be able to drop users, or create them as they were, due to dependancies caused by differences between the production & test databases)
Re: Get ALL user definitions [message #405086 is a reply to message #405055] Tue, 26 May 2009 05:33 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
In Enterprise Manager it is so easy. Users -> Generate DDL ... and you get the full DDL with privileges for a user.

So basically I am looking for the SQL behind this option, so that I can generate my own DDL for all users.

Is there an export option that will give me the user DDL's only (with privileges) ?
I tried a full export (without rows), but the output will take to long to manipulate to extract the users only.

Re: Get ALL user definitions [message #405092 is a reply to message #405079] Tue, 26 May 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to [...] to drop the users but not the objects belonging to them

No. If you drop a user (actually a schema) you drop its objects.

Quote:
Is there an export option that will give me the user DDL's only (with privileges) ?

No.

Quote:
In Enterprise Manager it is so easy. Users -> Generate DDL ... and you get the full DDL with privileges for a user.

You can do the same thing query Oracle catalog (dba_users, dba_roles, dba%privs and so on).

Regards
Michel
Re: Get ALL user definitions [message #405097 is a reply to message #405055] Tue, 26 May 2009 06:04 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Thank you Michel
Re: Get ALL user definitions [message #405124 is a reply to message #405055] Tue, 26 May 2009 07:37 Go to previous message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Got it. I am using the following queries to generate the sql script for me:


spool test_users_backup.sql

set linesize 120

--1. user ddl
select 'create user '||username||' identified by values '''||password||''' default tablespace '||default_Tablespace||';'
from dba_users
WHERE USERNAME NOT IN ('MGMT_VIEW','SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','WMSYS','ORDSYS','EXFSYS','XDB','DMSYS','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUG INS','MDSYS','CTXSYS','ANONYMOUS','MDDATA','TSMSYS','DIP','ORACLE_OCM','SCOTT');

--2. sys_privs
select 'grant '||privilege||' to '||grantee||';'
from dba_sys_privs
WHERE grantee NOT IN ('MGMT_VIEW','SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','WMSYS','ORDSYS','EXFSYS','XDB','DMSYS','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUG INS','MDSYS','CTXSYS','ANONYMOUS','MDDATA','TSMSYS','DIP','ORACLE_OCM','SCOTT');

--3. grants on roles
select 'grant '||privilege||' to '||grantee||';'
from dba_sys_privs
where grantee in (select role from dba_roles where role NOT IN ('CONNECT',
'RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE','EXP_FULL_DATABASE','IMP_FULL_DATABASE',
'RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS',
'LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE',
'GLOBAL_AQ_USER_ROLE','SCHEDULER_ADMIN','HS_ADMIN_ROLE',
'OEM_ADVISOR','OEM_MONITOR','WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV',
'JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT','JAVA_ADMIN','JAVA_DEPLOY',
'XDBADMIN','CTXAPP','AUTHENTICATEDUSER','XDBWEBSERVICES',
'OLAPI_TRACE_USER','OLAP_DBA','CWM_USER','OLAP_USER','MGMT_USER'))
union
select 'grant ' ||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs
where grantee in (select role from dba_roles
where role NOT IN ('CONNECT',
'RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE','EXP_FULL_DATABASE','IMP_FULL_DATABASE',
'RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS',
'LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE',
'GLOBAL_AQ_USER_ROLE','SCHEDULER_ADMIN','HS_ADMIN_ROLE',
'OEM_ADVISOR','OEM_MONITOR','WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV',
'JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT','JAVA_ADMIN','JAVA_DEPLOY',
'XDBADMIN','CTXAPP','AUTHENTICATEDUSER','XDBWEBSERVICES',
'OLAPI_TRACE_USER','OLAP_DBA','CWM_USER','OLAP_USER','MGMT_USER'));

--4. role privs
select 'grant '||granted_role||' to '||grantee||';'
from dba_role_privs
WHERE grantee NOT IN ('MGMT_VIEW','SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','WMSYS','ORDSYS','EXFSYS','XDB','DMSYS','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUG INS','MDSYS','CTXSYS','ANONYMOUS','MDDATA','TSMSYS','DIP','ORACLE_OCM','SCOTT');

--5. table_privs
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs
WHERE grantee NOT IN ('SYSMAN','OUTLN','WMSYS','ORDSYS','EXFSYS','XDB','DMSYS','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','MDSYS','CTXSYS','ANONYMOUS','M DDATA','TSMSYS','DIP','ORACLE_OCM','SCOTT');

--6. Profiles
select 'alter user '||username||' profile '||profile||';'
from dba_users
WHERE USERNAME NOT IN ('MGMT_VIEW','SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','WMSYS','ORDSYS','EXFSYS','XDB','DMSYS','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUG INS','MDSYS','CTXSYS','ANONYMOUS','MDDATA','TSMSYS','DIP','ORACLE_OCM','SCOTT');

quit;
Previous Topic: Data file modification date
Next Topic: How to automatically lock account after 10 minutes
Goto Forum:
  


Current Time: Wed Dec 07 18:41:31 CST 2016

Total time taken to generate the page: 0.10863 seconds