USERS

• Liste des users non système

SET lines 600
col USERNAME format a30
col ACCOUNT_STATUS format a20
col DEFAULT_TABLESPACE format a20
col PROFILE format a20
col LAST_LOGIN format a20
SELECT USERNAME,CREATED,ACCOUNT_STATUS,DEFAULT_TABLESPACE,PROFILE,LAST_LOGIN 
FROM dba_users
WHERE ORACLE_MAINTAINED='N';

• Liste des objets d'un user particulier

SELECT DISTINCT owner, tablespace_name FROM dba_segments WHERE owner IN ('xxx'); 

CREATE USER R2D2_READ_ONLY_USER identified BY "r2d2_2024";
grant CONNECT TO R2D2_READ_ONLY_USER ;
ALTER USER R2D2_READ_ONLY_USER profile LUCAS_PROFILE;
 
BEGIN
   FOR R IN (SELECT owner, object_name FROM dba_objects WHERE owner='R2D2USER' AND object_type IN ('TABLE','VIEW')) 
    LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.object_name||' to R2D2_READ_ONLY_USER ';
      EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM T24_READ_ONLY_USER.' || R.object_name || ' FOR ' || R.owner || '.' || R.object_name;
   END LOOP;
END;
/