Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm a PHP developer (not a DBA) and I've been working with Oracle 10g XE for a while. I'm used to XE's simplified user management:

  1. Go to Administration/ Users/ Create user
  2. Assign user name and password
  3. Roles: leave the default ones (connect and resource)
  4. Privileges: click on "Enable all" to select the 11 possible ones
  5. Create

This way I get a user that has full access to its data and no access to everything else. This is fine since I only need it to develop my app. When the app is to be deployed, the client's DBAs configure the environment.

Now I have to create users in a full Oracle 11g server and I'm completely lost. I have a new concept (profiles) and there're like 20 roles and hundreds of privileges in various categories.

What steps do I need to complete in Oracle Enterprise Manager in order to obtain a user with the same privileges I used to assign in XE?

==== UPDATE ====

I think I'd better provide a detailed explanation so I make myself clearer.

This is how I create a user in 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

I click on Enable All and I'm done.

This is what I'm asked when doing the same in 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

And, of course, I wonder what options I should pick.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

I don't know if Enterprise Manager 11g looks the same as Enterprise Manager Grid Control 10g, which is what I use. If it's similar, there should be a Security page, where you can manage users, roles, privileges, etc. Failing that, for sure you could use the following SQL commands:

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.
share|improve this answer
    
I removed my first comment because I think I hadn't understand you. You mean that setting the 11 "known" privileges with the GRANT statement and ignoring everything else should do the trick, don't you? –  Álvaro G. Vicario Feb 11 '10 at 9:25
    
Yes, that's what I mean (e.g., GRANT CREATE DATABASE LINK TO xxx; etc.). You can always grant additional privileges later if you decide you need them. Of course, it looks like you can do this directly in Enterprise Manager. It's likely you're not even using all of the privileges in the XE list anyway. –  DCookie Feb 11 '10 at 15:18
add comment

To give the new account the same privileges in 11g as you did in 10g XE, grant the RESOURCE role and the CREATE SESSION privilege.

From 10g (not 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.

From 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.

As you can see, the roles have the same privs in both versions. I don't know why the CONNECT role doesn't appear in your listing from 11g, it exists in my 11g databases. You can just grant the "CREATE SESSION" privilege directly to work around it being MiA.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.