Creating HR in Oracle Autonomous Database w/SQL Developer Web

When I say, ‘Oracle Autonomous Database,’ assume I mean one of the following environments:

• Autonomous Data Warehouse
• Autonomous Transaction Processing
• Autonomous Transactions Processing, Dedicated

In this quick post, I’m going to show you how to create a new USER, login as that user, build some stuff, and then see said stuff.

Creating the HR user

Now, before you start asking, well, why don’t you just Data Pump this up to your Cloud Service, I want to say – great idea! And you totally SHOULD do that if you can. However, not everyone will have a ‘living’ Oracle instance on-prem available for this, and sometimes you just want to get things done, quick-and-dirty style. That’s the idea behind this post.

I’m going to run this code as the ADMIN user in SQL Developer Web

CREATE USER hr IDENTIFIED BY PassWordGoodNotThis;
 
GRANT CONNECT, resource TO hr;
 
ALTER USER HR QUOTA UNLIMITED ON DATA; -- this part is important
 
 
BEGIN -- this part is so I can login as HR via SQL Developer Web
    ords_admin.enable_schema (
        p_enabled               => TRUE,
        p_schema                => 'HR',
        p_url_mapping_type      => 'BASE_PATH',
        p_url_mapping_pattern   => 'peeps', -- this flag says, use 'peeps' in the URIs for HR
        p_auto_rest_auth        => TRUE   -- this flag says, don't expose my REST APIs
    );
    COMMIT;
END;
/

With that executed, we can now login as HR.

The link your Database Console gives you for SQL Developer Web is setup for you to login as ADMIN. But we’re wanting to login as HR now.

I’m in, now let’s create stuff!

This part is pretty straightforward. I already have some scripts I generated for HR. Now I can just run them.

What’s it look like?

I wanted to add ALL of the tables to my diagram, how to do that? Well, there’s a few ways, but let’s look at this one, the ‘Add Objects to Diagram’ button –

One last thing…let’s create a MV

In the SQL Worksheet, switch your object navigator from TABLES to Materialized Views. Then hit the ‘New’ button, and supply your query to ‘feed’ the MV.

Fill in the properties, and when you’re happy, click the ‘Create’ button. Note, you can click on the DDL page to see the proposed CREATE MATERIALIZED VIEWS command, or just copy it out, and tweak as you want back in the Worksheet.

After it’s created, you can drag and drop the MV over to the worksheet, and we’ll create a nice SELECT for you. I’ve wrapped mine with an EXPLAIN command, and added a call to DBMS_XPLAN. With both statements selected, I hit the Execute as Script button, and I can see just how my query is satisfied.

Now, why did I need to do the two SQL commands as ‘one’ operation? In a SQL Developer Web world, each operation you do is done as a separate transaction, in a separate session. So if I combine the two and execute as a script, the DISPLAY() function call can work.

One final note: I needed to use my ADMIN account to GRANT CREATE VIEW and CREATE MATERIALIZED VIEW to my HR user before I could do the above.

18c: Creating the HR schema

Wait, we just covered this, right? Yes, but.

The but is that, yes you have your HR user and it’s objects, BUT it’s that USER part that could be worrisome. You see, someone could login as HR and…do stuff.

What if you JUST wanted the HR SCHEMA – the objects and their data – but you didn’t want anyone to be able to actually use HR to interact with the database?

In 18c, you now have this capability.

Ability to Create Schema Only Accounts

You now can create schema only accounts, for object ownership without allowing clients to log in to the schema.

A user (or other client) cannot log in to the database schema unless the account is modified to accept an authentication method. However, this type of schema user can proxy in a single session proxy.

So, if you want to go this route, you totally can in Autonomous Database (serverless) – as that’s currently running 18c with plans to go to 19c soon. Autonomous Transaction Processing Dedicated is already running 19c in case you’re wondering…

For a nice example and topic walk-through, check our Chris’ blog.