Basically to execute a ALTER SYSTEM KILL SESSION command you have to be a) a DBA or b) you need the ALTER SYSTEM privilege. Granting the ALTER SYSTEM privilege to a Non-DBA has big risks. This user is now able to change a lot of parameters like memory parameters, NLS settings etc.
In one of my projects, a small team of well known application administrators is having a read-only account in Enterprise Manager 12c to verify the performance, see the user sessions and many more of their subset of databases. And sometimes, they have to kill a hanging Oracle session. Until now they called the DBA: “Please do it for me”. Sure, we can build a small PL/SQL procedure on every database and give them the executions rights so they can kill a session in their terminal theirself. But this is not very user friendly.
Here is an approach to manage the small path between security and manageability. I am aware that this is – like we say in Switzerland – a “Kompromiss”. But in fact we have implemented this solution in a production environment two months ago without any negative impacts.
Note: All the steps which are show below in Enterprise Manager 13c can be executed in 12c too.
The Concept
- we create a new database user in the target databases
- we create a new role with ALTER SYSTEM privilege in the target databases
- we enable auditing for ALTER SYSTEM commands in the target databases
- we create a new Enterprise Manager role for the application administrators
- we create a new named credential with the new user and grant it to the application administrators
- we build an Enterprise Manager report which shows us the ALTER SYSTEM actions based on a metric extension
The New Database User
This user has to be created in every target database.
SQL> CREATE USER appl_admin IDENTIFIED BY mypassword; SQL> GRANT CONNECT TO appl_admin;
The New Database Role
This role has to be created in every target database.
SQL> CREATE ROLE role_appl_alter; SQL> GRANT SELECT ANY DICTIONARY TO role_appl_alter; SQL> GRANT ALTER SYSTEM TO role_appl_alter;
Grant role to the user:
SQL> GRANT role_appl_alter TO appl_admin;
Enable Auditing for ALTER SYSTEM Commands
For ALTER SESSION and ALTER SYSTEM:
SQL> AUDIT ALTER SYSTEM BY appl_admin;
Verify the enabled audit settings:
SQL> SELECT user_name,privilege,success,failure 2 FROM dba_priv_audit_opts 3 WHERE user_name = 'APPL_ADMIN'; USER_NAME PRIVILEGE SUCCESS FAILURE -------------------- -------------------- ---------- ---------- APPL_ADMIN ALTER SYSTEM BY ACCESS BY ACCESS
Verify the audit parameterin the target database. If audit_trail is not set to EXTENDED, the SQL command which was executed is not recorded. How it works with Unified Auditing will be verified in a later blog post.
SQL> SHOW PARAMETER audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string DB, EXTENDED
In the example below you can see the difference in the column SQL_TEXT.
TIME USERNAME ACTION_NAME CLIENT_ID SQL_TEXT
---------------- ---------- --------------- ------------------------------------ --------------------------------------------------
06.01.2016 11:33 APPL_ADMIN ALTER SYSTEM APPL_BERGER@192.168.58.1@Mozilla/5.0
(Windows NT 10.0; WOW64) Ap
06.01.2016 11:38 APPL_ADMIN ALTER SYSTEM APPL_BERGER@192.168.58.1@Mozilla/5.0 ALTER SYSTEM KILL SESSION '124,23652' IMMEDIATE
(Windows NT 10.0; WOW64) Ap
Enterprise Manager Role
Setup – Security – Role – Create
Set name and description – Next
Next
Activate the checkbox for the privilige Connect to any viewable target and scroll down
Add database targets and set the Manage Targets Privilege Grants
- For EM12c use: View
- For EM13c use: Manage Database Sessions
Next
Select the user to grant the role – Next
Finish
The role is now created and be granted to a user.
Enterprise Manager Named Credential
The application adninistrators don’t have to know the password for the created user with the ALTER privileges. We create a named credential and give the admins the permission to use it.
Setup – Security – Named Credentials – Create
Set Credential Name, Authenticated Target Type, Credential Type and set Scope to Global. The Credential Properties are according to our new created user APPL_ADMIN.
Scroll down to set Access Control
Add Grant
Search for the user, in my case it is APPL_BERGER
Select
Feel free to test ist against a target which contains the APPL_ADMIN user.
Save
Test
Now we test the configuration. The role APPL_ADMIN was granted to my user APPL_BERGER. On the target database TVD12 user SCOTT has locked some data.
On the target site we go to the Blocking Sessions page
The Named Credential is already filled in.
Login
Select the session – Kill Session
Confirm the action to kill the selected session immediate – Yes
Session has been killed.
Verification
On the target database, an audit record was generated. Login as user SYS and execute this query. You can see the TIME, the USERNAME, the EM13c USERNAME in column CLIENT_ID and the SQL statement which was executed in background.
SQL> SELECT TO_CHAR(TIMESTAMP,'DD.MM.YYYY HH24:MI') AS TIME,
2 username,
3 action_name,
4 client_id,
5 sql_text
6 FROM dba_audit_trail
7 WHERE action_name='ALTER SYSTEM'
8 ORDER BY TIMESTAMP;
TIME USERNAME ACTION_NAME CLIENT_ID SQL_TEXT
---------------- ---------- --------------- ------------------------------------ --------------------------------------------------
06.01.2016 11:38 APPL_ADMIN ALTER SYSTEM APPL_BERGER@192.168.58.1@Mozilla/5.0 ALTER SYSTEM KILL SESSION '124,23652' IMMEDIATE
(Windows NT 10.0; WOW64) Ap
Summary – Part 1
As I said in the introduction, giving some other users than DBAs the ALTER SYSTEM privilege is risky. But when the DBAs and application adminstrators are working as a team, then this can be a possible solution to make their daily business easier.
In the next blog post I will show how you can create a Enterprise Manager report based on a Metric Extension to produce daily reports of the ALTER SYSTEM actions.