How To Use LISTAGG To Create Awesome DBA Reports

How To Use LISTAGG To Create Awesome DBA Reports

October 4, 2017 Off By dianarobete

Do you want to write some kick ass reports? Convert some rows into lines very quickly and easily?
or
Do you want to be stuck with the SQL statements you are comfortable writing?

If you want to sharpen your SQL writing skills, I’ve got something for you today.

This is a report I had to write the other day. Pretty interesting and challenging, I would say.

“Write a query that will confirm that a role has all the grants that it is supposed to have.”

Initially this sounds very easy, but in order to be fully bulletproof, some extra touches are needed. Let me give you some background on this, to fully understand the purpose of this exercise.

I created a role called HR_ADMIN which contains full privileges on the HR schema’s objects. See grants below.


SQL> create role HR_ADMIN;

SQL> grant select, insert, update, delete on hr.COUNTRIES to hr_admin;
SQL> grant select, insert, update, delete on hr.DEPARTMENTS to hr_admin;
SQL> grant select, insert, update, delete on hr.EMPLOYEES to hr_admin;
SQL> grant select, insert, update, delete on hr.JOBS to hr_admin;
SQL> grant select, insert, update, delete on hr.JOB_HISTORY to hr_admin;
SQL> grant select, insert, update, delete on hr.LOCATIONS to hr_admin;
SQL> grant select, insert, update, delete on hr.REGIONS to hr_admin;
SQL> grant select, insert, update, delete on hr.EMP_DETAILS_VIEW to hr_admin;
SQL> grant execute, debug on hr.ADD_JOB_HISTORY to hr_admin;
SQL> grant execute, debug on hr.SECURE_DML to hr_admin;
SQL> grant select on hr.DEPARTMENTS_SEQ  to hr_admin;
SQL> grant select on hr.EMPLOYEES_SEQ to hr_admin;
SQL> grant select on hr.LOCATIONS_SEQ to hr_admin;

Question is: “How can I confirm that indeed I have not missed any grants especially if I am working with 100+ objects?”

One way would be to get all the objects HR owns (tables/views/procedure/functions), and a list of the objects listed in DBA_TAB_PRIVS granted to HR_ADMIN, and then do a diff on them (MINUS).
But this approach doesn’t solve my problem, you know why? What if I missed granting an update privilege on a table. I granted all the other permissions, except the update. The above solution would not catch that.

Another option would be to pivot the rows into columns, and have an output as below. The problem with this is that I still need to manually review the report, and check it for omissions.
In the example below is pretty easy to see if something got missed, but what if I am dealing with 100s of objects?


GRANTEE    OBJECT_TYPE		   OBJECT					      S I U D E D
---------- ----------------------- -------------------------------------------------- - - - - - -
HR_ADMIN   PROCEDURE		   HR.ADD_JOB_HISTORY					      X X
HR_ADMIN   PROCEDURE		   HR.SECURE_DML					      X X
HR_ADMIN   SEQUENCE		   HR.DEPARTMENTS_SEQ				      X
HR_ADMIN   SEQUENCE		   HR.EMPLOYEES_SEQ				      X
HR_ADMIN   SEQUENCE		   HR.LOCATIONS_SEQ				      X
HR_ADMIN   TABLE		   HR.COUNTRIES 				      X X X X
HR_ADMIN   TABLE		   HR.DEPARTMENTS				      X X X X
HR_ADMIN   TABLE		   HR.EMPLOYEES 				      X X X X
HR_ADMIN   TABLE		   HR.JOBS					      X X X X
HR_ADMIN   TABLE		   HR.JOB_HISTORY				      X X X X
HR_ADMIN   TABLE		   HR.LOCATIONS 				      X X X X
HR_ADMIN   TABLE		   HR.REGIONS					      X X X X
HR_ADMIN   VIEW 		   HR.EMP_DETAILS_VIEW				      X X X X

What I really want is a report that tells me if I missed something, or confirms that all grants are good.

In my quest to find the solution, I came across a very cool analytic function called LISTAGG.

Listagg transforms rows into a column, by aggregating strings. What do I mean by that?

Let’s say I want to transform this output:

GRANTEE   TABLE_NAME    PRIVILEGE
--------- ------------  --------------
HR_ADMIN  HR.COUNTRIES  SELECT
HR_ADMIN  HR.COUNTRIES  INSERT 
HR_ADMIN  HR.COUNTRIES  UPDATE
HR_ADMIN  HR.COUNTRIES  DELETE

into this


GRANTEE   TABLE_NAME    PRIVILEGE
--------- ------------  -----------------------------
HR_ADMIN  HR.COUNTRIES  DELETE,INSERT,SELECT,UPDATE

