Creating Database Users

In this video, we will see how to create a general purpose user for our Oracle Autonomous Database.

Login URL

content_copy

https://oracle.com/cloud/sign-in.html

Script to Create Student Account

content_copy

-- USER SQL
CREATE USER STUDENT IDENTIFIED BY Temporary_Password_1234;

-- ADD ROLES
GRANT CONNECT TO STUDENT;
GRANT RESOURCE TO STUDENT;
ALTER USER STUDENT DEFAULT ROLE CONNECT,RESOURCE;

-- ENABLE REST
BEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'STUDENT',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'student',
        p_auto_rest_auth=> TRUE
    );
    commit;
END;
/

Script to Create Professor Account

content_copy

CREATE USER professor IDENTIFIED BY Temporary_Password_1234;

-- ADD ROLES
GRANT ACCHK_READ TO PROFESSOR WITH ADMIN OPTION;
GRANT ADB_MONITOR TO PROFESSOR WITH ADMIN OPTION;
GRANT ADM_PARALLEL_EXECUTE_TASK TO PROFESSOR WITH ADMIN OPTION;
GRANT ADPADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT APEX_ADMINISTRATOR_READ_ROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT APEX_ADMINISTRATOR_ROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT AQ_ADMINISTRATOR_ROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT AQ_USER_ROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT AUDIT_ADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT AUDIT_VIEWER TO PROFESSOR WITH ADMIN OPTION;
GRANT CAPTURE_ADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT CONNECT TO PROFESSOR WITH ADMIN OPTION;
GRANT CONSOLE_ADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT CONSOLE_DEVELOPER TO PROFESSOR WITH ADMIN OPTION;
GRANT CONSOLE_MONITOR TO PROFESSOR WITH ADMIN OPTION;
GRANT CONSOLE_OPERATOR TO PROFESSOR WITH ADMIN OPTION;
GRANT CTXAPP TO PROFESSOR WITH ADMIN OPTION;
GRANT DATAPUMP_CLOUD_EXP TO PROFESSOR WITH ADMIN OPTION;
GRANT DATAPUMP_CLOUD_IMP TO PROFESSOR WITH ADMIN OPTION;
GRANT DCAT_SYNC TO PROFESSOR WITH ADMIN OPTION;
GRANT DV_ACCTMGR TO PROFESSOR WITH ADMIN OPTION;
GRANT DV_OWNER TO PROFESSOR WITH ADMIN OPTION;
GRANT DWROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT GATHER_SYSTEM_STATISTICS TO PROFESSOR WITH ADMIN OPTION;
GRANT GRAPH_DEVELOPER TO PROFESSOR WITH ADMIN OPTION;
GRANT HS_ADMIN_SELECT_ROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT LBAC_DBA TO PROFESSOR WITH ADMIN OPTION;
GRANT ODIADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT OML_DEVELOPER TO PROFESSOR WITH ADMIN OPTION;
GRANT OML_SYS_ADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT OPTIMIZER_PROCESSING_RATE TO PROFESSOR WITH ADMIN OPTION;
GRANT ORDS_ADMINISTRATOR_ROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT PDB_DBA TO PROFESSOR WITH ADMIN OPTION;
GRANT PROVISIONER TO PROFESSOR WITH ADMIN OPTION;
GRANT RESOURCE TO PROFESSOR WITH ADMIN OPTION;
GRANT SELECT_CATALOG_ROLE TO PROFESSOR WITH ADMIN OPTION;
GRANT SODA_APP TO PROFESSOR WITH ADMIN OPTION;
GRANT XS_CACHE_ADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT XS_CONNECT TO PROFESSOR WITH ADMIN OPTION;
GRANT XS_NAMESPACE_ADMIN TO PROFESSOR WITH ADMIN OPTION;
GRANT XS_SESSION_ADMIN TO PROFESSOR WITH ADMIN OPTION;

ALTER USER PROFESSOR DEFAULT ROLE ACCHK_READ,ADB_MONITOR,ADM_PARALLEL_EXECUTE_TASK,
ADPADMIN,APEX_ADMINISTRATOR_READ_ROLE,APEX_ADMINISTRATOR_ROLE,AQ_ADMINISTRATOR_ROLE,
AQ_USER_ROLE,AUDIT_ADMIN,AUDIT_VIEWER,CAPTURE_ADMIN,CONNECT,CONSOLE_ADMIN,
CONSOLE_DEVELOPER,CONSOLE_MONITOR,CONSOLE_OPERATOR,CTXAPP,DATAPUMP_CLOUD_EXP,
DATAPUMP_CLOUD_IMP,DCAT_SYNC,DV_ACCTMGR,DV_OWNER,DWROLE,GATHER_SYSTEM_STATISTICS,
GRAPH_DEVELOPER,HS_ADMIN_SELECT_ROLE,LBAC_DBA,ODIADMIN,OML_DEVELOPER,OML_SYS_ADMIN,
OPTIMIZER_PROCESSING_RATE,ORDS_ADMINISTRATOR_ROLE,PDB_DBA,PROVISIONER,RESOURCE,
SELECT_CATALOG_ROLE,SODA_APP,XS_CACHE_ADMIN,XS_CONNECT,XS_NAMESPACE_ADMIN,
XS_SESSION_ADMIN;

-- ENABLE REST
BEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'PROFESSOR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'professor',
        p_auto_rest_auth=> FALSE
    );
    commit;
END;
/