Scribd
Upload
41 views2 pages

Script 3

This document creates and configures a profile, user, procedure, and function in Oracle. It creates a profile called PROFIL_TP3 that limits connection time, idle time, sessions per user, and password attempts. It then creates a user called TP3 with that profile, assigns a tablespace quota. It verifies the user and profile. It then creates a procedure to list all profiles and a function to verify passwords meet criteria before allowing the profile to use that function. It alters the profile to use the password function and does a test create with the new requirements.

Uploaded by

Yalla Habibi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
41 views2 pages

Script 3

This document creates and configures a profile, user, procedure, and function in Oracle. It creates a profile called PROFIL_TP3 that limits connection time, idle time, sessions per user, and password attempts. It then creates a user called TP3 with that profile, assigns a tablespace quota. It verifies the user and profile. It then creates a procedure to list all profiles and a function to verify passwords meet criteria before allowing the profile to use that function. It alters the profile to use the password function and does a test create with the new requirements.

Uploaded by

Yalla Habibi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

--TP3

--1
/*
CREATE PROFILE profil_tp3 LIMIT
connect_time 7200
idle_time 180
sessions_per_user 2
failed_login_attempts 1
password_lock_time 5/1440;
*/
--Pour vérifier :
--select * from dba_profiles where profile='PROFIL_TP3';
--2
/*
CREATE USER TP3 identified by TP3
default tablespace USERS
quota 10M on USERS
temporary tablespace Temp
Profile profil_tp3;
*/
--Pour vérifier
--select username from dba_users;
--3
/*
select profile from dba_users
where username='TP3';
*/
--4
--select username from dba_users;
--5
/*
CREATE OR REPLACE PROCEDURE ps_list_profil
IS
BEGIN
for i in (select distinct profile A
from dba_profiles)
LOOP
dbms_output.put_line(i.A);
END LOOP;
END;
/
*/
--APPEL :
--execute ps_list_profil
--6
/*
CREATE OR REPLACE FUNCTION verif_password
(utilisateur varchar,
password varchar,
old_password varchar )
RETURN boolean
IS
c integer :=0;
BEGIN
if length(password)<6 then
raise_application_error(-20000,
'le mdp doit contenir plus que
6 caracteres');
end if;
for i in 1..length(password) Loop
if substr(password,i,1) in ('?','!','@')
then c:=c+1;
end if;
end loop;
if c=0 then
raise_application_error(-20000,
'le mdp doit contenir au moins un caractere
special');
end if;
if utilisateur=password then
raise_application_error(-20000,
'le mdp doit etre different du username');
end if;
RETURN true;
END;
/
*/
--7
/*
ALTER PROFILE profil_tp3 LIMIT
password_verify_function verif_password;
*/
--8
--create user testtp31 identified by system profile profil_tp3;
--9
DROP PROFILE profil_tp3 cascade;
select profile from dba_users
where username='TP3';

You might also like