alter user via dbms_sql

alter user via dbms_sql

Post by Kailash Awat » Sun, 31 Dec 1899 09:00:00



The following procedure, which changes a user password,
gives an ORA-01031 (insufficient privileges) error. This
even though the user executing the procedure has the
ALTER USER privilege granted directly (not via a role).

Any help as to the cause of  this error would be much
appreciated.

Thanks in advance.

Kailash.

----------------------------------------------------------------------------
-------------------------------------------
CREATE OR REPLACE PROCEDURE
NGE_PASSWORD(
     P_USER_ID   IN VARCHAR2,
     P_PASSWORD   IN varchar2)

IS

CURSOR_PASS INTEGER;
CURSOR_INT INTEGER;
v_string varchar2(100);

BEGIN
        CURSOR_PASS := dbms_sql.open_cursor;
        v_string := 'alter user ' || p_user_id || ' identified by ' || p_password;
       dbms_sql.parse(cursor_PASS,v_string,dbms_sql.v7);
       CURSOR_INT := dbms_sql.execute(CURSOR_PASS);
       dbms_sql.close_cursor(cursor_PASS);
END;

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

 
 
 

alter user via dbms_sql

Post by Kailash Awat » Sun, 31 Dec 1899 09:00:00


The following procedure, which changes a user password,
gives an ORA-01031 (insufficient privileges) error. This
even though the user executing the procedure has the
ALTER USER privilege granted directly (not via a role).

Any help as to the cause of  this error would be much
appreciated.

Thanks in advance.

Kailash.

----------------------------------------------------------------------------
-------------------------------------------
CREATE OR REPLACE PROCEDURE
NGE_PASSWORD(
     P_USER_ID   IN VARCHAR2,
     P_PASSWORD   IN varchar2)

IS

CURSOR_PASS INTEGER;
CURSOR_INT INTEGER;
v_string varchar2(100);

BEGIN
        CURSOR_PASS := dbms_sql.open_cursor;
        v_string := 'alter user ' || p_user_id || ' identified by ' || p_password;
       dbms_sql.parse(cursor_PASS,v_string,dbms_sql.v7);
       CURSOR_INT := dbms_sql.execute(CURSOR_PASS);
       dbms_sql.close_cursor(cursor_PASS);
END;

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

 
 
 

alter user via dbms_sql

Post by Alan Shei » Sun, 31 Dec 1899 09:00:00


Did you GRANT EXECUTE on the procedure to the user who needs to run it?
 
 
 

alter user via dbms_sql

Post by PaulCin » Sun, 31 Dec 1899 09:00:00


/*
Changing Passwords for Oracle Users
*/
/*
 The stored procedure script below allows you to change/set password for Oracle
users.

 Features of this script:

 This stored procedure can be called from various client environments like
 PowerBuilder, Visual Basic, Microsoft Excel, Oracle Forms, Oracle Reports or
SQL*Plus.

 It makes use of the DBMS_SQL package.
 The stored procedure needs to be owned by the SYS account
 but can be accessed by all users using a public synonym.

 It could be particularly useful when users accessing the instance are on
 different client environments and don't necessarily have access to SQL*Plus.
*/

REM ------------------------------------------------------------
REM This stored procedure sets/changes password for Oracle users
REM Accepts username and new password.
REM Compares the username with the 'signed-on' username.
REM Usage
REM For changing the password for USER user1 to password1,
REM       chng_pwd(user1,password1)
REM This script needs to be run from SYS account
REM ------------------------------------------------------------

create or replace procedure chng_pwd(uname varchar2, new_pwd varchar2)
as
  cursor1    integer;
  rows_processed integer;
begin
  if UPPER(user) = UPPER(uname) then
    cursor1 := dbms_sql.open_cursor;
    dbms_sql.parse(cursor1,'alter user '||uname||
    ' identified by '||new_pwd,dbms_sql.v7);
    rows_processed := dbms_sql.execute(cursor1);
    dbms_sql.close_cursor(cursor1);
  end if;
exception
  when others then
   dbms_sql.close_cursor(cursor1);
end chng_pwd;
/
create public synonym chng_pwd for sys.chng_pwd;
grant execute on chng_pwd to public;
exit;

Paul in VT

 
 
 

alter user via dbms_sql

Post by Kailash Awat » Sun, 31 Dec 1899 09:00:00


Yes.

>Did you GRANT EXECUTE on the procedure to the user who needs to run it?

 
 
 

alter user via dbms_sql

Post by Stephan Bor » Sun, 31 Dec 1899 09:00:00


> Yes.


> >Did you GRANT EXECUTE on the procedure to the user who needs to run it?

I found out, that Oracle 7.3.4 has problems with rights granted over a role
to a user.
System priviliges (like 'create table') have to be granted directly to the
user,
who wants to  use it within a procedure.

Try 'grant alter user to <user>' before running the dbms_sql-command within
a procedure

Regards, Stephan
--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin

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

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

alter user via dbms_sql

Post by Didier LENQUETT » Sun, 31 Dec 1899 09:00:00


In PL/SQL , role are not used.

You have to grant explicitely to the user the corresponding privilege, that
is to say
GRANT ALTER USER TO <owner of the procedure>

Bye !

 
 
 

1. Can DBMS_SQL Not Be Used to Alter Users?

I'm trying to put together a package that will allow me to expire user
passwords at predefined intervals.  Part of the package is supposed to
change the users password for him using the DBMS_SQL package routines.
However, I get "insufficient privileges" errors when I run it, even though
the owner of the procedure has full system privileges and can issue the
command successfully from an interactive prompt.  Is DBMS_SQL simply not
capable of handling this SQL comnmand?

For the record, I'm running Oracle 7.1.5 on Alpha/OpenVMS.  The relevant
portion of the stored procedure follows.

    vSQL := 'alter user ' || vUserName || ' identified by '
            || upper(NewPassword);
    dbms_output.put_line('vSQL = "' || vSQL || '"');
    sqlcursor := dbms_sql.open_cursor;
    dbms_sql.parse(sqlcursor, vSQL, dbms_sql.NATIVE);
    sqlreturn := dbms_sql.execute(sqlcursor);

--
========================================================================
"Villains, I say to you now:       |   Mike Carmack
 KNOCK OFF ALL THAT EVIL!"         |   Vulcan Dragon -==(UDIC)==-

2. Advanced Revelation questions

3. Dynamically Calling PLSQL Procedures via DBMS_SQL

4. Error 3028

5. dbms_sql.parse does not allow remote conn via dblink

6. IIUG Insider #27 - Referral Drive, Informix Logo Lives in Russia, and More...

7. alter user to change user's password returns pg_shadow: Permission

8. ZIM

9. Help : DBMS_SQL and CREATE USER Problems

10. HELP!! creating users with the DBMS_SQL package

11. Alter Table via an SP with objects?