SQL Formatting Question

[ChrisQuick]

I am trying to list out the results of a Select statement.

The statement is:
SELECT DISTINCT TABLE_NAME, PRIVILEGE
FROM ALL_TAB_PRIVS_RECD
WHERE
TABLE_NAME IN ('CONSTRUCTION_CONTRACTORS', 'CONSTRUCTION_CONTRACTS')
AND
PRIVILEGE IN
('UPDATE', 'INSERT', 'SELECT')
ORDER BY TABLE_NAME, PRIVILEGE;

This returns the following for a test user account:

CONSTRUCTION_CONTRACTORS INSERT
CONSTRUCTION_CONTRACTORS SELECT
CONSTRUCTION_CONTRACTORS UPDATE
CONSTRUCTION_CONTRACTS INSERT
CONSTRUCTION_CONTRACTS SELECT
CONSTRUCTION_CONTRACTS UPDATE

What I am hoping to have is something more like:
CONSTRUCTION_CONTRACTORS INSERT, SELECT,UPDATE
CONSTRUCTION_CONTRACTS INSERT, SELECT, UPDATE

Is there anyway to do this?

Thanks




[sig][/sig]

 

[DataType]

I did this not too long ago (I hope I saved it!). It's lumpy but it works ok. Instead of doing an 'in' for the privs, alias the table for each priv and join three times (or 4 or whatever). I'll see if I can find my sql and I'll post it.
[sig][/sig]

 

[ChrisQuick]

I tried using joins and it worked only if the user had all three of the privileges being checked for.

What I found instead is:

SELECT DISTINCT
TABLE_NAME, SELECT_PRIV, INSERT_PRIV,
DELETE_PRIV, UPDATE_PRIV
FROM TABLE_PRIVILEGES
WHERE
OWNER = 'XXX'
AND TABLE_NAME IN (
'CONSTRUCTION_CONTRACTORS', 'CONSTRUCTION_CONTRACTS'
)
AND GRANTEE <> 'PUBLIC'
ORDER BY TABLE_NAME [sig][/sig]

 

[Advocate]

playing around with the same theme ...

select
DISTINCT TABLE_NAME,
decode(SELECT_PRIV, 'Y','Select', ' X') SELECT_PRIV,
decode(INSERT_PRIV, 'Y','Insert', ' X') INSERT_PRIV,
decode(DELETE_PRIV, 'Y','Delete', ' X') Delete_PRIV,
decode(UPDATE_PRIV, 'Y','Update', ' X') UPDATE_PRIV
from table_privileges
WHERE OWNER = 'XXX' AND
TABLE_NAME
IN ('CONSTRUCTION_CONTRACTORS', 'CONSTRUCTION_CONTRACTS')
AND GRANTEE <> 'PUBLIC'
ORDER BY TABLE_NAME [sig]<p>Best of Irish Luck, David.<br><a href=mailto:djwilkes@hotmail.com>djwilkes@hotmail.com</a><br>[/sig]

 

[ChrisQuick]

I had to change second arguement for insert, delete, and update to 'A' for it to return correctly.

select
DISTINCT TABLE_NAME,
decode(SELECT_PRIV, 'Y','Select', ' X') SELECT_PRIV,
decode(INSERT_PRIV, 'A','Insert', ' X') INSERT_PRIV,
decode(DELETE_PRIV, 'A','Delete', ' X') Delete_PRIV,
decode(UPDATE_PRIV, 'A','Update', ' X') UPDATE_PRIV
from table_privileges
WHERE OWNER = 'XXX' AND
TABLE_NAME
IN ('CONSTRUCTION_CONTRACTORS', 'CONSTRUCTION_CONTRACTS')
AND GRANTEE <> 'PUBLIC'
ORDER BY TABLE_NAME [sig][/sig]

 

[Advocate]

not being a dba i was maybe too quick off the mark ... there appears to be 4 possible values in each field :
Y = yes = access privs ?
N = no privs?
G= grant privs ?
A = administration privs

so maybe the code should read something like ... ???

decode(INSERT_PRIV, 'A','Admin', 'G', 'Grant', 'Y', 'Yes', 'N', 'No', ' X') INSERT_PRIV,
[sig]<p>Best of Irish Luck, David.<br><a href=mailto:djwilkes@hotmail.com>djwilkes@hotmail.com</a><br>[/sig]