Category: User Privileges and Roles

How to Reset User Expired Password to the Original Password

We want to keep using the old password due to application restraints.

SQL>  alter user TESTUSER identified by "Testuser#Password123";
 alter user TESTUSER identified by "Testuser#Password123"
*
ERROR at line 1:
ORA-28007: the password cannot be reused

Check profile DEFAULT resource limits:

DEFAULT    PASSWORD_REUSE_MAX               UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME              UNLIMITED
SQL>  alter user TESTUSER profile default;
SQL> select  NAME, SPARE4 FROM SYS.USER$ WHERE NAME ='TESTUSER';

NAME            SPARE4
--------------- ------------------------------------------------
TESTUSER S:E75D474EB09C5E5222BAC532E6F7595BF3A4D7EE6927E615860872DE1892;T:C53F84BF7BF4D1D865D31E7D864B6FC732CF6CB0FE23DD0E780221D514BF05B663FF20B504DBDF2A17A4F82B0376E09C245E7F915BDFAB1FD6E841B21080774321AE15C9FF5A87A9C2E660E82789AC04
SQL>  alter user TESTUSER identified by values 'S:E75D474EB09C5E5222BAC532E6F7595BF3A4D7EE6927E615860872DE1892;T:C53F84BF7BF4D1D865D31E7D864B6FC732CF6CB0FE23DD0E780221D514BF05B663FF20B504DBDF2A17A4F82B0376E09C245E7F915BDFAB1FD6E841B21080774321AE15C9FF5A87A9C2E660E82789AC04';

User altered.

SQL> connect testuser
Enter password: Testuser#Password123 
Connected.
SQL>

ORA-28003: password verification for the specified password failed

The following error occurs when trying to change user password in Oracle Database.

SQL> alter user TESTUSER identified by "TestPassword";
alter user TESTUSER identified by "TestPassword";
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed

Check user profile:

SQL> select profile from dba_users where username='TESTUSER';

PROFILE
------------------------------------------------------
TEST_USER_PROFILE

Find password verify function

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT 
FROM DBA_PROFILES 
WHERE PROFILE='TEST_USER_PROFILE'  
AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

RESOURCE_NAME                    RESOURCE   LIMIT
--------------------------       ---------  ----------------------
PASSWORD_VERIFY_FUNCTION         PASSWORD    TEST_USER_VFY_FN

Extract code of password verify function

SQL> select OWNER,substr(TEXT,1,50)  
from dba_source 
where NAME='TEST_USER_VFY_FN' 
order by line;

OWNER      SUBSTR(TEXT,1,50)
---------- --------------------------------------------------
SYS        FUNCTION test_user_vfy_fn (username varchar2,
SYS        BEGIN

...
..
.

SYS                IF LENGTH(password) >= 25 THEN
SYS                        RETURN(TRUE);
SYS                ELSE
SYS                        RETURN(FALSE);
SYS                END IF;
SYS        END;

The password length should be equal or greater than 25.

ORA-28007: the password cannot be reused

Tring to change the user password to an old one, it failed:

SQL> alter user tester identified by "Welcome123#test";

alter user tester identified by "Welcome123#test";

*

ERROR at line 1:

ORA-28007: the password cannot be reused

SOLUTION

Check the profile used by user tester.

SQL> alter profile  default limit PASSWORD_REUSE_MAX unlimited;



Profile altered.



SQL> alter user tester identified by "Welcome123#test";

User altered.



SQL> alter profile  default limit PASSWORD_REUSE_MAX 10;



Profile altered.



SQL> alter user tester identified by "Welcome123#test";

alter user tester identified by "Welcome123#test";

*

ERROR at line 1:

ORA-28007: the password cannot be reused

Privileges Consideration when Refresh Oracle Schema

When DBA refreshes an Oracle schema or objects, privileges should be taken care carefully , specially for long time running complex environment.

After dropping a schema, all the granted privileges will be lost. So before refresh, DBA should record all the privileges granted to users, roles, etc.

Export a Schema from Source Database

$ expdp Username/Password directory=  dumpfile= logfile= SCHEMAS= FLASHBACK_TIME=systimestamp

Record / Extract the Granted Privileges

Granted Column Privileges

SQL> select * from dba_col_privs where GRANTOR='SCHEMANAME';

Here ‘SCHEMANAME’ is the schema to be refreshed.

Granted Objects Privileges

 SQL>select GRANTEE,OWNER||'.'||TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE from dba_tab_privs where GRANTOR ='SCHEMANAME';

Other privileges you may be interested from following views:

  • User_role_privs, dba_role_privs ( Users granted with with roles )
  • User_sys_privs, dba_sys_privs
  • Role_role_privs, role_sys_privs, role_tab_privs
  • Session_privs, session_roles

Refresh Schema

Drop Schema Objects

 SQL> select 'drop '||object_type|| ' '||owner||'.'||object_name ||' ;' from dba_objects where owner='SCHEMANAME';

Import Schema by Data Pump

$ impdp username/password directory= dumpfile= logfile= schemas=sourceschemaname remap_schema= remap_tablespace=

Fix Invalid Objects

Fix all invalid objects, check all synonyms, etc

Collect Stats

SQL>EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'SCHEMANAME');

Column Privileges in Oracle

Table column privileges include only INSERT,UPDATE without SELECT unfortunately.

How to Grant Column Privileges ?

SQL> GRANT update (columnname1),insert (columnname2, columnname3)  ON user1.table1 TO user2;

How to Check Column Privileges ?

SQL> select * from dba_col_privs ;

How to Remove Column Privileges ?

SQL> revoke insert (columnname2, columnname3) ON user1.table1 from user2;
revoke insert (columnname2, columnname3) ON user1.table1 from user2;
              *
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column

SQL> revoke insert  on  user1.table1 from user2;
Revoke succeeded.