ORACLE SQL 10G Pour les Nuls & Zéro

Appliquer les ROLES, PRIVILEGES, SYNONYM après un IMPORT USER.

Voici un petit script qui permet, à la suite d’un import User (voir plusieurs) de pouvoir recréer l’environnement stable d’origine sur la cible (ROLES, PRIVILEGES SYSTEM, OBJET, SYNONYM, GRANT) en interrogeant les vues du dictionnaire de données Oracle.

 

Contexte:

Vous avez besoin de recharger à partir d’un DUMP des user schéma provenant de la production vers votre base en environnement test ou dev ou autres.

Si vous faites plusieurs DROP USER username CASCADE; ce script peut être utile mais aussi formateur quand à l'utilisation des Vues du dictionnaire de données Oracle.

Il s’exécute (copier/coller en 1 seul bloc) sous C:\

Vous l’exécutez sur le serveur source d’ou provient le DUMP (cas de transfert) ou sur le serveur cible AVANT de faire le DROP USER CASCADE (cas de recréation).

Assurez vous de changer les parties en rouge, SID, MDP, NOMTNS, C:\schema_constructor.txt, USER1, USER2, USER3 (USER 2 et 3 facultatif, vous supprimez si pas besoin).

 

Ce script est inoffensif (ordres SELECT), vous pouvez l’exécuter sans crainte.

Il génère un fichier texte en sortie (chemin et nom à spécifier).

Vous exécuterez ensuite très certainement la CREATION USER et ROLES en premier, puis place à votre import puis ensuite PRIVILEGES OBJETS, PRIVILEGES SYSTEM, SYNONYM 1 et 2.(Exécution script en user SYS !!).

 

 

_______________S_C_R_I_P_T_________________________________________________

set ORACLE_SID=SID

sqlplus -s "sys/MDP@NOMTNS as sysdba"

SET pagesize 0

SET linesize 500

SET verify off

 

define batch='C:\schema_constructor.txt'

spool &batch;

 

--CREATION USER au nombre de 3 dans cet exemple

Select 'CREATE USER ' || username ||CHR(13)||

' IDENTIFIED BY VALUES '||'''' || password ||'''' ||CHR(13)||

' DEFAULT TABLESPACE ' || default_tablespace ||CHR(13)||

' TEMPORARY TABLESPACE ' || temporary_tablespace ||CHR(13)||

' PROFILE ' || profile ||CHR(13)||

' ACCOUNT UNLOCK;'

FROM DBA_USERS

WHERE username in ('USER1', 'USER2', 'USER3');

 

--ROLES

select 'GRANT ' ||drp.granted_role || ' to ' ||

drp.grantee ||';' sql

from dba_role_privs DRP

where grantee in ('USER1', 'USER2', 'USER3');

 

-- PRIVILEGES OBJETS

SELECT 'GRANT ' || sys.table_privilege_map.name ||

decode(sys.table_privilege_map.name,

'READ', ' ON DIRECTORY ',

'WRITE',' ON DIRECTORY ',

' ON ') ||

lower(uowner$.name) || '.' || lower(sys.obj$.name) ||

' TO ' || lower(ugrantee$.name) ||

decode(NVL(sys.objauth$.option$,0), 1, ' WITH GRANT OPTION;',';') sql

FROM sys.objauth$ ,

sys.obj$ ,

sys.user$ ugrantor$,

sys.table_privilege_map ,

sys.user$ ugrantee$,

sys.user$ uowner$

WHERE sys.obj$.obj# = sys.objauth$.obj#

AND sys.objauth$.privilege# = sys.table_privilege_map.privilege

AND sys.objauth$.col# IS NULL

AND sys.objauth$.grantor# = ugrantor$.user#

AND sys.objauth$.grantee# = ugrantee$.user#

AND sys.obj$.owner# = uowner$.user#

AND ugrantee$.name in ('USER1', 'USER2', 'USER3')

order by ugrantee$.name, uowner$.name, sys.obj$.name;

 

 

 

--PRIVILEGES SYSTEM

select 'GRANT ' || privilege || ' to ' ||

dsp.grantee ||';' sql

from dba_sys_privs DSP

where grantee in ('USER1', 'USER2', 'USER3');

 

--SYNONYM 1

Select 'CREATE OR REPLACE SYNONYM ' ||

owner || '.' || synonym_name ||

' for ' || table_owner || '.' ||

table_name ||' '  || decode(DB_LINK,null,';','@' || db_link || ' ;' ) sql

from dba_synonyms

where owner in ('USER1', 'USER2', 'USER3')

order by synonym_name;

 

-- SYNONYM 2

SELECT 'DROP PUBLIC SYNONYM ' || SYNONYM_NAME || ' ;' sql

FROM SYS.ALL_SYNONYMS

WHERE TABLE_OWNER in ('USER1', 'USER2', 'USER3')

AND OWNER = 'PUBLIC'

UNION ALL

SELECT 'CREATE ' || DECODE(OWNER, NULL, ' ', 'PUBLIC', 'PUBLIC ', ' ')

||'SYNONYM ' || SYNONYM_NAME || ' FOR ' ||

DECODE(TABLE_OWNER,NULL,' ', TABLE_OWNER||'.') || 

TABLE_NAME||DECODE(DB_LINK, NULL, ' ', '@'||db_link) || '  ;' sql

FROM SYS.ALL_SYNONYMS

WHERE TABLE_OWNER in ('USER1', 'USER2', 'USER3')

AND OWNER = 'PUBLIC';

 

SPOOL OFF;

EXIT;
___________________________________________________________________________

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser

• Dictionnaire de données et Vues DBA-All-User-V$