dba:oracle:oracle_sql_querys:users
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
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> | ||
- | * Activos: | ||
- | <code sql> | ||
- | |||
- | =====Profiles===== | ||
- | <code sql> | ||
- | 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 " | ||
- | <code sql> | ||
- | select dbms_metadata.get_ddl('' | ||
- | </ | ||
- | |||
- | |||
- | =====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(' | ||
- | 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: | ||
- | <code sql> | ||
- | SELECT GRANTED_ROLE FROM ( | ||
- | SELECT grantee, granted_role FROM dba_role_privs | ||
- | UNION | ||
- | SELECT grantee, privilege FROM dba_sys_privs | ||
- | ) | ||
- | WHERE GRANTEE=' | ||
- | </ | ||
- | ====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_TYPE, | ||
- | -- | ||
- | GRANTOR, | ||
- | GRANTEE, | ||
- | -- | ||
- | PERMISSION_NAME | ||
- | FROM ( | ||
- | SELECT | ||
- | decode (OBJECT.TYPE#, | ||
- | 0, 'NEXT OBJECT', | ||
- | 3, ' | ||
- | 7, ' | ||
- | 11, ' | ||
- | 14, 'TYPE BODY', 19, 'TABLE PARTITION', | ||
- | 20, 'INDEX PARTITION', | ||
- | 23, ' | ||
- | 29, 'JAVA CLASS', | ||
- | 32, ' | ||
- | 34, 'TABLE SUBPARTITION', | ||
- | 40, 'LOB PARTITION', | ||
- | 42, ' | ||
- | 44, ' | ||
- | 48, ' | ||
- | 56, 'JAVA DATA', 57, ' | ||
- | 60, ' | ||
- | 66, ' | ||
- | 69, ' | ||
- | 79, ' | ||
- | 87, ' | ||
- | 92, 'CUBE DIMENSION', | ||
- | 94, ' | ||
- | 100, 'FILE WATCHER', | ||
- | ) OBJECT_TYPE, | ||
- | U.NAME | ||
- | UR.NAME | ||
- | UE.NAME | ||
- | OA.PRIVILEGE# | ||
- | MIN(TPM.NAME) PERMISSION_NAME | ||
- | FROM sys.objauth$ oa, | ||
- | sys." | ||
- | sys.USER$ U, | ||
- | sys.USER$ UR, | ||
- | sys.USER$ UE, | ||
- | sys.table_privilege_map TPM | ||
- | WHERE | ||
- | AND | ||
- | AND | ||
- | AND | ||
- | AND | ||
- | AND | ||
- | GROUP BY OBJECT.NAME, | ||
- | ) | ||
- | WHERE GRANTOR=' | ||
- | AND GRANTEE=' | ||
- | AND OWNER=' | ||
- | AND OBJECT_TYPE=' | ||
- | AND OBJECT_NAME=' | ||
- | ORDER BY OBJECT_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: | ||
- | <code SQL> | ||
- | SELECT owner, table_name, select_priv, | ||
- | FROM table_privileges | ||
- | WHERE grantee = '& | ||
- | ORDER BY owner, table_name; | ||
- | </ | ||
- | * 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 = '& | ||
- | ORDER BY owner, table_name; | ||
- | </ | ||
- | |||
- | =====quota sobre tablespaces===== | ||
- | |||
- | <code sql> | ||
- | select * from DBA_TS_QUOTAS order by TABLESPACE_NAME, | ||
- | </ | ||
- | |||
- | =====Alta===== | ||
- | <code sql> | ||
- | PROFILE " | ||
- | IDENTIFIED BY " | ||
- | TEMPORARY TABLESPACE " | ||
- | ACCOUNT UNLOCK ; | ||
- | |||
- | grant CONNECT, RESOURCE to " | ||
- | </ | ||
- | =====Re-Crear usuario===== | ||
- | Si el usuario ya existe/ | ||
- | <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 <> ' | ||
- | | ||
- | else | ||
- | goto end_loop; | ||
- | end if; | ||
- | end if; | ||
- | if (v_old_hash is null) then | ||
- | v_hash := '''' | ||
- | end if; | ||
- | if ((v_old_hash is not null) and (v_new_hash is not null)) then | ||
- | v_hash := '''' | ||
- | end if; | ||
- | stmt := 'alter user ' | ||
- | end if; | ||
- | | ||
- | << | ||
- | 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: | ||
- | <code sql> | ||
- | PROFILE " | ||
- | IDENTIFIED BY VALUES '< | ||
- | DEFAULT TABLESPACE " | ||
- | TEMPORARY TABLESPACE " | ||
- | ACCOUNT UNLOCK ; | ||
- | </ | ||
- | |||
- | Podemos obtener previamente los datos necesarios (tablespace, | ||
- | <code sql> | ||
- | ' IDENTIFIED BY VALUES ''' | ||
- | ''' | ||
- | ' TEMPORARY TABLESPACE | ||
- | ' PROFILE ' || D.PROFILE || | ||
- | ' ACCOUNT UNLOCK ; ' | ||
- | FROM DBA_USERS D, SYS.USER$ S | ||
- | WHERE D.USER_ID=S.USER# | ||
- | AND | ||
- | USERNAME = ' | ||
- | </ | ||
- | |||
- | Se usa el combo '< | ||
- | |||
- | [[http:// | ||
- | =====Borrar===== | ||
- | <code sql>drop user USERNAME cascade ;</ | ||
- | 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=" | ||
- | </ | ||
- | Entre comillas por que lo que se cambia así es el // | ||
- | |||
- | Verificar: | ||
- | <code sql> | ||
- | select sys_context (' | ||
- | </ | ||
- | =====Cambiar password de usuario===== | ||
- | <code sql> | ||
- | |||
- | =====Desbloquear un usuario===== | ||
- | <code sql> | ||
- | |||
- | =====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 | ||
- | where lower(username) = lower('& | ||
- | / | ||
- | |||
- | accept poo prompt ' | ||
- | spool / | ||
- | |||
- | select ' | ||
- | ' | ||
- | ' | ||
- | ' | ||
- | from | ||
- | where username = '&& | ||
- | / | ||
- | |||
- | |||
- | select 'alter user & | ||
- | | ||
- | , | ||
- | ' | ||
- | from | ||
- | where username = '&& | ||
- | / | ||
- | |||
- | select 'grant ' ||granted_role || ' to & | ||
- | | ||
- | from | ||
- | where grantee = '&& | ||
- | / | ||
- | |||
- | select 'grant ' || privilege || ' to & | ||
- | | ||
- | from | ||
- | where grantee = '&& | ||
- | / | ||
- | |||
- | select 'grant ' || privilege || ' to & | ||
- | | ||
- | from | ||
- | where grantee = '&& | ||
- | / | ||
- | |||
- | |||
- | spool off | ||
- | |||
- | undefine user | ||
- | |||
- | set verify on | ||
- | set feedback on | ||
- | set heading on | ||
- | </ | ||
- | |||
- | Esto genera un script SQL "< | ||
- | 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 ; | ||
- | </ | ||
- | ====Activar==== | ||
- | <code sql> | ||
- | </ | ||
- | ====Crear profile con limite==== | ||
- | <code sql> | ||
- | CREATE PROFILE " | ||
- | </ | ||
- | y modificar el usuario: | ||
- | <code sql> | ||
- | ALTER USER USERNAME PROFILE LIMIT ; | ||
- | </ | ||