Cria usuário no Oracle 11g com privilégios iguais aos do Oracle 10g XE

Question

Eu sou um desenvolvedor PHP (não um DBA) e tenho trabalhado com o Oracle 10g XE por um tempo. Estou acostumado com o gerenciamento simplificado de usuários do XE:

1. Ir para Administração / Usuários / Criar usuário
2. Atribuir nome de usuário e senha
3. Funções: deixe as configurações padrão (conexão e recurso)
4. Privilégios: clique em "Ativar todos" para selecionar os 11 possíveis
5. Criar

Dessa forma, recebo um usuário com acesso total aos dados e sem acesso a todo o resto. Isso é bom, pois eu só preciso desenvolver meu aplicativo. Quando o aplicativo for implantado, os DBAs do cliente configuram o ambiente.

Agora tenho que criar usuários em um servidor Oracle 11g completo e estou completamente perdido. Eu tenho um novo conceito (perfis) e há 20 funções e centenas de privilégios em várias categorias.

Quais etapas eu preciso concluir no Oracle Enterprise Manager para obter um usuário com os mesmos privilégios que usei para atribuir no XE?

==== UPDATE ====

Acho melhor fornecer uma explicação detalhada para que eu fique mais claro.

É assim que eu crio um usuário em 10g XE :

Roles:
    [X] CONNECT
    [X] RESOURCE
    [ ] DBA
Direct Asignment System Privileges:
    [ ] CREATE DATABASE LINK
    [ ] CREATE MATERIALIZED VIEW
    [ ] CREATE PROCEDURE
    [ ] CREATE PUBLIC SYNONYM
    [ ] CREATE ROLE
    [ ] CREATE SEQUENCE
    [ ] CREATE SYNONYM
    [ ] CREATE TABLE
    [ ] CREATE TRIGGER
    [ ] CREATE TYPE
    [ ] CREATE VIEW

Clico em Ativar tudo e pronto.

Isso é o que me perguntam ao fazer o mesmo em 11g :

Profile:
    (*) DEFAULT
    ( ) WKSYS_PROF
    ( ) MONITORING_PROFILE
Roles:
    CONNECT:
        [ ] Admin option
        [X] Default value
    Edit List:
        AQ_ADMINISTRATOR_ROLE
        AQ_USER_ROLE
        AUTHENTICATEDUSER
        CSW_USR_ROLE
        CTXAPP
        CWM_USER
        DATAPUMP_EXP_FULL_DATABASE
        DATAPUMP_IMP_FULL_DATABASE
        DBA
        DELETE_CATALOG_ROLE
        EJBCLIENT
        EXECUTE_CATALOG_ROLE
        EXP_FULL_DATABASE
        GATHER_SYSTEM_STATISTICS
        GLOBAL_AQ_USER_ROLE
        HS_ADMIN_ROLE
        IMP_FULL_DATABASE
        JAVADEBUGPRIV
        JAVAIDPRIV
        JAVASYSPRIV
        JAVAUSERPRIV
        JAVA_ADMIN
        JAVA_DEPLOY
        JMXSERVER
        LOGSTDBY_ADMINISTRATOR
        MGMT_USER
        OEM_ADVISOR
        OEM_MONITOR
        OLAPI_TRACE_USER
        OLAP_DBA
        OLAP_USER
        OLAP_XS_ADMIN
        ORDADMIN
        OWB$CLIENT
        OWB_DESIGNCENTER_VIEW
        OWB_USER
        RECOVERY_CATALOG_OWNER
        RESOURCE
        SCHEDULER_ADMIN
        SELECT_CATALOG_ROLE
        SPATIAL_CSW_ADMIN
        SPATIAL_WFS_ADMIN
        WFS_USR_ROLE
        WKUSER
        WM_ADMIN_ROLE
        XDBADMIN
        XDB_SET_INVOKER
        XDB_WEBSERVICES
        XDB_WEBSERVICES_OVER_HTTP
        XDB_WEBSERVICES_WITH_PUBLIC
