6 Replies Latest reply: Nov 23, 2012 9:47 AM by khallas301 RSS

    Spool all privileges of a particular user

    khallas301
      I am trying cleaner and siple way to spool out all privileges of a user "ABC" in a way that it generates grant statement for them..

      searched OTN and google this but found too many different scripts...

      appreciate all help....
        • 1. Re: Spool all privileges of a particular user
          phaeus
          Hello,
          you can use something like
          dbms_metadata.get_granted_ddl with the option OBJECT_GRANT,SYSTEM_GRANT,ROLE_GRANT and TABLESPACE_QUOTA

          this should create you the script you need.

          regards
          Peter
          • 2. Re: Spool all privileges of a particular user
            khallas301
            Yes.. thanks for that.. I have used that and already created role, sys & obj level script but will it be enough?

            do we need column level grant or more fine detailed privileges on each objects?

            just looking for script which doesn't use dbms_metadata.get functionality...
            • 3. Re: Spool all privileges of a particular user
              Girish Sharma
              Your answer is download this script :
              find_all_privs.sql
              Writer : Pete Finnigan
              http://www.petefinnigan.com/find_all_privs.sql

              Regards
              Girish Sharma
              • 4. Re: Spool all privileges of a particular user
                khallas301
                Thanks Girish... this script does came to my search result but wanted more simpler script which produce similar result and also Pete Finnigan script has his copyright thing which I don't want to use in my prod env...hope you understand...

                btw do we have any difference in result if we use Pete script and below dbms_metadata function...

                dbms_metadata.get_granted_ddl with the option OBJECT_GRANT,SYSTEM_GRANT,ROLE_GRANT and TABLESPACE_QUOTA
                • 5. Re: Spool all privileges of a particular user
                  Girish Sharma
                  The script has this text :
                  -- -----------------------------------------------------------------------------
                  --  Usage      : The script provided here is available free. You can do anything 
                  --               you want with it commercial or non commercial as long as the 
                  --               copyrights and this notice are not removed or edited in any way. 
                  --               The scripts cannot be posted / published / hosted or whatever 
                  --               anywhere else except at www.petefinnigan.com/tools.htm
                  -- -----------------------------------------------------------------------------
                  If Pete is reading this thread he may comment on it, whether you can use commercially in your production db or not, but it seems me Ok. As far as I understand, text of script can not be posted without prior permission, but you can use it.

                  But, please don't take my comments, if Pete rejects it.

                  As far as concern of simplicity, script is simple enough in view of required output.

                  Below link may also be of your interest :
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:494205100346718343

                  Regards
                  Girish Sharma
                  • 6. Re: Spool all privileges of a particular user
                    khallas301
                    Thank you for your comments and quick response..

                    I think i will create something of my own and also improve my knowledge...

                    If I want to spool all privs of particular user then is below enough or still I am missing column level or other privs?

                    DBMS_METADATA.GRANTED_DDL ('ROLE_GRANT'___
                    DBMS_METADATA.GRANTED_DDL ('SYSTEM_GRANT'___
                    DBMS_METADATA.GRANTED_DDL ('OBJECT_GRANT'___