Differences

This shows you the differences between two versions of the page.

Link to this comparison view

oracle:oracle_sql_querys:users [2018/10/10 10:30] (current)
dodger created
Line 1: Line 1:
 +====== ORACLE SQL QUERIES : Users ======
 +
 +=====Listar=====
 +  * Todos:
 +<code sql>​SELECT USERNAME, PASSWORD, PROFILE from dba_users ;</​code>​
 +  * Activos:
 +<code sql>​SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS from dba_users WHERE ACCOUNT_STATUS LIKE '​OPEN';</​code>​
 +
 +=====Profiles=====
 +<code sql>
 +col profile format a30
 +col resource_name format a50 ;
 +col limit format a30
 +
 +SELECT *
 +FROM DBA_PROFILES ;
 +</​code>​
 +
 +=====Ver como esta creado=====
 +Esto se refiere a un "​usuario"​ pero viene siendo lo mismo:
 +<code sql>
 +select dbms_metadata.get_ddl(''​USER'',''​usuari''​) from dual;
 +</​code>​
 +
 +
 +=====Grants=====
 +==== De sistema ====
 +<code sql>
 +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
 +/
 +</​code>​
 +
 +Plain mode:
 +<code sql>
 +SELECT GRANTED_ROLE FROM (
 +SELECT grantee, granted_role FROM dba_role_privs
 +  UNION
 +SELECT grantee, privilege FROM dba_sys_privs
 +)
 +WHERE GRANTEE='​USERNAME';​
 +</​code>​
 +====Sobre Objetos====
 +
 +Mega query para obtener lo que sea:
 +<code sql>
 +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
 +;
 +</​code>​
 +
 +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:
 +<code SQL>
 +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;
 +</​code>​
 +  * Grants INdirectos:
 +<code sql>
 +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;
 +</​code>​
 +
 +=====quota sobre tablespaces=====
 +
 +<code sql>
 +select * from DBA_TS_QUOTAS order by TABLESPACE_NAME,​ USERNAME;
 +</​code>​
 +
 +=====Alta=====
 +<code sql>​CREATE USER "​USERNAME"​
 +    PROFILE "​DEFAULT" ​
 +    IDENTIFIED BY "​********"​ DEFAULT TABLESPACE "​TABLESPACE_DAT" ​
 +    TEMPORARY TABLESPACE "​TEMP" ​
 +    ACCOUNT UNLOCK ;
 +
 +grant CONNECT, RESOURCE to "​USERNAME"​ ;
 +</​code>​
 +=====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:
 +<code sql>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;
 +/
 +</​code>​
 +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:
 +<code sql>​CREATE USER "​USERNAME"​
 +    PROFILE "​DEFAULT" ​
 +    IDENTIFIED BY VALUES '<​PASSWORD>;<​SPARE4>'​
 +    DEFAULT TABLESPACE "​TABLESPACE_DAT" ​
 +    TEMPORARY TABLESPACE "​TEMP" ​
 +    ACCOUNT UNLOCK ;
 +</​code>​
 +
 +Podemos obtener previamente los datos necesarios (tablespace,​ profile...) con la query:
 +<code sql>​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'​ ;
 +</​code>​
 +
 +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).
 +
 +[[http://​marcel.vandewaters.nl/​oracle/​security/​password-hashes|Más info]]
 +=====Borrar=====
 +<code sql>drop user USERNAME cascade ;</​code>​
 +Cuidado con el cascade por el tema de privilegios concedidos (puede seguir el cascade a otros usuarios).
 +
 +=====Cambiar de usuario=====
 +<code sql>
 +ALTER SESSION SET CURRENT_SCHEMA="​NOMBRE_USUARIO";​
 +</​code>​
 +Entre comillas por que lo que se cambia así es el //​[[http://​download.oracle.com/​docs/​cd/​B19306_01/​server.102/​b14200/​statements_2012.htm#​i2143260|schema]]//​.
 +
 +Verificar:
 +<code sql>
 +select sys_context ('​userenv',​ '​current_schema'​) from dual ;
 +</​code>​
 +=====Cambiar password de usuario=====
 +<code sql>​ALTER USER username IDENTIFIED BY "​PASSWORD"​ ;</​code>​
 +
 +=====Desbloquear un usuario=====
 +<code sql>​alter user USERNAME account unlock;</​code>​
 +
 +=====Clonar un usuario=====
 +Para clonar un usuario sin clonar los datos (remap desde export) se puede usar el siguiente script:
 +<code sql>-- 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
 +</​code>​
 +
 +Esto genera un script SQL "<​code sql>/​tmp/​user_clone_tmp.sql</​code>"​ 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====
 +<code sql>
 +show parameter resource_limit ;
 +</​code>​
 +====Activar====
 +<code sql>​alter system set RESOURCE_LIMIT=true scope=both;
 +</​code>​
 +====Crear profile con limite====
 +<code sql>
 +CREATE PROFILE "​LIMIT"​ LIMIT SESSIONS_PER_USER 2;
 +</​code>​
 +y modificar el usuario:
 +<code sql>
 +ALTER USER USERNAME PROFILE LIMIT ;
 +</​code>​
  
  • oracle/oracle_sql_querys/users.txt
  • Last modified: 2018/10/10 10:30
  • by dodger