Overblog Suivre ce blog
Administration Créer mon blog
11 juin 2011 6 11 /06 /juin /2011 11:52

 

ORACLE SQL ALTER SYSTEM KILL SESSION

Comment tuer, déconnecter une session utilisateur existante ou tuer un processus d'arrière-plan Oracle?.

Syntax commande SQL ALTER SYSTEM KILL/DISCONNECT SESSION.

 
 ALTER SYSTEM { DISCONNECT SESSION 'sid, serial#' [ POST_TRANSACTION | IMMEDIATE ]} ; ALTER SYSTEM { KILL SESSION 'sid, serial#' [ IMMEDIATE ]} ; 

 

Trouver  une session active Oracle avec les vues GV$ (G=Grid=RAC)

 
 SQL> COLUMN inst_id FORMAT 9999 SQL> COLUMN sid FORMAT 9999 SQL> COLUMN serial# FORMAT 9999999 SQL> COLUMN spid FORMAT 9999 SQL> COLUMN username FORMAT A10 SQL> COLUMN program FORMAT A30 SQL> SET LINESIZE 150 SQL> SELECT s.inst_id, 2 s.sid, 3 s.serial#, 4 p.spid, 5 s.username, 6 s.program 7 FROM gv$session s 8 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id; INST_ID SID SERIAL# SPID USERNAME PROGRAM ------- ----- -------- ------------ ---------- ------------------------------ 1 170 1 5788 ORACLE.EXE (PMON) 1 169 1 5236 ORACLE.EXE (PSP0) 1 168 1 1304 ORACLE.EXE (MMAN) 1 167 1 4076 ORACLE.EXE (DBW0) 1 166 1 5328 ORACLE.EXE (LGWR) 1 165 1 3572 ORACLE.EXE (CKPT) 1 164 1 3932 ORACLE.EXE (SMON) 1 163 1 2292 ORACLE.EXE (RECO) 1 162 1 4000 ORACLE.EXE (MMON) 1 161 1 6064 ORACLE.EXE (MMNL) 1 160 3 5228 ORACLE.EXE (q000) INST_ID SID SERIAL# SPID USERNAME PROGRAM ------- ----- -------- ------------ ---------- ------------------------------ 1 158 1 6084 ORACLE.EXE (ARC0) 1 157 1 4432 ORACLE.EXE (ARC1) 1 140 14 3036 SYS sqlplus.exe 1 153 1 5912 ORACLE.EXE (QMNC) 1 143 5 4912 SYS sqlplus.exe 1 141 55 3804 SYSADM toad.exe 1 150 1 444 ORACLE.EXE (CJQ0) 1 148 1 3944 ORACLE.EXE (q001) 19 rows selected. SQL> 

 

Trouver  une session qui a une Transaction active.

 
 SQL> COLUMN STATUS FORMAT A10 SQL> COLUMN PROGRAM FORMAT A20 SQL> COLUMN MODULE FORMAT A20 SQL> SELECT T.STATUS, 2 T.START_TIME, 3 S.INST_ID, 4 S.SID, 5 S.SERIAL#, 6 S.USERNAME, 7 S.PROGRAM, 8 S.MODULE 9 FROM GV$TRANSACTION T 10 JOIN GV$SESSION S ON T.SES_ADDR = S.SADDR; STATUS START_TIME INST_ID SID SERIAL# USERNAME PROGRAM MODULE ---------- -------------------- ------- ----- -------- ---------- -------------------- ------------- ACTIVE 06/11/11 11:57:48 1 140 14 SYS sqlplus.exe sqlplus.exe SQL> 

 

KILL SESSION ORACLE.

 
 SQL> ALTER SYSTEM KILL SESSION '140,14'; SQL> ALTER SYSTEM KILL SESSION '140,14' IMMEDIATE; En RAC 10G SQL> ALTER SYSTEM KILL SESSION '140,14,1'; En RAC 11G SQL> ALTER SYSTEM KILL SESSION '140,14,@1'; 

 

DISCONNECT SESSION ORACLE.

 
 Attends la fin de la transaction existante et deconnexion (POST_TRANSACTION) SQL> ALTER SYSTEM DISCONNECT SESSION '140,14' POST_TRANSACTION; SQL> ALTER SYSTEM DISCONNECT SESSION '140,14' IMMEDIATE; 

 

