![]() (Windows NT 10.0 WOW64) Ap Enterprise Manager RoleĪctivate the checkbox for the privilige Connect to any viewable target and scroll downĪdd database targets and set the Manage Targets Privilege Grants 11:38 APPL_ADMIN ALTER SYSTEM ALTER SYSTEM KILL SESSION '124,23652' IMMEDIATE 11:33 APPL_ADMIN ALTER SYSTEM NT 10.0 WOW64) Ap TIME USERNAME ACTION_NAME CLIENT_ID SQL_TEXT In the example below you can see the difference in the column SQL_TEXT. How it works with Unified Auditing will be verified in a later blog post. If audit_trail is not set to EXTENDED, the SQL command which was executed is not recorded. ![]() Verify the audit parameterin the target database. Verify the enabled audit settings: SQL> SELECT user_name,privilege,success,failureĪPPL_ADMIN ALTER SYSTEM BY ACCESS BY ACCESS Grant role to the user: SQL> GRANT role_appl_alter TO appl_admin Enable Auditing for ALTER SYSTEM Commandsįor ALTER SESSION and ALTER SYSTEM: SQL> AUDIT ALTER SYSTEM BY appl_admin SQL> GRANT ALTER SYSTEM TO role_appl_alter SQL> GRANT SELECT ANY DICTIONARY TO role_appl_alter This role has to be created in every target database. SQL> GRANT CONNECT TO appl_admin The New Database Role SQL> CREATE USER appl_admin IDENTIFIED BY mypassword ![]() This user has to be created in every target database. we build an Enterprise Manager report which shows us the ALTER SYSTEM actions based on a metric extension.we create a new named credential with the new user and grant it to the application administrators.we create a new Enterprise Manager role for the application administrators.we enable auditing for ALTER SYSTEM commands in the target databases.we create a new role with ALTER SYSTEM privilege in the target databases.we create a new database user in the target databases.Note: All the steps which are show below in Enterprise Manager 13c can be executed in 12c too. But in fact we have implemented this solution in a production environment two months ago without any negative impacts. I am aware that this is – like we say in Switzerland – a “Kompromiss”. ![]() Here is an approach to manage the small path between security and manageability. 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. Until now they called the DBA: “Please do it for me”. And sometimes, they have to kill a hanging Oracle session. 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. This user is now able to change a lot of parameters like memory parameters, NLS settings etc. Granting the ALTER SYSTEM privilege to a Non-DBA has big risks. Basically to execute a ALTER SYSTEM KILL SESSION command you have to be a) a DBA or b) you need the ALTER SYSTEM privilege. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |