This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,147 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Delayed Statements ...

Mahesh Pednekar
Mahesh Pednekar MrMember Posts: 34
edited Apr 28, 2016 11:02PM in Database Ideas - Ideas

Many a time during my development I felt a requirement of a statement that gets executes not immediately but after  some n minutes/ seconds.

for example..

a. I want to grant a privilege on a table to a user but the table is not yet created.

b. I want to update or insert in a table but the table may not exist.

c. I want to auto grant privilege for any object that will be created in future. etc...

In all the above transaction I know that the table/ Object  is going to be created in future, but I want to perform the transaction now.

Also I don't want to write a job or a trigger because that may fail due to some unknown condition and disturb the further dependencies.

So there should exist some clauses (e.g. AFTER n)  that may fire some implicit job like objects to execute the command in 'n' time.

So our commands works like : -

a. grant select on scott.emp to tom AFTER 5;

The above command release the control immediately but gets executed in background after 5 seconds.

similarly we can have : -

b. insert into scott.emp....  AFTER 5;

c. update scott.emp ... AFTER 5;

This is somthing like FORCE clause in views where we can create a view even if we don't have the table.

Mahesh Pednekarctrieb
12 votes

Active · Last Updated

«1

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    step 1 - Create the object

    step 2 - Grant the privileges

    So you put the above in a script so it's done in order.

    I can't think of a reason why you would want to do this in reverse order?

  • Mahesh Pednekar
    Mahesh Pednekar Mr Member Posts: 34

    step 1 - Create the object

    step 2 - Grant the privileges

    So you put the above in a script so it's done in order.

    I can't think of a reason why you would want to do this in reverse order?

    Can you explain why FORCE option is there in view creation. Views can be created any time  after creation of the table. Then Why one should create a View with FORCE option before the base table.

    So What I want to explain here is some times the logics are built in reverse order. some times you are sure about the future activities and logics and not yet finalized about the current ones.

    Also I will give you an example.

    In people soft application all objects are put in sysadm schema.

    Developers are not been given direct access to the schema. They are been given read/write access in there dev schema.

    Now if these developers alter the table structure then they loose the access. so then have to run each time towards the admin to get the grant.

    Now If I want to automate this activity then I have to write a trigger like

    create trigger ...after alter....

    begin

    grant select, insert..... on ...

    end;;

    but the the above trigger fails because  grant cannot be given to the object  whose create statement is still in transit. The trigger fails with object lock error.

    Now here is the requirement we can say Grant select, inser... on ..AFTER 5;

    So as soon as the grant release the control the trigger gets completed and the table gets created. and after 5 seconds the grant will be given.

    So this an example of real time grant.

  • Chris Hunt
    Chris Hunt Freelance Oracle Consultant Leicester, UKMember Posts: 2,066 Gold Trophy

    If you don't know whether the object exists now, how can you be sure that it will exist in five seconds time? What if the object still doesn't exist after five seconds, how would you report the error and what action would you take?

    If you really want to schedule something to happen at some time in the future, use DBMS_SCHEDULER.

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    Can you explain why FORCE option is there in view creation. Views can be created any time  after creation of the table. Then Why one should create a View with FORCE option before the base table.

    So What I want to explain here is some times the logics are built in reverse order. some times you are sure about the future activities and logics and not yet finalized about the current ones.

    Also I will give you an example.

    In people soft application all objects are put in sysadm schema.

    Developers are not been given direct access to the schema. They are been given read/write access in there dev schema.

    Now if these developers alter the table structure then they loose the access. so then have to run each time towards the admin to get the grant.

    Now If I want to automate this activity then I have to write a trigger like

    create trigger ...after alter....

    begin

    grant select, insert..... on ...

    end;;

    but the the above trigger fails because  grant cannot be given to the object  whose create statement is still in transit. The trigger fails with object lock error.

    Now here is the requirement we can say Grant select, inser... on ..AFTER 5;

    So as soon as the grant release the control the trigger gets completed and the table gets created. and after 5 seconds the grant will be given.

    So this an example of real time grant.

    Mahesh Pednekar wrote:
    
    ...
    
    Developers are not been given direct access to the schema. They are been given read/write access in there dev schema.
    Now if these developers alter the table structure then they loose the access. so then have to run each time towards the admin to get the grant. 
    Now If I want to automate this activity then I have to write a trigger like 
    
    create trigger ...after alter....
    begin
    grant select, insert..... on ...
    end;;
    
    but the the above trigger fails because  grant cannot be given to the object  whose create statement is still in transit. The trigger fails with object lock error.
    
    Now here is the requirement we can say Grant select, inser... on ..AFTER 5;
    So as soon as the grant release the control the trigger gets completed and the table gets created. and after 5 seconds the grant will be given.
    So this an example of real time grant.
    

    There are some strange ideas here.

    First of all an ALTER table statement will not remove grants on that table. Only  DROP+CREATE statement would remove existing privileges.

    Secondly a trigger is not the proper way to add privs.

    Better would be a ROLE. And the people who write the DROP+CREATE table scripts would be responsible to provide the role with the necessary privs for the table.

    Delaying statemnts just sounds like the bad workaround for an already bad codeing practice.

    No need to vote for this.

  • This is an interesting conversation that has wandered from the original idea and justification of the idea.  May I suggest we adjourn to the to continue the thread?

    Cheers,

    @Laura Ramsey-Oracle

    (your friendly DB Community Manager)

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Can you explain why FORCE option is there in view creation. Views can be created any time  after creation of the table. Then Why one should create a View with FORCE option before the base table.

    So What I want to explain here is some times the logics are built in reverse order. some times you are sure about the future activities and logics and not yet finalized about the current ones.

    Also I will give you an example.

    In people soft application all objects are put in sysadm schema.

    Developers are not been given direct access to the schema. They are been given read/write access in there dev schema.

    Now if these developers alter the table structure then they loose the access. so then have to run each time towards the admin to get the grant.

    Now If I want to automate this activity then I have to write a trigger like

    create trigger ...after alter....

    begin

    grant select, insert..... on ...

    end;;

    but the the above trigger fails because  grant cannot be given to the object  whose create statement is still in transit. The trigger fails with object lock error.

    Now here is the requirement we can say Grant select, inser... on ..AFTER 5;

    So as soon as the grant release the control the trigger gets completed and the table gets created. and after 5 seconds the grant will be given.

    So this an example of real time grant.

    The FORCE option is there to test something out temporarily.

    It's not there for any other practical purpose, as normally the base tables are created before the view.

    As has been pointed out before, there are no issues altering a table as the privileges still remain.

    Grant the privileges and they remain for the life of the object.

  • Mahesh Pednekar
    Mahesh Pednekar Mr Member Posts: 34

    If you don't know whether the object exists now, how can you be sure that it will exist in five seconds time? What if the object still doesn't exist after five seconds, how would you report the error and what action would you take?

    If you really want to schedule something to happen at some time in the future, use DBMS_SCHEDULER.

    I can explain you about the surety of the table will be created in few seconds by a flow diagram below : -

    Following is the auto grant mechanism to grant privilege to other schema so that the created need not to do it manually al the time he created new table : -

    1. User executed "Create table" statement.----> 2. An "After create" schema level trigger executes. ---> 3. Trigger runs a code " grant select on .. after 5;" ---> 4.control is released ---> 5. the table got created --->> 6. after "n" statement executes that grants the privilege.

    So in the above flow of steps at step 3, we are pretty much sure that table is going to be created in few seconds.

    regards,

  • Mahesh Pednekar
    Mahesh Pednekar Mr Member Posts: 34

    This is an interesting conversation that has wandered from the original idea and justification of the idea.  May I suggest we adjourn to the to continue the thread?

    Cheers,

    @Laura Ramsey-Oracle

    (your friendly DB Community Manager)

    Please let me know how can I adjorn this discussion to OTN Watercooler ..?

  • Mahesh Pednekar
    Mahesh Pednekar Mr Member Posts: 34

    I request every one to Vote for this idea as much as possible so the it could be implemented in Database. I am quite sure that this Idea is going to open new doors of development. 

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    edited May 5, 2016 12:14AM

    I can explain you about the surety of the table will be created in few seconds by a flow diagram below : -

    Following is the auto grant mechanism to grant privilege to other schema so that the created need not to do it manually al the time he created new table : -

    1. User executed "Create table" statement.----> 2. An "After create" schema level trigger executes. ---> 3. Trigger runs a code " grant select on .. after 5;" ---> 4.control is released ---> 5. the table got created --->> 6. after "n" statement executes that grants the privilege.

    So in the above flow of steps at step 3, we are pretty much sure that table is going to be created in few seconds.

    regards,

    Granting privileges via a trigger is just unnecessary.

    If you want users to grant privileges to objects they don't own then give them "GRANT ANY OBJECT PRIVILEGE".