Oracle for Absolute Beginners: Users, Synonyms, Privileges, & Roles

I accidentally locked myself out of my house this evening, and so I had to climb up the wall like a burglar and clamber in through a bathroom window, while the neighbourhood kids all watched and giggled, probably taking secret photos to share on Snapchat or something.  Embarrassing, yes, but what’s it got to do with Oracle? Well, the point… Continue Reading →

I accidentally locked myself out of my house this evening, and so I had to climb up the wall like a burglar and clamber in through a bathroom window, while the neighbourhood kids all watched and giggled, probably taking secret photos to share on Snapchat or something. 

Embarrassing, yes, but what’s it got to do with Oracle? Well, the point is this: no one called the police.

And that’s because it’s my house. And while having gifs on Instagram of my bum squeezing through a small window might be embarrassing, we all understand that I can do whatever I please with my property. 

Those same rules apply within an Oracle database. When you create, and therefore, own, an object you can do pretty much whatever you please with it: wanna add data, delete data, add a column, remove a column, truncate a table, delete a table? Go for it. And it’s not just tables – functions, procedures, packages, triggers, sequences, whatever: this is your world, and you’re the god of all you create. 

But, for now, let’s stick to tables. All of the following, for example, is understandably legitimate:

CREATE TABLE test_table 
(col_a NUMBER,
 col_b VARCHAR2(100),
 col_c TIMESTAMP
 );
 
 -- Add a new column
 ALTER TABLE test_table ADD
 col_d CLOB;
 
 -- drop a column
 ALTER TABLE test_table DROP 
 COLUMN col_d;
 
-- insert some data
INSERT INTO test_table (col_a, col_b, col_c)
VALUES (1, 'Some data', SYSTIMESTAMP);

But to find out what rights other users have on your objects, we first need to talk about creating users.

Users

Thus far in this series, we’ve worked with a single user. However, realistically, a database will have multiple users. To create a new database user we’d employ the CREATE USER command:

CREATE USER joey IDENTIFIED BY passw0rd;

Aligned to the CREATE USER command, Oracle have created a few other commands too; ALTER USER and DROP USER are two you’ll probably want to be aware of. The DROP USER command allows us to, well, drop a user. Using the ALTER USER command we can carry out a huge range of operations, including changing a user’s password, expiring their password, locking and unlocking their account. 

We might, for example, recognise that we’ve set up our new user, joey, with a foolishly weak password and want to force him to change it immediately he logs in.

ALTER USER joey PASSWORD EXPIRE; 

However, to enable our new user, joey, to actually connect to the database, we must also give them permission to create a session.  Assuming we have the right to give this privilege, all we need do is run the following:

GRANT CREATE SESSION TO joey;

Now, we should be able to connect as joey and carry out operations on test_table, right? Wrong.  This is what happens when we try:

SELECT * 
FROM test_table; 

ERROR at line 2: ORA-00942: table or view does not exist

And to explain why that’s happening, we need to talk about…

Synonyms

Even though my name is David, back in school everyone called me Handsome because, well … because I’m so, so good-looking (this story, unfortunately, is a lie). The point is that Handsome is (or should have been!) my nickname, my … synonym. In Oracle, a synonym is an alternative name for an object. Without synonyms, every user would need to reference objects using the following notation: owner.object_name (where the owner is the username of the user who has created the object). However, that’s too much typing; with the CREATE SYNONYM command you can shorten that down, and call an object whatever you please. 

Oracle create synonym

Source: Oracle documentation

Assuming that the username of the user with which we created the test_table table in our example is user1, joey could run the following command:

CREATE SYNONYM test_table FOR user1.test_table;

That would tell Oracle that whenever it encounters the name test_table in a command being run by joey, it is referring to user1.test_table. (Synonyms do not have to have the same name as the object they’re working on; joey can just as easily write this command: CREATE SYNONYM pineapples FOR user1.test_table;.)

Synonyms like this are called private synonyms – all synonyms are private by default – and are only available to the user who has created them. However, there are things called PUBLIC synonyms which make the synonyms available to everyone.  What this means is that no one should get that ORA-00942: table or view does not exist error if user1 ran the following command:

CREATE PUBLIC SYNONYM test_table FOR test_table;

(User1 does not need to refer to it as user1.test_table since they’re the table’s owner.)

Now, we should be able to connect as joey and carry out operations on test_table, right? Wrong. This is what happens when we try:

SELECT * 
FROM test_table; 

ERROR at line 2: ORA-00942: table or view does not exist

And to explain why that’s happening, we need to talk about…

Privileges

Let’s go back to my bum getting stuck in a bathroom window. Now I think about it, I should have asked one of the neighbourhood kids to climb through that window for me. In other words, I should have granted them the right – the privilege – of climbing through my bathroom window.

And that’s what a privilege in Oracle is: a right to carry out an operation on an object.  The various privileges available for an object depends on what kind of object it is, and these privileges can be granted by the object’s owner. 