System Privileges:
    <Empty>
    Edit List:
        ACCESS_ANY_WORKSPACE
        ADMINISTER ANY SQL TUNING SET
        ADMINISTER DATABASE TRIGGER
        ADMINISTER RESOURCE MANAGER
        ADMINISTER SQL MANAGEMENT OBJECT
        ADMINISTER SQL TUNING SET
        ADVISOR
        ALTER ANY ASSEMBLY
        ALTER ANY CLUSTER
        ALTER ANY CUBE
        ALTER ANY CUBE DIMENSION
        ALTER ANY DIMENSION
        ALTER ANY EDITION
        ALTER ANY EVALUATION CONTEXT
        ALTER ANY INDEX
        ALTER ANY INDEXTYPE
        ALTER ANY LIBRARY
        ALTER ANY MATERIALIZED VIEW
        ALTER ANY MINING MODEL
        ALTER ANY OPERATOR
        ALTER ANY OUTLINE
        ALTER ANY PROCEDURE
        ALTER ANY ROLE
        ALTER ANY RULE
        ALTER ANY RULE SET
        ALTER ANY SEQUENCE
        ALTER ANY SQL PROFILE
        ALTER ANY TABLE
        ALTER ANY TRIGGER
        ALTER ANY TYPE
        ALTER DATABASE
        ALTER PROFILE
        ALTER RESOURCE COST
        ALTER ROLLBACK SEGMENT
        ALTER SESSION
        ALTER SYSTEM
        ALTER TABLESPACE
        ALTER USER
        ANALYZE ANY
        ANALYZE ANY DICTIONARY
        AUDIT ANY
        AUDIT SYSTEM
        BACKUP ANY TABLE
        BECOME USER
        CHANGE NOTIFICATION
        COMMENT ANY MINING MODEL
        COMMENT ANY TABLE
        CREATE ANY ASSEMBLY
        CREATE ANY CLUSTER
        CREATE ANY CONTEXT
        CREATE ANY CUBE
        CREATE ANY CUBE BUILD PROCESS
        CREATE ANY CUBE DIMENSION
        CREATE ANY DIMENSION
        CREATE ANY DIRECTORY
        CREATE ANY EDITION
        CREATE ANY EVALUATION CONTEXT
        CREATE ANY INDEX
        CREATE ANY INDEXTYPE
        CREATE ANY JOB
        CREATE ANY LIBRARY
        CREATE ANY MATERIALIZED VIEW
        CREATE ANY MEASURE FOLDER
        CREATE ANY MINING MODEL
        CREATE ANY OPERATOR
        CREATE ANY OUTLINE
        CREATE ANY PROCEDURE
        CREATE ANY RULE
        CREATE ANY RULE SET
        CREATE ANY SEQUENCE
        CREATE ANY SQL PROFILE
        CREATE ANY SYNONYM
        CREATE ANY TABLE
        CREATE ANY TRIGGER
        CREATE ANY TYPE
        CREATE ANY VIEW
        CREATE ASSEMBLY
        CREATE CLUSTER
        CREATE CUBE
        CREATE CUBE BUILD PROCESS
        CREATE CUBE DIMENSION
        CREATE DATABASE LINK
        CREATE DIMENSION
        CREATE EVALUATION CONTEXT
        CREATE EXTERNAL JOB
        CREATE INDEXTYPE
        CREATE JOB
        CREATE LIBRARY
        CREATE MATERIALIZED VIEW
        CREATE MEASURE FOLDER
        CREATE MINING MODEL
        CREATE OPERATOR
        CREATE PROCEDURE
        CREATE PROFILE
        CREATE PUBLIC DATABASE LINK
        CREATE PUBLIC SYNONYM
        CREATE ROLE
        CREATE ROLLBACK SEGMENT
        CREATE RULE
        CREATE RULE SET
        CREATE SEQUENCE
        CREATE SESSION
        CREATE SYNONYM
        CREATE TABLE
        CREATE TABLESPACE
        CREATE TRIGGER
        CREATE TYPE
        CREATE USER
        CREATE VIEW
        CREATE_ANY_WORKSPACE
        DEBUG ANY PROCEDURE
        DEBUG CONNECT SESSION
        DELETE ANY CUBE DIMENSION
        DELETE ANY MEASURE FOLDER
        DELETE ANY TABLE
        DEQUEUE ANY QUEUE
        DROP ANY ASSEMBLY
        DROP ANY CLUSTER
        DROP ANY CONTEXT
        DROP ANY CUBE
        DROP ANY CUBE BUILD PROCESS
        DROP ANY CUBE DIMENSION
        DROP ANY DIMENSION
        DROP ANY DIRECTORY
        DROP ANY EDITION
        DROP ANY EVALUATION CONTEXT
        DROP ANY INDEX
        DROP ANY INDEXTYPE
        DROP ANY LIBRARY
        DROP ANY MATERIALIZED VIEW
        DROP ANY MEASURE FOLDER
        DROP ANY MINING MODEL
        DROP ANY OPERATOR
        DROP ANY OUTLINE
        DROP ANY PROCEDURE
        DROP ANY ROLE
        DROP ANY RULE
        DROP ANY RULE SET
        DROP ANY SEQUENCE
        DROP ANY SQL PROFILE
        DROP ANY SYNONYM
        DROP ANY TABLE
        DROP ANY TRIGGER
        DROP ANY TYPE
        DROP ANY VIEW
        DROP PROFILE
        DROP PUBLIC DATABASE LINK
        DROP PUBLIC SYNONYM
        DROP ROLLBACK SEGMENT
        DROP TABLESPACE
        DROP USER
        ENQUEUE ANY QUEUE
        EXECUTE ANY ASSEMBLY
        EXECUTE ANY CLASS
        EXECUTE ANY EVALUATION CONTEXT
        EXECUTE ANY INDEXTYPE
        EXECUTE ANY LIBRARY
        EXECUTE ANY OPERATOR
        EXECUTE ANY PROCEDURE
        EXECUTE ANY PROGRAM
        EXECUTE ANY RULE
        EXECUTE ANY RULE SET
        EXECUTE ANY TYPE
        EXECUTE ASSEMBLY
        EXPORT FULL DATABASE
        FLASHBACK ANY TABLE
        FLASHBACK ARCHIVE ADMINISTER
        FORCE ANY TRANSACTION
        FORCE TRANSACTION
        FREEZE_ANY_WORKSPACE
        GLOBAL QUERY REWRITE
        GRANT ANY OBJECT PRIVILEGE
        GRANT ANY PRIVILEGE
        GRANT ANY ROLE
        IMPORT FULL DATABASE
        INSERT ANY CUBE DIMENSION
        INSERT ANY MEASURE FOLDER
        INSERT ANY TABLE
        LOCK ANY TABLE
        MANAGE ANY FILE GROUP
        MANAGE ANY QUEUE
        MANAGE FILE GROUP
        MANAGE SCHEDULER
        MANAGE TABLESPACE
        MERGE ANY VIEW
        MERGE_ANY_WORKSPACE
        ON COMMIT REFRESH
        QUERY REWRITE
        READ ANY FILE GROUP
        REMOVE_ANY_WORKSPACE
        RESTRICTED SESSION
        RESUMABLE
        ROLLBACK_ANY_WORKSPACE
        SELECT ANY CUBE
        SELECT ANY CUBE DIMENSION
        SELECT ANY DICTIONARY
        SELECT ANY MINING MODEL
        SELECT ANY SEQUENCE
        SELECT ANY TABLE
        SELECT ANY TRANSACTION
        UNDER ANY TABLE
        UNDER ANY TYPE
        UNDER ANY VIEW
        UNLIMITED TABLESPACE
        UPDATE ANY CUBE
        UPDATE ANY CUBE BUILD PROCESS
        UPDATE ANY CUBE DIMENSION
        UPDATE ANY TABLE
