Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Question Unanswered: Drop user procedure

    I am trying to make a procedure that drops a user. Needed to make it possible to drop a user from a list (table) using a trigger.

    The procedure I created is like this:

    CREATE OR REPLACE procedure VDS.drop_user(initialer IN varchar2)
    is PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    declare
    sqlstr varchar2(50);
    begin
    sqlstr := 'drop user ' || initialer || ' cascade';
    EXECUTE IMMEDIATE sqlstr;
    end;
    end;
    /

    The procedure is compiled with no errors or warnings, but when I call the procedure, I get a missing privileges message:

    call VDS.DROP_USER ('TMP');

    ORA-01031: insufficient privileges
    ORA-06512: at "VDS.DROP_USER", line 8

    but if I just sql this 'drop user TMP cascade', the user is dropped.

    What is the difference - and how do I get the procedure to work?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Privilege(s) granted via role are not available in stored procedure or trigger.
    The user owning / running (depending on AUTHID clause; standard is owner) must have the appropriate privilege granted directly.

    For more details, please study this AskTom thread: http://asktom.oracle.com/pls/asktom/...:1065832643319

  3. #3
    Join Date
    Jun 2003
    Posts
    81

    Smile

    Thanks

    I understand, that the DBA role is not enough. I granted 'grant drop user' from SYS and that did it.

  4. #4
    Join Date
    Jun 2003
    Posts
    81

    Red face Help still needed

    Hmm - actually that was not enough anyway. Now I can call the procedure, but not from my trigger!

    CREATE OR REPLACE TRIGGER VIEW1_DEL instead of delete ON VIEW1 for each row
    begin
    VDS.DROP_USER (ld.INITIALS);
    end;
    /

    but when I delete from the view I once again get:

    ORA-01031: insufficient privileges

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by flyboy View Post
    Privilege(s) granted via role are not available in stored procedure or trigger.
    Does the owner of that trigger have direct EXECUTE privilege on VDS.DROP_USER?
    As for DBA role, you really should not grant it to everyone.

  6. #6
    Join Date
    Jun 2003
    Posts
    81
    The user does have EXECUTE privileges granted directly - so that does not solve the problem.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kfc@vd.dk View Post
    The user does have EXECUTE privileges granted directly - so that does not solve the problem.
    Your posts do not show where is the "problem" exactly happening, so it is hard to deduce the real cause. I would recommend to examine the whole exception message to find where did the insufficient privilege occur:
    - dropping the user
    - executing the procedure
    - deleting from the view
    - ... (whatever you do you did not post yet)

  8. #8
    Join Date
    Jun 2003
    Posts
    81
    I have created the procedure and the trigger mentioned above. I have granted execute privileges to the user. I have granted the "drop user" rights to the user. But when I run the sql "delete from view1" I get the message:
    Error at line 1

    ORA-01031: insufficient privileges
    Script Terminated on line 1.

    I can drop the user manually - also using the procedure, but not by triggering it from the delete-trigger.

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    Works for me correctly.
    Code:
    SQL> conn system/<password>
    Connected.
    SQL> grant create session, create procedure, drop user to vds;
    
    Grant succeeded.
    
    SQL> grant create session, create table, create view, create trigger, unlimited tablespace to a;
    
    Grant succeeded.
    
    SQL> conn vds/vds
    Connected.
    SQL> CREATE OR REPLACE procedure VDS.drop_user(initialer IN varchar2)
      2  is PRAGMA AUTONOMOUS_TRANSACTION;
      3  sqlstr varchar2(50);
      4  begin
      5  sqlstr := 'drop user ' || initialer || ' cascade';
      6  EXECUTE IMMEDIATE sqlstr;
      7  end;
      8  /
    
    Procedure created.
    
    SQL> grant execute on drop_user to a;
    
    Grant succeeded.
    
    SQL> conn a/a
    Connected.
    SQL> create table table1( initials varchar2(30) );
    
    Table created.
    
    SQL> create view view1 as select initials from table1;
    
    View created.
    
    SQL> CREATE OR REPLACE TRIGGER VIEW1_DEL instead of delete ON VIEW1 for each row
      2  begin
      3  VDS.DROP_USER (:old.INITIALS);
      4  end;
      5  /
    
    Trigger created.
    
    SQL> insert into view1( initials ) values ( 'test' );
    
    1 row created.
    
    SQL> delete view1;
    delete view1
           *
    ERROR at line 1:
    ORA-01918: user 'TEST' does not exist
    ORA-06512: at "VDS.DROP_USER", line 6
    ORA-06512: at "A.VIEW1_DEL", line 2
    ORA-04088: error during execution of trigger 'A.VIEW1_DEL'
    Note how whole error message looks like - it is more than a single line. In my case it states I do not have any user TEST in my database. However, I have no privileges issue.
    Also, for simplicity, I delete from the view from the same user who owns it. No prove in your post you do so or do not. Are you able to DELETE from VIEW1 without a trigger too (see my 3rd note in my previous message)?

    It is impossible to say what did you forget (maybe another trigger involved), but I would believe Oracle that you missed something.

  10. #10
    Join Date
    Jun 2003
    Posts
    81
    I can not delete from the view without an "instead-of-trigger", because my view is based on sys-tables and besides that consists of a union. Therefore I need a trigger to do something else, than just delete. My view looks as follows:

    CREATE OR REPLACE FORCE VIEW VIEW1
    (
    SYSTEM,
    GRANTEE
    )
    AS
    SELECT 'TILBUD' SYSTEM, grantee
    FROM dba_role_privs
    WHERE -- Oracle-brugere med tildelt TILBUD-rolle, men ikke i TILBUD-system
    UPPER (granted_role) LIKE 'TILBUD%'
    AND UPPER (grantee) NOT IN
    (SELECT UPPER (brugernavn) FROM tilbud.tbl_bruger)
    AND UPPER (grantee) NOT IN (SELECT UPPER (role) FROM dba_roles)
    AND NOT UPPER (grantee) = 'TILBUD'
    UNION
    SELECT 'WEBVAND' SYSTEM, grantee
    FROM dba_role_privs
    WHERE UPPER (granted_role) LIKE 'WEBVAND%'
    AND (UPPER (grantee) NOT IN
    (SELECT UPPER (user_name) FROM afvand.tksafv_brugere)
    AND NOT UPPER (grantee) = 'CAF')
    AND UPPER (grantee) NOT IN (SELECT UPPER (role) FROM dba_roles);

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kfc@vd.dk View Post
    I can not delete from the view without an "instead-of-trigger", because my view is based on sys-tables and besides that consists of a union. Therefore I need a trigger to do something else, than just delete.
    Wow, another piece of information. Maybe, in a few posts, you will also be able to post the whole error stack as I did. Or, maybe analyze it by yourself. If you are not using sqlplus for running the DELETE statement, I recommend you to do so at least for debugging purposes.
    Quote Originally Posted by kfc@vd.dk View Post
    My view looks as follows:
    I am afraid "like" is describing it as it does not contain INITIALS column. Why not post it "exactly"?
    Who owns the view? Does the owner of the view SELECT and REFERENCE privileges on all tables/views used there (I can see 2 system views and 2 user ones, but they may be more)? Who runs DELETE statement? Does he have DELETE privilege on VIEW1? Did I not post the last two questions in my previous posts?

  12. #12
    Join Date
    Jun 2003
    Posts
    81
    I tried to translate some of the danish and tried to make the code more general. Thats the reason for the difference. Below I will now try to show it all without translation. Hope that it makes sence.

    connect vds_validate/*******

    CREATE OR REPLACE FORCE VIEW VDS_VALIDATE.TJEK33
    (
    SYSTEM,
    GRANTEE
    )
    AS
    SELECT 'TILBUD' SYSTEM, grantee
    FROM dba_role_privs
    WHERE -- Oracle-brugere med tildelt TILBUD-rolle, men ikke i TILBUD-system
    UPPER (granted_role) LIKE 'TILBUD%'
    AND UPPER (grantee) NOT IN
    (SELECT UPPER (brugernavn) FROM tilbud.tbl_bruger)
    AND UPPER (grantee) NOT IN (SELECT UPPER (role) FROM dba_roles)
    AND NOT UPPER (grantee) = 'TILBUD'
    UNION
    SELECT 'WEBVAND' SYSTEM, grantee
    FROM dba_role_privs
    WHERE UPPER (granted_role) LIKE 'WEBVAND%'
    AND (UPPER (grantee) NOT IN
    (SELECT UPPER (user_name) FROM afvand.tksafv_brugere)
    AND NOT UPPER (grantee) = 'CAF')
    AND UPPER (grantee) NOT IN (SELECT UPPER (role) FROM dba_roles);

    View created.

    connect vds/********

    CREATE OR REPLACE procedure VDS.drop_user(initialer IN varchar2)
    is PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    declare
    sqlstr varchar2(50);
    begin
    sqlstr := 'drop user ' || initialer || ' cascade';
    EXECUTE IMMEDIATE sqlstr;
    end;
    end;
    /

    Procedure created.

    grant execute on VDS.drop_user to VDS_VALIDATE;

    Grant complete.

    connect vds_validate/*******

    CREATE OR REPLACE TRIGGER TJEK33_DEL instead of delete ON TJEK33 for each row
    begin
    VDS.DROP_USER (ld.GRANTEE);
    end;
    /

    Trigger created.

    delete from tjek33
    Error at line 1
    ORA-01031: utilstrækkelige privilegier

    Script Terminated on line 1.

  13. #13
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    I think you dont have sufficient rights to drop a user or you may be trying to drop super user. Please checkout the rights you have as a user.

  14. #14
    Join Date
    Mar 2007
    Posts
    623
    Fine, although you did not post the privileges (DROP USER for vds, object privileges for VDS_VALIDATE), I think it is clearer now. In fact, I paid too much attention to the thing you did not post and did not focus on that little bit that you posted in your previous post. I noticed you are trying to do something terribly bizarre, however I did not pay it as much attention as I should (so I could post the next content yesterday).

    Even you are realizing you are doing something totally and utterly stupid, otherwise you would not state:
    Quote Originally Posted by kfc@vd.dk View Post
    I can not delete from the view without an "instead-of-trigger", because my view is based on sys-tables and besides that consists of a union.
    Yes, UNION as a set operator makes the view non-updatable, so you cannot delete from it without INSTEAD OF trigger.
    However, why should using system views prevent deleting rows from it? If you have sufficient privileges and they are updatable, you may break Oracle data dictionary as you want. Doing something slightly less stupid instead of that on database level (trigger - normally, you would issue DELETE statement on any base table there) does not put away the need of those privileges. You are running the same DELETE statement after all.

    DELETE statement and its prerequisites are described in SQL Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
    For 11gR2, it is placed here: http://download.oracle.com/docs/cd/E...5.htm#i2117787
    For you to delete rows from the base table of a view, the owner of the schema containing the view must have the DELETE object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the DELETE object privilege on the view.
    It might be unclear what is "base table" for such a complex view; in CREATE VIEW statement (placed for 11gR2 here: http://download.oracle.com/docs/cd/B...4.htm#i2065510), you may find this definition:
    The tables upon which a view is based are called base tables.
    As SYS, you probably would be able to give DELETE privilege to those system views. If they are updatable, you may succeed.

    However, it would be better if you resumed this approach and returned to the wise and sane way - call the procedure from the proper place in the application (whatever it is).
    Last edited by flyboy; 10-11-11 at 05:38. Reason: last paragraph separation

  15. #15
    Join Date
    Jun 2003
    Posts
    81
    Maybe it is possible to grant delete privileges to the SYS table, but that is really not what I want. That is the main reason for the INSTEAD OF trigger. In my view I localize users, which have been deleted from their system, but still exists in the database. Since they are no longer active users, I wanted it to be possible to delete them directly from my view instead of making it necessary to leave the view, and drop the user afterwards. There could be more than one user in the view, so I wanted it to make it more easy to "clean up". Maybe it is terribly bizarre and utterly stupid, but I don't really see the difference between calling the procedure from an application than calling it from a trigger - but unfortunately there are some privilege issues which makes it impossible.

Tags for this Thread

Posting Permissions

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