oracle users - ghdrako/doc_snipets GitHub Wiki

Oracle User Management

select username, account_status, default_tablespace from dba_users;  -- list users
show user; -- check current user
alter user scott account unlock;
alter user scott identified  by  "tiger" account unlock;
alter user scott account lock;
create user usr1 identified by usr1; -- create user
select dbms_metadata.get_ddl('USER','scott') from dual;

user with tablespace

create user usr2 identified by usr2 default tablespace users; -- -- create user asign default ts
alter user usr1 identified by oracle;
select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%DEFAULT%';  --  find database default tablespace

alter user usr1 default tablespace example; --  change default tablespace of a user

Note: The objects created in the old tablespace remain unchanged even after changing a default tablespace for a user

  • tablespace quota
Alter user usr1 quota 10M on users;

Note: Allocating quota doesn’t represent reserving the space. If 2 or more users are sharing a tablespace, quota will be filled up in first come first serve basis

Roles in Oracle

  • new user must at least have assigned CREATE SESSIONS privilege so the user can connect to the database
grant create session to usr1;
  • create role
CREATE ROLE SALES_MANAGER;
GRANT INSERT ON SCOTT.EMP TO SALES_MANAGER;
GRANT INSERT ON SCOTT.DEPT TO SALES_MANAGER;
GRANT UPDATE ON SCOTT.DEPT TO SALES_MANAGER;
GRANT DELETE ON SCOTT.BONUS TO SALES_MANAGER;
  • Grant Role to a User
SQL> GRANT SALES_MANAGER TO USR1;

Profile Management in Oracle

Password Management

The password management allows a DBA to have more control over user passwords. Some of the parameters you might be familiar in general like failed login attempts, password lock time etc

  • FAILED_LOGIN_ATTEMPTS: How many times a user can fail to login
  • PASSWORD_LOCK_TIME: Users who exceed failed login attempts, their password will be locked for specific time
  • PASSWORD_LIFE_TIME: Till when password is valid in days
  • PASSWORD_GRACE_TIME: Grace period for user to change password, else account will be locked
  • PASSWORD_REUSE_TIME: After how many days user can re-use same password
  • PASSWORD_REUSE_MAX: Specify how many times old password can be used
  • PASSWORD_VERIFY_FUNCTION: Defines rules for setting a new password

Resource Management

Resource management helps in limiting the database abuse a user can cause. For example, if a user connects to database and never runs a query then this ideal connection will take system resources like CPU. To restrict such kind of issues, we have resource management parameters

  • SESSIONS_PER_USER: How many concurrent sessions user can open
  • IDLE_TIME: Total time user can stay inside database without doing any activity
  • CONNECT_TIME: Total time user can stay inside database whether idle of active Note: resource management parameters will take in effect only if RESOURCE_LIMIT parameter is set to TRUE.

Use below command to check the RESOURCE_LIMIT parameter

SQL> show parameter resource_limit;

By default the parameter is set to FALSE. You can change it via below

SQL> alter system set resource_limit=TRUE scope=both;

To create a new user profile

SQL> create profile my_profile limit
failed_login_attempts 3
password_lock_time 1
sessions_per_user 1
idle_time 5;

Note: password lock time by default is for 1 day. You can specify it in minutes (n/1440) or even in seconds (n/86400)

To assign profile to a user

SQL> alter user scott profile my_profile;

To check profiles assigned to a user

SQL> SELECT USERNAME, PROFILE FROM DBA_USERS WHERE USERNAME='SCOTT';

To check profile parameter values

SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE='&PROFILE_NAME';

Find User Permissions

  • To check system privileges granted to a user
select privilege from dba_sys_privs where grantee='SCOTT';
  • To check object level privileges granted to a user or role
SQL> select owner, table_name, privilege from dba_tab_privs where grantee='SALES_CLERK';
  • To check roles assigned to a user
select granted_role from dba_role_privs where grantee='SCOTT';
  • To check permissions assigned to role
select privilege from role_sys_privs where role='SALES_CLERK';
select owner, table_name, privilege from role_tab_privs where role='SALES_CLERK';
  • To check roles granted to another role
SQL> select granted_role from role_role_privs where role='SALES_CLERK';

Przywrócenie kontu użytkownika zaszyfrowanego hasła, jakie miał wcześniej.

Oracle przechowuje zaszyfrowane hasła użytkowników w tabeli USER$, w kolumnie `SPARE4`` (dla hasła w formacie 12C, tzn. od Oracle 12c wzwyż). Hasła te są zapisane jako tzw. hash, a czasami z dodatkowymi metadanymi, jak S: (SHA1 hash) i T: (SHA512 hash).

Polecenie ALTER USER ... IDENTIFIED BY VALUES pozwala przywrócić konto użytkownika z tym samym zaszyfrowanym hasłem, jakie miał wcześniej.

SQL> SELECT username,password_versions FROM dba_users WHERE 
username='HR';
USERNAME     PASSWORD_VERSIONS
------------ -----------------
HR           11G 12C

Wygeneruj polecenie SQL dla oryginalnego hasła

SQL> SELECT 'ALTER USER '||name||' IDENTIFIED BY VALUES 
'''||SPARE4||''';'
  2  FROM user$ WHERE name='HR';
'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||SPARE4||''';'
------------------------------------------------------------
ALTER USER HR IDENTIFIED BY VALUES 
'S:324A084F477A484C84185ACB7B549937582D4392977037A800E62469B
F29;T:8EA88B8229AA7ED34FD832790C29C2EB4AAC37170F7CB9E7106D8B
4459746932D59AC2EE0D3B68F7DAB5F9CD4A82154A001DB73D1B3E64E259
39D41C1418154345CD68D3C0186463EE8F6FDF37BDC0FF';

Kiedy to jest potrzebne?

  • Migracja danych lub użytkowników – Gdy przenosisz konta użytkowników między bazami danych i chcesz zachować ich hasła.
  • Odtwarzanie konta użytkownika – Jeśli przypadkowo usuniesz konto i chcesz je przywrócić z dokładnie tym samym hasłem.
  • Kopia zapasowa użytkowników – Możesz zapisać hashe użytkowników na potrzeby audytu, bezpieczeństwa lub późniejszego przywracania.
  • Przywracanie po rollbacku lub testach – W testowych środowiskach, gdzie przywracasz stan bazy do poprzedniego punktu.

Displays the DDL for a specific user

 cat sql/user_ddl.sql
-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/user_ddl.sql
-- Author       : Tim Hall
-- Description  : Displays the DDL for a specific user.
-- Call Syntax  : @user_ddl (username)
-- Last Modified: 07/08/2018
-- -----------------------------------------------------------------------------------

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

variable v_username VARCHAR2(30);

exec :v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u

cat u.sql
set linesize 160
set pagesize 80
set verify off
set head on
col username for a25
col account_status for a14 word_wrapped
col default_Tablespace for a25
col temp_tablespace for a20
col profile for a16

prompt Database users like '%&1%':

select
        user_id,
        username,
        account_status,
        created,
        profile,
        default_tablespace,
        temporary_tablespace temp_tablespace
from
        dba_users
where
        upper(username) like upper('%&1%')
order by username
/