CODEWITHSUNDEEP
oracle-databaseplsqltriggerssql-grant
Josh
Josh

Reputation: 105

How to grant trigger access to a table

I'm getting an error message for a trigger I've successfully created.

I've tried...

GRANT SELECT ON OTHER_TABLE TO [me];
call SYS.DBA_ASSIST.GRANT_OBJ_PERMS('dbo.MYTABLE','SELECT','dbo.OTHER_TABLE');`

Here is the code for the trigger...

  CREATE OR REPLACE TRIGGER PREVENT_INVALID_ID
  BEFORE INSERT OR UPDATE ON dbo.MYTABLE
  FOR EACH ROW
  DECLARE ROW_COUNT NUMBER;
  BEGIN
  SELECT COUNT(*) INTO ROW_COUNT 
         FROM [OTHER_TABLE] WHERE OTHER_TABLE_ID = :new.MYTABLE_ID;
  IF ROW_COUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20101, 'The ID provided is invalid.');
    END IF;
  END;`

The error message in user_errors says, "PL/SQL: ORA-00942: table or view does not exist"

Screen shot

Any idea how I can get the trigger to access OTHER_TABLE?

I understand that it is not possible to grant access/authorizations to triggers. But I'm unclear what code I need to run in order to allow the trigger to work.

Thanks in advance, josh

Upvotes: 0

Views: 3635

Answers (2)

Josh
Josh

Reputation: 105

Figured it out: GRANT SELECT ON OTHER_TABLE TO dbo

What threw me off is the use the words 'table owner' when I was looking for instructions on how to do this. I thought I was the table owner. Rather, it's the schema (dbo) that needs the privileges.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

If both MYTABLE and OTHER_TABLE belong to the same user, no privilege is required:

SQL> show user
USER is "SCOTT"
SQL> create table other_table (other_Table_id number);

Table created.

SQL> create table mytable (mytable_id number);

Table created.

SQL> create or replace trigger prevent_invalid_id
  2    before insert or update on mytable
  3    for each row
  4  declare
  5    row_count number;
  6  begin
  7    select count(*) into row_count
  8    from other_table where other_table_id = :new.mytable_id;
  9
 10    if row_count = 0 then
 11       raise_application_error(-20101, 'The ID provided is invalid');
 12    end if;
 13  end;
 14  /

Trigger created.

SQL>

However, if they belong to different user, then OTHER_TABLE's owner has to grant SELECT privilege to me. Though, that's not enough - you have to either precede OTHER_TABLE with its owner name (e.g. other_user.other_table), or create a synonym in my own schema which points to other user's OTHER_TABLE. For example:

SQL> drop table other_table;

Table dropped.

SQL> connect mike/lion@xe
Connected.
SQL> create table other_table (other_Table_id number);

Table created.

SQL> grant select on other_table to scott;

Grant succeeded.

SQL> connect scott/tiger@xe
Connected.
SQL> create or replace trigger prevent_invalid_id
  2    before insert or update on mytable
  3    for each row
  4  declare
  5    row_count number;
  6  begin
  7    select count(*) into row_count
  8    from MIKE.other_table where other_table_id = :new.mytable_id;
  9
 10    if row_count = 0 then
 11       raise_application_error(-20101, 'The ID provided is invalid');
 12    end if;
 13  end;
 14  /

Trigger created.

SQL>

Note line 8: MIKE.other_table.

Just to show what happens if you omit/remove owner's name:

SQL> l8
  8*   from MIKE.other_table where other_table_id = :new.mytable_id;
SQL> c/mike.//
  8*   from other_table where other_table_id = :new.mytable_id;
SQL> l
  1  create or replace trigger prevent_invalid_id
  2    before insert or update on mytable
  3    for each row
  4  declare
  5    row_count number;
  6  begin
  7    select count(*) into row_count
  8    from other_table where other_table_id = :new.mytable_id;
  9
 10    if row_count = 0 then
 11       raise_application_error(-20101, 'The ID provided is invalid');
 12    end if;
 13* end;
SQL> /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER PREVENT_INVALID_ID:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
5/8      PL/SQL: ORA-00942: table or view does not exist
SQL>

See? ORA-00942.

Upvotes: 1

Related Questions