{ "video": "rpH8G5zbzS0", "seconds": 194, "title": "Setup Database Accounts", "prior": "Create an Oracle Autonomous Database", "transcript": "This video shows how to create two database \"user accounts\" that you'll need to complete your coursework. I'm assuming you've already set up a database and you're able to access a tool for executing commands as the ADMIN user. If not, see the prior videos in this playlist or the links to prior videos listed above.\r\n\r\nAlthough it's possible to create user accounts through the database's graphical user interface, we are going to create them using Structured Query Language.\r\n\r\nThis language is abbreviated as S Q L but it's usually pronounced \"sequel\" by data professionals.\r\n\r\nFirst, let's create an account for you to use for your class assignments. The account will be named STUDENT and it's the one you'll use for just about all the work for this course. You'll find the SQL statement to create the STUDENT account below this video. Click to display the SQL commands then you can copy and paste the code into the SQL window. Be sure to change the password from \"Temporary password 1 2 3 4\" to a strong password. You'll need to keep track of the password you choose because you'll use it each time you log in.\r\n\r\nYou'll notice that this script has several statements. One to create the user account and others to give it the needed permissions. This button allows you to execute one statement in the script at a time but we want to execute the whole thing, so click this one.\r\n\r\nI'll just scan through the script output to be sure there are no error messages, which are shown in red.\r\n\r\nEverything looks good.\r\n\r\nNow let's create the account your professor will use to access your database. You can copy the script for this from below as well. This account is similar to your ADMIN account so your professor can make administrative changes to your database if needed.\r\n\r\nFor this one, don't change the password that's in the script. Your professor will change it when he logs on to verify that you have created these accounts correctly.\r\n\r\nLet's clear the output from the last script and execute this one.\r\n\r\nI'm scanning the output.\r\n\r\nEverything looks good.\r\n\r\nNow, let's see how to use the newly created student account to login directly to the database.\r\n\r\nClick on the options menu in the upper left corner of your browser. Under the \"Administration\" heading, choose \"Database Users.\"\r\n\r\nI'll find the \"student\" user and click this button to copy the direct login link.\r\n\r\nNow I'll open an \"incognito\" browser and paste in the direct login link to verify that I can use the student account to access my database directly. \r\n\r\nI'll enter \"student\" as the username and supply the password I chose for the account.\r\n\r\nIt looks as though everything works great. We're ready to start working with data but that will be the topic for another video. \r\n\r\nTake a minute now and record the direct login link and the password that you created for the \"student\" account. With them, you'll be able to log in directly to your database without having to log in to your Oracle Cloud console first. Plus, your professor will probably ask for that direct login link to verify that your accounts are configured correctly.\r\n\r\n", "code": [ { "summary": "SQL to create Student Account", "detail": "\r\n-- USER SQL\r\nCREATE USER STUDENT IDENTIFIED BY Temporary_Password_1234;\r\n\r\n-- ADD ROLES\r\nGRANT CONNECT TO STUDENT;\r\nGRANT RESOURCE TO STUDENT;\r\nGRANT CREATE VIEW TO STUDENT;\r\nALTER USER STUDENT DEFAULT ROLE CONNECT,RESOURCE;\r\nALTER USER STUDENT QUOTA UNLIMITED on DATA;\r\n\r\n-- ENABLE REST\r\nBEGIN\r\n ORDS.ENABLE_SCHEMA(\r\n p_enabled => TRUE,\r\n p_schema => 'STUDENT',\r\n p_url_mapping_type => 'BASE_PATH',\r\n p_url_mapping_pattern => 'student',\r\n p_auto_rest_auth=> TRUE\r\n );\r\n commit;\r\nEND;\r\n\/ " }, { "summary": "SQL to remove Student Account (in case you want to practice creating it again)", "detail": "DROP USER STUDENT;" }, { "summary": "SQL to create Professor Account", "detail": "CREATE USER professor IDENTIFIED BY Temporary_Password_1234;\r\n\r\n-- ADD ROLES\r\nGRANT ACCHK_READ TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT ADB_MONITOR TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT ADM_PARALLEL_EXECUTE_TASK TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT ADPADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT APEX_ADMINISTRATOR_READ_ROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT APEX_ADMINISTRATOR_ROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT AQ_ADMINISTRATOR_ROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT AQ_USER_ROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT AUDIT_ADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT AUDIT_VIEWER TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT CAPTURE_ADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT CONNECT TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT CONSOLE_ADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT CONSOLE_DEVELOPER TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT CONSOLE_MONITOR TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT CONSOLE_OPERATOR TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT CTXAPP TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT DATAPUMP_CLOUD_EXP TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT DATAPUMP_CLOUD_IMP TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT DCAT_SYNC TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT DV_ACCTMGR TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT DV_OWNER TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT DWROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT GATHER_SYSTEM_STATISTICS TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT GRAPH_DEVELOPER TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT HS_ADMIN_SELECT_ROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT LBAC_DBA TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT ODIADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT OML_DEVELOPER TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT OML_SYS_ADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT OPTIMIZER_PROCESSING_RATE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT ORDS_ADMINISTRATOR_ROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT PDB_DBA TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT PROVISIONER TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT RESOURCE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT SELECT_CATALOG_ROLE TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT SODA_APP TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT XS_CACHE_ADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT XS_CONNECT TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT XS_NAMESPACE_ADMIN TO PROFESSOR WITH ADMIN OPTION;\r\nGRANT XS_SESSION_ADMIN TO PROFESSOR WITH ADMIN OPTION;\r\n\r\nALTER USER PROFESSOR DEFAULT ROLE ACCHK_READ,ADB_MONITOR,ADM_PARALLEL_EXECUTE_TASK,\r\nADPADMIN,APEX_ADMINISTRATOR_READ_ROLE,APEX_ADMINISTRATOR_ROLE,AQ_ADMINISTRATOR_ROLE,\r\nAQ_USER_ROLE,AUDIT_ADMIN,AUDIT_VIEWER,CAPTURE_ADMIN,CONNECT,CONSOLE_ADMIN,\r\nCONSOLE_DEVELOPER,CONSOLE_MONITOR,CONSOLE_OPERATOR,CTXAPP,DATAPUMP_CLOUD_EXP,\r\nDATAPUMP_CLOUD_IMP,DCAT_SYNC,DV_ACCTMGR,DV_OWNER,DWROLE,GATHER_SYSTEM_STATISTICS,\r\nGRAPH_DEVELOPER,HS_ADMIN_SELECT_ROLE,LBAC_DBA,ODIADMIN,OML_DEVELOPER,OML_SYS_ADMIN,\r\nOPTIMIZER_PROCESSING_RATE,ORDS_ADMINISTRATOR_ROLE,PDB_DBA,PROVISIONER,RESOURCE,\r\nSELECT_CATALOG_ROLE,SODA_APP,XS_CACHE_ADMIN,XS_CONNECT,XS_NAMESPACE_ADMIN,\r\nXS_SESSION_ADMIN;\r\n\r\n-- ENABLE REST\r\nBEGIN\r\n ORDS.ENABLE_SCHEMA(\r\n p_enabled => TRUE,\r\n p_schema => 'PROFESSOR',\r\n p_url_mapping_type => 'BASE_PATH',\r\n p_url_mapping_pattern => 'professor',\r\n p_auto_rest_auth=> FALSE\r\n );\r\n commit;\r\nEND;\r\n/\r\n" } ] }