This discussion is archived
3 Replies Latest reply: Aug 29, 2013 12:23 PM by 991805 RSS

DDL operations of all objects of a schema to a role

991805 Newbie
Currently Being Moderated

Hi Gurus,

 

Can someone please let me know how to grant the DDL operartions like drop,alter etc to a role for all the objects in a schema. Please let me know ASAP this is very urgent ?

  • 1. Re: DDL operations of all objects of a schema to a role
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    You can do something like that

    SQL> create user t identified by test;

     

     

    User created.

     

     

    SQL> create role myrole;

     

     

    Role created.

     

     

    SQL> grant create table,create session to myrole;

     

     

    Grant succeeded.

     

     

    SQL> grant myrole to t;

     

     

    Grant succeeded.

     

     

    SQL> conn t/test

    Connected.

    SQL> create table t_1 (id number);

     

     

    Table created.

     

     

     

     

    SQL>  alter table t_1 add (name varchar2(10));

     

     

    Table altered.

     

     

    SQL> drop table t_1 purge;

     

     

    Table dropped.

    For more object related details you can check this:GRANT

     

    HTH

  • 2. Re: DDL operations of all objects of a schema to a role
    Justin Cave Oracle ACE
    Currently Being Moderated

    Are you trying to give a user the ability to execute DDL in that user's schema?  Or to execute DDL in some other user's schema?

     

    DK2010 showed you how to give a user permission to execute DDL in their own schema.  Looking at your question, however, my guess is that you are asking about how to give a user privileges to execute DDL in some other schema.  You can't grant DDL privileges on individual objects or individual schemas.  In theory, you could grant the user appropriate ANY privileges (i.e. CREATE ANY TABLE) but that would give the user the ability to do DDL in any schema in the database.  Those are extremely powerful privileges that should only be granted to DBAs.

     

    If you are trying to give a user the ability to execute DDL against a single different schema, can you explain the business problem that you are trying to solve?  There are probably other ways of solving whatever business problem you have.

     

    Justin

  • 3. Re: DDL operations of all objects of a schema to a role
    991805 Newbie
    Currently Being Moderated

    Hi Justin,

     

    i was able to figure out that it cannot be done

     

    Thank you

Legend

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