This discussion is archived
2 Replies Latest reply: Sep 13, 2013 2:40 AM by Paul M. RSS

user name

ABDULLAH18 Newbie
Currently Being Moderated

i can create a new connection using SYSTEM as a username but can i have another username and if yes how can i get it
ty in advance

  • 1. Re: user name
    jariola Guru
    Currently Being Moderated

    Hi,

     

    Login as SYSTEM user and create another user e.g.

    CREATE USER myuser IDENTIFIED BY mypasswd;
    GRANT CONNECT,RESOURCE TO myuser;

     

    You can also grant other privileges to new user e.g.

    GRANT CONNECT TO myuser;
    GRANT RESOURCE TO myuser;
    GRANT CREATE SESSION TO myuser;
    GRANT CREATE CLUSTER TO myuser;
    GRANT CREATE DIMENSION TO myuser;
    GRANT CREATE INDEXTYPE TO myuser;
    GRANT CREATE JOB TO myuser;
    GRANT CREATE MATERIALIZED VIEW TO myuser;
    GRANT CREATE OPERATOR TO myuser;
    GRANT CREATE PROCEDURE TO myuser;
    GRANT CREATE SEQUENCE TO myuser;
    GRANT CREATE SNAPSHOT TO myuser;
    GRANT CREATE SYNONYM TO myuser;
    GRANT CREATE TABLE TO myuser;
    GRANT CREATE TRIGGER TO myuser;
    GRANT CREATE TYPE TO myuser;
    GRANT CREATE VIEW TO myuser;

     

    Regards,
    Jari

  • 2. Re: user name
    Paul M. Oracle ACE
    Currently Being Moderated

    CREATE USER myuser IDENTIFIED BY mypasswd; GRANT CONNECT,RESOURCE TO myuser;

     

    Assuming we are speaking of XE database (we are on this forum), I'd change something...

     

    On XE the default tablespace, when not specified, is SYSTEM, so I'd assign a different one. Also, RESOURCE role (and this is true for any versions) includes UNLIMITED TABLESPACE privilege, so I wouldn't use it. Example :

    SQL> create user test identified by test;

     

    User created.

     

    SQL> grant connect,resource to test;

     

    Grant succeeded.

     

    SQL> select DEFAULT_TABLESPACE from dba_users

      2  where username='TEST';

     

    DEFAULT_TABLESPACE

    ------------------------------

    SYSTEM

     

    SQL> conn test/test

    Connected.

    SQL> select * from session_privs;

     

    PRIVILEGE

    ----------------------------------------

    CREATE SESSION

    UNLIMITED TABLESPACE

    CREATE TABLE

    CREATE CLUSTER

    CREATE SEQUENCE

    CREATE PROCEDURE

    CREATE TRIGGER

    CREATE TYPE

    CREATE OPERATOR

    CREATE INDEXTYPE

     

    10 rows selected.

     

    SQL>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points