Tuer une session Oracle avec ORAKILL à partir du système d'exploitation Windows.

 
 INST_ID SID SERIAL# SPID USERNAME PROGRAM ------- ----- -------- ------------ ---------- ------------------------------ 1 158 1 6084 ORACLE.EXE (ARC0) 1 157 1 4432 ORACLE.EXE (ARC1) 1 140 14 3036 SYS sqlplus.exe 1 153 1 5912 ORACLE.EXE (QMNC) 1 143 5 4912 SYS sqlplus.exe 1 141 55 3804 SYSADM toad.exe 1 150 1 444 ORACLE.EXE (CJQ0) 1 148 1 3944 ORACLE.EXE (q001) C:\> orakill 'instance_name' 'spid' C:\> orakill dbtest 3036 

 

Tuer un process Oracle avec kill -9 sous UNIX.

 
 % ps -ef | grep DBTEST oracle 4200 1 0 12:54:10 ? 0:01 ora_smon_DBTEST oracle 4201 1 0 12:54:10 ? 0:00 ora_arc0_DBTEST oracle 4210 1 0 12:54:10 ? 0:02 ora_lgwr_DBTEST oracle 4212 1 0 12:54:10 ? 0:00 ora_ckpt_DBTEST oracle 4230 1 2 11:32:19 ? 0:00 oracleTARGDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4237 1 0 12:54:10 ? 0:20 ora_qmn0_DBTEST oracle 4321 1 0 12:54:10 ? 0:00 ora_arc1_DBTEST oracle 4325 1 0 12:54:11 ? 0:00 ora_dbw0_DBTEST oracle 4340 1 0 12:54:11 ? 0:00 ora_reco_DBTEST oracle 4376 1 0 12:54:11 ? 0:02 ora_pmon_DBTEST oracle 4389 1 0 12:54:11 ? 0:00 ora_cjq0_DBTEST % kill -9 4230 

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Oracle USER Quota - Account Lock Unlock - Expire
• Create USER Oracle
Repost 0
Published by Daniel Roesch - dans SECURITE - USER
commenter cet article
2 juin 2011 4 02 /06 /juin /2011 11:18

 

ALTER USER Quota - Account Lock / Unlock / Expired.

Comment verrouiller, de-verrouiller un utilisateur Oracle.
Comment voir les Quota Tablespace d'un utilisateur dans Oracle.

Voici quelques commandes qui permettent de verrouiller un compte oracle, déverrouiller, forcer un changement de mot de passe à la prochaine connexion, de voir les entrées dans le fichier de mot de passe PWDsid.ora, de voir les USER qui ont des quotas sur les tablespace.

Comment Verrouiller un USER Oracle.

 
 SQL> SQL> ALTER USER SCOTT ACCOUNT LOCK; User altered. SQL> connect scott/tiger ERROR: ORA-28000: compte verrouille Warning: You are no longer connected to ORACLE. SQL> 

 

Comment déverrouiller un USER Oracle.

 
 SQL> SQL> ALTER USER SCOTT ACCOUNT UNLOCK; User altered. SQL> connect scott/tiger Connected. SQL> 

 

Comment forcer le changement de mot de passe d'un USER Oracle.

 
 SQL> SQL> ALTER USER SCOTT PASSWORD EXPIRE; User altered. SQL> connect scott/tiger ERROR: ORA-28001: le mot de passe est expire Changing password for scott New password: Retype new password: Password changed Connected. SQL> connect scott/change Connected. SQL> 

 

Comment voir les entrées USER du fichier de mot de passe PWDsid.ora

 
 SQL> SQL> SELECT * FROM V$PWFILE_USERS; USERNAME SYSDB SYSOP ------------------------------ ----- ----- SYS TRUE TRUE SQL> 

 

Comment voir les QUOTA et UNLIMITED par USER sur les TABLESPACES

 
 SQL> SQL> SET LINESIZE 110 SQL> SELECT USERNAME, 2 TABLESPACE_NAME, 3 DECODE (SIGN (MAX_BYTES), -1, 'UNLIMITED', 'QUOTA') "QUOTA ?" 4 FROM DBA_TS_QUOTAS 5 WHERE USERNAME IN ('RMAN', 'SCOTT'); USERNAME TABLESPACE_NAME QUOTA ? ------------------------------ ------------------------------ --------- RMAN RMAN_CATALOG UNLIMITED RMAN DATA QUOTA SCOTT DATA UNLIMITED SQL> 

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Create USER Oracle
Repost 0
Published by Daniel Roesch - dans SECURITE - USER
commenter cet article
2 octobre 2010 6 02 /10 /octobre /2010 18:23

 

