Home » RDBMS Server » Server Administration » Get ALL user definitions (10g, sles 9)
| Get ALL user definitions [message #405055] |
Tue, 26 May 2009 03:38  |
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 #405079 is a reply to message #405055] |
Tue, 26 May 2009 04:38  |
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 |
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 #405124 is a reply to message #405055] |
Tue, 26 May 2009 07:37 |
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;
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 22:40:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
