Oracle Migrations – Identifying Dependent Users

Identifying dependent users and roles for a given list of schema accounts in support of database migrations.

The first step in preparing for the migration of an application database is to analyse how that is configured in its current environment. That gives the information needed to plan and configure the target database and to plan the migration.

One question that we need answers to is this: if I have identified one or more schema accounts for an application, which roles, other schema accounts, service accounts and end user accounts use or have dependencies on these accounts?

This helps inform us what other accounts need to be migrated or remediated when we move our application schema(s), so we can plan accordingly.

We would typically start by consulting with the relevant technical support teams and maybe review available documentation and user management systems to see what accounts might relate to the application in question. We would then analyse the database to see what is defined in the data dictionary. But the database wont always tell the whole story, so we would normally go back to the tech support folks for further clarification if we find discrepencies. For example, it is quite common to find apparently redundant accounts and roles and unexpected dependencies that nobody seems to be aware of.

Starting with dependencies…

The scenario might be as follows (note that for clarity I named accounts and roles as SCHEMAn, USERn or ROLEn): We are told that the schema for a particular application is called SCHEMA1. So we query dba_dependencies and maybe get this…

SQL> select owner, name, referenced_name from dba_dependencies where referenced_owner = ‘SCHEMA1’
2  /

OWNER    NAME         REFERENCED_N
——– ———— ————
SCHEMA2  V1           T1

So it looks like one table in the application schema has a dependent object owned by SCHEMA2 . Well, maybe not…

SQL> select owner, name, referenced_name from dba_dependencies where referenced_owner = ‘SCHEMA2’
2  /

OWNER    NAME         REFERENCED_N
——– ———— ————
SCHEMA3  V3           V1

Hmm, so there is an extra level of nesting. This could go on and on and whilst its an ugly way to do things in my opinion, we keep seeing scenarios like this so we need to have an easy way to report on it.

Tree walking comes to the rescue…

1  select owner, name, referenced_name
2  from dba_dependencies
3  start with referenced_owner = ‘SCHEMA1’
4* connect by prior owner = referenced_owner
SQL> /

OWNER    NAME         REFERENCED_N
——– ———— ————
SCHEMA2  V1           T1
SCHEMA3  V3           V1

Now we have the full tree of nested dependencies. Table SCHEMA1.T1 has a dependent view SCHEMA2.V1, which in turn has a dependent view SCHEMA3.V3.

We have identified that schema accounts SCHEMA2 and SCHEMA3 also relate in some way to the application we want to migrate and we need to evaluate how to manage them in the migration, whether they should be migrated with SCHEMA1, removed, remediated, or just left as they are.

So this is an example of where we would go back to the App Support folks to seek clarification about these two additional dependent schemas and what to do about them.

So what about dependent end users, service account users and roles?

We want to know what users and roles have privileges on objects in our application schema accounts, and whilst we can easily identify which users have privileges directly granted on our application schema’s objects, finding those that have been indirectly granted via a series of roles for example can be a bit trickier…

1  select table_name, grantee, privilege
2  from dba_tab_privs where owner = ‘SCHEMA1’
3* order by 1,2
SQL> /

TABLE_NAME      GRANTEE        PRIVILEGE
————— ————– ———————————–
T1              ROLE1          SELECT
T1              SCHEMA2        SELECT
T1              USER1          SELECT

Here we’ve identified one schema, one end user and one role that have privileges on SCHEMA1’s objects. But the privilege granted to that role is suspicious; is the role then granted to something else? What else is not shown by this query? Let’s try tree walking again…

SELECT grantee, granted_role from (
SELECT grantee , null granted_role
FROM  dba_tab_privs
WHERE owner in (‘SCHEMA1’)
union
SELECT grantee , granted_role
from dba_role_privs
)
start with granted_role is null
connect by granted_role = prior grantee
order by 1
SQL> /

GRANTEE        GRANTED_ROLE
————– ——————–
ROLE1
ROLE2          ROLE1
ROLE3          ROLE2
SCHEMA2
SCHEMA4
USER1
USER2          ROLE3
USER3          ROLE3

So there are three roles, two other schema accounts and three end user accounts with direct or indirectly granted privileges on objects in the schema we are interested in. As with the dependencies we saw earlier, we need to evaluate how to manage these in the migration, so we would normally go back to app suport to get agreement on whether these accounts and roles should be migrated with SCHEMA1, removed, remediated, or just left as they are.

Caveats

If any object privilege is granted to PUBLIC then we have no 100% reliable way to determine which end user accounts, service accounts or roles might be making use of that privilege, so in that case we would need to discuss with application support.

Conclusion

So by using tree walking we can develop simple queries to give a comprehensive list of database users with some sort of direct or indirect dependency or privilege on the application schema(s) we’re interested in.

However, these kinds of queries may not always show the whole story and we must always validate our findings through consultation with the relevant application support teams.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s