Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Just when you think you have it

    I am creating a trigger on a table in another schema. That schema belongs to a role which has the SELECT_CATALOG_ROLE. If I log in as that user:

    Code:
    SQL> connect facet@dev
    Enter password: ******
    Connected.
    
    SQL> desc dba_role_privs
     Name                                      Null?    Type
     ----------------------------------------- -------- ------------------
     GRANTEE                                            VARCHAR2(30)
     GRANTED_ROLE                              NOT NULL VARCHAR2(30)
     ADMIN_OPTION                                       VARCHAR2(3)
     DEFAULT_ROLE                                       VARCHAR2(3)
    
    SQL> select * 
      2  from dba_role_privs
      3  where grantee = USER;
    
    GRANTEE                        GRANTED_ROLE                   ADM DEF
    ------------------------------ ------------------------------ --- ---
    FACET                          FARBT                  NO  YES
    FACET                          FAOBT               NO  YES
    But when I write a trigger which references this table, I get a 'PL/SQL: ORA-00942: table or view does not exist' error during compile.

    Could someone help me understand why?
    -Chuck

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The schema for the trigger must be directly granted the rights. Rights granted through roles arn't available to the trigger.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Is there a place which would describe this again?

    My goal was to have only particular TRIGGER code fire for people that belong to a specific role. I tried using USER_ROLE_PRIVS first, but the TRIGGER executes under the rights of the SCHEMA owner, rather than the actual USER in the database.

    Would I be approaching this correctly by granting select on DBA_ROLE_PRIVS to the schema owner, and using that table to perform something in the TRIGGER like:

    Code:
    ...
      SELECT count(*)
      into WS_IS_DATA_ENTRY_USER
      FROM DBA_ROLE_PRIVS
      WHERE GRANTEE = USER AND GRANTED_ROLE = 'FAORBT';
    
      IF WS_IS_DATA_ENTRY_USER > 0 then
         :new.DIVISION_CODE := substr(WS_NEW_LOC,1,2);
         return;
      end if;
    ...
    Or is there a better strategy?
    -Chuck

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    ...
    SELECT count(*)
    into WS_IS_DATA_ENTRY_USER
    FROM USER_ROLE_PRIVS
    WHERE GRANTED_ROLE = 'FAORBT';

    IF WS_IS_DATA_ENTRY_USER > 0 then
    :new.DIVISION_CODE := substr(WS_NEW_LOC,1,2);
    return;
    end if;
    ...

    The view USER_ROLE_PRIVS is viewable by the user and shows only their assigned roles. You can look at the roles, you just can't be assigned priviledges in them to your trigger.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I tried that approach first, but USER_ROLE_PRIVS is filtered to those objects owned by the TRIGGER owner, rather than the user inserting the records:

    Code:
    SQL> connect forbesc@dev
    Enter password: *************
    Connected.
    
      1  CREATE TABLE developer.priv
      2  (username VARCHAR2(30),
      3*  granted_role VARCHAR2(30))
    SQL> /
    
    Table created.
    
      1  CREATE TABLE developer.ins_test
      2* (f1 NUMBER(1))
    SQL> /
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER developer.ins_test_bt
      2  BEFORE
      3  INSERT OR UPDATE OR DELETE
      4  ON developer.ins_test
      5  FOR EACH ROW
      6  DECLARE
      7  
      8      CURSOR cur IS
      9      SELECT username, granted_role
     10        FROM USER_ROLE_PRIVS;
     11      
     12  BEGIN
     13    
     14      FOR a_rec IN cur
     15      LOOP
     16          INSERT INTO developer.priv VALUES (a_rec.username, a_rec.granted_role);
     17      END LOOP;  
     18  END;
     19  /
    
    Trigger created.
    
    SQL> select username, granted_role
      2  from user_role_privs;
    
    USERNAME                       GRANTED_ROLE
    ------------------------------ ------------------------------
    FORBESC                        CKR_REPOS_MANAGER
    FORBESC                        DBA
    PUBLIC                         PLUSTRACE
    
    SQL> INSERT INTO developer.ins_test VALUES (7);
    
    1 row created.
    
    SQL> SELECT * FROM developer.priv;
    
    USERNAME                       GRANTED_ROLE
    ------------------------------ ------------------------------
    DEVELOPER                      IMD_SCHEMA_OWNER_ROLE
    PUBLIC                         PLUSTRACE
    Did I do something wrong during my testing?

    -Chuck
    Last edited by chuck_forbes; 03-07-05 at 17:04.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I also tried to set up a package with authid current_user, but it still returns the name of the schema owner in which the TRIGGER resides:

    Code:
    CREATE OR REPLACE PACKAGE SHARED.SHARED_CURRENT_USER_PG 
      AUTHID CURRENT_USER
    AS
    
      FUNCTION WHO_AM_I RETURN VARCHAR2;
    
    END SHARED_CURRENT_USER_PG;
    /
    
    
    CREATE OR REPLACE PACKAGE BODY SHARED.SHARED_CURRENT_USER_PG 
    AS
    	FUNCTION WHO_AM_I RETURN VARCHAR2
    	is
    		myname varchar2(30);
    	begin
    		select distinct username
    		into myname
    		FROM USER_ROLE_PRIVS
    		where username <> 'PUBLIC';
    
    		return myname;
    	
    	END WHO_AM_I;
    
    END SHARED_CURRENT_USER_PG;
    -Chuck

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In the newer versions of Oracle (YMMV) Oracle is able to differentiate between definer's rights & invoker's rights.
    As usual, head over to http://asktom.oracle.com for execellent examples

    http://asktom.oracle.com/pls/ask/f?p...:1035431863958

    http://asktom.oracle.com/pls/ask/f?p...:1523186194900
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    I thought I'd set up the PACKAGE I mentioned above using invoker's rights, since it includes the AUTHID CURRENT_USER clause. However, when I call this package from the table trigger, I get back the schema owner name, rather than the name of the invoker.

    Like I titled this, as soon as you thought you had it figured out ...

    -Chuck

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Privs acquired via roles do NOT apply within PL/SQL procedures!
    You can test SQL using SQL*Plus by first issuing
    SET ROLE NONE.
    If the SQL performs as desired then it will succeed within PL/SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    I think that what's happening is that the TRIGGER is running using definer's rights, so that when my function (see WHO_AM_I above) is called during the trigger, the AUTHID CURRENT_USER is working, it's picking up the TRIGGER owner, because by that point Oracle believes the CURRENT_USER to be the TRIGGER owner.

    I wouldn't expect that, but that's what I'm seeing

    Code:
    SQL> CREATE TABLE developer.test_table (f1 VARCHAR2(10));
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER developer.test_table_bt
      2  BEFORE
      3  INSERT
      4  ON developer.test_table
      5  FOR EACH ROW
      6  BEGIN
      7    DBMS_OUTPUT.PUT_LINE(SHARED.SHARED_CURRENT_USER_PG.WHO_AM_I);
      8  END;
      9  /
    
    Trigger created.
    
    SQL> set serveroutput on
    SQL> BEGIN
      2    DBMS_OUTPUT.PUT_LINE(SHARED.SHARED_CURRENT_USER_PG.WHO_AM_I);
      3  END;
      4  /
    FORBESC
    
    PL/SQL procedure successfully completed.
    
    SQL> INSERT INTO developer.test_table VALUES ('dummy');
    DEVELOPER
    
    1 row created.
    In this case, I don't need to user's role to be in effect, as much as I need to just check what role they are in so that I can conditionally perform something. I don't mind setting up a package people can call which uses the DBA_ROLE_PRIVS, I just didn't want to add more complexity to the system by tracking the rights to the DBA_ROLE_PRIVS view that I'll end of supplying to the schema which owns the package.

    Anyone seen an alternative?

    -Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •