Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I need to query an Oracle database and get back which privileges are granted which roles and on which objects. Because I am going to either revoke or grant privileges to roles on objects according to configurations in a database management system I'm creating.

When I loop over the objects I'm going to process, I need to know if a role has already been granted any of the privileges on that object. This needs to be done before queuing up the GRANT or REVOKE to be performed.

The ODP.NET provider doesn't support running multiple Oracle statements, they must be run one by one or encapsulated in a BEGIN END block (which then counts as a single statement). Some statements even have to be put inside EXECUTE IMMEDIATE statements inside the block for it to work.

I know you can grant the same privilege on an object to a role multiple times without breaking anything, but (correct me if I'm wrong) revoking a privilege from role on an object which the role doesn't have will raise an exception and break the whole block execution.

Before performing any action on the Oracle database I first query for all the information I need to do my work, as I've found that to be best for performance, not playing ping pong with the server by querying for each object, role or privilege...

While processing the Oracle database I then compare the local information I just queried for, with the configured information, and perform different actions based on that.

I used SQLTracker and found Toad using this query for finding privileges granted on objects for a role and removed the roles where clause

SELECT dtp.PRIVILEGE,
       dtp.grantable ADMIN,
       dtp.grantee,
       dtp.grantor,
       dbo.owner,
       dbo.object_type,
       dbo.object_name,
       dbo.status,
       '' column_name
  FROM ALL_TAB_PRIVS dtp, DBA_OBJECTS dbo
 WHERE dtp.TABLE_SCHEMA = dbo.owner AND dtp.table_name = dbo.object_name
       AND dbo.object_type IN
              ('TABLE',
               'VIEW',
               'PACKAGE',
               'PROCEDURE',
               'FUNCTION')
UNION ALL
SELECT dcp.PRIVILEGE,
       dcp.grantable ADMIN,
       dcp.grantee,
       dcp.grantor,
       dbo.owner,
       dbo.object_type,
       dbo.object_name,
       dbo.status,
       dcp.column_name
  FROM ALL_COL_PRIVS dcp, DBA_OBJECTS dbo
 WHERE dcp.TABLE_SCHEMA = dbo.owner AND dcp.table_name = dbo.object_name
       AND dbo.object_type IN
              ('TABLE',
               'VIEW',
               'PACKAGE',
               'PROCEDURE',
               'FUNCTION');

I think this query will give me the information I need, but I'm afraid of performance issues if working with a very large Oracle database with A LOT of objects.

What is the best possible way to query for which privileges are granted to which roles on which objects on an Oracle database?

The query I posted is just one of my own suggestions, but since I'm asking this question here I'm obviously not sure about it, and would like some input on the query.

Am I on the right track? Does it need to be modified? Should I scrap it and use a totally different approach?

share|improve this question

1 Answer

up vote 0 down vote

well, there is no way of retrieving data without actually retrieving it so you'l have to query the tables. if your problem is revoking privileges that don't exists you can write a function for it. something like this

create or replace procedure revoke_priv (
    object_owner    all_objects.owner%type , 
    object_name     all_objects.object_name%type , 
    privilege       all_tab_privs.privilege%type , 
    role_name       role_tab_privs.role%type)
authid current_user
as
    err number;
    stmt varchar2(4001);
begin
    stmt := 'revoke ' || privilege || ' on ' || object_owner || '.' || object_name ||' from ' || role_name;
    execute immediate stmt;
exception when others then 
    err := SQLCODE;
    if (err = -1927) then
        dbms_output.put_line('the privilege does not exists for the role');
    else
        raise;
    end if;
end;
/

and this is the scenario 

SQL>create role my_role;

Role created.

SQL>grant select on scott.dept to my_role;

Grant succeeded.

SQL>select role , owner , table_name , PRIVILEGE from role_tab_privs where role = 'MY_ROLE';

ROLE                           OWNER                          TABLE_NAME                     PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
MY_ROLE                        SCOTT                          DEPT                           SELECT

SQL>revoke select on scott.dept from my_role;

Revoke succeeded.

SQL>revoke select on scott.dept from my_role;
revoke select on scott.dept from my_role
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

SQL>grant select on scott.dept to my_role;

Grant succeeded.

SQL>exec revoke_priv('SCOTT','DEPT','SELECT','MY_ROLE');

PL/SQL procedure successfully completed.

SQL>select role , owner , table_name , PRIVILEGE from role_tab_privs where role = 'MY_ROLE';

no rows selected

SQL>exec revoke_priv('SCOTT','DEPT','SELECT','MY_ROLE');
the privilege does not exists for the role

PL/SQL procedure successfully completed.

SQL>drop role my_role;

Role dropped.
share|improve this answer
So you are saying its enough to query only the ROLE_TAB_PRIVS table? – furier 17 hours ago
this is the only way to get the roles privs. any other way will implicitly use this table any way. – haki 10 hours ago

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.