Tree Queries
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Tree Queries
(Created page with "Hierarchical or tree data can be obtained from an Oracle database table. In this example, we are using the role_role_privs view. This view gives the roles that are granted ...") |
|||
| Line 1: | Line 1: | ||
| - | Hierarchical or tree data can be obtained from an [[Oracle | + | Hierarchical or tree data can be obtained from an [[Oracle Database]] table using a hierarchical queries and tree queries. |
In this example, we are using the role_role_privs view. This view gives the roles that are granted (role_granted) to another role (role). A hierarchy of roles can be built up by roles that contain roles being granted to yet another role. | In this example, we are using the role_role_privs view. This view gives the roles that are granted (role_granted) to another role (role). A hierarchy of roles can be built up by roles that contain roles being granted to yet another role. | ||
| Line 12: | Line 12: | ||
connect by prior granted_role=role | connect by prior granted_role=role | ||
; | ; | ||
| + | |||
| + | ROLE LPAD('',LEVEL*4,'')||GRANTED_ROLE | ||
| + | ------------------------------ --------------------------------------------- | ||
| + | DBA DATAPUMP_EXP_FULL_DATABASE | ||
| + | DATAPUMP_EXP_FULL_DATABASE EXP_FULL_DATABASE | ||
| + | EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE | ||
| + | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
| + | EXP_FULL_DATABASE SELECT_CATALOG_ROLE | ||
| + | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| + | DBA DATAPUMP_IMP_FULL_DATABASE | ||
| + | DATAPUMP_IMP_FULL_DATABASE EXP_FULL_DATABASE | ||
| + | EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE | ||
| + | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
| + | EXP_FULL_DATABASE SELECT_CATALOG_ROLE | ||
| + | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| + | DATAPUMP_IMP_FULL_DATABASE IMP_FULL_DATABASE | ||
| + | IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE | ||
| + | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
| + | IMP_FULL_DATABASE SELECT_CATALOG_ROLE | ||
| + | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| + | DBA DELETE_CATALOG_ROLE | ||
| + | DBA EXECUTE_CATALOG_ROLE | ||
| + | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
| + | DBA EXP_FULL_DATABASE | ||
| + | EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE | ||
| + | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
| + | EXP_FULL_DATABASE SELECT_CATALOG_ROLE | ||
| + | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| + | DBA GATHER_SYSTEM_STATISTICS | ||
| + | DBA IMP_FULL_DATABASE | ||
| + | IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE | ||
| + | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
| + | IMP_FULL_DATABASE SELECT_CATALOG_ROLE | ||
| + | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| + | DBA JAVA_ADMIN | ||
| + | DBA JAVA_DEPLOY | ||
| + | DBA OLAP_DBA | ||
| + | DBA OLAP_XS_ADMIN | ||
| + | DBA SCHEDULER_ADMIN | ||
| + | DBA SELECT_CATALOG_ROLE | ||
| + | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| + | DBA WM_ADMIN_ROLE | ||
| + | DBA XDBADMIN | ||
| + | DBA XDB_SET_INVOKER | ||
| + | |||
</pre> | </pre> | ||
The query selects role and granted role from the table. It treats those rows where the role is DBA as the top nodes of the hierarchy. It follows the hierarchy by using the connect by prior clause. | The query selects role and granted role from the table. It treats those rows where the role is DBA as the top nodes of the hierarchy. It follows the hierarchy by using the connect by prior clause. | ||
| - | The query then lists those rows with a granted_role same as | + | The query then lists those rows with a granted_role same as this row's role, in turn listing the rows below each of these rows. |
