strings export.dmp | grep -i ' TO
or
strings export.dmp|grep –i GRANT |sort > grants.sql
2) Classical dictionary views querys:
-- USUERS ASSIGNEDS TO EVERY ROLE --
SELECT GRANTED_ROLE as rol, GRANTEE as User
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
order by granted_role;
-- ROLES OF EVERY USER --
SELECT GRANTEE as Usuario, GRANTED_ROLE as rol
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
order by grantee;
-- PRIVILEGES OF EVERY USER --
select * from dba_sys_privs
where grantee in (select username from dba_users)
order by grantee;
-- OBJECT PRIVILEGES OF EVERY USER --
select substr(grantee, 1,15) usuario,
substr(table_name,1,35) tabla,
substr(privilege,1,20) privilegio
from dba_tab_privs
where GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
ORDER BY table_name;
-- DBA USERS --
select grantee from dba_role_privs
where granted_role ='DBA';
3) The most powerful an flexible: query the dictionary.
select 'grant '||m$.name||' on '||uo$.name||'.'||o$.name||' to '||ue$.name
from
sys.objauth$ t$,
sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$,
sys.user$ ue$,
sys.user$ uo$
where
o$.obj# = t$.obj#
and t$.privilege# = m$.privilege
and t$.col# is null
and t$.grantor# = ur$.user#
and t$.grantee# = ue$.user#
and o$.owner# = uo$.user#
and t$.grantor# != 0
-- (to filter by role name:) and ue$.name='ROLE';
2 comments:
Bon dia company
estic intentant recrear el repositori del EM d'una BBDD, doncs la dbconsole ha deixat de funcionar.
Ho faig amb la comanda:
emca -config dbcontrol db -repos recreate
però el procés es queda clavat en un punt, no hi ha manera de seguir.
Hi ha algun mètode de fer neteja total fora del "emca" i poder crear el repositori "from scratch"?
Gràcies, i gran blog.
Apa, mira aquesta nota:
Overview Of The EMCA Commands Available for DB Control 10.2 Installations
330130.1
La comanda que fas servir és la que jo tinc apuntada, hauria de funcionar. Si de cas atura-ho tot.
Sort,
Toni
Post a Comment