Création d'un compte utilisateur (User) avec l'ordre SQL CREATE USER.

Comment créer un USER dans ma base de données Oracle ? .

Rappel :

• Un utilisateur Oracle peut être identifié par le Système d'exploitation ou par Oracle.
• Les droits utilisateurs sont gérés avec des privilèges systèmes (ex : droit de créér une table).
• Les droits utilisateurs sont gérés avec des privilèges objets (ex : droit de mettre à jour une table).
• Privilèges systèmes et  Privilèges objets peuvent être regroupés dans un RÔLE.
• Un RÔLE est un regroupement de privilèges systèmes et de privilèges objets.
• On définit à la création du USER le ou les Tablespace (ou aucun) dans lesquels il sera autorisé à créer des objets.
• On définit à la création du USER des Quota d'utilisation dans les Tablespace.

Voici la valeur de mes différents paramètres pour cet article :

Dans SQLNET.ORA --> SQLNET.AUTHENTICATION_SERVICES=(ALL)
Paramètre OS_AUTHENT_PREFIX --> alter system set os_authent_prefix='' scope=spfile sid='*';
Hostname de mon serveur Oracle --> SRV_ORA
Compte OS en cours --> DANIEL

 

Syntax ordre sql CREATE USER.

 
 CREATE USER user IDENTIFIED { BY password | EXTERNALLY [ AS 'certificate_DN' ] | GLOBALLY [ AS '[ directory_DN ]' ] } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } ] [ QUOTA integer [ K | M | G | T | P | E ] | UNLIMITED } ON tablespace ] | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK }; 

 

Création d'un compte utilisateur (User) identifié par le système d'exploitation.

 

 
 SQL> CREATE USER "SRV_ORA\DANIEL" IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK; 

A ce moment précis, aucune connexion possible encore.

 
 SQL> connect / ERROR: ORA-01045: l'utilisateur SRV_ORA\DANIEL n'a pas le privilege CREATE SESSION ; connexion refusee 

Nous allons donc donner le rôle CONNECT à l'utilisateur Daniel (CREATE SESSION fait parti de ce RÔLE).

 
 SQL> GRANT CONNECT TO "SRV_ORA\DANIEL"; Grant succeeded.
SQL> connect / Connected. SQL> show user USER is "SRV_ORA\DANIEL"

 

Création d'un compte utilisateur (User) identifié par Oracle.

 

 
 SQL> CREATE USER sysdan IDENTIFIED BY "sysdan" DEFAULT TABLESPACE "data" TEMPORARY TABLESPACE "temp" PROFILE DEFAULT QUOTA UNLIMITED ON "data" QUOTA 30M ON "data_vente" PASSWORD EXPIRE; SQL> GRANT "CONNECT" TO sysdan; 

L'utilisateur SYSDAN devra à la première connexion changer sont mot de passe.

 

Descriptions de quelques Options.

IDENTIFIED.
Cette clause indique la manière dont l'utilisateur sera identifié ( par l'OS ou par Oracle).

DEFAULT TABLESPACE.
Indique le Tablespace dans lequel les objets de l'utilisateur seront crées.

TEMPORARY TABLESPACE.
Indique le Tablespace dans lequel les tris par exemple seront effectués pour cet utilisateur.

QUOTA.
Limite d'espace attribué à l'utilisateur sur un Tablespace.

PASSWORD EXPIRE.
Permet de forcer un changement de mot de passe à la première connexion de l'utilisateur.

ACCOUNT LOCK / UNLOCK.
Permet de verrouiller ou déverrouiller le compte utilisateur.


Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Oracle Tablespace Annulation (UNDO)
• Oracle Tablespace Temporaire (TEMPORARY)
• Oracle Tablespace description
Repost 0
Published by Daniel Roesch - dans SECURITE - USER
commenter cet article
12 janvier 2010 2 12 /01 /janvier /2010 00:35

 

Encrypter vos données avec DBMS_CRYPTO.

Comment crypter des données dans Oracle.
Comment utiliser DBMS_CRYPTO.


DBMS_CRYPTO est un package Oracle 10g qui permet l’encryptions des données. Anciennement nommé DBMS_OBFUSCATION_TOOLKIT, ce nouveau package possède des algorithmes de cryptage et des fonctions de chiffrement plus puissants.

 

Je vais vous le présenter en action dans un package fonctionnel testé sur un Oracle 10gR2 en environnement Windows.

