PROCEDURE TO EXECUTE IMMEDIATE

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Tue, 13 Jan 2009 4:08 pm

Hello guys, I have a package that works for me on the bench, create users change their passwords and adiona them in roles and desired through the forms prófiles ok?
My problem is as follows, on procedure add_role_select the user is added normally, but in the other of the following error: admin option not granted for roll!
And when I use the procedure add_profile_user of the following error: missing user or role name.
Does anyone know why are you giving?


Code: Select all
CREATE OR REPLACE PACKAGE BODY pck_username_oracle IS

   ------------

   --CRIA USUÁRIO ORACLE
   PROCEDURE cria_username_oracle(p_username VARCHAR2
                                                ,p_senha    VARCHAR2) IS
   BEGIN
      -- 
      EXECUTE IMMEDIATE 'CREATE USER ' || p_username || ' IDENTIFIED BY ' || p_senha;
      --
   END cria_username_oracle;

   ------------

   --ALTERA SENHA DO USUÁRIO ORACLE
   PROCEDURE altera_senha_username_oracle(p_username VARCHAR2
                                                            ,p_senha    VARCHAR2) IS
   BEGIN
      --
      EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' IDENTIFIED BY  ' || p_senha;
      --
   END altera_senha_username_oracle;

   ------------

   --ADD USUÁRIO NAS ROLES
   PROCEDURE add_role_select(p_username VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'GRANT role_select TO ' || p_username;
   END add_role_select;
   --
   PROCEDURE add_role_update(p_username VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'GRANT role_update TO ' || p_username;
   END add_role_update;
   --
   PROCEDURE add_role_insert(p_username VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'GRANT role_insert TO ' || p_username;
   END add_role_insert;
   --
   PROCEDURE add_role_delete(p_username VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'GRANT role_delete TO ' || p_username;
   END add_role_delete;
   --
   PROCEDURE add_profile_user(p_username VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'alter user ' || p_username || ' profile GRAFICO';
   END add_profile_user;
davidmeloboy
Location: Parobé - RS

David.

Poston Wed, 14 Jan 2009 7:06 am

Try to put in the header of the procedure this way: CREATE OR REPLACE PACKAGE BODY pck_username_oracle AUTHID CURRENT_USER IS IS--will check the permissions of the user
zenitram
Location: sp

KBLO

Poston Wed, 14 Jan 2009 10:41 am

put in the body of the package CREATE OR REPLACE PACKAGE BODY pck_username_oracle AUTHID CURRENT_USER IS in the Declaration did not accept, and gave the following error: conflicts with this use
davidmeloboy
Location: Parobé - RS

David.

Poston Wed, 14 Jan 2009 2:29 pm

Dude, guess this error \"admin option not granted for role\" means that you do not have administrative control to grant these privileges.

Oane in WITH ADMIM OPTION and WITH GRANT OPTION.

You may need to speak with your DBA.
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: No registered users and 4 guests