{"id":73072,"date":"2017-03-01T10:48:20","date_gmt":"2017-03-01T10:48:20","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-for-absolute-beginners-users-synonyms-privileges-roles\/"},"modified":"2021-07-14T13:06:55","modified_gmt":"2021-07-14T13:06:55","slug":"oracle-for-absolute-beginners-users-synonyms-privileges-roles","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/oracle-for-absolute-beginners-users-synonyms-privileges-roles\/","title":{"rendered":"Oracle for Absolute Beginners: Users, Synonyms, Privileges, & Roles"},"content":{"rendered":"

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.\u00a0<\/p>\n

Embarrassing, yes, but what\u2019s it got to do with Oracle? Well, the point is this: no one called the police.<\/p>\n

And that\u2019s because it\u2019s 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.\u00a0<\/p>\n

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\u2019s not just tables \u2013 functions, procedures, packages, triggers, sequences, whatever: this is your world, and you\u2019re the god of all you create.\u00a0<\/p>\n

But, for now, let\u2019s stick to tables. All of the following, for example, is understandably legitimate:<\/p>\n

CREATE TABLE test_table \r\n(col_a NUMBER,\r\n col_b VARCHAR2(100),\r\n col_c TIMESTAMP\r\n );\r\n \r\n -- Add a new column\r\n ALTER TABLE test_table ADD\r\n col_d CLOB;\r\n \r\n -- drop a column\r\n ALTER TABLE test_table DROP \r\n COLUMN col_d;\r\n \r\n-- insert some data\r\nINSERT INTO test_table (col_a, col_b, col_c)\r\nVALUES (1, 'Some data', SYSTIMESTAMP);<\/pre>\n
But to find out what rights other users have on your objects, we first need to talk about creating users.<\/p>\n
Users<\/h5>\n
Thus far in this series, we\u2019ve worked with a single user. However, realistically, a database will have multiple users. To create a new database user we\u2019d employ the CREATE USER command:<\/p>\n
<\/p>\n
CREATE USER joey IDENTIFIED BY passw0rd;\r\n<\/pre>\n
Aligned to the CREATE USER command, Oracle have created a few other commands too; ALTER USER and DROP USER are two you\u2019ll 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\u2019s password, expiring their password, locking and unlocking their account.\u00a0<\/p>\n
<\/p>\n
We might, for example, recognise that we\u2019ve set up our new user,\u00a0joey,<\/em> with a foolishly weak password and want to force him to change it immediately he logs in.<\/p>\n
ALTER USER joey PASSWORD EXPIRE;\u00a0<\/pre>\n
However, to enable our new user,\u00a0joey,<\/em>\u00a0to actually connect to the database, we must also give them permission to create a session. \u00a0Assuming we have the right to give this privilege, all we need do is run the following:<\/p>\n
GRANT CREATE SESSION TO joey;<\/pre>\n
Now, we should be able to connect as joey and carry out operations on\u00a0test_table<\/em>, right? Wrong. \u00a0This is what happens when we try:<\/p>\n
SELECT * \r\nFROM test_table; \r\n\r\nERROR at line 2: ORA-00942: table or view does not exist<\/pre>\n
And to explain why that\u2019s happening, we need to talk about\u2026<\/p>\n
Synonyms<\/h5>\n
Even though my name is David, back in school everyone called me Handsome<\/em> because, well \u2026 because I\u2019m so, so good-looking (this story, unfortunately, is a lie). The point is that Handsome is (or should have been!) my nickname, my \u2026 synonym<\/em>. In Oracle, a synonym is an alternative name for an object. Without synonyms, every user would need to reference objects using the following notation:\u00a0owner.object_name<\/em> (where the owner<\/em> is the username of the user who has created the object). However, that\u2019s too much typing; with the CREATE SYNONYM command you can shorten that down, and call an object whatever you please.\u00a0<\/p>\n
\"Oracle<\/p>\n
Source: Oracle documentation<\/em><\/p>\n
Assuming that the username of the user with which we created the\u00a0test_table<\/em> table in our example is\u00a0user1<\/em>, joey could run the following command:<\/p>\n
CREATE SYNONYM test_table FOR user1.test_table;<\/pre>\n
That would tell Oracle that whenever it encounters the name\u00a0test_table<\/em> 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\u2019re working on; joey can just as easily write this command: CREATE SYNONYM pineapples FOR user1.test_table;<\/em>.)<\/p>\n
Synonyms like this are called private synonyms \u2013 all synonyms are private by default \u2013 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. \u00a0What this means is that no one should get that\u00a0ORA-00942: table or view does not exist<\/em> error if user1 ran the following command:<\/p>\n
CREATE PUBLIC SYNONYM test_table FOR test_table;<\/pre>\n
(User1 does not need to refer to it as user1.test_table since they\u2019re the table\u2019s owner.)<\/p>\n
Now, we should be able to connect as joey and carry out operations on\u00a0test_table<\/em>, right? Wrong. This is what happens when we try:<\/p>\n
SELECT * \r\nFROM test_table; \r\n\r\nERROR at line 2: ORA-00942: table or view does not exist<\/pre>\n
And to explain why that\u2019s happening, we need to talk about\u2026<\/p>\n
Privileges<\/h5>\n
Let\u2019s 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 \u2013 the privilege \u2013\u00a0<\/em>of climbing through my bathroom window.<\/p>\n
And that\u2019s what a privilege in Oracle is: a right to carry out an operation on an object. \u00a0The various privileges available for an object depends on what kind of object it is, and these privileges can be grant<\/em>ed by the object\u2019s owner.\u00a0<\/p>\n
<\/p>\n
Privileges can also be taken away. \u00a0The syntax for that is as follows:<\/p>\n
<\/p>\n
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, \u201cPlease, climb up my wall and climb into that open window\u201d, I would sound crazy if I said, \u201cPlease, climb into<\/em> my wall and climb up<\/em> that open window.\u201d<\/p>\n
Here\u2019s a list of some of those privileges:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
Privilege<\/strong><\/td>\nObject<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
\u00a0SELECT<\/td>\nTABLE<\/td>\nPermission to query a table<\/td>\n<\/tr>\n
INSERT<\/td>\nTABLE<\/td>\nPermission to insert data into a table<\/td>\n<\/tr>\n
DELETE<\/td>\nTABLE<\/td>\nPermission to delete from a table<\/td>\n<\/tr>\n
UPDATE<\/td>\nTABLE<\/td>\nPermission to update a table<\/td>\n<\/tr>\n
ALTER<\/td>\nTABLE<\/td>\nPermission to alter the structure of the table<\/td>\n<\/tr>\n
ALL<\/td>\nTABLE<\/td>\nAll permissions<\/td>\n<\/tr>\n
EXECUTE<\/td>\nPACKAGE<\/td>\nPermission to run the package<\/td>\n<\/tr>\n
EXECUTE<\/td>\nFUNCTION<\/td>\nPermission to run the function<\/td>\n<\/tr>\n
EXECUTE<\/td>\nPROCEDURE<\/td>\nPermission to run the procedure<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
What this means is that\u00a0user1<\/em> could, if they wanted to grant\u00a0joey<\/em> permission to query test_table, run the following:<\/p>\n
GRANT SELECT ON test_table TO joey;<\/pre>\n
This would mean that joey<\/em> would not get an error now if they ran a select on the table. However, if\u00a0joey<\/em> tried to insert a record into the table, they\u2019ll get this error:<\/p>\n
ORA-01031: insufficient privileges <\/pre>\n
And this is because while\u00a0joey<\/em> 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\u2019t mean I wouldn\u2019t 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.<\/p>\n
You may have noticed two catch-alls above. You could grant\u00a0all<\/em> privileges on test_table to\u00a0joey<\/em>; indeed you could grant\u00a0all<\/em> privileges to everyone.<\/p>\n
GRANT ALL ON test_table TO joey;<\/pre>\n
GRANT ALL ON test_table TO PUBLIC;<\/pre>\n
It is usually a bad idea to grant privileges to PUBLIC. It\u2019s kinda like hiding your spare house key somewhere safe \u2013 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.<\/p>\n
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.<\/p>\n
If you\u2019ve got a good memory you may remember that while we were talking about creating users we ran the following command:<\/p>\n
GRANT CREATE SESSION TO joey;<\/pre>\n
With what we now know, I\u2019m sure you can recognise that as a privilege statement. However, what we\u2019ve been talking about thus far are called\u00a0object<\/em> privileges \u2013 because they apply to objects, such as tables and packages \u2013 CREATE SESSION is another type of privilege; it is a\u00a0system<\/em> privilege.<\/p>\n
Unlike object privileges which apply to individual objects, system privileges grant the grantee the right to carry out a general action \u2013 create a database session, for instance \u2013 or to carry out an action on any object of a particular type. Even though they\u2019re really important, we\u2019re not going to talk a lot about system privileges here; however, here are a few important ones:<\/p>\n\n\n\n\n\n\n\n\n\n\n
Privilege<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
CREATE SESSION<\/td>\nPermission to connect to the database<\/td>\n<\/tr>\n
CREATE ANY TABLE<\/td>\nPermission to create tables<\/td>\n<\/tr>\n
CREATE PROCEDURE<\/td>\nPermission to create a PL\/SQL package, procedure or function<\/td>\n<\/tr>\n
CREATE PUBLIC SYNONYM<\/td>\nPermission to create public synonyms<\/td>\n<\/tr>\n
DROP ANY TABLE<\/td>\nPermission to drop tables<\/td>\n<\/tr>\n
DROP ANY PROCEDURE<\/td>\nPermission to drop packages, procedures and functions<\/td>\n<\/tr>\n
ADMIN<\/td>\nPermission to carry out high-level admin tasks such as creating and deleting users.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Roles<\/h5>\n
So far we\u2019ve been pretending to live in this tiny world of a single table and two users. Let\u2019s throw the windows open and allow reality back in; chances are we\u2019ll have, at least, dozens of tables, and possibly even more users.\u00a0<\/p>\n
Remember all I said about being security conscious with \u00a0your 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\u2019d resign within a week!<\/p>\n
Fortunately we have\u00a0roles.<\/strong> A role is a group of privileges. Privileges can be granted to \u2013 and revoked from \u2013 roles; and roles can be granted to \u2013 and revoked from \u2013 users. \u00a0<\/p>\n
What this means is that if, say, we have a role named low_level we could grant the SELECT privilege \u2013 and nothing else \u2013 on all our tables to it.\u00a0We could then grant the low_level role to a user and he\u2019ll 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\u2019ll get all the privileges in it.\u00a0<\/p>\n
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.<\/p>\n
However, before we do all that, we\u2019d need to create the role.<\/p>\n
\"Create<\/p>\n
CREATE ROLE low_level;<\/pre>\n
And once we\u2019ve got our role, it\u2019s a straightforward thing to grant privileges to it.<\/p>\n
\"Grant<\/p>\n
GRANT SELECT ON test_table TO low_level;<\/pre>\n
And, finally, we would grant this role to a user.<\/p>\n
GRANT low_level TO joey;<\/pre>\n
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.\u00a0<\/p>\n
I hope this article has helped you understand \u2013 and appreciate \u2013 them.\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u00a0 Embarrassing, yes, but what’s it got to do with Oracle? Well, the point…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533,143531],"tags":[48365,48366,48414,48479,124952,48493,48519,48535],"coauthors":[48557],"class_list":["post-73072","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","category-t-sql-programming-sql-server","tag-beginner","tag-beginners-guide","tag-grant","tag-privilege","tag-redgate-deploy","tag-role","tag-synonym","tag-user"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73072"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73072\/revisions"}],"predecessor-version":[{"id":83122,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73072\/revisions\/83122"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73072"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}