hierarchical query question
Date: Tue, 12 Oct 2010 09:38:02 -0700
Message-ID: <805091A1731E4A2CA4D69695968BAC6F_at_usscript.com>
So I thought I’d help our DBA out, and build a little stored procedure to help him with a task. Namely, to individually grant to a list of users, a set permissions to tables matching the permissions granted to a role. These users are developers who frequently compile stored procedures in their own schemas for testing purposes, and to accomplish this they need the privileges granted to them directly. In doing this rather mundane task, I ran into a small problem that I can’t explain, and I’m hoping someone here can help out or point me in the right direction.
select table_name, ltrim(max(sys_connect_by_path(priv, ',')),',') priv_list from (select p.table_name,p.privilege priv, row_number() over (partition by p.table_name order by rownum) rn
from sys.dba_tables t, SYS.DBA_TAB_PRIVS p where p.grantee in('MASTER_ROLE')and p.table_name=t.table_name
and p.owner='SCHEMA_OWNER'
and p.owner=t.owner)
connect by table_name = prior table_name and rn = prior rn+1 start with rn=1 group by table_name order by table_name;
Which produced;
TABLE_ONE UPDATE, SELECT, INSERT,DELETE
TABLE_TWO UPDATE,SELECT,INSERT,DELETE
The above actually worked admirably, I took the output, and formatted it
into a grant statement for execute immediate.
Then I got the idea that this process, which would execute a few hundred grant statements, would be improved by only explicitly granting privileges that a user didn’t already have. Like so;
select table_name, ltrim(max(sys_connect_by_path(priv, ',')),',') priv_list from (select p.table_name,p.privilege priv, row_number() over (partition by p.table_name order by rownum) rn
from sys.dba_tables t, SYS.DBA_TAB_PRIVS p where p.grantee in('MASTER_ROLE')and p.table_name=t.table_name
and p.owner='SCHEMA_OWNER'
and p.owner=t.owner
and not exists (select 1
from sys.dba_tab_privs p1
where p1.grantee =CURSOR_INPUT_USER and p1.table_name=p.table_name and p1.owner=p.owner and p1.privilege=p.privilege) ) connect by table_name = prior table_name and rn = prior rn+1 start with rn=1
group by table_name
order by table_name;
which produced
TABLE_ONE DELETE,SELECT,INSERT,DELETE
TABLE_TWO DELETE,SELECT,INSERT,DELETE
Note the Delete at the beginning and end of my list of privileges. The
“connect_by_path” returned inexplicable, to me, results
The inline view Produces seemingly appropriate output
TABLE_ONE DELETE 1 TABLE_ONE INSERT 2 TABLE_ONE SELECT 3 TABLE_ONE UPDATE 4 TABLE_TWO DELETE 1 TABLE_TWO INSERT 2 TABLE_TWO SELECT 3 TABLE_TWO UPDATE 4
To top it all off, I can get the query to work with the not exists clause IF
I further qualify the table. Even if the further qualification is
redundant, like the example I've provided. Which, while it works, does not
make sense to me.
Here is that example
select table_name, ltrim(max(sys_connect_by_path(priv, ',')),',') priv_list from (select p.table_name,p.privilege priv, row_number() over (partition by p.table_name order by rownum) rn
from sys.dba_tables t, SYS.DBA_TAB_PRIVS p where p.grantee in('MASTER_ROLE')
and p.owner='SCHEMA_OWNER'
and p.table_name in (select table_name from dba_tables where
owner=’SCHEMA_OWNER’)--<This line added
and p.table_name=t.table_name
and p.owner=t.owner
and not exists (select 1
from sys.dba_tab_privs p1
where p1.grantee =CURSOR_INPUT_USER and p1.table_name=p.table_name and p1.owner=p.owner and p1.privilege=p.privilege) ) connect by table_name = prior table_name and rn = prior rn+1 start with rn=1
group by table_name
order by table_name;
Which produced the desired results
TABLE_ONE UPDATE, SELECT, INSERT,DELETE
TABLE_TWO UPDATE,SELECT,INSERT,DELETE
I can also use LIKE or IN () to qualify the table, and yield my anticipated
results.
It took me thirty minutes to write the procedure, but now I’ve spent half a day trying to explain this.
Anyone have any thoughts?
Steve McClure
Former DBA promoted into Damagement
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 12 2010 - 11:38:02 CDT