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 to SQL Developer Web needs the new URL Mapping Pattern for the REST Enabled Schema inserted.

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.

Even if I get the user/pass right, if my URL for the HR user isn’t provided, SQL Developer Web won’t let you authenticate as HR.

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.

Paste in your code and hit F5 or just hit the 2nd execute button, just like the desktop.

What’s it look like?

Quite standard, really

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 –

For your more complex schemas, this will come in handy.

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.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. Leon Van Zyl Reply

    Hi Thanks for the post, Would be nice if you include the scripts

Write A Comment