-
Hi
I am trying to get the privleges for the users for each table in a sql
I want the output as
user1 table1 delete insert select update
user1 table2 delete insert
I am querying from dba_tab_privs and getting the output as
user1 table1 delete
insert
select
update
ie all the privs are appearing on new lines, how do i get it on the same line
Thanks
badrinath
-
I would try something like this :
select TAB.owner,TAB.table_name,S.P,I.P,D.P,U.P
from
(
select distinct owner,table_name from dba_tab_privs
) TAB,
(
select owner,table_name,privilege P from dba_tab_privs where privilege = 'SELECT'
) S,
(
select owner,table_name,privilege P from dba_tab_privs where privilege = 'INSERT'
) I,
(
select owner,table_name,privilege P from dba_tab_privs where privilege = 'DELETE'
) D,
(
select owner,table_name,privilege P from dba_tab_privs where privilege = 'UPDATE'
) U
where TAB.owner = S.owner (+)
and TAB.table_name = S.table_name (+)
and TAB.owner = I.owner (+)
and TAB.table_name = I.table_name (+)
and TAB.owner = D.owner (+)
and TAB.table_name = D.table_name (+)
and TAB.owner = U.owner (+)
and TAB.table_name = U.table_name (+)
/
Hope this helps
Gert
-
A simple set of decodes will work much more efficiently:
SELECT
---GRANTEE,
---TABLE_NAME,
---MAX(DECODE(PRIVILEGE,'SELECT','SELECT',NULL)) AS SELECTPRIV,
---MAX(DECODE(PRIVILEGE,'UPDATE','UPDATE',NULL)) AS UPDATEPRIV,
---MAX(DECODE(PRIVILEGE,'DELETE','DELETE',NULL)) AS DELETEPRIV,
---MAX(DECODE(PRIVILEGE,'INSERT','INSERT',NULL)) AS INSERTPRIV
FROM
---DBA_TAB_PRIVS
GROUP BY
---GRANTEE,
---TABLE_NAME
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|