LLI Wiki — My Digital Knowledge Base

Oracle: users, roles, grants and locked or expired accounts

Cheat sheet to create and manage Oracle users, roles and grants, unlock accounts and tune password profiles.

Memo for managing Oracle users, roles, privileges, locked or expired accounts and password profiles. All passwords below are placeholders.

List users and session information

select USERNAME,
       USER_ID,
       DEFAULT_TABLESPACE,
       TEMPORARY_TABLESPACE,
       PASSWORD,
       ACCOUNT_STATUS,
       PROFILE,
       CREATED
from DBA_USERS
order by USERNAME;
Goal Query
Current user select user from dual;
Accounts with status OPEN select username,account_status from dba_users where account_status like '%OPEN%';
Current schema select sys_context('userenv','current_schema') x from dual;
Change schema for the session ALTER SESSION SET current_schema=SCHEMA_OWNER;
Privileges of the session select * from session_privs;

Create a tablespace for a user

select * from dba_data_files;
SELECT * FROM dba_tablespaces WHERE contents = 'TEMPORARY';

create tablespace app_tabspace
datafile '/u02/oracle/oradata/appdb/app_tabspace.dbf'
 size 10M autoextend on;

Create a user

create user app_owner identified by '<PASSWORD>'
  default tablespace app_tabspace
  temporary tablespace temp;

grant create session to app_owner;
grant create table to app_owner;
grant unlimited tablespace to app_owner;

Then connect with the new user and type its password to check the login.

Copy a user to another server without knowing the password

IDENTIFIED BY VALUES takes the password hash instead of the clear text password, so a user can be recreated on another server with the same password.

CREATE USER TEST IDENTIFIED BY '<PASSWORD>';
select PASSWORD FROM DBA_USERS WHERE USERNAME='TEST';

CREATE USER TEST2 IDENTIFIED BY VALUES '<PASSWORD_HASH>';
select PASSWORD FROM DBA_USERS WHERE USERNAME IN ('TEST','TEST2');

Schema owner and application user

A common pattern separates the account that owns the objects (schema owner) from the account used by the application. See the Oracle-Base article on schema owners and application users for the full description.

CREATE USER schema_owner IDENTIFIED BY '<PASSWORD>'
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO schema_owner;

CREATE USER app_user IDENTIFIED BY '<PASSWORD>'
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

GRANT CONNECT TO app_user;

Two ways let the application user reach the objects of the schema owner:

Approach Principle Remarks
CURRENT_SCHEMA An AFTER LOGON trigger runs ALTER SESSION SET current_schema=SCHEMA_OWNER No synonyms to maintain; fits an application user that is only another entry point to the schema
Synonyms The application user (granted CREATE SYNONYM) owns one synonym per object Cumbersome with many objects or users; useful when developers create their own objects

In both cases, grant the object privileges to roles (for example a read-only and a read-write role) and grant the role to the application user.

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;
GRANT schema_rw_role TO app_user;

CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
  DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';
END;
/

-- as schema_owner, after creating test_tab
GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

Administrator and developer privileges

create user admin_user identified by '<PASSWORD>';
GRANT CONNECT, RESOURCE, DBA TO admin_user;
grant all privileges to admin_user;

-- set default roles
ALTER USER "ADMIN_USER" DEFAULT ROLE "DBA","CONNECT","RESOURCE";

-- SYSDBA
grant sysdba to admin_user;
select * from v$pwfile_users;

Minimal set for a developer:

GRANT
    CREATE SESSION,
    ALTER SESSION,
    CREATE TABLE,
    CREATE VIEW,
    CREATE SEQUENCE,
    CREATE TRIGGER,
    CREATE PROCEDURE,
    CREATE SYNONYM
TO
    dev_user
/

Monitoring user

Grants for a monitoring account (the set follows the Centreon plugin documentation).

CREATE USER monitor_usr IDENTIFIED BY '<PASSWORD>'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

GRANT CREATE SESSION TO monitor_usr;
GRANT "SELECT_CATALOG_ROLE" TO "monitor_usr";
GRANT "CONNECT" TO "monitor_usr";
GRANT SELECT any dictionary TO monitor_usr;
GRANT SELECT ON V_$SYSSTAT TO monitor_usr;
GRANT SELECT ON V_$INSTANCE TO monitor_usr;
GRANT SELECT ON V_$LOG TO monitor_usr;
GRANT SELECT ON SYS.DBA_DATA_FILES TO monitor_usr;
GRANT SELECT ON SYS.DBA_FREE_SPACE TO monitor_usr;

