Forum Stats

  • 3,757,058 Users
  • 2,251,192 Discussions
  • 7,869,716 Comments

Discussions

Is it possible to restrict REPLACE command?

T101_cyberdyne
T101_cyberdyne Member Posts: 54
edited Jun 17, 2014 12:53AM in General Database Discussions

Hi all,

Im currently using oracle DB 11gR2. Currently i have my main schema which is db1 and i have created another schema db2. I would like to allow certain privileges to db2 to access db1. In my case case, db2 can only create or view any table or view in db1.drop and update is restricted. I have successfully done that and all the privileges are in place.

However, when create command is allowed, db2 can create a view or a table but it can also use the 'OR REPLACE' command. This brings a problem for me if it can replace any of the views in db1. I would only want to give privilege to create new ones.

Would it be possible to restrict the REPLACE command?

Thanks,

T101

Tagged:

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

    IMO, the 'design" is fatally flawed.

    database objects should be static & unchanged between software version releases.

    There should NEVER be the situation where USER_A needs to CREATE object in USER_B schema.

    No Production code should ever issue CREATE statement.

  • Would it be possible to restrict the REPLACE command?

    No - the CREATE grant implies and includes the ability to DROP or REPLACE the object.

  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown

    May be by using before create on schema trigger :

    SQL> show user;

    USER is "SCOTT"

    SQL>

    CREATE OR REPLACE TRIGGER no_replace

    BEFORE CREATE

    ON SCHEMA

    DECLARE

    x user_tables.table_name%TYPE;

    n number;

    BEGIN

      SELECT upper(ora_dict_obj_name)

      INTO x

      FROM DUAL;

      SELECT count(*) into n from user_views where view_name=x;

      If n <> 0 Then

      RAISE_APPLICATION_ERROR(-20099, 'Table/View Already Exists');

      END IF;

    END no_replace;

    /

    SQL> create or replace view myemp as select * from emp;

    View created.

    SQL> create or replace view myemp as select * from emp;

    create or replace view myemp as select * from emp

                                                  *

    ERROR at line 1:

    ORA-00604: error occurred at recursive SQL level 1

    ORA-20099: Table/View Already Exists

    ORA-06512: at line 12

    SQL>

    Regards

    Girish Sharma

This discussion has been closed.