Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Location
    France
    Posts
    3

    Unanswered: Create user or role via stored procedure

    Hello,

    I would like to know how can I create/delete users ou roles using a stored procedure ...

    I tried but it seems that there's a problem of privilege ...

    By example, my procedure which should create a user :
    CREATE PROCEDURE ps_AddUser (UserName IN VARCHAR2,
    Password IN VARCHAR2,
    GroupName IN VARCHAR2)
    IS
    BEGIN


    EXECUTE IMMEDIATE 'CREATE USER ' || UserName || ' IDENTIFIED BY '|| Password || ' DEFAULT TABLESPACE USERS';

    EXECUTE IMMEDIATE 'GRANT ' || GroupName || ' TO ' || UserName || '';

    END;
    /


    When I execute it, I've got the message :
    ORA-01031: insuffisant privilege


    Also, I've got the DBA right ....

    How can I do?
    Is there any system functions which can make the same thing ?

    Thanks,
    Guyom

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try granting 'create user' and 'alter user' with the admin option explicitly to the user which executes the stored proc as below

    grant create user, alter user, grant any role to <your user> with admin option;

Posting Permissions

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