root/livinglogic.python.xist/src/ll/orasql/scripts/oragrant.py @ 4450:d995326e7fde

Revision 4450:d995326e7fde, 5.3 KB (checked in by Walter Doerwald <walter@…>, 8 years ago)

Add oragrant example.

Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4## Copyright 2005-2011 by LivingLogic AG, Bayreuth/Germany.
5## Copyright 2005-2011 by Walter Dörwald
6##
7## All Rights Reserved
8##
9## See orasql/__init__.py for the license
10
11
12"""
13Purpose
14-------
15
16``oragrant`` prints all existing grants in an Oracle database schema.
17It can also be used to execute these grant statements directly.
18
19
20Options
21-------
22
23``oragrant`` supports the following options:
24
25    ``connectstring``
26        An Oracle connectstring.
27
28    ``-v``, ``--verbose`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
29        Produces output (on stderr) while to datebase is read or written.
30
31    ``-c``, ``--color`` : ``yes``, ``no`` or ``auto``
32        Should the output (when the ``-v`` option is used) be colored. If ``auto``
33        is specified (the default) then the output is colored if stderr is a
34        terminal.
35
36    ``-x``, ``--execute`` : connectstring
37        When the ``-x`` argument is given the SQL script isn't printed on stdout,
38        but executed in the database specfied as the ``-x`` argument.
39
40    ``-k``, ``--keepjunk`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
41        If given, database objects that have ``$`` or ``SYS_EXPORT_SCHEMA_`` in
42        their name will be skipped (otherwise these objects will be included).
43
44    ``-i``, ``--ignore`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
45        If given, errors occuring while the database is read or written will be
46        ignored.
47
48    ``-m``, ``--mapgrantee`` : Python expression: ``list`` or ``dict``
49        A Python ``dict`` or ``list`` literal which will be evaluated. If the
50        grantee is not in this list (or dictionary) no grant statement will be
51        returned. If it's a dictionary and the grantee exists as a key, the
52        privilege will be granted to the user specified as the value instead of
53        the original one. The default is to grant all privileges to the original
54        grantee.
55
56    ``-e``, ``--encoding`` : encoding
57        The encoding of the output (if ``-x`` is not given; default is ``utf-8``).
58
59
60Example
61-------
62
63Grant all privileges that ``alice`` has in the schema ``user@db`` to ``bob`` in
64``user2@db2``::
65
66    $ oragrant user/pwd@db -x user2/pwd2@db2 -m '{"alice": "bob"}' -v
67"""
68
69
70import sys, os, argparse
71
72from ll import misc, astyle, orasql
73
74
75__docformat__ = "reStructuredText"
76
77
78s4warning = astyle.Style.fromenv("LL_ORASQL_REPRANSI_WARNING", "red:black")
79s4error = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ERROR", "red:black")
80s4connectstring = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CONNECTSTRING", "yellow:black")
81s4object = astyle.Style.fromenv("LL_ORASQL_REPRANSI_OBJECT", "green:black")
82
83
84def main(args=None):
85    p = argparse.ArgumentParser(description="Print (and execute) grants statements from an Oracle database schema")
86    p.add_argument("connectstring", help="Oracle connect string")
87    p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
88    p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
89    p.add_argument("-x", "--execute", metavar="CONNECTSTRING2", dest="execute", help="Execute in target database")
90    p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action="store_true")
91    p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction)
92    p.add_argument("-m", "--mapgrantee", dest="mapgrantee", help="Map grantees (Python expression: list or dict)", default="True")
93    p.add_argument("-e", "--encoding", dest="encoding", help="Encoding for output (default %(default)s)", default="utf-8")
94
95    args = p.parse_args(args)
96
97    if args.color == "yes":
98        color = True
99    elif args.color == "no":
100        color = False
101    else:
102        color = None
103    stdout = astyle.Stream(sys.stdout, color)
104    stderr = astyle.Stream(sys.stderr, color)
105
106    connection = orasql.connect(args.connectstring)
107
108    if args.execute:
109        connection2 = orasql.connect(args.execute)
110        cursor2 = connection2.cursor()
111        term = False
112    else:
113        term = True
114
115    cs1 = s4connectstring(connection.connectstring())
116    if args.execute:
117        cs2 = s4connectstring(connection2.connectstring())
118
119    mapgrantee = eval(args.mapgrantee)
120
121    def keep(obj):
122        if args.keepjunk:
123            return True
124        if "$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_"):
125            return False
126        return True
127
128    for (i, obj) in enumerate(connection.iterprivileges(schema="user")):
129        keepobj = keep(obj)
130        if args.verbose:
131            if args.execute:
132                msg = astyle.style_default("oragrant.py: ", cs1, " -> ", cs2, ": fetching/granting #{}".format(i+1))
133            else:
134                msg = astyle.style_default("oragrant.py: ", cs1, " fetching #{}".format(i+1))
135            msg = astyle.style_default(msg, " ", s4object(str(obj)))
136            if not keepobj:
137                msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
138            stderr.writeln(msg)
139
140        if keepobj:
141            ddl = obj.grantddl(connection, term, mapgrantee=mapgrantee)
142            if ddl:
143                if args.execute:
144                    try:
145                        cursor2.execute(ddl)
146                    except orasql.DatabaseError, exc:
147                        if not args.ignore or "ORA-01013" in str(exc):
148                            raise
149                        stderr.writeln("oragrant.py: ", s4error("{}: {}".format(exc.__class__.__name__, str(exc).strip())))
150                else:
151                    stdout.writeln(ddl.encode(args.encoding))
152                    stdout.writeln()
153
154
155if __name__ == "__main__":
156    sys.exit(main())
Note: See TracBrowser for help on using the browser.