• Todos:

SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS FROM dba_users ORDER BY 1 ;

• Activos:

SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS LIKE 'OPEN';

col profile format a30
col resource_name format a50 ;
col LIMIT format a30
 
SELECT *
FROM DBA_PROFILES ;

Esto se refiere a un “usuario” pero viene siendo lo mismo:

SELECT dbms_metadata.get_ddl(''USER'',''usuari'') FROM dual;

SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
    SELECT NULL grantee, username granted_role FROM dba_users WHERE username LIKE UPPER('%&uname%')
  UNION
    SELECT grantee, granted_role FROM dba_role_privs
  UNION
    SELECT grantee, privilege FROM dba_sys_privs
  )
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role
/

Plain mode:

SELECT GRANTED_ROLE FROM (
SELECT grantee, granted_role FROM dba_role_privs
  UNION
SELECT grantee, privilege FROM dba_sys_privs
)
WHERE GRANTEE='USERNAME';

Mega query para obtener lo que sea:

COL OBJECT_NAME FORMAT A35
COL OBJECT_TYPE FORMAT A25
COL OWNER FORMAT A33
COL GRANTOR FORMAT A33
COL GRANTEE FORMAT A33
COL PERMISSION_NAME FORMAT A50
SELECT  OBJECT_NAME, 
        OBJECT_TYPE, 
--         OWNER, 
        GRANTOR, 
        GRANTEE, 
--         PERMISSION, 
        PERMISSION_NAME
FROM (
    SELECT  OBJECT.NAME OBJECT_NAME,
            decode (OBJECT.TYPE#, 
                    0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 
                    3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                    7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                    11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 
                    14, 'TYPE BODY', 19, 'TABLE PARTITION', 
                    20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 
                    23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 
                    29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                    32, 'INDEXTYPE', 33, 'OPERATOR',
                    34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                    40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                    42, 'MATERIALIZED VIEW', 43, 'DIMENSION',
                    44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                    48, 'CONSUMER GROUP', 55, 'XML SCHEMA', 
                    56, 'JAVA DATA', 57, 'EDITION', 59, 'RULE',
                    60, 'CAPTURE', 61, 'APPLY', 62, 'EVALUATION CONTEXT',
                    66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 
                    69, 'WINDOW', 72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 
                    79, 'CHAIN', 81, 'FILE GROUP', 82, 'MINING MODEL', 
                    87, 'ASSEMBLY', 90, 'CREDENTIAL', 
                    92, 'CUBE DIMENSION', 93, 'CUBE', 
                    94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                    100, 'FILE WATCHER', 101, 'DESTINATION','UNDEFINED'
                    ) OBJECT_TYPE,
            U.NAME      OWNER, 
            UR.NAME     GRANTOR, 
            UE.NAME     GRANTEE,
            OA.PRIVILEGE#  PERMISSION,
            MIN(TPM.NAME) PERMISSION_NAME
    FROM    sys.objauth$ oa, 
            sys."_CURRENT_EDITION_OBJ" OBJECT,
            sys.USER$ U,
            sys.USER$ UR,
            sys.USER$ UE,
            sys.table_privilege_map TPM
    WHERE   OA.OBJ#=OBJECT.OBJ#
    AND     OA.COL# IS NULL
    AND     U.USER#=OBJECT.OWNER#
    AND     oa.grantor# = ur.USER#
    AND     oa.grantee# = ue.USER#
    AND     TPM.PRIVILEGE = OA.PRIVILEGE#
    GROUP BY OBJECT.NAME,U.NAME,OBJECT.TYPE#,UR.NAME,UE.NAME, OA.PRIVILEGE#
    )
WHERE GRANTOR='USERNAME'
AND GRANTEE='USERNAME'
AND OWNER='USERNAME'
AND OBJECT_TYPE='TABLE'
AND OBJECT_NAME='NAME'
ORDER BY OBJECT_NAME, PERMISSION_NAME
;

Aparte, los grants sobre objetos se detallan en las vistas:

• tableprivileges * dbarole_privs

De CADA ESQUEMA. Es decir, los que concede SYS están en su esquema, los que concede ADMINUSER están en su propio esquema. Hay que hacer switch a cada esquema para conocer sus grants

• Grants directos: <code SQL> SELECT owner, tablename, selectpriv, insertpriv, deletepriv, updatepriv, referencespriv, alterpriv, indexpriv FROM tableprivileges WHERE grantee = '&theUser' ORDER BY owner, tablename; </code>
• Grants INdirectos: <code sql> SELECT DISTINCT owner, tablename, PRIVILEGE FROM dbaroleprivs rp JOIN roletabprivs rtp ON (rp.grantedrole = rtp.role) WHERE rp.grantee = '&theUser' ORDER BY owner, table_name; </code>

SELECT * FROM DBA_TS_QUOTAS ORDER BY TABLESPACE_NAME, USERNAME;

CREATE USER "USERNAME"
    PROFILE "DEFAULT" 
    IDENTIFIED BY "********" DEFAULT TABLESPACE "TABLESPACE_DAT" 
    TEMPORARY TABLESPACE "TEMP" 
    ACCOUNT UNLOCK ;
 
GRANT CONNECT, RESOURCE TO "USERNAME" ;

Si el usuario ya existe/existía y lo que queremos es reutilizar su password, primero hemos de obtener el hash del password con el siguiente procedure:

SET serveroutput ON SIZE 200000
 
DECLARE
    stmt varchar2(200);
    v_old_hash USER$.password%TYPE;
    v_new_hash USER$.spare4%TYPE;
    v_hash varchar2(200);
