{{parent page="Oracle"}}
=== Create Oracle user ===

Create a role so it can be assigned to future users
%%(sql)
# Privileged user
CREATE ROLE "DBUSR_SUPER" NOT IDENTIFIED;
GRANT "SELECT_CATALOG_ROLE" TO "DBUSR_SUPER";
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE ROLE, CREATE PROCEDURE TO "DBUSR_SUPER";
GRANT CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, CREATE ANY SEQUENCE TO "DBUSR_SUPER";

# Unprivileged user
CREATE ROLE "DBUSR_NORMAL" NOT IDENTIFIED;
GRANT "SELECT_CATALOG_ROLE" TO "DBUSR_NORMAL";
GRANT CREATE SESSION TO DBUSR_NORMAL;
%%

create a tablespace
%%(sql)
create tablespace "MYTABLESPACE"
logging
datafile
'/ora200g/dbfiles/marlow_tbs1.dbf' size 500M,
'/oradata/devdb/marlow_tbs2.dbf' size 500M
extent management local segment space management auto;
%%

Create Users
%%(sql)
create user mytbsusr identified by xxxxxx
default tablespace mytablespace
quota unlimited on mytablespace;

grant dbusr_normal to mytbsusr;
%%

Create index tablespace
%%(sql)
create tablespace "MYTBS_IDX"
logging
datafile '/ora200g/dbfiles/mytbs_idx1.dbf' SIZE 300M
extent management local segment space management auto;
%%

Changing password of sys
%%
sqlplus /nolog
SQL> connect sys as sysdba
SQL> alter user sys identified by oracle_password;
%%

Query privileges granted to a certain user:
%%
select * from dba_sys_privs where grantee = 'FOO';
%%