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;
