python code
#!/usr/bin/env python
import cx_Oracle
username='zwc'
password='zwc'
database='oraprod'
conn=cx_Oracle.connect(username,password,database)
curs=conn.cursor()
user_query='select username from dba_users order by username'
curs.parse(user_query)
curs.execute(None)
rows=curs.fetchall()
user_sysprivs_curs=conn.cursor()
user_sysprivs_query='select privilege from dba_sys_privs where grantee = :username'
user_sysprivs_curs.parse(user_sysprivs_query)
user_roleprivs_curs=conn.cursor()
user_roleprivs_query='select granted_role from dba_role_privs where grantee = :username'
user_roleprivs_curs.parse(user_roleprivs_query)
fl=open('username_privs_report.html','w')
fl.write('<html> \
<body> \
<h1>Username SysPrivs/Roles Report</h1> \
<table border=1> \
<tr><th>UserName</th><th>Role/Priv</th>Detail</th></tr>')
for r in rows:
fl.write('<tr style="background-color: #CCCCCC;"><td>' + r[0] + '</td><td> </td><td>')
user_roleprivs_curs.execute(None,username=r[0])
rows_roleprivs=user_roleprivs_curs.fetchall()
if not rows_roleprivs:
fl.write('<tr><td> </td><td>Role</td><td>No roles</td></tr>')
else:
fl.write('<tr><td> </td><td>Role</td><td> </td></tr>')
for role_row in rows_roleprivs:
fl.write('<tr><td> </td><td> </td><td>' + role_row[0] + '</td></tr>')
user_sysprivs_curs.execute(None,username=r[0])
rows_sysprivs=user_sysprivs_curs.fetchall()
if not rows_sysprivs:
fl.write('<tr><td> </td><td>Sys Privs</td><td>No Sys Privs</td></tr>')
else:
fl.write('<tr><td> </td><td>Sys Privs</td><td> </td></tr>')
for syspriv_row in rows_sysprivs:
fl.write('<tr><td> </td><td> </td><td>' + syspriv_row[0] + '</td></tr>')
fl.write('</table> \
</body> \
</html>')
fl.close()
report
