Differences

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

--- dba:oracle:oracle_sql_querys:users [2022/02/11 11:36] – external edit 127.0.0.1
+++ dba:oracle:oracle_sql_querys:users [2023/01/31 08:16] (current) – removed dodger
@@ Line 1: / Line 1: @@
-====== ORACLE SQL QUERIES : Users ======
-=====Listar=====
-  * Todos:
-<code sql>SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS from dba_users order by 1 ;</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#,
-, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE',
-, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
-, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
-, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE',
-, 'TYPE BODY', 19, 'TABLE PARTITION',
-, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY',
-, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE',
-, 'JAVA CLASS', 30, 'JAVA RESOURCE',
-, 'INDEXTYPE', 33, 'OPERATOR',
-, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
-, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
-, 'MATERIALIZED VIEW', 43, 'DIMENSION',
-, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
-, 'CONSUMER GROUP', 55, 'XML SCHEMA',
-, 'JAVA DATA', 57, 'EDITION', 59, 'RULE',
-, 'CAPTURE', 61, 'APPLY', 62, 'EVALUATION CONTEXT',
-, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS',
-, 'WINDOW', 72, 'SCHEDULER GROUP', 74, 'SCHEDULE',
-, 'CHAIN', 81, 'FILE GROUP', 82, 'MINING MODEL',
-, 'ASSEMBLY', 90, 'CREDENTIAL',
-, 'CUBE DIMENSION', 93, 'CUBE',
-, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
-, '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 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
-</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>