Database Reference
In-Depth Information
# display user account heading
print
"
\n
User Account:
%s
@
%s
"
% (
user_name
,
host_address
)
print
"------------------------------------------"
# query mysql for grants for user account
sql_stmnt
=
"SHOW GRANTS FOR "
+
user_account
cur
.
execute
(
sql_stmnt
)
# loop through grant entries for user account
for
grants
in
cur
.
fetchall
() :
# skip 'usage' entry
if
re
.
search
(
'USAGE'
,
grants
[
0
]) :
continue
# extract name of database and table
dbtb
=
re
.
search
(
'ON\s(.*)\.+?(.+?)\sTO'
,
grants
[
0
])
db
=
dbtb
.
group
(
1
)
tb
=
dbtb
.
group
(
2
)
# change wildcard for tables to 'all'
if
re
.
search
(
'\*'
,
tb
) :
tb
=
"all"
# display database and table name for privileges
print
"database:
%s
; table:
%s
"
% (
db
,
tb
)
# extract and display privileges for user account
# for database and table
privs
=
re
.
search
(
'GRANT\s(.+?)\sON'
,
grants
[
0
])
print
"privileges:
%s
\n
"
% (
privs
.
group
(
1
))
cur
.
close
()
cnx
.
close
()
This program does much more than the previous snippets. As a result, I've annotated it at
various points to help you understand it. Still, let's go through the key points, especially
the additions.
First, the program gets a list of user accounts, storing them in an array named
user_accounts
. Using a
for
statement, it goes through each row of
user_accounts
to extract each
user_account
. For each, it prints a heading to dis-
play the user account to the administrator. This part is similar to the previous excerpts.
We then put a new SQL statement,
SHOW GRANTS
, in
sql_stmnt
for each
user_account
. We execute and then use another
for
statement to go through the res-