BEGIN
   FOR user_rec IN (SELECT name, password, spare4 FROM USER$ WHERE TYPE#=1 AND astatus IN(0,1,2)) loop
      v_old_hash := user_rec.password;
      v_new_hash := user_rec.spare4;
      IF NOT ((v_old_hash IS NULL) AND (v_new_hash IS NULL)) THEN
         IF (v_new_hash IS NULL) THEN
             IF v_old_hash <> 'EXTERNAL' THEN
                 v_hash := ''''||v_old_hash||'''';
             ELSE
             GOTO end_loop;
         END IF;
      END IF;
      IF (v_old_hash IS NULL) THEN
          v_hash := ''''||v_new_hash||'''';
      END IF;
      IF ((v_old_hash IS NOT NULL) AND (v_new_hash IS NOT NULL)) THEN
          v_hash := ''''||v_old_hash||';'||v_new_hash||'''';
      END IF;
          stmt := 'alter user '||user_rec.name||' identified by values'||v_hash;
      END IF;
     dbms_output.put_line(stmt||';');
  <<end_loop>>
  NULL;
  END loop;
END;
/

La columna ASTATUS de USER$ la he limitado a 3 valores:

• 0 = ACCOUNTSTATUS OPEN * 1 = ACCOUNTSTATUS EXPIRED
• 2 = ACCOUNT_STATUS EXPIRED(GRACE)

Y luego usarlo en la query:

CREATE USER "USERNAME"
    PROFILE "DEFAULT" 
    IDENTIFIED BY VALUES '<PASSWORD>;<SPARE4>'
    DEFAULT TABLESPACE "TABLESPACE_DAT" 
    TEMPORARY TABLESPACE "TEMP" 
    ACCOUNT UNLOCK ;

Podemos obtener previamente los datos necesarios (tablespace, profile…) con la query:

SELECT 'CREATE USER ' || USERNAME ||
    ' IDENTIFIED BY VALUES ''' || S.PASSWORD || ';' || S.SPARE4 ||
    ''' DEFAULT TABLESPACE ' || D.DEFAULT_TABLESPACE ||
    ' TEMPORARY TABLESPACE  ' || D.TEMPORARY_TABLESPACE ||
    ' PROFILE ' || D.PROFILE || 
    ' ACCOUNT UNLOCK ; '
    FROM DBA_USERS D, SYS.USER$ S 
    WHERE D.USER_ID=S.USER# 
    AND
    USERNAME = 'VOXELADMIN' ;

Se usa el combo '<11g password hash>;<10g password hash>' por que si especificamos HASH normal o SPARE4, el otro deja de existir (en caso de que exista).

Más info

DROP USER USERNAME cascade ;

Cuidado con el cascade por el tema de privilegios concedidos (puede seguir el cascade a otros usuarios).

ALTER SESSION SET CURRENT_SCHEMA="NOMBRE_USUARIO";

Entre comillas por que lo que se cambia así es el schema.

Verificar:

SELECT sys_context ('userenv', 'current_schema') FROM dual ;

ALTER USER username IDENTIFIED BY "PASSWORD" ;

ALTER USER USERNAME account UNLOCK;

Para clonar un usuario sin clonar los datos (remap desde export) se puede usar el siguiente script:

-- user_clone.sql
-- Andy Barry/A
-- 20/02/06
-- modified by dodger
-- 17/02/2010
 
SET LINES 999 pages 999
SET verify off
SET feedback off
SET heading off
 
SELECT username FROM dba_users ORDER BY username
/
 
undefine USER
 
accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '
 
SELECT username, created
FROM   dba_users
WHERE  LOWER(username) = LOWER('&newuser')
/
 
accept poo prompt 'Continue? (ctrl-c to exit)'
spool /tmp/user_clone_tmp.sql
 
SELECT 'create user ' || '&newuser' ||
       ' identified by ' || '&passwd' ||
       ' default tablespace ' || default_tablespace ||
       ' temporary tablespace ' || temporary_tablespace || ';' "user"
FROM   dba_users
WHERE  username = '&&userid'
/
 
 
SELECT 'alter user &newuser quota '||
       decode(max_bytes, -1, 'unlimited'
       ,                     CEIL(max_bytes / 1024 / 1024) || 'M') ||
       ' on ' || tablespace_name || ';'
FROM   dba_ts_quotas
WHERE  username = '&&userid'
/
 
SELECT 'grant ' ||granted_role || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
FROM   dba_role_privs
WHERE  grantee = '&&userid'
/
 
SELECT 'grant ' || privilege || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
FROM   dba_sys_privs
WHERE  grantee = '&&userid'
/
 
SELECT 'grant ' || privilege || ' to &newuser' ||
       decode(GRANTABLE, 'NO', ';', 'YES', ' with grant option;') "PRIV"
FROM   dba_tab_privs
WHERE  grantee = '&&userid'
/
 
 
spool off
 
undefine USER
 
SET verify ON
SET feedback ON
SET heading ON

Esto genera un script SQL “

/tmp/user_clone_tmp.sql

” que contiene todos los privilegios del usuario objetivo. Hay que tener en cuenta si queremos que el usuario tenga los datos en un tablespace diferente, para lo cual hay que cambiar el create user por supuesto.

SHOW parameter resource_limit ;

ALTER system SET RESOURCE_LIMIT=TRUE scope=BOTH;

CREATE PROFILE "LIMIT" LIMIT SESSIONS_PER_USER 2;

y modificar el usuario:

ALTER USER USERNAME PROFILE LIMIT ;