It reminded me of a recent conversation I had with a client who wanted to protect his databases against any changes and wanted to ensure that user and general application schema accounts could not change the structure of the database. This was easy for general users accounts as we simply audited all of them for granted privileges with my find_all_privs.sql script. Those users found with any SYSTEM PRIVILEGES had those privileges revoked.
This left application schemas. In general, application schema accounts tend to have been granted many CREATE PRIVILEGES such as CREATE TABLE, CREATE VIEW or CREATE PROCEDURE or any of the other myriad of system privileges that allow objects to be created. This is normal for application schema accounts. The question is do these accounts need these CREATE PRIVILEGES to function normally? - in general the answer to this question is no. There are some applications that create objects on the fly of course. So if an application does not need CREATE PRIVILEGES normally the why do they have them? The answer is that they had them to create the schema in the first place and possibly to alter it and change it when any application changes were needed.
So in the case of my client, he wanted to ensure that his application schema accounts could not be used to add or change objects, even only in the application schemas. So what we did was analyse all of the privileges granted to the application schemas, directly or via ROLES and decided to isolate these privileges. We also considered ALTER PRIVILEGES at the same time. The application schemas had privileges granted directly and also to ROLES and in some cases to ROLES granted to ROLES. We also found that some privileges were granted multiple times via roles and not via roles.
What we did first was to extract all of the privileges from the roles and directly grant privileges and listed them out and tried to define what they were used. We established that all of the SYSTEM PRIVILEGES in this case were all needed only for schema creation. This was clear in this case as all of the application source code was available and could be parsed for embedded SQL. The company uses a proper change control procedure so the solution was to create a privilege creation and revoke scripts to be used when change controls were applied. All of the identified privileges were revoked and the applications tested. The company uses quite a rigid change control procedure and release mechanism. A change was needed to allow a privileged connection to grant and revoke the privileges to the schema owner before the release scripts could now be run. As the release was controlled manually this was easy.
The system has worked well so far, the schema accounts no longer have object creation or alteration privileges except when changes are applied and all the user accounts were reviewed and pruned.
The next steps are to completely review the privilege list and prune it somewhat. At the moment all of the original privileges are simply granted back and revoked again for releases. The list has slack in it and will be reduced.
Anyway I thought that this would be a good subject to talk about generally as quite often I see application schemas that have lots of system privileges that can be used to change the schema; in general they are not needed during normal application use. The problems involved are that the designers of the applications need to consider SYSTEM PRIVILEGES not just in general but also in different time frames. Quite often SYSTEM PRIVILEGES are decided on at the beginning of development and then added to and this whole list of privileges gets added to the production databases - remember I am talking about schema accounts here not general user accounts. A better approach is to consider what privileges are needed to
- Install and create the application schema
- what privileges are needed to perform upgrades - remember that this list can change depending upon what the change is. The privileges needed to install a change need to be decided when a change control is designed
- Finally what privileges are needed to run the schema in production - This is not the same as the privileges needed by application users although if a shared schema is used and unfortunately the schema account has been used for this purpose then it could be. In general after a schema has been installed it should be locked, an invalid password set, password management features enabled and all privileges revoked apart from those needed by PL/SQL code running as the schema owner.
If you have the unfortunate issue of having a shared account that is also the schema account then i would advise changing to use another account. This can often be difficult but a starting point is to grant the existing schema privileges to the new account and work onwards from there.
So what about reducing privileges in any of the three time periods that the schema account is used for? The installation and change periods are easier as the schema and changes can be reviewed to create the correct privileges. The general use period can be more difficult especially if the source code is not available. One method to establish the privileges needed is to monitor the application for SYSTEM PRIVILEGES that have been used. This can be achieved through the use of audit or via a database system trigger. Even commercial tools that audit all of the database network connections coming into the database could be monitored and the SQL / DDL extracted and reviewed. There are commercial tools listed on my web site that are aimed at doing just this. The problems with this method are obvious - That is you can never know if all code paths have been used. A much better approach to reduce SYSTEM PRIVILEGES in an application are to review the source code for the attempted use of these privileges.
Review the privileges granted to your application users and also to the schema owners. Use my scripts find_all_privs.sql for this. Attempt to remove SYSTEM PRIVILEGES from these users. If SYSTEM PRIVILEGES are needed in applications normal running then encapsulate their use in package procedures.
