Hierarchical Queries in Oracle

The concept of hierarchical queries is one that seems simple, but can quickly become confusing to those who don’t commonly write them. I still have to occasionally walk away from the keyboard when writing these queries and I have been doing it for years.

Everybody has seen the example queries with employees and managers. I am not going to rehash this example again. Instead I am going to provide a practical example looking at roles granted to a user in Oracle.

The same concept can be applied to inheritance and all sorts of other real world data rollup issues.

Privileges in Oracle can be granted on an object to a user or a role. It gets more difficult to traverse when you have roles granted to other roles.

Here is a basic query that needs some work to get there.

SQL> SET LINESIZE 200;
SQL> SET PAGESIZE 50;
SQL> SELECT SYS_CONNECT_BY_PATH(GRANTED_ROLE,'-->') AS USER_AND_ROLE_STR
 2   FROM   ( SELECT NULL as GRANTEE,
 3                   USERNAME as GRANTED_ROLE
 4            FROM   DBA_USERS
 5            WHERE  USERNAME = 'CHRIS'
 6            UNION
 7            SELECT GRANTEE, GRANTED_ROLE
 8            FROM   DBA_ROLE_PRIVS
 9          )
 10 START WITH GRANTEE IS NULL
 11 CONNECT BY GRANTEE = PRIOR GRANTED_ROLE;

USER_AND_ROLE_STR
-----------------------------------------------------
-->CHRIS
-->CHRIS-->AA_USER
-->CHRIS-->CONNECT
-->CHRIS-->CONTRACT_TRACKING
-->CHRIS-->PROJPLANNING
-->CHRIS-->PROJPLANNING-->PLANNINGUPDT
-->CHRIS-->EDUSER
-->CHRIS-->PLANNINGUPDT
-->CHRIS-->GISUSER
-->CHRIS-->GISUSER-->TELE_USER
-->CHRIS-->MC_CLIENT
-->CHRIS-->AGENDAUSER
-->CHRIS-->VACUPDATE
-->CHRIS-->CAM_S
-->CHRIS-->CAM_U
-->CHRIS-->PLNING
-->CHRIS-->SDE_VIEWER
-->CHRIS-->SDE_VIEWER-->SDE_SYS_PRIVS
-->CHRIS-->ENGDEPT
-->CHRIS-->PLAN_DATA_ENTRY
-->CHRIS-->PLAN_DATA_ENTRY-->PLAN_USER
-->CHRIS-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
-->CHRIS-->PLAN_POWER_USER
-->CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY
-->CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY-->PLAN_USER
-->CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
-->CHRIS-->PR_USER
-->CHRIS-->TRACKING_USER
-->CHRIS-->PORTAL_ADMIN
-->CHRIS-->STRATEGIC_PLAN_ADMIN

30 rows selected.

Neat. The query above shows the user, roles granted to the user, and then roles granted to those roles.

What’s happening in the query…
1) We are looking for our user in the DBA_USERS object. And then we are getting a list of all the roles, and roles granted to roles from DBA_ROLE_PRIVS. The union of the two queries gives us our base set of data that we will then use.
2) The “START WITH” and “CONNECT BY” portions of the query are telling Oracle to look at the data as a tree using a parent/child relationship.
3) “SYS_CONNECT_BY_PATH” returns the path of the hierarchy set up with the “START WITH” and “CONNECT BY”. First parameter is the column you want to work with. Second parameter is your delimiter. Note: Your delimiter cannot be something that can be found in the data of your column. Do that and you get a nasty error. Example: If your data has commas, don’t use a comma as your delimiter.

Next, let’s clean things up a bit to make the data slightly more “pretty”. Well… as pretty as this type of data can be.

SQL> SET LINESIZE 200;
SQL> SET PAGESIZE 50;
SQL> SELECT LTRIM(SYS_CONNECT_BY_PATH(GRANTED_ROLE,'-->'),'-->') AS USER_AND_ROLE_STR
 2   FROM   ( SELECT NULL as GRANTEE,
 3                   USERNAME as GRANTED_ROLE
 4            FROM   DBA_USERS
 5            WHERE  USERNAME = 'CHRIS'
 6            UNION
 7            SELECT GRANTEE, GRANTED_ROLE
 8            FROM   DBA_ROLE_PRIVS
 9          )
 10  where  CONNECT_BY_ISLEAF = 1
 11  START WITH GRANTEE IS NULL
 12  CONNECT BY GRANTEE = PRIOR GRANTED_ROLE;

USER_AND_ROLE_STR
-----------------------------------------------------------
CHRIS-->AA_USER
CHRIS-->CONNECT
CHRIS-->CONTRACT_TRACKING
CHRIS-->PROJPLANNING-->PLANNINGUPDT
CHRIS-->EDUSER
CHRIS-->PLANNINGUPDT
CHRIS-->GISUSER-->TELE_USER
CHRIS-->MC_CLIENT
CHRIS-->AGENDAUSER
CHRIS-->VACUPDATE
CHRIS-->CAM_S
CHRIS-->CAM_U
CHRIS-->PLNING
CHRIS-->SDE_VIEWER-->SDE_SYS_PRIVS
CHRIS-->ENGDEPT
CHRIS-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
CHRIS-->PLAN_POWER_USER-->PLAN_DATA_ENTRY-->PLAN_USER-->PLAN_VIEW
CHRIS-->PR_USER
CHRIS-->TRACKING_USER
CHRIS-->PORTAL_ADMIN
CHRIS-->STRATEGIC_PLAN_ADMIN

21 rows selected.

So what’s different?
1) We have added an “LTRIM()” function that strips off our delimiter from the front of our return. You don’t really need that leading delimiter.
2) We are restricting our results to only leaves instead of the branch and leaf. Think of the data as a tree with the trunk being the user you searched for. If they have a role granted to them and it ends there, it is a leaf. If you have a role (1) granted to the “trunk”, and then another role (2) granted to that… role (1) is a branch where role (2) is a leaf. We still want to know about the branch (role (2)), but having a separate row returned just muddies the water.  CONNECT_BY_ISLEAF is a pseudocolumn that returns 1 for leaves, and 0 for branches. In the query above, we reduce our results using this pseudocolumn.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: