This discussion is archived
7 Replies Latest reply: Oct 12, 2012 1:51 PM by Kodiak_Seattle RSS

table permission question

Kodiak_Seattle Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    DDL & DML :-)

    what is the Theoretical answer ?

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

    what is the Theoretical answer ?

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

    learning!
  • 6. Re: table permission question
    rp0428 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    Thanks for all tips and clearing it up!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points