Usuários - Criação e alteração

http://ss64.com/ora/user_c.html


*************************************************************
--  VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA
*************************************************************
todos
SELECT USERNAME, created,account_status FROM DBA_USERS;

por usuário
SELECT USERNAME, created, account_status,FROM DBA_USERS WHERE lower (USERNAME)LIKE ‘USER%‘;

*************************************************************
-- CRIAÇÃO
*************************************************************

create USER nome_do_user identified by password;

lockar, deslocar
alter user nome_do_user ACCOUNT UNLOCK /LOCK;
ALTER user username password EXPIRE  -> forçar alteração de senha

OPÇÕES DE CRIAÇÃO

Syntax:

   CREATE USER username
      IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
         options;

options:
 
   DEFAULT TABLESPACE tablespace
   TEMPORARY TABLESPACE tablespace
   QUOTA int {K | M} ON tablespace
   QUOTA UNLIMITED ON tablespace
   PROFILE profile_name
   PASSWORD EXPIRE
   ACCOUNT {LOCK|UNLOCK}


*************************************************************
ATIVAÇÃO
*************************************************************

ALTER user username account lock
ALTER user username account unlock


*************************************************************
---VERIFICAR COTAS E TABLESPACES:
*************************************************************

set linesize 128
SELECT USERNAME, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE lower(USERNAME) LIKE 'USER%'
SELECT USERNAME, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE lower(USERNAME) LIKE 'scott%';

ALTERAR TABLESPACE
ALTER USER USERNAME TEMPORARY |DEFAULT  TABLESPACE tablespace_name


*************************************************************
--COTAS
*************************************************************

ALTERAR A COTA
ALTER USER nome_do_user quota tamanho_da_cota on nome_tablespace
alter user scott quota 10m on users;
alter user scott quota unlimited on example;

CHECAR COTA
select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='nome_do_user';
select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='GTORRES';

TABLESPACES PADRÃO DO BANCO
SELECT property_name , property_value from database_properties WHERE property_name like'%TABLESPACE';


*************************************************************
---  AUTENTICAÇÃO
*************************************************************

connect username/password@db_alias as privilegio
conn sys/oracle@orcl as sysdba

CHECAR OS PRIVILEGIOS SYSDBA E SYSOPER checar a view V$PWFILE_USERS
select *from V$PWFILE_USERS;

CHECAR AUTENTICAÇÃO PELO SO 
select value from v$parameter where name='os_authent_prefix';


CRIANDO  um user com autenticação pelo SO -> inserir o valor padrão ops$ antes do nome criado e após o parametro identified externally
NO Unix, qualquer usuário criado dessa maneira será capaz de emitir o comando sqlplus /
create user ops$nome_user indentified externally;

no windows:
create user "OPS$DOMINIO_DA_MAQUINA\USER_DO_SO" identified externally;


*************************************************************
-- GRANTS
*************************************************************

grant privilegio to user
revoke privilegio from user
ex:
GRANT select any table to username
GRANT CREATE SESSION to username
GRANT ROLE_NAME TO USER_NAME with admin option
GRANT privilegio on schema.user object to username
GRANT privilegio on schema.user object to username with GRANT option
GRANT privilegio on schema.user object to username with ADMIN option
grant all on schema.user to ROLE_NAME

checando privilégios
select *from dba_role_privs where grantee ='JON';


DROPANDO

DROP USER SchemaOwner CASCADE;

CREATE USER MySchemaOwner IDENTIFIED BY ChangeThis
       DEFAULT TABLESPACE data  
       TEMPORARY TABLESPACE temp
       QUOTA UNLIMITED ON data;

CREATE ROLE conn;

GRANT CREATE session, CREATE table, CREATE view, 
      CREATE procedure,CREATE synonym,
      ALTER table, ALTER view, ALTER procedure,ALTER synonym,
      DROP table, DROP view, DROP procedure,DROP synonym,
      TO conn;

GRANT conn TO SchemaOwner;