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

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.

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.

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

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.

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.

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.

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.

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;

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

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

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

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

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.