Privileges can also be taken away.  The syntax for that is as follows:

The privileges available on an object, as I have said, depend on the type of object it is. This makes sense: while I could say to the neighbourhood kid, “Please, climb up my wall and climb into that open window”, I would sound crazy if I said, “Please, climb into my wall and climb up that open window.”

Here’s a list of some of those privileges:

Privilege Object Description
 SELECT TABLE Permission to query a table
INSERT TABLE Permission to insert data into a table
DELETE TABLE Permission to delete from a table
UPDATE TABLE Permission to update a table
ALTER TABLE Permission to alter the structure of the table
ALL TABLE All permissions
EXECUTE PACKAGE Permission to run the package
EXECUTE FUNCTION Permission to run the function
EXECUTE PROCEDURE Permission to run the procedure

What this means is that user1 could, if they wanted to grant joey permission to query test_table, run the following:

GRANT SELECT ON test_table TO joey;

This would mean that joey would not get an error now if they ran a select on the table. However, if joey tried to insert a record into the table, they’ll get this error:

ORA-01031: insufficient privileges 

And this is because while joey has been granted the right to query the table, they have not been given permission to do anything else. Just because I granted the neighbourhood kid permission to climb through the window and open my front door doesn’t mean I wouldn’t get mad if he opened my fridge and helped himself to a beer while he was at it! He was granted insufficient privileges for that level of liberty.

You may have noticed two catch-alls above. You could grant all privileges on test_table to joey; indeed you could grant all privileges to everyone.

GRANT ALL ON test_table TO joey;
GRANT ALL ON test_table TO PUBLIC;

It is usually a bad idea to grant privileges to PUBLIC. It’s kinda like hiding your spare house key somewhere safe – and then announcing your hiding place to the whole street! It is a much better security practice to only grant individual privileges to individual users who require it.

This may mean that while you grant SELECT on test_table to, say, low-level users, you may want to grant SELECT and UPDATE to mid-level users. High-level users may get SELECT, UPDATE, INSERT and DELETE. And admin users might get all of that and ALTER.

If you’ve got a good memory you may remember that while we were talking about creating users we ran the following command:

GRANT CREATE SESSION TO joey;

With what we now know, I’m sure you can recognise that as a privilege statement. However, what we’ve been talking about thus far are called object privileges – because they apply to objects, such as tables and packages – CREATE SESSION is another type of privilege; it is a system privilege.

Unlike object privileges which apply to individual objects, system privileges grant the grantee the right to carry out a general action – create a database session, for instance – or to carry out an action on any object of a particular type. Even though they’re really important, we’re not going to talk a lot about system privileges here; however, here are a few important ones:

Privilege Description
CREATE SESSION Permission to connect to the database
CREATE ANY TABLE Permission to create tables
CREATE PROCEDURE Permission to create a PL/SQL package, procedure or function
CREATE PUBLIC SYNONYM Permission to create public synonyms
DROP ANY TABLE Permission to drop tables
DROP ANY PROCEDURE Permission to drop packages, procedures and functions
ADMIN Permission to carry out high-level admin tasks such as creating and deleting users.
Roles

So far we’ve been pretending to live in this tiny world of a single table and two users. Let’s throw the windows open and allow reality back in; chances are we’ll have, at least, dozens of tables, and possibly even more users. 

Remember all I said about being security conscious with  your objects and only granting individual privileges to the individual users who require them? Well, that would get pretty tedious pretty quickly. Imagine if every time you create a new table you have to grant individual privileges to, say, 100 different users. Or every time you create a new user you have to grant that user a tailored set of privileges on, say, 50 tables and packages. It would be soooo boring. You’d resign within a week!

Fortunately we have roles. A role is a group of privileges. Privileges can be granted to – and revoked from – roles; and roles can be granted to – and revoked from – users.  

What this means is that if, say, we have a role named low_level we could grant the SELECT privilege – and nothing else – on all our tables to it. We could then grant the low_level role to a user and he’ll automatically have the right to select from all our tables. And whenever we create a new table, all we need do is grant the SELECT privilege to low_level. And if we create a new low-status user we will just grant them the low_level role and they’ll get all the privileges in it. 

We could similarly have a mid_level role and grant all our individual SELECT and UPDATE privileges to it; and a high_level role to which we grant SELECT, UPDATE, INSERT and DELETE.

However, before we do all that, we’d need to create the role.

Create role

CREATE ROLE low_level;

And once we’ve got our role, it’s a straightforward thing to grant privileges to it.

Grant role

GRANT SELECT ON test_table TO low_level;

And, finally, we would grant this role to a user.

GRANT low_level TO joey;

It is important to note that once a role is granted to a user, they not only acquire all the privileges that come wrapped up within that role but also any privileges that may be granted to that role in the future. That is the power of roles; that is what makes them so useful. Coupled with synonyms, roles are the oil in the joints of a database, the seemingly-insignificant heroes that allow everything else run smoothly. 

I hope this article has helped you understand – and appreciate – them.