Gestione Utenti: Schema, Quota, Profili, Ruoli, Privilegi

L'utenza SYSTEM viene verificata nel database perché non appartiene al gruppo dba e quindi si connette da sqlplus con il comando:

SQL> conn system/password
SQL> show user
USER is "SYSTEM"

Essendo l'amministratore del Sistema Operatvo, con questa utenza è possibile connettersi all'istanza oracle.
Questa utenza permette di eseguire Export, Import, monitoraggi ma no Backup e Shutdown:
SQL> shutdown immediate;
ORA-01031: insufficient privileges

Un utente è individuato dal suo nome univoco, dal metodo di autenticazione, da un tablespace predefinito (e dai privilegi al suo interno) e uno temporaneo ma soprattutto dallo schema.
Lo Schema è un'area logica del db che contiene la totalità degli oggetti di pertinenza di un utente. Può essere visto come un db all'interno del vero db.
Di conseguenza ogni Server Process associato ad un utente, cerca nello schema dell'utente all'interno del db. Un utente può essere una persona, un'applicazione o un dispositivo.

SQL> conn hr/oracle;
SQL> select * from CAT;
E' un sinonimo, vista degli oggetti che appartengono ad hr.

SQL> conn sh/oracle;
SQL> select * from employees;
ERROR....tabella o vista insesistente

Il dba può assegnare ad sh le grant per visualizzare la tabella employees invece di copiarla nello schema sh che occuperebbe spazio nel datafile.

Non è consigliabile associare a due utenze lo stesso tablespace perché se va offline, nessuna delle due utenze si può collegare al db. La best practice prevede di associare ad ogni schema un tablespace così da permettere anche un backup mirato.
Di default ad ogni utenza è associato il tablespace USERS e quindi i suoi datafile.
Inoltre ad ogni utente è associato una QUOTA, cioè una concessione di spazio all'interno dello stesso tablespace .

Per visualizzare i tablespace associati agli utenti:
SQL> select username, default_tablespace from DBA_USERS;
USERNAME       DEFAULT_TABLESPACE
----------------- ------------------------------
SYS                           SYSTEM
SYSTEM                   SYSTEM
SH                             USERS
HR                             USERS

S i n t a s s  i per la creazione di un utente

CREATE USER demo1 
IDENTIFIED BY <password> 
DEFAULT TABLESPACE demo_tbs1 
TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

QUOTA 10M | UNLIMITED ON demo_tbs1;

L'utente creato non ha alcun privilegio.
Da EM andare sotto la sezione Security\Users.

Attenzione non è possibile associare ad un utenza il tablespace UNDOTBS1 perché è usato per le transazioni.
Infatti dal seguente comando:
SQL> show parameter UNDO;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_management             string      AUTO
undo_retention                   integer     900
undo_tablespace                 string      UNDOTBS1

undo_management = AUTO vuol dire il suddetto tablespace è gestito in automatico dal sistema.

Non è ancora possibile connettersi come demo1 perché non abbiamo assegnato alcun privilegio:
SQL> conn demo1/oracle;
ERROR: ORA-01045: user DEMO1 lacks CREATE SESSION privilege; logon denied

Collegarsi come Sysdba e diamo le grant di connessione al db CONNECT che include il privilegio "Create Session".

SQL> conn /as sysdba;
Connected.
SQL> GRANT CONNECT TO demo1;
Grant succeeded.
SQL> conn demo1/oracle;
Connected.


Il tablespace demo_tbs1 è vuoto infatti la seguente query non restituisce righe.
SQL> select * from CAT;
no rows selected
Potremmo creare una tabella e popolarla, ma l'utente demo1 non ha ancora i corrispondenti privilegi.
SQL> create table X (id number);
create table X (id number)
ERROR at line 1:
ORA-01031: insufficient privileges

                                                                           PRIVILEGI                                                                  

Per individuare i privilegi delle utenze e cioè il diritto di eseguire specifiche istruzioni SQL e di accesso agli oggetti di altri utenti usiamo la seguente query.

sqlplus / as sysdba
SQL> select * from DBA_SYS_PRIVS where grantee= 'DEMO1';

Accedendo con utenza DEMO1:
SQL> select * from USER_SYS_PRIVS where username = 'DEMO1';
no rows selected.

Il messaggio indica che l'utente ha solo le grant di Select. Si tratta di una vista di sistema che può essere letta da tutte le utenze che hanno la grant di connessione; è una porzione del Data Dictionary.

