User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA.
HR is an existing schema in the database.
SCOTT creates the following trigger:
CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object’); END:
SCOTT does not grant the execute privilege on this trigger to any other users. For which user(s) would this trigger fire by default when they drop an object in the hr schema?
A.
Only HR
B.
SCOTT and HR
C.
Only SCOTT
D.
SCOTT, HR, and SYS
Why choose A?
Why not C? help!!
Because SCOTT can only create a trigger. And he created it on HR schema (=user HR)
HR schema(user) has rights to execute it.
http://docs.oracle.com/cd/B28359_01/server.111/b28318/triggers.htm#CNCPT017
A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.
A
A is correct ans bcs Scott has only create trigger and alter table rights.
And even if drop table/object right is given to scott then also the object gets dropped but the trigger does not get fire.
It is fired only when HR drops the object.
Ans: A
Proof:
SQL> connect scot/tiger
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scot
SQL>
SQL> CREATE OR REPLACE TRIGGER drop_trigger
2 BEFORE DROP ON hr.SCHEMA
3 BEGIN
4 RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object’); END;
5 /
Trigger created
SQL> create table emp (dat number);
Table created
SQL> drop table emp;
Table dropped
SQL> connect hr/hr
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as hr
SQL> drop table emp;
drop table emp
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Cannot drop object
ORA-06512: at line 2
SQL>
A
A
HI All,
I have 1z0-144 exam on 13th of this month, can someone help me with the questions and preparing for these 80 questions would be enough.
need guidance.
thanks
Sreema
why not d? help
I see,
DATABASE — The trigger fires whenever any database user initiates
the triggering event.
SCHEMA — The trigger is created on a schema and fires whenever the
user who owns it is the current user and initiates the triggering event.
For Schema, only “the schema owner intiates the trigger event” can fire the trigger. Even though Scott has the drop permission on HR schema, “Scott drop one HR table” cannot fire the trigger.
Thanks manjusha