DBA_ROLES : définition bizarre dans DBA_VIEWS
Bonjour tout le monde,
Pouvez-vous m'expliquer pourquoi dans la définition de DBA_ROLES on a deux decode sur la colonne password et pourquoi le select * sur cette vue renvoie les colonnes "PASSWORD" et "AUTHENTICAT" au lieu de renvoyer "PASSWORD" deux fois?
Définition de DBA_ROLES
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| select text from dba_views where view_name = 'DBA_ROLES';
TEXT
--------------------------------------------------------------------------------
select name, decode(password, null, 'NO',
'EXTERNAL', 'EXTERNAL',
'GLOBAL', 'GLOBAL',
'YES'),
decode(password, null, 'NONE',
'EXTERNAL', 'EXTERNAL',
'GLOBAL', 'GLOBAL',
'APPLICATION', 'APPLICATION',
'PASSWORD')
from user$
where type# = 0 and name not in ('PUBLIC', '_NEXT_USER') |
Et voilà le résultat du SELECT. On voit bien que les données sont différentes entre les colonnes "PASSWORD" et "AUTHENTICAT" mais dans ce cas là d'où viennent ces données pour "AUTHENTICAT"?
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
| select * from dba_roles order by role;
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
ACC_CONSULT NO NONE
ACC_USER NO NONE
ADM_PARALLEL_EXECUTE_TASK NO NONE
APPLI_DEVELOPER NO NONE
APPLI_MANAGER NO NONE
APPLI_USER NO NONE
AQ_ADMINISTRATOR_ROLE NO NONE
AQ_USER_ROLE NO NONE
AUTHENTICATEDUSER NO NONE
CONNECT NO NONE
DATAPUMP_EXP_FULL_DATABASE NO NONE
DATAPUMP_IMP_FULL_DATABASE NO NONE
DBA NO NONE
DBA_LEVEL1 NO NONE
DBA_LEVEL2 YES APPLICATION
DBA_LEVEL3 YES APPLICATION
DBA_PERF YES APPLICATION
DBA_RELEASE YES APPLICATION
DBFS_ROLE NO NONE
DELETE_CATALOG_ROLE NO NONE
EJBCLIENT NO NONE
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
EXECUTE_CATALOG_ROLE NO NONE
EXP_FULL_DATABASE NO NONE
GATHER_SYSTEM_STATISTICS NO NONE
GLOBAL_AQ_USER_ROLE GLOBAL GLOBAL
HS_ADMIN_EXECUTE_ROLE NO NONE
HS_ADMIN_ROLE NO NONE
HS_ADMIN_SELECT_ROLE NO NONE
IMP_FULL_DATABASE NO NONE
JAVA_ADMIN NO NONE
JAVADEBUGPRIV NO NONE
JAVA_DEPLOY NO NONE
JAVAIDPRIV NO NONE
JAVASYSPRIV NO NONE
JAVAUSERPRIV NO NONE
JMXSERVER NO NONE
LOGSTDBY_ADMINISTRATOR NO NONE
OEM_ADVISOR NO NONE
OEM_MONITOR NO NONE
PLUSTRACE NO NONE
R_BRIN NO NONE
R_CONSULT_BRIN NO NONE
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
R_CONSULT_SITI_GSAREF NO NONE
R_CONSULT_SITI_PTF NO NONE
R_CONSULT_SITI_SIR NO NONE
RECOVERY_CATALOG_OWNER NO NONE
REF_MOD_TOUT NO NONE
REF_SEL_TOUT NO NONE
RESOURCE NO NONE
R_FWK NO NONE
R_NOY_LVA NO NONE
R_SITI_PTF NO NONE
SCHEDULER_ADMIN NO NONE
SELECT_CATALOG_ROLE NO NONE
SNMPAGENT NO NONE
SYNCHRO_BDR NO NONE
VM_BDR NO NONE
WM_ADMIN_ROLE NO NONE
WM_POLLING NO NONE
XDBADMIN NO NONE
XDB_SET_INVOKER NO NONE
XDB_WEBSERVICES NO NONE
XDB_WEBSERVICES_OVER_HTTP NO NONE
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
XDB_WEBSERVICES_WITH_PUBLIC NO NONE
64 rows selected. |
J'ai même vérifié avec DBMS_METADATA au cas où la définition de BA_ROLES serait différente (j'avais eu le problème avec d'autres vues dernièrement). On a bien la même définition de la vue.
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| SELECT DBMS_METADATA.GET_DDL('VIEW','DBA_ROLES','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('VIEW','DBA_ROLES','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_ROLES" ("ROLE", "PASSWORD_REQUIRED", "
AUTHENTICATION_TYPE") AS
select name, decode(password, null, 'NO',
'EXTERNAL', 'EXTERNAL',
'GLOBAL', 'GLOBAL',
'YES'),
decode(password, null, 'NONE',
'EXTERNAL', 'EXTERNAL',
'GLOBAL', 'GLOBAL',
'APPLICATION', 'APPLICATION',
'PASSWORD')
from user$
where type# = 0 and name not in ('PUBLIC', '_NEXT_USER') |
Si je fais un desc de user$, il y a une seule colonne password.
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| desc SYS.user$;
TABLE SYS.user$
Name Null? Type
----------------------------------------- -------- ----------------------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(30)
DATATS# NOT NULL NUMBER
TEMPTS# NOT NULL NUMBER
CTIME NOT NULL DATE
PTIME DATE
EXPTIME DATE
LTIME DATE
RESOURCE$ NOT NULL NUMBER
AUDIT$ VARCHAR2(38)
DEFROLE NOT NULL NUMBER
DEFGRP# NUMBER
DEFGRP_SEQ# NUMBER
ASTATUS NOT NULL NUMBER
LCOUNT NOT NULL NUMBER
DEFSCHCLASS VARCHAR2(30)
EXT_USERNAME VARCHAR2(4000)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE |
Si on regarde le contenu du champ password, voilà le résultat.
Code:
1 2 3 4 5 6 7 8 9
| select password, count(*) from SYS.user$ having count(*) > 1 group by password order by count(*) desc;
PASSWORD COUNT(*)
------------------------------ ----------
61
GLOBAL 6
NO_PASSWORD 5
APPLICATION 4
4 rows selected. |