I want as many Oracle privileges as possible…
It’s common place for people to grant privileges to Oracle users without taking the time to work out exactly what privileges they actually need. For example, a developer comes along and says “I don’t have permission to create tables and sequences, plus I need some tablespace quota and there are a few other things I want to create as well. To stop me from pestering you all the time, can you just grant me all the privileges?”
On some development environments, it might be OK to be more frivolous with the permissions you grant. However, you should still not grant them any privileges like SYSDBA, or anything that violates your security procedures. The best and most secure way to grant privileges is to always grant privileges with caution and give Oracle users only what they need. The idea is to work on a least privileges basis, which means that you only grant a user the privileges that they actually need and no more.
On environments where you have a lot of users and they all require the same privileges, or you can put them into logical groups who require the same privs, you should look at granting the privileges through the use of Oracle roles rather than explicitly granting the privilege to each user directly. This is what I will cover in the next section.
Granting Privileges Through Oracle Roles
Also, while on the topic of security and granting privileges, it’s a good idea to grant privileges to a role and then grant that role to Oracle users as they need it. This makes administration much easier and means that you can keep track of what roles there are. A good example of why this is a much more efficient way to deal with users and privileges is the removing or adding of privileges to a set of Oracle users.
Say you have 50 users who need a new privilege, and these users all have the role “developer”. Now, all you have to do is grant the new privilege to the developer role. However, if you didn’t use roles and had granted the privilege to each user individually, you would need to work out which users require the privilege and grant it to each one separately.
How to Find the Privileges an Oracle User has
Within the Oracle database there are 3 main views to find out what privileges and roles any user has:
Although I’m using the DBA_ views here, you can also view USER_ views.
SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = 'RMANBACK'; SELECT owner, table_name, privilege, grantable FROM dba_tab_privs WHERE grantee = 'RMANBACK'; SELECT granted_role, admin_option FROM dba_role_privs WHERE grantee = 'RMANBACK';
Note: With all of the queries above you can actually use a ROLE name for the grantee in order to find out what privileges the ROLE has. For example, say that we want to know what system privileges the CONNECT ROLE has. This is the query to do this:
SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = 'CONNECT';
The above queries are an example of how to query the views to determine which system, table and role privileges the user RMANBACK has (RMANBACK could also be a ROLE, as I explain next).
Unidentified User or Role
As I mentioned earlier, a good way to manage security among your Oracle users is to implement a role based privilege structure. If this is in place already your queries above based on specific users will not return any results, because the users themselves have not had any grants granted to them explicitly; it’s all managed through a role. In this case you would substitute the name of the role into each query where I have put RMANBACK to find out which privileges the ROLE has and therefore what privileges users with the ROLE have…
So, you can use the query against DBA_ROLE_PRIVS to determine which roles a user has and then you can use the views ROLE_SYS_PRIVS, ROLE_TAB_PRIVS and ROLE_ROLE_PRIVS to determine exactly what privileges the roles have. This means that there are two ways to get the information that you want. You will often find that there are multiple approaches to get the same result in Oracle.
It can also be necessary to see what quota your users or roles have on different tablespaces. This can be seen from the DBA_TS_QUOTAS view. Query it and have a look at what you can find out from it. I’ve written a query below which will show you the information for a specific user and what quota they have on a tablespace. A value of -1 in the max_bytes column indicates UNLIMITED quota, but I have used a DECODE statement to say UNLIMITED when this happens:
SELECT tablespace_name , username , round(bytes/1024/1024,0) Used_MB , decode(max_bytes,-1,'UNLIMITED',max_bytes/1024/1024) Max_MB FROM dba_ts_quotas WHERE username = 'username' ORDER BY tablespace_name;
Some users, but hopefully only one or two, should have the SYSDBA and/or SYSOPER privileges. This can be determined from the V$PWFILE_USERS view. This will tell you which Oracle users have the above privileges.
I Want to CONNECT to Oracle…
The CONNECT role in Oracle is a strange privilege. In Oracle 9i it was a role with the following privileges:
CREATE DATABASE LINK
All these privileges for simply making a connection to the database!? That’s far too many. Oracle saw the error of their ways and changed this in Oracle 10g so that the only privilege that the CONNECT role has is the CREATE SESSION privilege. This also introduces problems when users who have the CONNECT role no longer have all the privileges that they were used to having. This is covered in more detail in the Oracle 10g upgrade documentation.
To conclude, if you always stick to the mantra of “I will only grant the privileges that you need”, you won’t go too far wrong.
Don’t go PUBLIC…
After Oracle has been installed and you have your database up and running you should look to see what privileges have been granted to PUBLIC. Having privileges granted to the PUBLIC really introdcues security issues. You should revoke as many privileges as possible from PUBLIC and manage them all via roles and grant them only as needed to individual users. A couple of examples are:
Like it, share it...