Pete Finnigan's Oracle Security Weblog

I see questions relating to creating read only users on the mailing lists and newsgroups very regularly and sometimes directly to my own mail box. I also see regularly a deep misunderstanding of how to do this and also more importantly how to do it securely.

Quite often someone will suggest granting SELECT ANY TABLE or will suggest solutions with no common sense like I saw today where one guy suggested granting the CONNECT role.

This tells me that there are two problems. First people do not understand what a read only user is, i.e. the assumption that the word CONNECT allows a user to connect and presumably read all data is obviously wrong. But granting CONNECT to a user in this scenario is also wrong as this role allows far too many create privileges as well as ALTER SESSION, not to mention the user created would not be able to see the data he or she needed. The second issue is with the sweeping grant of SELECT ANY TABLE. This is often done without considering the side effects of this.

Quite often there is a strong business need to have some users that can simply read any business data. I rarely find that this is totally necessary though. If a business user wants to query the data to produce ad-hoc reports using reporting tools or even SQL*Worksheet or SQL*Plus then they inevitably need access only to sub sets of data not all of it.

The first step in creating a read only user is to start with the minimum privileges necessary. The user should have a strong password, password management should be enabled to cover things like password reuse, length of time a password can be used, password length and complexity can be enforced with a password verification function and also use the kernel resources and set shared_sessions_per_user to 1.

If a read only account is to be created then ensure it can only be used once and not shared amongst a whole department of power users. The user should at first only be granted the CREATE SESSION privilege and should then be granted SELECT PRIVILEGES only on the tables or views needed. You should ensure no CREATE PRIVILEGES are granted and that the user has no quotas granted on any tablespace. In summary start with just CREATE SESSION and nothing else.

The next step is to gather requirements and decide what tables or views need to be accessed by the user. This is the bit often missed where instead the user is simply granted SELECT ANY TABLE instead. This should not be done. In general the tables and views needed will be from one or possibly a couple of schemas, usually the applications schema. Identify with the user the necessary data that he needs. I know this is often not easy but it must be done rather than the easy option. The necessary grants can then be scripted from the list of tables or views if necessary.

The actually grants should be assigned to a new role created for the purpose. This role is then granted to the user. This has two benefits. The permissions are ring fenced and also if it turns out that others in the department also need this access granting can then be done via granting the role. If only one user is going to need these privileges then it can be OK to grant them directly. This is the hard part over.

You can use a script such as find_all_privs.sql to check the privileges granted to the new user. If any other privileges except CREATE SESSION and the role are returned revoke them. Always follow the least privilege principle.

Do not take the easy route and grant roles like CONNECT or RESOURCE they do not create read only users and they have far too many privileges included.

Never grant SELECT ANY TABLE or SELECT ANY DICTIONARY and ensure that the parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE (the default in 9i) as any use of SELECT ANY TABLE would then allow the user also to access the dictionary including password hashes.

The whole process requires planning and forethought not quick fixes. This is the key. Finally consider auditing the access to the tables and views that have been given if any of the data is critical. Just because the user has access to create some ad-hoc reports doesn't mean he should access the data wholesale. If detailed audit is needed then use triggers. These can create some performance overhead if used so be aware and audit critical data. It can also be good to let the user know that he has the access but his access is also being monitored. A good source of audit configuration is the paper I wrote called An introduction to simple Oracle auditing. It can also be a good idea to consider the use of Row Level Security if you are allowing users to have read only access. This can be a good solution to preventing access even in these cases to certain data. A good two part paper on row level security can be found here_1 and here_2. Remember do not simply grant SELECT ANY TABLE and also remember the least privilege principle.