This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Tag: User

Kill All Oracle Sessions For Specific User – 11gR2

Here’s a small script to display, then kill all sessions in an Oracle 11gR2 database for a specific username.

First let’s check the sessions of the specific username:

NOTE: Change “<USER NAME HERE>” in both blocks of code, for the actual username to be killed.

SQL> set serveroutput on size 10000;
SELECT sid,serial#
  FROM v$session
WHERE username = '<USER NAME HERE>';

Now run the code to kill the user sessions:

SQL> DECLARE
   dummy NUMBER;
   c     NUMBER := dbms_sql.open_cursor;
BEGIN
   FOR c1_row IN (SELECT sid,serial#
                  FROM v$session
                  WHERE username = '<USER NAME HERE>') LOOP
      DBMS_OUTPUT.PUT_LINE('Killing session: '||c1_row.sid||' serial#: '||c1_row.serial#);
      DBMS_SQL.PARSE(c,'alter system kill session '||''''||c1_row.sid||','||c1_row.serial#||'''',dbms_sql.NATIVE);
      dummy := DBMS_SQL.EXECUTE(c);
      DBMS_OUTPUT.PUT_LINE('Session: '||c1_row.sid||' serial#: '||c1_row.serial#||' killed.');
   END LOOP;

   dbms_sql.close_cursor(c);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

You can now run the check code again to see that all sessions for the user have been killed.

HowTo: Query to show SAP roles and transaction codes by user

Scenario: You have been requested to provide a list of all roles currently assigned to your SAP user accounts, plus the transaction codes that are assigned to each role and user account.

HINT: To be able to do this within SAP, you can use the SAP QuickViewer (SQVI) to create a query and join the required tables. You could then generate a program and then copy it to create your own Z-report.

Using the following Oracle SQL*Plus query at the database level, will allow you to produce a report containing the USERNAME, ROLENAME, TCODE_RANGE_START and TCODE_RANGE_END.

set linesize 500 pagesize 9999 newpage none recsep none
SELECT u.uname USERNAME,
               r.agr_name ROLENAME,
               r.low TCODE_RANGE_START,
               r.high TCODE_RANGE_END
  FROM agr_1251 r,
       (select mandt,
               uname,
               agr_name
          from agr_users) u
 WHERE r.agr_name = u.agr_name
   AND r.mandt = u.mandt
   AND r.mandt = <YOUR CLIENT>
   AND r.object='S_TCODE'
ORDER BY u.uname,r.agr_name,r.low,r.high;

NOTE: You should adjust “<YOUR CLIENT>” to be the client number you wish to check.

You should note that TCODE_RANGE_START and TCODE_RANGE_END could contain wild cards as per the usual methods of providing a range of values to an authorisation object in PFCG.

SAP Users With Roles Not Assigned via Composite Roles

Have you ever needed to list SAP roles that are assigned to user accounts, but show only the single roles that are directly assigned (not single roles inherited through composite roles)?

Here’s how you can do it:
Using SE16, get the records from AGR_USERS table with field COL_FLAG=’ ‘

Relate this to USR02 table BNAME field to decide if the user account is locked (valid) or not in use anymore.

I’ve also discovered this can be done in transaction S_BCE_68001394 (Users by User ID).  You just input * into the user ID field, execute the report and then sort the two columns for “Direct Assignment” and “Role Type”.  This will give you the Single roles assigned directly.