Monday, February 18, 2008

Grants, three ways to obtain it

1) If you are in Oracle <= 9i you can use the Unix commands strings and grep to filter the metadata existing in the export file header (interesting if you have a huge one):

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:

Josep Arqués said...

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.

Toni Blanco said...

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