Showing posts with label Users. Show all posts
Showing posts with label Users. Show all posts

Thursday, June 5, 2025

create user in postgres - basic syntax

I will create

  • a group role called common_users
  • a user called ro_user1
  • in a database called db01

    The read-only user ro_user1 should be able perform queries against all tables owned by the schema schema1.

    First, create the role common_users by logging onto the postgres (default) database
    psql
    
    CREATE ROLE common_users WITH
      NOLOGIN
      NOSUPERUSER
      INHERIT
      NOCREATEDB
      NOCREATEROLE
      NOREPLICATION
      NOBYPASSRLS;
    
    GRANT pg_read_all_stats TO common_users;
    
    Then, create the user ro_user1:
    create user ro_user1 password 'mysecretpassword';
    grant common_users to ro_user1;
    grant connect on database db01 to ro_user1;
    
    Log into the database db01 and revoke and grant some privileges:
    psql
    \connect db01
    revoke all on schema schema1 from ro_user1;
    grant usage on schema schema1 to ro_user1;
    grant select on all tables in schema schema1 to ro_user1;
    
    Confirm the privileges:
    \connect postgres
    select database_privs('ro_user1');
    
  • Tuesday, October 29, 2024

    Lock and expire users

    An anonymous PL/SQL block to lock and expire open accounts:
    SET VERIFY OFF
    connect "SYS"/"&&sysPassword" as SYSDBA
    set echo on
    spool /oracle/admin/mydb01/scripts/lockAccount.log append
    BEGIN
     FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
    'SYS','SYSTEM') )
     LOOP
    IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
      dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
      execute immediate 'alter user ' ||
             sys.dbms_assert.enquote_name(
             sys.dbms_assert.schema_name(
             item.USERNAME),false) || ' password expire account lock' ;
     ELSE
      dbms_output.put_line('Locking: ' || item.USERNAME);
      execute immediate 'alter user ' ||
             sys.dbms_assert.enquote_name(
             sys.dbms_assert.schema_name(
             item.USERNAME),false) || ' account lock' ;
     END IF;
     END LOOP;
    END;
    /
    spool off
    

    Wednesday, October 19, 2022

    How to prevent a user from login into a postgres database

    alter role scott with nologin;
    ALTER ROLE
    
    Remember that in postgres, "users" and "roles" are used interchangably, so this would also work:
    alter user scott with nologin;
    ALTER ROLE
    
    In either case, the postgres server will echo "ALTER ROLE" back to the administrator.

    To see the result of such an operation:
    echo "\du" | psql
    
    Example output:
                                                   List of roles
                Role name            |                         Attributes                         |      Member of
    ---------------------------------+------------------------------------------------------------+---------------------
     scott                           | Cannot login                                              +| {business_users}
     

    You can also query the postgres data dictionary for the answer, like this:
    postgres=# select rolcanlogin from pg_roles where rolname='jim';
     rolcanlogin
    -------------
     t
    (1 row)
    
    postgres=# select rolcanlogin from pg_roles where rolname='scott';
     rolcanlogin
    -------------
     f
    (1 row)
    
    where t and f indicates true if the user can login and false if the user cannot, respectively.

    Wednesday, September 4, 2019

    How to create a proxy authenticated user in Oracle



    Create a general user for authentication:
    CREATE USER APP_POOL
    IDENTIFIED BY app_pool_users123
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE APP_USERS
    ACCOUNT UNLOCK;

    GRANT CREATE SESSION TO APP_POOL;

    To illustrate how proxy authentication can be used efficiently, I create two roles:
    create role app_pool_role1 not identified;
    create role app_pool_role2 not identified;

    Grant object privileges on two different tables to the two new roles:
    grant select on SCOTT.DEPT to app_pool_role1;
    grant select on SCOTT.EMP to app_pool_role2;

    Create a user:
    CREATE USER VEGARD
    IDENTIFIED BY vegard123
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE APP_USERS
    ACCOUNT UNLOCK;

    Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:
    GRANT CREATE SESSION TO VEGARD;
    GRANT APP_POOL_ROLE1 TO VEGARD;
    GRANT APP_POOL_ROLE2 TO VEGARD;
    ALTER USER VEGARD DEFAULT ROLE ALL;

    Change the user so it connects through a proxy user.
    Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:
    ALTER USER VEGARD
    GRANT CONNECT THROUGH APP_POOL
    WITH ROLE APP_POOL_ROLE1;

    Let's connect to the database:
    sqlplus app_pool[vegard]/app_pool_users123@pdb01

    Verify the connection and session settings by using sys_context:
    select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
           decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
           sys_context('USERENV','CURRENT_SCHEMA') "current schema",
           sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
           decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
           sys_context('USERENV','PROXY_USER') "proxy user"
    from dual;
    

    Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:
    AuthIdentity used auth method current schema grantee of privileges used identification type proxy user
    VEGARD proxy VEGARD VEGARD Password APP_POOL

    Which roles are enabled?
    select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
    from dual
    union
    select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
    from dual
    ;

    Result shows that only APP_POOL_ROLE1 is enabled:
    Role granted?
    APP_POOL_ROLE1: TRUE
    APP_POOL_ROLE2: FALSE

    Let's verify that the roles are actually working.
    Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:
    VEGARD@pdb01 SQL> select count(*) from scott.emp;
    select count(*) from scott.emp
                               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    VEGARD@pdb01 SQL> select count(*) from scott.dept;
    
      COUNT(*)
    ----------
             4
    

    There is nothing preventing the user VEGARD from connecting directly to the database:
    sqlplus vegard/vegard123@pdb01
    VEGARD@pdb01 SQL> show user
    USER is "VEGARD"
    

    When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:
    VEGARD@vegdb01 SQL> select count(*) from scott.emp;
    
      COUNT(*)
    ----------
            14
    
    

    Some advantages with using proxy connections:

    * No more password administration for individual users
    * Proxy authentication allows for role based access control
    * Identity preservation of the real user behind the proxy user is automatically set up

    Keep in mind that you still have to create and provision the end user accounts.

    Thursday, May 9, 2019

    How to unlock users with expired password in postgreSQL


    select now()+'90 days' as expiry;
     expiry
    -------------------------------
     2019-08-07 10:07:01.172082+02
    \gset
    alter user jim valid until :'expiry';
    ALTER ROLE
    

    The command "\gset" sends the current query buffer to the server and stores the query's output into a psql variable.

    After the change, use \du to verify that the password has the correct expiry time:
    mydb01=# \du
                                                List of roles
         Role name     |                         Attributes                         |      Member of
    -------------------+------------------------------------------------------------+---------------------
     postgres          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                       | Password valid until 2019-01-16 00:00:00+01                |
     superuser         | Superuser                                                 +| {}
                       | Password valid until 2019-08-04 00:00:00+02                |
     jim               | Password valid until 2019-08-07 10:07:03.237862+02         | {}
    

    Use the "\list" command to list databases and access privileges, if desirable:
    mydb01=# \list
                                        List of databases
       Name    |  Owner   | Encoding |  Collate   |   Ctype    |      Access privileges
    -----------+----------+----------+------------+------------+------------------------------
     postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
     template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
               |          |          |            |            | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
               |          |          |            |            | postgres=CTc/postgres
     mydb01    | jim      | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/jim                     +
               |          |          |            |            | jim=CTc/jim                 +
    

    To give the user a new password, use this syntax:
    alter role jim password 'mynewpassword';
    

    Consult the documentation for more information

    Friday, December 7, 2018

    How to create the SCOTT user

    To create the user SCOTT, run the script

    $ORACLE_HOME/rdbms/admin/utlsampl.sql

    Observe that user SCOTT will be created with the following statement:

    GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;

    Note that if the default profile is using a password verification function, the creation will fail.

    To work around, exchange default password "tiger" with something different that will honor the verification function, for example "T123G456R".

    Remember to set
    SET TERMOUT ON
    SET ECHO ON
    
    at the top, if you want to see the script output.

    Thursday, October 18, 2018

    What is the meaning of the "10G 11G 12C" value in DBA_USERS.PASSWORD_VERSIONS?


    Since I wrote my post What is the meaning of the "10G 11G" value in DBA_USERS.PASSWORD_VERSIONS?, Oracle 12c added yet another value to this column.

    It's now common to see the string

    PASSWORD_VERSIONS
    10G 11G 12C

    when you query the password_versions column of dba_users view.

    What does it mean?

    It's a list of password versions that was generated at the time the account was created.
    This list will look a little different depending on your setting of the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER in your $TNS_ADMIN/sqlnet.ora at the time of account creation.

    Oracle explains:

    "The PASSWORD_VERSIONS column shows the list of password versions that exist for the account. 10G refers to the earlier case-insensitive Oracle password DES-based version, 11G refers to the SHA-1 version, and 12C refers to the SHA-2-based SHA-512 version."


    In my 12.2 database, I have set the following parameter in my $TNS_ADMIN/sqlnet.ora file:
    SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
    

    to allow older clients to connect.

    When I then create a user with the CREATE USER statement, it will automatically generate all three password versions. Since my SQLNET.ALLOWED_LOGON_VERSION_SERVER was set to 11 at the time of creation, my password will indeed by case-sensitive, since case sensitive password was introduced in version 11.1 of the Oracle software.

    If I adjust the parameter sqlnet.ora parameter:
    SQLNET.ALLOWED_LOGON_VERSION_SERVER=12
    

    and drop/recreate the user, my password version will have changed:

    PASSWORD_VERSIONS
    11G 12C

    The setting of SQLNET.ALLOWED_LOGON_VERSION have the following effects:

    SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 will keep generating 10G, 11G and 12c password versions
    SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 will generate both 11G and 12C password versions, and also remove the 10G password version.
    SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a will generate only 12c password versions

    Oracle call these three settings Greatest level of compatibility, Medium level of security, and Highest level of security, respectivly.

    If you for some reason want the old-school case-insensitive password versions to apply, set your SQLNET.ALLOWED_LOGON_VERSION_SERVER to 10 or lower, and make sure the parameter sec_case_sensitive_logon is set to FALSE.

    Any user created after setting SQLNET.ALLOWED_LOGON_VERSION_SERVER to 10 or lower, will be able to logon using case-insensitive passwords.



    Thursday, January 18, 2018

    How to work around ORA-01017 in a migrated 12c database



    You may see some users in your 12c database that have the password versions set to 10G:

    select username, password_versions from dba_users where username='SCOTT';
    
    USERNAME             PASSWORD_VERSIONS
    -------------------- -----------------
    SCOTT                10G
    

    At the same time, most other users have their password_versions set to the value 10G 11G 12C.

    Oracle uses different password versions in all these three versions:

    * Oracle 11g it uses SHA1 password based version
    * Oracle 10g uses DES based version.
    * Oracle 12c uses SHA-2-based SHA-512 password version

    Since the Oracle 12c database runs in exclusive mode by default, users with passwords generated in previous versions
    will not be able to login (exclusive mode means that the SQLNET.ALLOWED_LOGON_VERSION_SERVER is set either to 12 or 12a).

    Workaround is to force a password reset so that the password is generated for the current version.
    But before you do that, you need to change the database's minimum allowed authentication protocol.
    This is done by editing the file $TNS_ADMIN/sqlnet.ora.
    The parameter controlling the sqlnet authentication protocol is SQLNET.ALLOWED_LOGON_VERSION_SERVER.

    Here is how I did it:

    1. Find the user(s) with password versions of 10G
    select username, password_versions 
    from dba_users 
    where password_versions = '10G';
    

    I have found cases where there is a space after the string '10G' so that you need to actually search for the string '10G '.

    2. Edit the $TNS_ADMIN/sqlnet.ora file so that the database doesn't run in exclusive mode. Add

    SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11

    3. Restart database

    4. Expire the user(s) that you want to force a password reset for:
    alter user SCOTT password expire;
    

    5. Try to connect as the user:
    connect SCOTT
    Enter password:
    ERROR:
    ORA-28001: the password has expired
    
    
    Changing password for SCOTT
    New password:
    Retype new password:
    Password changed
    Connected.
    

    Check that the users now has the correct password version:
    select username, password_versions,account_status from dba_users where username='SCOTT';
    
    USERNAME             PASSWORD_VERSIONS ACCOUNT_STATUS
    -------------------- ----------------- --------------------------------
    SCOTT                10G 11G 12C       OPEN
    

    6. When all the affected users have been changed, set the database to run in exclusive mode.
    Change the SQLNET.ALLOWED_LOGON_VERSION_SERVER from 11 to 12 in $TNS_ADMIN/sqlnet.ora, and restart once more.


    Wednesday, January 11, 2017

    How to find users in the database with default passwords

    select d.username,account_status
    from dba_users_with_defpwd d inner join dba_users u
    on d.username = u.username
    where u.account_status = 'OPEN';
    exit
    
    Output in my case:
    USERNAME                                 ACCOUNT_STATUS
    ---------------------------------------- --------------------
    CTXSYS                                   OPEN
    MGDSYS                                   OPEN
    

    These accounts should have their password changed for security reasons.

    Sunday, December 18, 2016

    How to create a common and a local user in a 12c multitenant database

    In a multitenant container database, there are two types of users:

    * Common users, who are known in both the root containers and in all the pluggable database containers
    * Local users, who are only known in a single pluggable database container

    Common users can, if granted the necessary privileges, perform administrative tasks across all the PDBs in multitenant database.
    They can also perform tasks specific to the container database, also called the ROOT container.

    To create a common user, make sure you are connected to the ROOT container

    On my client, my tnsnames.ora entry looks as follows:
    # container database
    CDB =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.no)(PORT = 1531))
         (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = "container#db01")
         )
       )
    


    Since my client is a windows pc, I start sqlplus from the start menu, and connect as system@cdb and enter the password.
    My default container will be CDB$ROOT, which is what you need to create a common user:
    show con_name
    
    CON_NAME
    ------------------------------
    CDB$ROOT
    


    Create the common user:
    create user C##DBAMASTER
    identified by ****
    default tablespace USERS
    temporary tablespace TEMP
    quota unlimited on USERS
    container=all;
    
    SQL> user C##DBAMASTER created.
    

    Note the new rules for creating common users in a CDB database:
    In Oracle Database 12c Release 1 (12.1.0.1), the name of a common user must begin with C## or c##

    Verify that the common user was created:
    select USERNAME,CREATED,LAST_LOGIN,COMMON
    from dba_USERS
    where trunc(created) = trunc(sysdate)
    
    USERNAME             CREATED   LAST_LOGIN                               COM
    -------------------- --------- ---------------------------------------- ---
    C##DBAMASTER         18-DEC-16 18-DEC-16 03.16.16.000000000 PM +01:00   YES
    


    Give the common user the right to create a session, to switch between all containers in the CDB and the right to create and drop users in all the containers in the CDB:
    grant connect to C##DBAMASTER;
    grant set container to C##DBAMASTER container=all;
    grant create user to C##DBAMASTER container=all;
    grant drop user to C##DBAMASTER container=all;
    
    SQL> Grant succeeded.
    

    Connect to ROOT container as the new common user:
    connect C##DBAMASTER/***@cdb
    Connected.
    show user
    USER is "C##DBAMASTER"
    
    Switch container:
    alter session set container=vpdb01;
    
    SQL> Session altered.
    
    Create a new local user in the current container:
    create user "1Z0061"
    identified by ****
    default tablespace users
    quota unlimited on users
    temporary tablespace TEMP
    container=current;
    
    SQL> User created.
    

    Note that without the "container=all" privilege, the new common user C##DBAMASTER cannot connect directly to the vpdb01 pluggable database

    Here is what happened:
    SQL> connect C##DBAMASTER/****@vpdb01
    ERROR:
    ORA-01045: user C##DBAMASTER lacks CREATE SESSION privilege; logon denied
    
    
    Warning: You are no longer connected to ORACLE.
    SQL> connect system/*****@vpdb01
    Connected.
    SQL> show con_name
    
    CON_NAME
    ------------------------------
    VPDB01
    SQL> grant create session to C##DBAMASTER container=current;
    
    Grant succeeded.
    
    SQL> connect C##DBAMASTER/****@vpdb01
    Connected.
    SQL> show con_name
    
    CON_NAME
    ------------------------------
    VPDB01
    

    This means that these two privileges are distinctly different:
    grant create session to C##DBAADMIN container=all;
    
    vs
    grant set container to C#DBAADMIN container=all;
    

    Monday, December 12, 2016

    How to find the last password change a user made


    Limit the search to those users who changed their password today:

    select name,ctime "created", ptime "passw change time",ltime "last locked", lcount "# failed logins"
     from user$
     where trunc(ptime) = trunc(sysdate)
     order by 3 desc;
    

    Example output:

    NAME created passw change time last locked # failed logins
    JIM 25.11.2016 02:25:38 12.12.2016 09:59:25  
    0
    JOHN 25.11.2016 02:25:55 12.12.2016 09:53:19  
    0
    JAMES 25.11.2016 02:25:54 12.12.2016 09:29:50  
    0

    Tuesday, December 22, 2015

    Generate "drop user" statement for schemas with and without objects

    set lines 200
    col owner format a20
    col username format a30
    col schema format a30
    
    
    PROMPT ===================================================
    PROMPT List of all schemas and number of Objects they own:
    PROMPT ===================================================
    select u.username "Schema", decode(count(*),1,0, count(*)) "Number of Objects"
    from dba_users u left outer join dba_objects o
    on u.username = o.owner
    where u.username in (USER1','USER2','USER3')
    group by u.username
    order by username ASC;
    
    set heading off
    set trimspool on
    set lines 200
    set feedback off
    spool drop_users.sql
    select 'spool drop_users_SD4440.log' from dual;
    select 'DROP USER ' || u.username
                        || DECODE(decode(count(*),1,0, count(*)),
                           '0',';'
                           , ' CASCADE;')
    from dba_users u left outer join dba_objects o
    on u.username = o.owner
    where u.username in ('USER1','USER2','USER3')
    group by u.username;
    select 'exit' from dual;
    exit
    

    Tuesday, August 26, 2014

    What is the meaning of the "10G 11G" value in DBA_USERS.PASSWORD_VERSIONS?

    When the value of DBA_USERS.PASSWORD_VERSIONS is shown as "10G 11G", it means that both old and new-style hash values are available for the user.

    Note that instead of storing the hashed password values directly in the DBA_USERS table, they are from 11gR1 and onwards both stored in the table USER$, column PASSWORD for the 10G style hash value, and column SPARE4 for the 11G SHA-1 style hash value.

    A NULL value indicates that the password has not been changed since the migration and the user still has the old case insensitive password.

    In my query below, USER1 through USER4 have been migrated and will now take advantage of 11g password case-sensitivity, if enabled.

    USER5 is still using 10G style case insensitive passwords.

    USER6 is created after migration, and will also take advantage of the 11g password case-sensitivity, if available.

    SELECT U.NAME "Name",
                NVL(REGEXP_REPLACE(U.SPARE4,'^.+', 'Password changed since migration',1,0), 'Password unchanged since migration') "Action undertaken",
                DU.PASSWORD_VERSIONS "Password Version"
    FROM USER$ U LEFT OUTER JOIN DBA_USERS DU
    ON U.NAME = DU.USERNAME
    WHERE DU.USERNAME NOT IN (SELECT ROLE FROM DBA_ROLES)
    ORDER BY NAME ASC;
    


    Name Action undertaken Password Version
    USER1 Password changed since migration 10G 11G
    USER2 Password changed since migration 10G 11G
    USER3 Password changed since migration 10G 11G
    USER4 Password changed since migration 10G 11G
    USER5 Password unchanged since migration 10g
    USER6 Password changed since migration 11G

    Users that are imported from an earlier release into an 11g database, will remain case-insensitive until the password is changed.


    To generate "alter user identified by values" statements, use the following:

    SELECT 'alter user ' || NAME || ' identified by values ' || '''' || SPARE4 ||';' || PASSWORD ||''';' 
    FROM USER$ 
    WHERE  NAME IN ('USER1','USER2');
    

    Sources: Oracle Documentation

    Wednesday, November 27, 2013

    How to write a procedure that lets developers set the time for the database using the FIXED_DATE procedure

    The following procedure may be used to let designated database developers change the system time according to their testing needs.

    CREATE OR REPLACE PACKAGE admin_pack AUTHID DEFINER
    AS
     PROCEDURE set_fixed_date(vv_fixed_date VARCHAR2);
     PROCEDURE unset_fixed_date;
    END admin_pack;
    /
    
    CREATE OR REPLACE PACKAGE body admin_pack
    AS
    PROCEDURE set_fixed_date (vv_fixed_date VARCHAR2) IS
     BEGIN
      IF UPPER(vv_fixed_date) = 'NONE' THEN
         EXECUTE IMMEDIATE 'alter system set fixed_date=none';
      ELSE
         EXECUTE IMMEDIATE 'alter system set fixed_date=''' || vv_fixed_date || '''';
      END IF;
    END;
    PROCEDURE unset_fixed_date IS
     BEGIN
      EXECUTE IMMEDIATE 'alter system set fixed_date=none';
     END;
    END admin_pack;
    /
    
    
    CREATE PUBLIC SYNONYM ADMIN_PACK FOR SYS.ADMIN_PACK;
    GRANT EXECUTE ON ADMIN_PACK TO SCOTT;
    

    Note the AUTHID clause in the package declaration:

    A unit whose AUTHID value is CURRENT_USER is called an "invoker's rights" unit, or IR unit. A unit whose AUTHID value is DEFINER is called a "definer's rights" unit, or DR unit.

    The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time. It will check:

    • If the context for name resolution is CURRENT_SCHEMA.
    • If the privileges checked are those of the CURRENT_USER and the enabled roles.

    At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.

    Author: Tomasz Gozdz
    Documentation for the AUTHID DEFINER SETTING
    Documentation for the FIXED_DATE procedure

    Friday, November 15, 2013

    How to temporarily alter a PROFILE

    Temporarily disable the password verify function and set it back to the original value again when you're done:
    
    SQL> ALTER PROFILE DEFAULT LIMIT password_verify_function NULL;
    Profile altered.
    
    SQL> ALTER PROFILE DEFAULT LIMIT password_verify_function VERIFY_PASSWORD;
    
    Profile altered.