User Management

Copyright Oriole Corporation, 1999

Users, roles, profiles and privileges

User management is one of those routine tasks devoted to DBAs which must be properly done if you do not want to have lots of trouble. Let's say it frankly, a userless life would be more quiet.
When we say users we refer in fact to Oracle accounts, similar by many ways to computer accounts : when you log into a computer, you need an account and a password, and you usually land in a place where you can create files. Moreover, you are usually assigned to a group of users who are allowed to execute some commands and not others, to modify some files and not some others, and who may even not be able to read some secure files. On some operating systems, some special characteristics may be assigned to your account, such as forcing the renewal of your password after a while, or forcing your deconnection after your spending too much time at the coffee-machine.
All of this has something equivalent with Oracle :

  • You create Oracle accounts by creating users, identified by a password (in some cases you can have a single sign-on, which means that the user has been identified by a password once and for all when connecting to the machine or network, and needs not entering the same or another password when connecting to Oracle).
    You must assign to users a default tablespace which is the place (physical files, see the paper on physical storage management) where his/her tables will be stored (if allowed to create tables ...). You must also define for them a temporary tablespace (usually the same for everybody) which is where everything temporary will be created (during sorts, especially) and is strictly equivalent to /tmp or C:\TEMP on a machine. Note that users need not having any special right on the temporary tablespace. What else? Users can also be given tablespace quotas, which are the same as disk quotas, or in other words how much storage their tables and associated indexes can use in the named tablespaces. No quota means no right to create anything there. In practice, you should always give unlimited quotas on tablespaces where users can create their tables : what would you do if a user were running out of quotas? Increase them...
  • Roles are the equivalent of user groups. The mains differences are
    1. the way you envision things : you say that an operating system user belongs to one or several groups, whereas with Oracle you grant a role (or several ones) to a user.
    2. you can also grant a role to a role, thus creating cascades of rights. Do not use this too heavily, it quickly makes matters complicated and after a while you no longer know where the various privileges come from, which is not properly excellent for security. Avoid more than two levels.
  • privileges are also quite often called grants, after the SQL command GRANT which is used to give a right to a user (REVOKE is used to take it off). In fact you have two categories of privileges :
    • system privileges which define, roughly, what you can create in the database (tables, stored procedures, views, simple synonyms, but also other users, roles, tablespaces ...) - those privileges are granted by a DBA,
    • and objects privileges which define which tables you can see, which stored procedures you can execute, which tables you can update ... - those privileges are granted by the object owner and, interestingly, cannot be granted by a DBA, unless the object owner has expressly allowed the DBA to do it.
    As said above, those privileges can be granted (except for very few and somewhat peculiar ones) to a user or a role.
  • profiles which can only be assigned to users (not to roles) define limits on a number of thinks, including I/Os, CPU used, etc. In practice they are only useful for two things :
    • defining after how much idle time the session is disconnected from the database (putting this in place is rarely immensely popular but can be useful),
    • (only with Oracle8) defining some expiry date for passwords.

In practice ...

In practice you have only four types of users :

  1. Database Administrators, you, me, quite obviously la crème de la crème.
    When you create a database, two DBA accounts are created : SYS, which owns the database dictionary tables, and SYSTEM, the archetypal DBA. Nobody should ever connect as SYS, so set its password to something like hgqshdygthqshaez and that's it. We advise you to create a third DBA account, which corresponds to the operating system account used to install Oracle and is externally identified (please refer to the Oracle documentation for external authentification, it is a subject not extremely complicated but which has the bad taste of being pretty dependent on the underlying operating system). The reason for this is that you will probably have to run, for maintenance reasons, DBA scripts started by the operating system task scheduler. With an externally identified account, you need not hard-code passwords in scripts. The trouble with hard-coded passwords is, first that somebody else can read the file and the password, and even if files are strictly unreadable by somebody else it prevents you from regularly changing passwords. Keep SYSTEM for DBA operations you may have to do through SQL*Net (when you work in client/server instead of being directly connected to the server).
  2. Application owners. For any application running on your database, you have a set of tables, indexes, procedures etc. which belong to same user (in this case schema is smarter but means exactly the same as user). Create once and for all a role named APP_OWNER and grant it the following privileges :
    • create session
    • alter session
    • create table
    • create view
    • create synonym
    • create public synonym
    • create sequence
    • create procedure
    • create database link
    • create type (Oracle8 with the Object Option)
    • create snapshot
    • create role
    that's about all you need in most cases (you can always add something if you have special needs). Two tablespaces at least, one for tables, one for indexes, and unlimited quotas on both. In practice, developers have a lot of trouble creating tables and indexes at the right places (especially indexes associated with primary key constraints). Do you want a useful trick? Set the default tablespace to a tablespace where the user has no quota and no right to create anything. This way, developers will be unable to create anything unless they specify the tablespace as they should. Hehehehe.
  3. Advanced Users. Those are users who may need to create tables, either because they do some small developments or they need temporary work tables for some programs. You can create a role with fewer privileges than before for those users too. Additionally, create a one-size-fits-all USERS tablespace, make it the default tablespace of the advanced users (give them unlimited quotas on it too), and you should be quiet.
  4. Rank-and-file Users. It's amazing how few privileges they really need. You can create some END_USER role with the following privileges :
    • create session
    • alter session
    and that's all. No need to create synonyms, public synonyms (valid for everybody) should normally be created by application owners, who should also create the roles needed to access and update their tables, and grant them to users. In other words, most of the user management belongs in fact for end users to application owners.

By following these simple rules, you should be able to support fairly happily a huge army of Oracle users.

previous Table spaces etc The mythical rollback segment next page