• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

wrapper to a main SQL script to pass each username from DBA_USER

I have a SQL script that lists for a specific user all the roles and other privileges granted

I need a wrapper so that, I can pass each user from the dba_users.username to the main script which looks like

-- Main script for single User
set lines 100 pages 1000 ver off
col grantedto for a15
col priv form a75 heading "PRIVILEGE" wrap

define usertolist = '&userid'                -- This is where I need help

select * from
(
.....
union
.....
union
......
)
order by1;


Thanks
0
Greens8301
Asked:
Greens8301
  • 5
  • 3
  • 3
1 Solution
 
sdstuberCommented:
sqlplus doesn't really have a procedural language.

but if you use pl/sql, then you can't invoke other sqlplus scripts (not without a lot of jumping through hoops)

you "could" create a shell script that runs a query from sqlplus and reads the output and invokes sqlplus again for each user.

Best solution would probably be to have your existing script recognize a keyword like "--ALL--"  and query dba_users for you and iterate

can you post more of your existing script and I can help you modify it
0
 
sdstuberCommented:
even easier,  put all your logic in a pl/sql package.  Then simply call the package from sqlplus, your script could collapse to being just one line
0
 
slightwv (䄆 Netminder) Commented:
Do you want a separate report for 'each'?

Just join the queries to dba_users and set up the sqlplus report to break on each individual username.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Greens8301Author Commented:
Below is the query for a single user

Thanks for your help

==================================

set lines 110 pages 1000 ver off

col grantee for a15
col pv for a75 hea 'PRIVILEGE OR ROLE' wrap

---bre on grantee on type skip 1

define usercheck = '&userid'

select * from
(
select grantee
, 'ROL' type
, granted_role pv
from dba_role_privs
where grantee like '&usercheck'
union
select grantee
, 'PRV' type
, privilege pv
from dba_sys_privs
where grantee like  '&usercheck' union
select grantee
, 'OBJ' type,
regexp_replace(max(decode(privilege,'WRITE','WRITE,'))||
max(decode(privilege,'READ','READ,'))||
max(decode(privilege,'EXECUTE','EXECUTE')),'WRITE,READ,EXECUTE','ALL')||
regexp_replace(max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||
max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT')),'SELECT,DELETE,UPDATE,INSERT','ALL')||
' ON '||object_type||' "'||a.owner||'"."'||table_name||'"' pv
from dba_tab_privs a
, dba_objects b
where a.owner=b.owner
and a.table_name = b.object_name
and a.grantee like '&usercheck'
group by a.owner
, table_name
, object_type
, grantee
union
select grantee
, 'COL' type,
privilege||' ('||column_name||') ON "'||owner||'"."'||table_name||'"' pv
from dba_col_privs
where grantee like '&usercheck'
union
select username grantee
, '---' type
, 'empty user ---' pv
from dba_users
where not username in (select distinct grantee from dba_role_privs)
and not username in (select distinct grantee from dba_sys_privs)
and not username in (select distinct grantee from dba_tab_privs)
and username like '%&usercheck%'
group by username
order by grantee
, type
, pv) where grantee in (
select role as grantee from dba_roles where role in ('NDS_RO','NDS_RW','NRS_DM_RO','NRS_DM_RW')
union all
select username as grantee from dba_users where username not in  (select username from dba_users where trunc(created) = (select trunc(created) from dba_users where username = 'SYS'))
) order by 1;
0
 
Greens8301Author Commented:
I want one report for all users in a database and I am planning on exporting the output to a excel file

Thanks
0
 
slightwv (䄆 Netminder) Commented:
I'm confused.  If you want one report for ALL users, just comment out the 'grantee like' pieces?
set lines 110 pages 1000 ver off 

col grantee for a15
col pv for a75 hea 'PRIVILEGE OR ROLE' wrap

---bre on grantee on type skip 1

--define usercheck = '&userid'

select * from
(
select grantee
, 'ROL' type
, granted_role pv 
from dba_role_privs 
--where grantee like '&usercheck' 
union 
select grantee
, 'PRV' type
, privilege pv 
from dba_sys_privs 
--where grantee like  '&usercheck' union
union
select grantee
, 'OBJ' type, 
regexp_replace(max(decode(privilege,'WRITE','WRITE,'))||
max(decode(privilege,'READ','READ,'))||
max(decode(privilege,'EXECUTE','EXECUTE')),'WRITE,READ,EXECUTE','ALL')||
regexp_replace(max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||
max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT')),'SELECT,DELETE,UPDATE,INSERT','ALL')||
' ON '||object_type||' "'||a.owner||'"."'||table_name||'"' pv 
from dba_tab_privs a
, dba_objects b 
where a.owner=b.owner 
and a.table_name = b.object_name 
--and a.grantee like '&usercheck'
group by a.owner
, table_name
, object_type
, grantee 
union
select grantee
, 'COL' type, 
privilege||' ('||column_name||') ON "'||owner||'"."'||table_name||'"' pv 
from dba_col_privs
--where grantee like '&usercheck'
union
select username grantee
, '---' type
, 'empty user ---' pv 
from dba_users 
where not username in (select distinct grantee from dba_role_privs) 
and not username in (select distinct grantee from dba_sys_privs) 
and not username in (select distinct grantee from dba_tab_privs) 
--and username like '%&usercheck%'
group by username
order by grantee
, type
, pv) where grantee in (
select role as grantee from dba_roles where role in ('NDS_RO','NDS_RW','NRS_DM_RO','NRS_DM_RW')
union all
select username as grantee from dba_users where username not in  (select username from dba_users where trunc(created) = (select trunc(created) from dba_users where username = 'SYS'))
) order by 1;

Open in new window

0
 
sdstuberCommented:
or wildcard it with a keyword like I mentioned above


and (a.grantee like '&usercheck' or '&usercheck' ='--ALL--')
0
 
sdstuberCommented:

or let the absence of a username be the key


and (a.grantee like '&usercheck' or '&usercheck' is NULL)
0
 
slightwv (䄆 Netminder) Commented:
>>or wildcard it with a keyword like I mentioned above


When prompted could you not just enter a % instead of a delimiter or null?
0
 
sdstuberCommented:
yeah, since it's "like" then % will be the keyword
0
 
Greens8301Author Commented:
Great , Quck Respnse

Thanks
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now