Object Privileges:
    <Empty>
    Add:
        Clase Java
        Clases de Trabajos
        Cola
        Columna de Tabla
        Columna de Vista
        Espacio de Trabajo
        Función
        Instantánea
        Origen Java
        Paquete
        Planificaciones
        Procedimiento
        Programas
        Secuencia
        Sinónimo
        Tabla
        Tipos
        Trabajos
        Vista
Consumer Group Privileges:
    <Empty>
    Default Consumer Group:
        (*) None
    Edit List:
        AUTO_TASK_CONSUMER_GROUP
        BATCH_GROUP
        DEFAULT_CONSUMER_GROUP
        INTERACTIVE_GROUP
        LOW_GROUP
        ORA$AUTOTASK_HEALTH_GROUP
        ORA$AUTOTASK_MEDIUM_GROUP
        ORA$AUTOTASK_SPACE_GROUP
        ORA$AUTOTASK_SQL_GROUP
        ORA$AUTOTASK_STATS_GROUP
        ORA$AUTOTASK_URGENT_GROUP
        ORA$DIAGNOSTICS
        SYS_GROUP

E, claro, gostaria de saber quais opções devo escolher.

    

Answer 1

Eu não sei se o Enterprise Manager 11g é o mesmo que o Enterprise Manager Grid Control 10g, que é o que eu uso. Se for semelhante, deve haver uma página de Segurança, na qual você pode gerenciar usuários, funções, privilégios, etc. Ao falhar, com certeza você pode usar os seguintes comandos SQL:

CREATE USER xxx IDENTIFIED BY pppp DEFAULT TABLESPACE users;
GRANT CONNECT, RESOURCE TO xxx;

GRANT privilege TO xxx;   -- repeat for each privilege in your step 4.

    

Answer 2

Para dar à nova conta os mesmos privilégios em 11g como você fez no 10g XE, conceda a função RECURSO e o privilégio CREATE SESSION.

De 10g (não XE):

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 5 13:06:38 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

SQL> select grantee, privilege
2 from dba_sys_privs
3 where grantee in ('CONNECT','RESOURCE')
4 order by grantee, privilege;

GRANTEE PRIVILEGE

---

CONNECT CREATE SESSION
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE

9 rows selected.

A partir de 11g:

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 5 13:07:24 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Release 11.1.0.6.0 - 64bit Production

SQL> select grantee, privilege
2 from dba_sys_privs
3 where grantee in ('CONNECT','RESOURCE')
4 order by grantee, privilege;

GRANTEE PRIVILEGE

---

CONNECT CREATE SESSION
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE

9 rows selected.

Como você pode ver, as funções têm os mesmos privs nas duas versões. Não sei porque o papel CONNECT não aparece na sua listagem a partir do 11g, existe nos meus bancos de dados 11g. Você pode conceder o privilégio "CREATE SESSION" diretamente para contornar o MiA.