ALTER USER "monitor_usr" DEFAULT ROLE "SELECT_CATALOG_ROLE","CONNECT";

-- system privilege
GRANT SELECT ANY TABLE TO "monitor_usr";

To drop the user (the name is case sensitive when quoted):

drop USER monitor_usr cascade;
-- or
DROP USER "monitor_usr" CASCADE;

Change the password and unlock the account:

ALTER USER "monitor_usr" IDENTIFIED BY '<PASSWORD>'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK;

Change the password of SYS or SYSTEM

alter user system identified by '<PASSWORD>';
alter user SYS identified by '<PASSWORD>';

Locked and expired accounts

Which accounts are not OPEN, and since when:

alter session set nls_date_format = 'YYYYMMDD HH24:MI:SS';
set linesize 1000;
set pagesize 1000;

COLUMN username FORMAT A20
COLUMN account_status FORMAT A20
SELECT username, account_status, created, lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN';

Lock and expiry settings of the DEFAULT profile:

SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME');

Expiry date of one user:

SELECT EXPIRY_DATE from dba_users where username = 'YourUser';

Expired password: reuse the existing one

Read the existing hash, then set it again so the user keeps the same password:

SELECT DBMS_METADATA.get_ddl ('USER', 'APP_USER') FROM DUAL;
-- or
select name, password from user$ where name='APP_USER';

ALTER USER "APP_USER" IDENTIFIED BY VALUES '<PASSWORD_HASH>';

Lock or unlock an account

ALTER USER username ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK;
alter user username identified by '<PASSWORD>';

Avoid the problem: password lifetime in the profile

Check the profile limits, then adapt password_life_time if needed:

connect / as SYSDBA
select * from dba_profiles;
alter profile default limit password_life_time unlimited;

To remove all limits of the DEFAULT profile:

ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED;

Roles and privileges

Query DBA_SYS_PRIVS, DBA_TAB_PRIVS and DBA_ROLE_PRIVS to see the system, object and role grants.

Read-only access to all tables of a schema

There is no single GRANT SELECT on all tables of a schema. Workarounds:

CREATE ROLE ro_role;

BEGIN
  FOR x IN (SELECT * FROM dba_tables WHERE owner='SCHEMA_NAME')
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON schema_name.' || x.table_name ||
                      ' TO ro_role';
  END LOOP;
END;
/

GRANT ro_role TO ro_user;
GRANT SELECT ANY DICTIONARY TO ro_user;

The same for views:

begin
for x in ( select VIEW_NAME from DBA_VIEWS WHERE OWNER = '<SCHEMA_NAME>' )
loop
execute immediate 'grant select on <SCHEMA_NAME>.' || x.VIEW_NAME || ' to <ROLE_NAME>';
end loop;
end;
/

As a reusable procedure:

CREATE PROCEDURE grant_select(
    username VARCHAR2,
    grantee VARCHAR2)
AS
BEGIN
    FOR r IN (
        SELECT owner, table_name
        FROM all_tables
        WHERE owner = username
    )
    LOOP
        EXECUTE IMMEDIATE
            'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee;
    END LOOP;
END;
/

-- grants SELECT on all tables of user OT to user DW
EXEC grant_select('OT','DW');

Revoking and granting in bulk with the same loop pattern:

BEGIN
  FOR cur IN (SELECT table_name
  FROM DBA_TAB_PRIVS
 WHERE grantee = 'APP_USER' and owner = 'SCHEMA_A'  )
  LOOP
      EXECUTE IMMEDIATE 'revoke  select,references on SCHEMA_A.'||cur.table_name ||' from APP_USER';
    end loop;
END;
/

BEGIN
  FOR cur IN (SELECT view_name FROM all_views WHERE owner = 'SCHEMA_B'  )
  LOOP
      EXECUTE IMMEDIATE 'grant select,references on SCHEMA_B.'||cur.view_name ||' to APP_USER with grant option';
    end loop;
END;
/

Case-sensitive passwords

Check and disable case sensitivity of passwords (11g, see Oracle-Base):

SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

Copy a role to another database

SELECT dbms_metadata.get_ddl('ROLE', role) FROM dba_roles;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT',  '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&your_role_name') FROM dual;

Notes

Mastodon LLIMastodon SocialFosstodon