Nous allons utiliser l’algorithme de cryptage AES128 (Advanced Encryptions Standard 128 bits) + fonction de chiffrement par bloc (CHAIN_CBC) + fonction de padding (PAD_PKCS5).

 

 

1 – dbmsobtk.sql

 

Tout d’abord il vous faut exécuter le dbmsobtk.sql en user SYS qui se trouve dans ORACLE_HOME/rdbms/admin/  (création des Library,package,synonym)

 

SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\dbmsobtk.sql;

 

 

2 – GRANT DBMS_CRYPTO

 

Ensuite donner les droits d’exécutions et debug sur DBMS_CRYPTO.

 

SQL> GRANT EXECUTE, DEBUG ON DBMS_CRYPTO to PUBLIC;

 

 

3 – Création d’un fichier dans un répertoire Windows

 

Nous allons créer un fichier à l’aide de Notepad à la racine de C:\ , nous allons le nommer MA_CLE.DAT et y placer un texte (la clé en fait) sur 16 caractères.

 

Ex : cle128bitsoracle   -> (16 caractères)

 

Vous pouvez aussi stocker votre clé dans une table Oracle, mais dans cet exemple nous choisirons de mettre la clé en dehors de la BDD (un des avantages de cet exemple c’est que les données cryptées dans un export Full ou User sera illisible car la clé n’est pas exportée avec la base et la fonction de décryptage ou cryptage plantera simplement si les données sont chargées sur un autre environnement et c’est très bien d’ailleurs c’est le but recherché !!!).

 

 

4 – Création d’un DIRECTORY Oracle

 

Nous allons créer un DIRECTORY se nommant ’FICHIER_KEY’ dans oracle afin que nos fonctions puissent lire la clé dans le fichier C:\MA_CLE.DAT et ensuite attribution des droits de lecture. Connectez-vous dans SQLPLUS, TOAD ou SQL DEVELOPPER avec votre User BDD.

 

SQL> CREATE OR REPLACE DIRECTORY FICHIER_KEY as ’c:\’;

SQL> GRANT READ on DIRECTORY FICHIER_KEY TO Public ;

 

 

5 – Création de notre Package contenant nos deux fonctions

 

Connectez-vous avec votre User BDD. Remplacez USERBDD par votre user (Schéma).

Puis exécutez ces deux scripts. Les deux fonctions ont comme paramètre en entrée la chaine à Crypter ou la chaine à Décrypter.

 

CREATE OR REPLACE PACKAGE USERBDD.PACK_CRYPTAGE AS

 

  FUNCTION F_ENCRYPT(mdp IN varchar2) RETURN VARCHAR2;

  FUNCTION F_DECRYPT(mdp IN varchar2) RETURN VARCHAR2;

 

END PACK_CRYPTAGE;

/

CREATE OR REPLACE PACKAGE BODY USERBDD.PACK_CRYPTAGE AS

 

FUNCTION F_ENCRYPT(mdp IN varchar2) RETURN VARCHAR2 is

v_key               VARCHAR2(16);

v_fichier           VARCHAR2(10) := 'MA_CLE.DAT';

v_file UTL_FILE.FILE_TYPE;

l_enc_key           RAW(2000);

l_enc               RAW(2000);

l_mod pls_integer := DBMS_CRYPTO.ENCRYPT_AES128 +

 DBMS_CRYPTO.chain_cbc +

                     DBMS_CRYPTO.pad_pkcs5;

   

begin   

   v_file := utl_file.fopen('FICHIER_KEY', v_fichier,'r',128);

   utl_file.get_line(v_file,v_key,2000);

   utl_file.fclose(v_file);

       

     begin

        l_enc_key := utl_i18n.string_to_raw (v_key, 'AL32UTF8');

        l_enc := DBMS_CRYPTO.encrypt (utl_i18n.string_to_raw (mdp, 'AL32UTF8'),

                                      l_mod,

                                      l_enc_key);

     end;

     return RAWTOHEX(l_enc);

end;   

 

 

FUNCTION F_DECRYPT(mdp IN varchar2) RETURN VARCHAR2 is

v_key               VARCHAR2(16);

v_fichier           VARCHAR2(10) := 'MA_CLE.DAT';

v_file UTL_FILE.FILE_TYPE;

l_enc_key           RAW(2000);

l_dec               RAW(2000);

l_in_val            RAW(2000)      := HEXTORAW (mdp);

l_mod pls_integer := DBMS_CRYPTO.ENCRYPT_AES128 +

 DBMS_CRYPTO.chain_cbc +

 DBMS_CRYPTO.pad_pkcs5;

   

