Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 roles. I wrote the following script:

DEFINE ROLE_1 = &INPUT_1
DEFINE ROLE_2 = &INPUT_2

SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET LINESIZE 140

COLUMN PRIVILEGE    FORMAT A20    HEADING "PRIVILEGE"
COLUMN TABLE_NAME   FORMAT A30    HEADING "TABLE_NAME"
COLUMN GRANTABLE    FORMAT A5     HEADING "GRANTABLE"

PROMPT
PROMPT OVERLAPPINGS BETWEEN 1st ROLE AND 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

INTERSECT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 1st ROLE BUT NOT IN 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 2nd ROLE BUT NOT IN 1st ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

It works all fine, but for some reason it doesn't display the table head.

The real problem why I'm asking is, is there a more comfortable way to automatically compare all roles to one another? Some neat way to do all this? I'm quite new to PL/SQL and don't really know how to loop this thing up. Is there even maybe a possibly way to display the corresponding column of the tables?

share|improve this question
    
It's not an answer, but consider Oracle's hierarchical (CONNECT BY) queries using DBA_ROLE_PRIVS to navigate the role tree as well as using the INTERSET/MINUS operators to determine if one role includes all privileges of another role. –  Adam Musch Jul 22 '13 at 3:25
    
I already have a hierarchical query to list all roles of an user, so it wouldn't be a problem to change it into comparing two roles. The main problem is that I want the querie to do this for all the users automatically. Like fetching the content of DBA_ROLES and comparing it like some kind of A x A cartesian product. –  Chris.V Jul 22 '13 at 6:03
    
One thing to consider is that dba_role_privs contains both users granted roles and roles granted roles. If you want duplicate roles, regardless of users, you will need the queries over role_role_privs, role_sys_privs, and role_tab_privs. Is this interpretation correct? –  Henry Helgen Jul 22 '13 at 15:27
    
So far it's correct, but I'm mostlikely using `dba_tab_privs' for comparing as you can see in my MWE. It would be nice, if there was a way to loop the whole query. –  Chris.V Jul 23 '13 at 6:42

2 Answers 2

up vote 2 down vote accepted

Here is a query that finds duplicate role_tab_privs. It uses Oracle LISTAGG aggregate function to create a list of privileges for each role, owner, table.

SELECT arole, brole, aowner, atable, aprivlist
FROM 
      --join to each grouped view by role, owner, table
      (
      SELECT A.ROLE AS arole, A.owner AS aowner, A.table_name AS atable,
                  --one field for all privileges concatenated with | separator
                  listagg(a.privilege, ' | ') WITHIN GROUP(ORDER BY a.privilege) AS aprivlist
      FROM role_tab_privs a
      GROUP BY A.ROLE, A.owner, A.table_name
      )
JOIN 
      (
      SELECT b.ROLE as brole, b.owner as bowner, b.table_name as btable,
                  listagg(b.privilege, ' | ') WITHIN GROUP(ORDER BY b.privilege) AS bprivlist
      FROM role_tab_privs b
      GROUP BY b.ROLE, b.owner, b.table_name
      )
  -- the roles cannot be equal but the owner, table, and privileges must be
  ON AROLE != bROLE
 AND aowner = bowner
 AND atable = btable
 AND aprivlist = bprivlist
ORDER BY aowner, atable, arole 
;

Note that the LISTAGG is used only on the privileges within a role, owner, table group. The result of a `LISTAGG' function must fit within the max varchar(4000) or the error:

ORA-01489: result of string concatenation is too long

This query gives a good view of how the table privileges relate.

share|improve this answer
    
That's a really nice approach for my problem. I changed role_tab_privs to dba_tab_privs and used a WHERE clause to only display my roles and not the users. The idea is pretty cool, but the output is kinda messy and "unorganised". I would like to see what they have in common and in what they differentiate from each other. Like in my MWE. I could see at the first glance how the correlate and make a rough estimate of how much % they overlap. –  Chris.V Jul 23 '13 at 7:01
    
One thing that might help to organize it the way you want is to ORDER BY arole, brole, aowner, atable. –  Henry Helgen Jul 23 '13 at 13:47

I would do it using shell scripts.

The code below is not tested, there could be problems with IFS and spaces in file names and maybe a couple of typos. It also lacks optimization, it loops over all the roles again and again. It should especially be a problem with the "extraction" part, as it makes a new connection to the database for each role.

But still, if it's a one shot thing, I wouldn't mind too much.

You'll need :

a sql file to extract privileges for a role (your request, I added the order by clause because we will diff them later)

--extract_privileges.sql
SELECT
  PRIVILEGE,
  TABLE_NAME,
  GRANTABLE
FROM
  SYS.DBA_TAB_PRIVS
WHERE
  GRANTEE IN ( '&1' )
ORDER BY
  PRIVILEGE,
  TABLE_NAME,
  GRANTABLE;

a configuration file with the roles you want to test :

ROLE_1
ROLE_2
...

and a shell script :

ROLE_FILE='/path/to/the/configuration/file'
EXTRACT_PRIVILEGES_FILE='/path/to/the/sql/script'
TEMP_DIR='/path/to/temp/dir/'
CONNECTION_STRING='user/pass@instance'

IFS='
'

# Make sure we have temp dirs
EXTRACT_DIR='${TEMP_DIR}/extract/'
COMPARE_DIR='${TEMP_DIR}/compare/'

mkdir -p ${EXTRACT_DIR}
mkdir -p ${COMPARE_DIR}

# Extract all privileges
for role in `cat ${ROLE_FILE}`
do
  ${ORACLE_HOME}/bin/sqlplus ${CONNECTION_STRING} @${EXTRACT_PRIVILEGES_FILE} ${role} > ${EXTRACT_DIR}/${role}
done

# Compare privileges
for role1 in `cat ${ROLE_FILE}`
do
  for role2 in `cat ${ROLE_FILE}`
  do
    diff ${EXTRACT_DIR}/${role1} ${EXTRACT_DIR}/${role2} > ${COMPARE_DIR}/${role1}_${role2}
  done
done

# Now you have all the diffs, you can look for those with nothing in it (privileges are the same)
for file in `ls ${COMPARE_DIR}`
do
  #There is certainly a more elegant way to do this.
  if [[`wc -l ${file}` -eq 0 ]]
  then
    echo '${file} is empty'
  fi
done
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.