SQL> conn /as sysdba;
Connected.
SQL> GRANT CREATE TABLE TO demo1;
Grant succeeded.

SQL> select * from USER_SYS_PRIVS where username = 'DEMO1';
USERNAME                       PRIVILEGE                                ADM
------------------------------ ----------------------------------------------------
DEMO1                          CREATE TABLE                             NO

Il privilegio Create Table contiene implicitamente i comandi select, insert, delete, alter table, commit.
Accediamo come demo1 ed eseguiamo le seguente dml.
SQL> create table X (coll date);
Table created.

La tabella è creata su demo_tbs1 anche se già ne dovesse esistere una con lo stesso nome X sul un altro schema.

                                                                                 RUOLI                                                                     

I ruoli non sono altro che pacchetti di privilegi. Devono essere interrogati come se fossero utenti.

SQL> select * from ROLE_SYS_PRIVS where ROLE  like 'RESOURCE%'
ROLE                     PRIVILEGE                    ADM
------------------------------ ------------------------------
CONNECT           CREATE SESSION           NO

Stesso risualtato da questa passando però il ruolo come utente.

SQL>  select * from DBA_SYS_PRIVS where grantee  like 'RESOURCE%'

SQL> conn /as sysdba;
SQL> CREATE ROLE student;
SQL> GRANT create session, create table, create synonym, create procedure, create index TO student;

Da EM i ruoli sono visibili sotto Server\Security\Roles che coincide con la seguente query:

SQL> select * from ROLE_SYS_PRIVS where role = 'STUDENT';

ROLE                           PRIVILEGE                 ADM
------------------------------ ---------------------------------
STUDENT              CREATE SYNONYM       NO
STUDENT              CREATE SESSION           NO
STUDENT              CREATE TABLE               NO

SQL> create users demo3 identified by oracle;
SQL> alter user deom3 quota 10m on USERS;
SQL> GRANT student TO demo3;

Posso dare ulteriori privilegi singoli a demo3.

Se si accede come sysdba per visualizzare i ruoli associati ad un utente;

SELECT * FROM DBA_ROLE_PRIVS where grantee ='<user>';

I privilegi dei ruoli sulle tabelle.

SQL> select * from ROLE_TAB_PRIVS;

Un ruolo non può essere rimosso con il comando Revoke ma può essere droppato.

DROP ROLE <nome_ruolo> FROM <user>;

E' possibile revocare da un ruolo alcuni privigeli

REVOKE <privilege> FROM <nome_ruolo>

Il privilegio può essere sia di sistema come "create synonym" che sugli oggetti "update|insert|delete.... on <nome_tabella>" 

SUPERAMENTO DELLA QUOTA

Colleghiamoci come demo1 ed dopo aver creato la tabella Y effettuiamo enne insert. 
Ad un certo punto non si possono fare più inserimenti perché è stata superata la quota di 10m sul tbs demo_tbs1.

SQL> insert into Y (select * from y);
insert into Y (select * from y)
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'DEMO_TBS1'

Anche se non effettuo commit, e quindi lavoro in cache, viene eseguito un controllo preliminare sullo spazio (quota) senza dover aspettare un Chekpoint e quindi che si avvii il processo DBWR.

GRANT PER LA CREAZIONE DI SINONIMI

SQL> conn hr/oracle
Connected.
SQL> Grant select on employees TO demo1;
Grant succeeded.
SQL> conn demo1/oracle;
Connected.
SQL> select *from employees;
select *from hr.employees
ERROR at line 1:
ORA-00942: table or view does not exist

Demo1 non vede la tabella perché appartiene allo schema hr;  nella query occorre specificare lo schema.

SQL> select * from hr.employees;
107 row selected
SQL> CREATE SYNONYM employees FOR hr.employees;
ERROR at line 1:
ORA-01031: insufficient privileges

Va in errore perché demo1 non ha la grant per creare i sinonimi.

SQL> conn /as sysdba;
SQL> Grant create synonym TO demo1;
Grant succeeded.
SQL> conn demo1/oracle;
SQL> Create Synonym employees FOR hr.employees;
Synonym created.
SQl> select count(*) from employees;
107 row selected

Per vedere tutti i sinonimi:
SQL> select SYNONYM_NAME , TABLE_OWNER, TABLE_NAME from USER_SYNONYMS;

