hierarchical query question
- From: "Steve McClure" <smcclure@xxxxxxxxxxxx>
- To: <oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 12 Oct 2010 09:38:02 -0700
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.owner='SCHEMA_OWNER'
and p.table_name=t.table_name
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.owner='SCHEMA_OWNER'
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
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » hierarchical query question - Steve McClure
