• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

roles, grants & Privileges Report

I was trying to build a nice output table of the various roles, grants & Privileges
I wanted by output to look something like this:

ROLE1
      • Granted to
            o USER1
            o USER2
            o USER4
      • Roles Granted
            o ROLE2
            o ROLE3
            o ROLE4
            o ROLE5
      • Privileges
            o ALTER
                  - TABLE1
                  - TABLE2
                  - TABLE4
                  - TABLE5
                  - TABLE9
            o DELETE
                  - TABLE2
                  - TABLE4
                  - TABLE5
                  - TABLE9

ROLE2
      • Granted to
            o USER3
            o USER4
      • Roles Granted
            o ROLE5
      • Privileges
            o SELECT
                  - TABLE1
                  - TABLE2
                  - TABLE3
                  - TABLE4
                  - TABLE5


My main trick is getting the output to look some what nice (similar layout to the above example).

To select the role I have:
      SELECT DISTINCT r.granted_role
                 FROM dba_role_privs r

To get the 'Granted to' I have:
      SELECT DISTINCT r2.grantee
                 FROM dba_role_privs r2
                WHERE r2.granted_role = r.granted_role

To get the 'Roles Granted' I have:
      SELECT DISTINCT r3.granted_role
                 FROM dba_role_privs r3
                WHERE r3.grantee = r.granted_role

To get the 'Privileges' I have:
      SELECT DISTINCT r4.privilege
                 FROM dba_tab_privs r4
                WHERE r4.grantee = r.granted_role

To get the individual Privileges I have:
      SELECT DISTINCT r5.table_name
                 FROM dba_tab_privs r5
                WHERE r5.grantee = r.granted_role
                AND r5.privilege = r4.privilege

All my quires appear to be correct I just want to be able to bring it all up and indent each one so it looks nice with out repeating columns.

BTW: I am trying to run this on Oracle9i Release 9.2.0.1.0.

Does anyone have some good ways to format the out put from SQL to that I can get my output to look nice? Ideally I would like the query to display it as above.


0
jim_1234567890
Asked:
jim_1234567890
  • 3
1 Solution
 
actonwangCommented:
>>Does anyone have some good ways to format
     If you are talking about sql*plus, it doesn't support much format. The furthest you can get is to use:

set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off

you will get a table like html page.

0
 
actonwangCommented:
It would be hard to format it in just one query in sql*plus.

I would say that you can do it in one PL/SQL block. Use sth like:

for role in  (  SELECT DISTINCT granted_role  FROM dba_role_privs) loop
   -- for user granted
       --another loop then in loop you use DBMS_OUTPUT.PUT_LINE(' ...') to control your format
       
   --for roles granted

   --priviligets
end loop;
/

I belive that you will get my idea.
0
 
jim_1234567890Author Commented:
Thanks for the insight Actonwang.

I was able to build a little procedure that I still need to test.  But It is something like this:

CREATE OR REPLACE PROCEDURE oracle_roles IS
   CURSOR role_cur IS
      SELECT DISTINCT granted_role
                 FROM dba_role_privs
             ORDER BY granted_role;

   
-- To get the 'Granted to' I have:
   CURSOR grant2_cur (in_granted_role VARCHAR2) IS
      SELECT DISTINCT r2.grantee
                 FROM dba_role_privs r2
                WHERE r2.granted_role = in_granted_role;

   
-- To get the 'Roles Granted' I have:
   CURSOR rolegranted_cur (in_granted_role VARCHAR2) IS
      SELECT DISTINCT r3.granted_role
                 FROM dba_role_privs r3
                WHERE r3.grantee = in_granted_role;

   
-- To get the 'Privileges' I have:
   CURSOR privileges_cur (in_granted_role VARCHAR2) IS
      SELECT DISTINCT r4.privilege
                 FROM sys.dba_tab_privs r4
                WHERE r4.grantee = in_granted_role;

   
-- To get the individual Privileges I have:
   CURSOR iprivileges_cur (in_granted_role VARCHAR2, in_privilege VARCHAR2) IS
      SELECT DISTINCT r5.table_name
                 FROM dba_tab_privs r5
                WHERE r5.grantee = in_granted_role AND r5.privilege = in_privilege;

   role_rec          role_cur%ROWTYPE;
   grant2_rec        grant2_cur%ROWTYPE;
   rolegranted_rec   rolegranted_cur%ROWTYPE;
   privileges_rec    privileges_cur%ROWTYPE;
   iprivileges_rec   iprivileges_cur%ROWTYPE;
BEGIN
   
-- spool c:\temp\priviliges.txt;

   FOR role_rec IN role_cur LOOP
      DBMS_OUTPUT.put_line (role_rec.granted_role);
      DBMS_OUTPUT.put_line ('     '|| 'Granted to:');
      OPEN grant2_cur (role_rec.granted_role);
      FETCH grant2_cur INTO grant2_rec;

      IF grant2_cur%NOTFOUND THEN
         CLOSE grant2_cur;
         DBMS_OUTPUT.put_line ('       '|| 'None');
      ELSE
         CLOSE grant2_cur;

         FOR grant2_rec IN grant2_cur (role_rec.granted_role) LOOP
            DBMS_OUTPUT.put_line ('       '|| grant2_rec.grantee);
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line ('     '|| 'Roles Granted:');
      OPEN rolegranted_cur (role_rec.granted_role);
      FETCH rolegranted_cur INTO rolegranted_rec;

      IF rolegranted_cur%NOTFOUND THEN
         CLOSE rolegranted_cur;
         DBMS_OUTPUT.put_line ('       '|| 'None');
      ELSE
         CLOSE rolegranted_cur;

         FOR rolegranted_rec IN rolegranted_cur (role_rec.granted_role) LOOP
            DBMS_OUTPUT.put_line ('       '|| rolegranted_rec.granted_role);
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line ('     '|| 'Privileges:');
      OPEN privileges_cur (role_rec.granted_role);
      FETCH privileges_cur INTO privileges_rec;

      IF privileges_cur%NOTFOUND THEN
         CLOSE privileges_cur;
         DBMS_OUTPUT.put_line ('       '|| 'None');
      ELSE
         CLOSE privileges_cur;

         FOR privileges_rec IN privileges_cur (role_rec.granted_role) LOOP
            DBMS_OUTPUT.put_line ('       '|| privileges_rec.privilege);
            OPEN iprivileges_cur (role_rec.granted_role, privileges_rec.privilege);
            FETCH iprivileges_rec INTO iprivileges_cur;

            IF iprivileges_cur%NOTFOUND THEN
               CLOSE iprivileges_cur;
               DBMS_OUTPUT.put_line ('         '|| 'None');
            ELSE
               CLOSE iprivileges_cur;

               FOR iprivileges_rec IN iprivileges_cur (role_rec.granted_role, privileges_rec.privilege) LOOP
                  DBMS_OUTPUT.put_line ('       '|| iprivileges_rec.table_name);
               END LOOP;
            END IF;
         END LOOP;
      END IF;
   END LOOP;
END;
0
 
actonwangCommented:
yes, sth like this would work.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now