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:

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:

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.

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:

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:

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. 

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:

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:

(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:

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:

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

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:

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.

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:

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:

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.

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

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

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.