Listagg is here to help with this.

Listagg was introduced in 11gR2 and is available in 12c as well.

Let me show you listagg in action, to get a feel on how to use it.

col grantee for A10
col table_name for A30
col Permissions for A60
col owner for A10
set linesize 200

-->> List tables and permissions granted to HR_ADMIN role

select grantee, owner, table_name, listagg(privilege,',') within group (order by privilege) as "Permissions"
from dba_tab_privs
where grantee='HR_ADMIN' 
group by table_name, grantee, owner
order by table_name;

GRANTEE    OWNER      TABLE_NAME		     Permissions
---------- ---------- ------------------------------ -----------------------------------
HR_ADMIN   HR	      ADD_JOB_HISTORY		     DEBUG,EXECUTE
HR_ADMIN   HR	      COUNTRIES 		     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      DEPARTMENTS		     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      DEPARTMENTS_SEQ		     SELECT
HR_ADMIN   HR	      EMPLOYEES 		     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      EMPLOYEES_SEQ		     SELECT
HR_ADMIN   HR	      EMP_DETAILS_VIEW		     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      JOBS			     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      JOB_HISTORY		     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      LOCATIONS 		     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      LOCATIONS_SEQ		     SELECT
HR_ADMIN   HR	      REGIONS			     DELETE,INSERT,SELECT,UPDATE
HR_ADMIN   HR	      SECURE_DML		     DEBUG,EXECUTE

What does listagg(privilege,’,’) within group (order by privilege) in my SQL Statement do?

Here it is in plain English:

I am asking Oracle to concatenate the privilege column, within my group (which in this case is table_name, grantee, owner — as per the group by clause of the sql statement), AND use ‘,’ (comma) as a delimiter between the values, AND within each group order the entries by the privilege column.

There are other examples using the listagg functions, that Oracle put together. You can check it out here. Also Tim Hall from www.oracle-base.com, has it covered very well here.

Now, going back to my original report, to write a query that will confirm a role has all the grants that it is supposed to have.

Here is the solution:


col "Permission s/b" for A100
select object_name, decode(object_type, 'TABLE'       ,'DELETE,INSERT,SELECT,UPDATE',
                                        'VIEW'        ,'DELETE,INSERT,SELECT,UPDATE',
                                        'SEQUENCE'    ,'SELECT',
                                        'FUNCTION'    ,'DEBUG,EXECUTE',
                                        'PACKAGE'     ,'DEBUG,EXECUTE',
                                        'PACKAGE BODY','DEBUG,EXECUTE',
                                        'PROCEDURE'   ,'DEBUG,EXECUTE') "Permission s/b"        
from dba_objects where owner='HR' and object_type not in ('INDEX','CONSTRAINT','DATABASE LINK','TRIGGER')
minus
select table_name, listagg(privilege,',') within group (order by privilege) as "permission"
from dba_tab_privs
where grantee='HR_ADMIN' and owner='HR'
group by table_name
;

If your permissions are setup properly, no rows should be selected:

no rows selected

If a permission is missing, then the query will list what the permissions should be, same as here:

SQL> revoke update on HR.LOCATIONS from HR_ADMIN;   -->>I revoked the grant on purpose, 
                                                    -->>to confirm script is working.

Revoke succeeded.

SQL>select object_name, decode(object_type, 'TABLE'   ,'DELETE,INSERT,SELECT,UPDATE',
                                        'VIEW'        ,'DELETE,INSERT,SELECT,UPDATE',
                                        'SEQUENCE'    ,'SELECT',
                                        'FUNCTION'    ,'DEBUG,EXECUTE',
                                        'PACKAGE'     ,'DEBUG,EXECUTE',
                                        'PACKAGE BODY','DEBUG,EXECUTE',
                                        'PROCEDURE'   ,'DEBUG,EXECUTE') "Permission s/b"        
from dba_objects where owner='HR' and object_type not in ('INDEX','CONSTRAINT','DATABASE LINK','TRIGGER')
minus
select table_name, listagg(privilege,',') within group (order by privilege) as "permission"
from dba_tab_privs
where grantee='HR_ADMIN' and owner='HR'
group by table_name
;

OBJECT_NAME		       Permission s/b
------------------------------ ----------------------------------
LOCATIONS		       DELETE,INSERT,SELECT,UPDATE

In the above query you define the rules, as to what the grants should be for each object type. Of course the query could be turned into a script and automated by using substitution variables for the role name, and schema owner. The possibilities are endless!

I am curious, have you seen this function before? Have you used it? Leave a message in the comments section, I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana