User Tools

Site Tools


oracle:oracle_sql_querys:users

ORACLE SQL QUERIES : Users

Listar

  • Todos:
SELECT USERNAME, PASSWORD, PROFILE FROM dba_users ;
  • Activos:
SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS LIKE 'OPEN';

Profiles

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

Ver como esta creado

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

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

Grants

De sistema

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';

Sobre Objetos

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:

  • table_privileges
  • dba_role_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:
SELECT owner, TABLE_NAME, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
  FROM table_privileges
 WHERE grantee = '&theUser'
 ORDER BY owner, TABLE_NAME;
  • Grants INdirectos:
SELECT DISTINCT owner, TABLE_NAME, PRIVILEGE 
  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = '&theUser'
 ORDER BY owner, TABLE_NAME;

quota sobre tablespaces

SELECT * FROM DBA_TS_QUOTAS ORDER BY TABLESPACE_NAME, USERNAME;

Alta

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

Re-Crear usuario

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 = ACCOUNT_STATUS OPEN
  • 1 = ACCOUNT_STATUS 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

Borrar

DROP USER USERNAME cascade ;

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

Cambiar de usuario

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 ;

Cambiar password de usuario

ALTER USER username IDENTIFIED BY "PASSWORD" ;

Desbloquear un usuario

ALTER USER USERNAME account UNLOCK;

Clonar un usuario

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 jholgado
-- 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.

Limites

Estado

SHOW parameter resource_limit ;

Activar

ALTER system SET RESOURCE_LIMIT=TRUE scope=BOTH;

Crear profile con limite

CREATE PROFILE "LIMIT" LIMIT SESSIONS_PER_USER 2;

y modificar el usuario:

ALTER USER USERNAME PROFILE LIMIT ;
oracle/oracle_sql_querys/users.txt · Last modified: 2019/07/18 09:17 (external edit)