7 Replies Latest reply: Oct 12, 2012 3:51 PM by Kodiak_Seattle RSS

    table permission question

    Kodiak_Seattle
      Is there any way for me to create a Table inside of another users schema ?

      If I ask that person for permissions, and technically could they issue some Grant statement that would allow
      me to create/drop/insert/upadte a table within their own schema ?

      the SQL would be kicked off in my schema ?

      just wondering, is all :- )

      using 11G R2

      thanks!
        • 1. Re: table permission question
          sb92075
          Kodiak_Seattle wrote:
          Is there any way for me to create a Table inside of another users schema ?

          If I ask that person for permissions, and technically could they issue some Grant statement that would allow
          me to create/drop
          above is DDL & requires different privileges than the DML below
          /insert/upadte a table within their own schema ?

          the SQL would be kicked off in my schema ?
          Theoretically YES, but don't do that which is not Best Practices
          • 2. Re: table permission question
            Frank Kulash
            Hi,
            Kodiak_Seattle wrote:
            Is there any way for me to create a Table inside of another users schema ?
            A user with the CREATE ANY TABLE system privilege can create a table in any schema, including the SYS schema. You want to be so careful giving this privilege that you porobably never do it.
            If I ask that person for permissions, and technically could they issue some Grant statement that would allow
            me to create/drop/insert/upadte a table within their own schema ?

            the SQL would be kicked off in my schema ?
            They could write a stored procedure with AUTHID DEFINER, and use EXECUTE IMMEDIATE to have the procedure create (and/or drop) the table(s). They can then give you EXECUTE privileges on the procedure. Anything done inside the procedure will be done with the owner's privileges, not yours.

            The table owner can give you INSERT and UPDATE privileges, so you can do those thing directly, but sometimes the stored procedure approach is handy for INSERT and UPDATE, too. Of course, you don't need EXECUTE IMMEDIATE to do those things in PL/SQL.
            • 3. Re: table permission question
              Kodiak_Seattle
              DDL & DML :-)

              what is the Theoretical answer ?

              :-)
              • 4. Re: table permission question
                sb92075
                Kodiak_Seattle wrote:
                DDL & DML :-)

                what is the Theoretical answer ?

                :-)
                see Frank's answer
                • 5. Re: table permission question
                  Kodiak_Seattle
                  Cool, thanks!

                  learning!
                  • 6. Re: table permission question
                    rp0428
                    >
                    the SQL would be kicked off in my schema ?
                    >
                    No - SQL is not 'kicked off' in any schema. It is executed in a user's session. However a user executes the SQL it executes in that user's session.
                    • 7. Re: table permission question
                      Kodiak_Seattle
                      Thanks for all tips and clearing it up!