Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Alan.

Asked: March 20, 2007 - 6:48 pm UTC

Last updated: November 10, 2012 - 3:50 pm UTC

Version: 10.2.0.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm working on a project to limit access to our databases. I've noticed that the CREATE SESSION privilege is granted to all users and understand that it does, as the name implies, create a session to the database. My question however is why would you NOT want to grant a user CREATE SESSION? I could understand in the case of an application user you can simply revoke CREATE SESSION thereby leaving the objects that belong to the account intact but preventing the account from being used to log on. However, can't you just simply lock the account in this case?

Thanks in advance for clarifying this.

-Alan

and Tom said...

probably never - you would lock the account these days.

In the past, you could revoke it in order to accomplish something similar to locking the account, but that is no longer a 'trick' we need to use.

Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Michel CADOT, March 21, 2007 - 7:11 am UTC

Actually, we have schemas that are only schemas that is containers of objects. These are not users so they don't need to connect and so they don't need to have CREATE SESSION privilege.
We also have users that need to connect but only from time to time (they are batch users used by monthly jobs). These users has been granted CREATE SESSION but they are locked.

In summary, it is just a matter of what is the purpose of the account. If it is for processing then grant it CREATE SESSION and lock it when it is not used. If it is a pure schema then don't grant it CREATE SESSION.

Just my 2 (euro) cents.

Regards
Michel

Tom Kyte
March 21, 2007 - 7:41 am UTC

that can easily be accomplished by the more straightforward "lock account".

Those schemas that are containers - I'm sure they need to log in sometimes, to process upgrades and the like.

but - it is a matter of choice - either approach is viable and 'safe'

Schema vs. User

John Flack, March 21, 2007 - 8:36 am UTC

Either revoking or failing to grant CREATE SESSION is a viable way to protect schemas that are just that, schemas - intended to hold objects, but not intended to log in as users. However, to create these objects, you either have to log in as that schema - i.e. create a session, or you have to have CREATE ANY privileges. I'm not crazy about either option, but I do allow people to create sessions as a schema long enough to create objects - better that than grant CREATE ANY, which I regard as EVIL. I wish there were grantable privileges that let you create objects in a specific schema, but not ANY schema.

Schema vs. User

Michel CADOT, March 21, 2007 - 10:28 am UTC


Just talking about the options we took.

On development/test servers, container schemas (we called them application owners) are accessible to any developers, these accounts have CREATE SESSION privilege and many others to be able to create and modify their objects.

On production system, only DBA are allowed to create objects or execute any other DDL (with few exceptions). As DBA can create objects in other schemas, there is no need to application owner schema to be able to connect or have any privilege. This is just a container, others fill it.

Tom,
Yes, no CREATE SESSION privilege or LOCK ACCOUNT have the same effect but when we check privileges it is easier (for us) to not see application owners in the list of users having privileges than checking if they have CREATE SESSION then their account must be locked.

Regards
Michel

Revoke create session versus lock account

Glen, March 21, 2007 - 11:39 am UTC

Is there any technical reason to use lock over the create session privilege?

For instance, when I'm upgrading even if I'm using lock, I'd have to unlock->upgrade->lock. What would be the difference if I grant create session->upgrade->revoke create session?
Tom Kyte
March 21, 2007 - 12:08 pm UTC

six one way.
1/2 dozen the other.

use either one, "lock" just seems "more sensible" to me - personally

When not to use "create session"

Alan Williams, March 21, 2007 - 12:20 pm UTC

Thanks for the reply Tom.

I was wondering, was there ever a reason (maybe back in Oracle 7/8 days) to use CREATE SESSION as opposed to LOCK or vice versa?

Thanks again!
-Alan
Tom Kyte
March 21, 2007 - 12:27 pm UTC

sure, there was no LOCK ACCOUNT back then :)

To Glen

Michel Cadot, March 21, 2007 - 12:43 pm UTC


If you need to connect with the user, then it is more sensible, as Tom said, to use lock/unlock.
But if you never connect with the user, even for upgrade, then, imo, don't grant CREATE SESSION.

Regards
Michel

To Michel CADOT

Scot, March 21, 2007 - 1:34 pm UTC

Hi. Yes I've come to use basically the same technique as you describe, to have schemas that are object / application owners but not actual users, and therefore find it "cleanest" to not ever grant them create session. I have seen too many environments with "shared" accounts, where everyone on the team / organization just shares the password with everyone else, even in production, logging on to do whatever they need done.

I also agree with Tom in that it can be done either way, and in actuality I both lock and expire the account in addition to not granting it create session to begin with. I find that this displays nicely in reports from both dba_users as well as dba_sys_privs. Quite explicit as to the intent.

reason to use Create Session instead of Lock account

Jeff, March 21, 2007 - 2:12 pm UTC

One advantage of Create session over Lock account is if you have many accounts you want to be able to work with at once. In that case you can grant each of these schemas a role, than grant/revoke Create Session to the role and affect all of the accounts at once, rather than having to run many Lock statements.

I've used this in managing oracle apps databases, where you have hundreds of schemas that never need to log in except when applying patches or running upgrades. Makes the SOX auditors happy to have these accounts "turned off", makes me happy to be able to toggle them with one grant or revoke, rather than having to generate a lock or unlock statement for each individual username.


To Michel Cadot

Glen, March 21, 2007 - 2:19 pm UTC

Not what I understood from Tom. I've always used lock to handle suspended accounts. If you grant CREATE SESSION, do your upgrade then revoke CREATE SESSION how is that different from UNLOCK, do upgraded then LOCK? To me the grant->upgrade->revoke cycle is much more logical and straight-forward. I was concerned that using LOCK may have technical advantages.