begin   

   v_file := utl_file.fopen('FICHIER_KEY', v_fichier,'r',128);

   utl_file.get_line(v_file,v_key,2000);

   utl_file.fclose(v_file);

           

     begin

        l_enc_key := utl_i18n.string_to_raw (v_key, 'AL32UTF8');

        l_dec := DBMS_CRYPTO.decrypt (l_in_val,

                                      l_mod,

                                      l_enc_key);

     end;

     return utl_i18n.raw_to_char(l_dec);

end;   

END;

/

 

 

6 – Autoriser l’exécution du Package TO Public (changer USERBDD par votre USER)

 

SQL> GRANT EXECUTE ON USERBDD.PACK_CRYPTAGE TO PUBLIC;

 

 

7 – On test les 2 fonctions?

 

On encrypte...

 

SQL> select pack_cryptage.f_encrypt('http://www.dba-ora.fr/') from dual;

 

 

Normalement si vous avez mis la clé (cle128bitsoracle) de l’exemple, ça vous donne ceci :

0E96283994B6D4DFC07623D9584002F0D137727A6965FBFE914611D1A91A23AB

 



 

On décrypte...

 

SQL> select pack_cryptage.f_decrypt('0E96283994B6D4DFC07623D9584002F0D137727A6965FBFE914611D1A91A23AB') from dual;

 

Ça vous donne ceci :

http://www.dba-ora.fr/

 

 

7 – On sécurise encore??

 

Vous me direz oui mais si un utilisateur averti tombe sur ce Package avec les deux fonctions, il peut facilement voir le chemin du fichier sur C:\, voir quel algorithme nous avons choisi etc etc...

Voilà une solution Oracle avec l’utilitaire WRAP utilisable en ligne de commande.

Placez vous dans le répertoire ou vous avez sauvegardé votre package et exécutez cette commande. (INAME= nom du package à compiler, ONAME= nom du package compilé)

 

C:\wrap iname=PACK_CRYPTAGE.pkb oname=PACK_CRYPTAGEW.pkb

 

 

Puis sous TOAD, SQLPLUS, ou SQL DEVELOPPER, ouvrez le nouveau package compilé et RECOMPILER le, maintenant c'est illisible!!

 

 

Repost 0
Published by Daniel R. - dans SECURITE - USER
commenter cet article
7 janvier 2010 4 07 /01 /janvier /2010 00:11

 

Autoriser, contrôler, refuser les connexions clientes Oracle.


Renforcer la sécurité de la BDD Oracle, contrôler et filtrer avec OracleNet les adresses IP des postes clients.
Pour cela, dans le fichier $ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA ajouter les paramètres suivants (tcp.validnode_checking, tcp.invited_nodes et/ou tcp.excluded_nodes)

 

tcp.validnode_checking = yes  

 

yes =filtre activé

no = filtre désactivé

 

Cette commande active le check IP/Hostname au niveau de votre module d’écoute, toutes les connexions passent par le module d’ecoute(LISTENER).

Elle va lire la liste tcp_invited_nodes et les autoriser ou lire la liste tcp.excluded_nodes et les bloquer à chaques demandes de connexions.

 

      tcp.invited_nodes (hostname1, hostname2, hostname3, IP 4, IP 5, etc)

      tcp.excluded_nodes (hostname6, hostname7, hostname8, IP 9, IP 10, etc)

 

Si vous modifiez les paramètres yes/no ou les listes d’accès invited ou excluded, pensez à faire un RELOAD (prise en compte des changements et pas d’interruption du module d’écoute) ou un STOP/START(prise en compte des changements mais interruption du module d’écoute)

 

      c:\lsnrctl reload

 

ou

 

      c:\lsnrctl stop

+

      c:\lsnrctl start

 

Cette configuration est très intéressante dans un environnement en architecture 3 tiers.
Les serveurs applicatifs (Citrix etc), serveurs Infocentre Business Object, EAI, Standby, Replication MV se retrouvent tous dans la liste
tcp.invited_nodes.


Il ne
vous reste plus qu’à rajouter à la liste d’éventuels postes clients d’administrations (TOAD, SQL DEVELOPER,…) et donner aussi l’accès à quelques développeurs.


Les tentatives de
connexions clientes indélicates dont les hostnames ou les IP ne seront pas dans la liste, auront un joli message d’alerte TNS CLOSED !!!

Repost 0
Published by Daniel R. - dans SECURITE - USER
commenter cet article