SYNONYM_NAME  TABLE_OWNER  TABLE_NAME
-------------------- --------------------------------------------------------
EMPLOYEES          HR                        EMPLOYEES

SQL> select OWNER,  TABLE_NAME, GRANTOR , PRIVILEGE 
from USER_TAB_PRIVS_RECD;

OWNER  TABLE_NAME  GRANTOR  PRIVILEGE
--------- --------------------------------------------------------
HR          EMPLOYEES   HR                SELECT

Grantor = indica chi ha dato il privilegio di select sulla tabella employees.

Per vedere le grant concesse da un utente sulle sue tabelle:
SQL> conn hr/oracle;
SQL> select GRANTEE , TABLE_NAME,GRANTOR ,PRIVILEGE from USER_TAB_PRIVS_MADE
where grantee ='DEMO1';

GRANTEE  TABLE_NAME GRANTOR  PRIVILEGE
-------- -----------------------------------------------------------
DEMO1       EMPLOYEES   HR              SELECT

La grant di update non implica quella di select.
SQL> conn hr/oracle;
Connected.
SQL> grant update on jobs to demo1;
Grant succeeded.
SQL> conn demo1/oracle;
Connected.
SQL> select * from hr.jobs;
select * from hr.jobs
ERROR at line 1:
ORA-01031: insufficient privileges

PRIVILEGI DI UN OGGETTO
SQL> conn hr/oracle;
SQL> grant update(MIN_SALARY) on jobs to demo1;
SQL> select * from jobs  where job_id ='ST_MAN';
JOB_ID     JOB_TITLE           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN  Stock Manager  5500                 8500

SQL> conn demo1/oracle;
SQL> update hr.jobs set MIN_SALARY = 200 where job_id ='ST_MAN';
1 row updated.
SQL> update hr.jobs set MAX_SALARY = 400 where job_id ='ST_MAN';
ERROR at line 1:
ORA-01031: insufficient privileges
Di seguito il comando per revocare un privilegio:
SQL> REVOKE select on employees FROM demo1;

                                                                    PROFILI                                                                                  

I profili sono limitazioni degli utenti all'utilizzo delle risorse del sistema e dell'istanza:

Ogni utente ha solo un profilo alla volta e quando viene creato ha un profilo di deafult.

- Connection Time: durata in minuti della connessione di un utente prima che venga automaticamente disconnesso;
- CPU/Session 

 - CPU/Call    esempio 3000 significa che una singola chiamata (come l'esecuzione di una procedura) fatta da un utente non può consumare più di 30 sec del tempo della CPU. Dopodichè viene killata;

- Idle Time: se l'utente non fa nulla dopo un certo tempo viene buttato fuori;
- Password: lunghezza password, data di scadenza e così via.

Queste informazioni sono presenti nel file utlpwdmg.sql al path
$ORACLE_HOME/rdbms/admin

Nel file  utlpwdmg.sql è scritto:
- check for mimin length od password
-elenco parole vietate
IF NLS_LOWER (password) IN ('...'...)

Se il parametro del databaase RESOURCE _LIMIT è false (valore di dafault) signifa che vengono ignorati i limiti di risorse specificato nei profili.

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

resource_limit                       boolean     FALSE

Per visualizzare i parametri dei profili:

SQL> select * from DBA_PROFILES where profile='DEFAULT';

Ad esempio FAILED_LOGIN_ATTEMPTS=10 vuol dire che dopo 10 tentativi errati di inseirmento della password l'account è locked.

Compare l'errore:

ERROR:

ORA-28000: the account is locked

PASSWORD_LIFE_TIME=180 vuol dire che dopo 180 giorni la pwd deve essere cambiata.

SESSIONS_PER_USER = 5 ad esempio indica che lo stesso utente si può collegarsi (ad esempio tramite sqlplus) al massimo 5 volte contemporanemante. La 6sta richiesta di connessione non viene ammessa.

select * from dba_profiles where resource_name in (

'PASSWORD_LIFE_TIME',

'PASSWORD_GRACE_TIME',

'PASSWORD_LOCK_TIME',

'FAILED_LOGIN_ATTEMPTS',

'PASSWORD_LOCK_TIME')  

Per modificare un profile di default

ALTER PROFILE DEFAULT   LIMIT PASSWORD_LIFE_TIME  UNLIMITED;