1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
| connect sys as sysdba
create user admi5_admin identified by admi5_admin
default tablespace users
temporary tablespace temp
quota 1M on users;
grant all privileges to admi5_admin with admin option;
grant execute on dbms_rls to admi5_admin;
connect admi5_admin/admi5_admin;
create user Dupond identified by Dupond
default tablespace users
temporary tablespace temp
quota 1M on users;
grant create session to Dupond;
-- Création des tables, je n'en montre qu'une seule
create table adherent (
id_adherent varchar(8),
id_club number(8),
id_groupe number(10),
nom varchar(20),
prenom varchar(20),
sexe char,
annee_naissance number(4),
categorie varchar(10),
classement varchar(10),
adresse varchar(30),
telephone varchar(10),
capitaine number(1),
primary key (id_adherent),
foreign key (id_club) references club(id_club),
foreign key (id_groupe) references groupe(id_groupe)
);
create role admi5_role_adherent;
CREATE OR REPLACE FUNCTION auth_adh (schema_var IN VARCHAR2 DEFAULT NULL, table_var IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
return_val VARCHAR2(400);
BEGIN
IF USER='admi5_admin' THEN
return_val := '1=1' ;
ELSE
return_val := 'nom=SYS_CONTEXT(''userenv'',''session_user'')';
END IF;
RETURN return_val;
END auth_adh;
/
BEGIN
--DBMS_RLS.DROP_POLICY('admi5_admin','adherent','adh_policy');
DBMS_RLS.ADD_POLICY(
object_schema=>'admi5_admin',
object_name=>'adherent',
policy_name=>'adh_policy',
function_schema=>'admi5_admin',
policy_function=>'auth_adh',
statement_types=>'SELECT'
);
END;
/
GRANT SELECT ON adherent to admi5_role_adherent;
GRANT admi5_role_adherent to Dupond; |
Partager