<div>
The only way is the 'any' addition on the grant and I would NOT suggest doing this.<br />
<br />
for example:<br />
grant alter any table to someuser;<br />
<br />
The danger is ANY means ANY, even SYSTEM and SYS tables.<br />

</div>


<div>
I dont want to grant create any table, without this there is any solution.
</div>


<div>
I am not sure how to do it, but &nbsp;an idea, <br />
<br />
see if you can achieve this by giving grant ANY, and create a schema level event trigger to restrict user from dropping table other than from the schema intended.
</div>


<div>
yes, you are right.<br />
&nbsp;without granting individually on a table or index name. there is any way.
</div>


<div>
I've not heard of any way without the smoke and mirrors the other Experts are saying.<br />
<br />
You'll likely need to write code to do this.<br />
<br />
Can I ask what the danger is in just letting the user log in to schemaA directly? &nbsp;You are wanting to give them drop table privs, there isn't that much more damage they could do.<br />
<br />
Giving individual grants isn't that bad unless there is a LOT of activity on creating new tables and indexes.
</div>


<div>
the database ddl trigger might look something like this...<br />

<pre><code class="code-10 nolinks" id="code-8190639"><span>CREATE OR REPLACE TRIGGER check_ddl</span>
<span>    BEFORE DDL</span>
<span>    ON DATABASE</span>
<span>BEGIN</span>
<span>    IF ora_login_user != 'YOURUSER'</span>
<span>    THEN</span>
<span>        RETURN;   -- the trigger won't interfere with other privileged users</span>
<span>    END IF;</span>
<span></span>
<span>    IF ora_dict_obj_owner NOT IN ('ALLOWED_SCHEMA', 'OTHER_ALLOWED_SCHEMA')</span>
<span>    THEN</span>
<span>        raise_application_error(-20001, ora_login_user || ' may not modify this schema');</span>
<span>    END IF;</span>
<span>END;</span>
</code></pre>
</div>


<div>
&gt;&gt;the database ddl trigger might look something like this... <br />
<br />
You sure you wand a DATABASE level not a SCHEMA level?<br />
<br />
If I read the sample trigger you posted, that would prevent other schemas, say schemaQ (Or SYS/SYSTEM) from altering their own objects since userB and schemaA are the only ones in the code.
</div>


<div>
 note the comment in the first IF<br />
<br />
if the user is NOT &quot;YOURUSER&quot; &nbsp;then return, &nbsp;so, if you are not the droids we're looking for, then move on.<br />
<br />
<br />
if you ARE the droids we're looking for, then check the schema you're trying to modify to see if you're allowed. &nbsp;If it's not in the approved list, then error other wise let it go through.
</div>


<div>
I am looking for schema level.
</div>


<div>
if you try to create a DDL trigger at the schema level, &nbsp;it doesn't really do anything, it compiles, but has no effect<br />
<br />
I just tested it too<br />
With schema trigger in place I was able to create and drop other schema objects, with database I wasn't<br />
<br />
<br />
also, if it did, you'd have to create a trigger for every schema
</div>


<div>
&gt;&gt;&gt; I am looking for schema level. <br />
<br />
yes, we know<br />
<br />
what we're telling you is there is no such thing as a &quot;schema level privilege&quot;<br />
<br />
rather than giving up because it's not possible in a literal sense, we're suggesting functionally equivalent alternatives.<br />
<br />
wrap the operations in a secured set of procedures and grant access to those<br />
or<br />
grant ANY privileges and use the database ddl trigger I posted above to block access to all schemas except the ones you want<br />

</div>


<div>
as another alternate<br />
<br />
you can create a &quot;schema level&quot; ddl trigger on the user that you want to control, &nbsp;not the schemas you want to protect &nbsp;(maybe this is what slightwv meant, &nbsp;I assumed the other way)<br />
<br />
BUT &nbsp;you still need to grant the ANY privilege to that user<br />
AND you still need to do the schema checks inside that trigger just as you did for the database trigger<br />
<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-36155299-1"><span>CREATE OR REPLACE TRIGGER check_ddl</span>
<span>    BEFORE DDL</span>
<span>    ON YOURUSER.schema</span>
<span>BEGIN   </span>
<span>    IF ora_dict_obj_owner NOT IN ('ALLOWED_SCHEMA', 'OTHER_ALLOWED_SCHEMA')</span>
<span>    THEN</span>
<span>        raise_application_error(-20001, 'YOURUSER may not modify this schema');</span>
<span>    END IF;</span>
<span>END;</span>
</code></pre>
</div>


<div>
if you go with the &quot;schema level&quot; ddl trigger I posted, you'll need one for every user you want to control.<br />
<br />
Instead, I'd use the database level trigger. &nbsp;If you make multiple triggers then you have to maintain them and more maintenance means more opportunity to get them wrong. &nbsp;<br />
<br />
getting security wrong isn't acceptable. &nbsp;you can't guarantee you'll always get everything right, so at least make it easier on yourself by consolidating the rules in one place.
</div>


<div>
&gt;&gt;getting security wrong isn't acceptable. <br />
<br />
I cannot agree more. &nbsp;This is why I would not risk granting ANY to a user just in case the trigger get's messed up/deleted/disabled/???.<br />
<br />
I'm still waiting for the real reason behind this question.<br />
<br />
I have a SQL script for my setup that has all the grants for all my tables. &nbsp;When I add a table, I add rows to my grant script. &nbsp;Short/sweet and simple.
</div>


<div>
also, if you try the schema level, you'll have to jump through some extra hoops to make sure the user can modify his/her own objects, &nbsp;but NOT that particular trigger, otherwise it's not really secure if the user could simply disable or drop the trigger.<br />
<br />
database level is safest as well as easiest
</div>


<div>
&gt;&gt;&gt; database level is safest as well as easiest <br />
<br />
when I said this, I was referring to using a trigger based approach to simulate what you are trying to do.<br />
database is safest/easiest in this context<br />
<br />
<br />
even safer is to not try at all and simply use the normal grants and restrictions rather than create exceptions.
</div>


<div>
You can create a ROLE containg all required privilage, ALTER ALL TABLE, EXEXCUTE ALL PROCEDURE, add any required privilages as required.<br />
<br />
Grant this ROLE to you prefered user.<br />
<br />
this is the optimum structure.
</div>


<div>
<div class="content wysiwyg-content">
Hi Guys,<br />
can I grant access to a particular user for any particular schema, <br />
like alter table, alter index, drop table, drop index, without the table or index names.
</div>
</div>


Hi Guys,
can I grant access to a particular user for any particular schema, 
like alter table, alter index, drop table, drop index, without the table or index names.

alter table , drop, select,  and alter index,drop, select

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.