BTW, I've NEVER used shared accounts in my 20 years of designing database systems, such a thing did not even occur to me until someone else mentioned them. If one of my people suggested it, he/she'd catch hell.
Tom Kyte
March 21, 2007 - 7:35 pm UTC

gee, because I've never used something, if someone suggests to use it, I'd yell at them.

Fascinating.

Given that lock account has been available for a small fraction of the 20 years - that you haven't used it in 20 years doesn't really make a sensible argument.

To me the unlock -> upgrade -> lock is much more logical. That is as sensible and opinionated as your grant->upgrade->revoke.

Your last paragraph however frightens me. Sorry - but you are part of a general problem if that truly is your attitude.

To Glen

Michel Cadot, March 21, 2007 - 4:10 pm UTC


Maybe I badly express myself but I never grant CREATE SESSION to these users. Upgrade are made by another user (a DBA) that upgrades the objects in those schemas. This is why I don't need CREATE SESSION, even these accounts have an invalid password.
To be fair, I also lock these accounts.
I don't know how you say that in English but in French they nickname me "ceinture et bretelles" (belt and suspenders).

Regards
Michel

?

Glen, March 22, 2007 - 10:11 am UTC

Tom,

Huh?
Tom Kyte
March 22, 2007 - 10:27 am UTC

eh?

grunt...

umm? yes - what do you mean?

Lock versus session

Glen, March 22, 2007 - 10:31 am UTC

Tom,

I am trying to find out if LOCK is better technially than CREATE SESSION (which would still be my preference if LOCK is not better for technial reasons) not arguing whose preference is better.

As I said, I use LOCK to suspend acounts. My first thought when I see a locked account is that the user was suspended for some reason (like the user went on vacation or used a bad password). An account with no create session (also an impossible password and locked) tells me that this account is a sort of schema-only type.
Tom Kyte
March 22, 2007 - 10:48 am UTC

they are functionally equivalent.

I and see I misread your comment - I thought for some reason you were saying

...
BTW, I've NEVER used shared accounts in my 20 years of designing database systems, such a thing did not even occur to me until someone else mentioned them. If one of my people suggested it, he/she'd catch hell.
......

about locking an account - sorry about that, I read it entirely wrong. doh.

Grunt?

Glen, March 22, 2007 - 10:32 am UTC

I was asking what you meant.
Tom Kyte
March 22, 2007 - 10:48 am UTC

see above, I misread your followup.... sorry about that.

To everyone I guess :)

Scot, March 26, 2007 - 9:06 am UTC

I mentioned above about going the route of not granting create session to begin with. With this comes the thought that the application schema, the owner of the app objects, doesn't ever connect. Like someone else mentioned, a DBA or an application installer user simply creates objects in the appropriate application schema.

In this way, an upgrade doesn't require you to grant and revoke create session, nor does it require an unlock and then lock again. Literally nobody ever logs in.

Has anyone who has used this approach run into any problems? Or does anyone know of any technical issues that would make this approach..."less desirable"?

The only one I know so far involves not being able to create private database links in someone elses schema.

Locking users after a period of inactivity...

Craig, July 13, 2007 - 11:43 am UTC

Tom,

Is there a way to automatically lock an account that has been inactive for so many days? The profiles provide for password expiration and also for locking the account after some number of failed login attempts, but it's not quite what I need.

Thanks and best regards,
Craig
Tom Kyte
July 13, 2007 - 12:05 pm UTC

not directly that I know of. If you audited connects, you would be able to schedule a rather simple job to run once a day to lock accounts that hadn't been used in so many days....

Locking inactive accounts

Richard, July 13, 2007 - 1:37 pm UTC

One option you may consider would be a simple table with username and a date column. Then, create a logon trigger that checks/updates the date in this table for the username; if the date is too old, raise an error to prevent the login from proceeding, otherwise, update the date on the table. If you're already using session contexts you could add an attribute for the "age" of the last login based on user types, etc., if you wanted to avoid blocking query-only generic users who may not login regularly.

Why I think UNLOCK is better

Felipe Moreno, November 09, 2012 - 7:41 pm UTC

Hi guys, I know this post is old but I guess this might be of some use...

I think unlocking an account is really the only (or the easiest) way to prevent a user from logging in.
Because even if you revoke the CREATE SESSION privilege from a user they would still be able to log in to the database by using a ROLE that has this privilege.
E.g:

-- Let's say there's some evil role with the create session privilege:
SQL> CREATE ROLE hidden_privileges;
SQL> GRANT create session TO hidden_privileges;

-- That's the schema/batch user
SQL> CREATE USER userx IDENTIFIED BY teste;
SQL> GRANT create session TO userx;

-- If the user has an "alternative" way for connecting to the database
SQL> GRANT hidden_privileges TO userx;

-- Just doing this wouldn't prevent them from logging in
-- You would have to revoke the role as well
SQL> REVOKE create session FROM userx;

-- That would work
SQL> ALTER USER userx ACCOUNT LOCK;

I know this sounds stupid. And you guys probably know a fancy way to determine if a given user has access to a role to which was granted the CREATE SESSION privilege but that's not my point. What I wanted to say is that revoking a privilege might not actually "work". 


Anyway, I'm very new to the world of Oracle (so what?) and albeit basic this issue might be extremely important for the DBAs of tomorrow, so please point it out if I'm mistaken. 


Thanks
=)

Tom Kyte
November 10, 2012 - 3:50 pm UTC

but that is what we said way back when?

" you would lock the account these days. "