ORACLE PRIVILEGES

uprawnienia:
SELECT * FROM user_role_privs; -- role aktualnego usera
SELECT * FROM user_sys_privs; -- uprawnienia systemowe aktualnego usera
SELECT * FROM role_sys_privs order by 1; -- uprawnienia systemowe powiązane z rolami
SELECT * FROM session_privs;
SELECT * FROM session_roles;
SELECT * FROM dba_roles; -- wypisanie ról
SELECT * FROM dba_role_privs; -- powiązanie ról z userami
SELECT * FROM dba_sys_privs; -- powiązanie uprawnienień systemowych z userami
SELECT b.grantee,a.role,a.privilege FROM role_sys_privs a,dba_role_privs b WHERE b.grantee=upper('&user') AND a.role=b.granted_role UNION ALL SELECT c.grantee,null,c.privilege FROM dba_sys_privs c WHERE c.grantee=upper('&user') ORDER BY 2,3;
SELECT * FROM dba_tab_privs; -- wszystkie prawa do obiektów w bazie także do procedur,funkcji,pakietów
SELECT * FROM role_tab_privs WHERE role=''; --prawa do obiektów dla roli
katalogi:
SELECT * FROM dba_tab_privs WHERE table_name = '[katalog]';

przykłady:

SELECT distinct privilege FROM dba_sys_privs where privilege like '%[text]%';
SELECT * FROM dba_role_privs WHERE grantee IN ('USER1','USER2') order by 1,2; #role przypisane do usera
SELECT * FROM role_sys_privs where role = '[role_name]' #uprawnienia przypisane do roli
użytkownicy źródła:
set long 2000
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','SYSTEM') FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SYSTEM') FROM DUAL;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','SYSTEM') FROM DUAL;
uwaga: jak nie będzie uprawnień to wywali błąd ORA-31608

case study:

---

-- create new role, add priviliges to it, grant role to user:

create role new_role;

begin
  for x in (SELECT object_name FROM ALL_OBJECTS WHERE object_type = 'TABLE' and owner = 'OWNER_NAME') loop
    execute immediate 'GRANT select,insert,update,delete on ea_test.'|| x.object_name || ' to NEW_ROLE';
  end loop;
end;

grant NEW_ROLE to username

-- check:

SELECT grantee,granted_role FROM dba_role_privs WHERE granted_role = upper('new_role');
SELECT table_name,column_name,privilege,grantable FROM role_tab_privs WHERE role = upper('new_role') order by table_name;