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:
- grant
SELECT ANY TABLE(very broad, covers every schema); - generate one grant per table to a role, then grant the role to the user.
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
GRANT ... DBA,ALL PRIVILEGES,SELECT ANY TABLEand unlimited profile settings are very permissive; use them only where the security policy allows it.- Never keep real passwords in notes or scripts; use placeholders and a secret store.