Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Wednesday, September 26, 2012

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.

Monday, September 24, 2012

Oracle ReCreate DBSNMP User

Here's how to drop and recreate the DBSNMP Oracle user:

> sqlplus / as sysdba

SQL> @?/rdbms/admin/catnsnmp.sql -- drop user

SQL> @?/rdbms/admin/catsnmp.sql -- create user

Wednesday, September 12, 2012

Oracle 11gR2, KEWBMBTA: Maintain BSLN Thresholds failed

If you tighten up the security in an Oracle 11gR2 database, you may be tempted to remove access to the DBMS_OBFUSCATION_TOOLKIT package from PUBLIC ("revoke execute on DBMS_OBFUSCATION_TOOLKIT from PUBLIC;").

This causes an issue, seen in 11gR2
The error was visible in the user traces:

*** 2012-09-11 20:00:43.692
KEWBMBTA: Maintain BSLN Thresholds failed, check for details.

This was tracked to an invalid package body DBSNMP.BSLN.

SQL> alter package DBSNMP.BSLN compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors

-------- -----------------------------------------------------------------
88/5 PL/SQL: Statement ignored
88/19 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
200/7 PL/SQL: Statement ignored
200/21 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
241/7 PL/SQL: Statement ignored
242/8 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
1332/7 PL/SQL: Statement ignored
1332/21 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared

Re-granting access to the DBMS_OBFUSCATION_TOOLKIT package to the PUBLIC user, fixes the issue again:

SQL> connect / as sysdba


Grant succeeded.

SQL> alter package DBSNMP.BSLN compile body;

Package body altered.

Wednesday, September 05, 2012

GRANT or REVOKE on User in DB Link Connection

If you GRANT additional privileges to an Oracle user who is used in a DB Link connection (from another database), then if the DB Link is already open and in use (the target DB has ACTIVE or INACTIVE sessions), you will need to close the DB link connection before you see the new privileges.
The same is true if you grant a new role to the user.

However, if you REVOKE privileges from an Oracle user who is used in a DB Link connection then these changes are seen immediately the next time the user is used to perform an operation across the DB link.

Monday, September 03, 2012

Oracle Support for GFS/2 Cluster Filesystem

When implementing Oracle into a RedHat Linux based landscape, you may be looking for a cluster filesystem other than Oracle's own ASM offering (or clusterware if you like old hats).
Oracle have updated their support status for GFS/2.

Checking out My Oracle Support doc id 279069.1 for 11g and RHEL 5, it says:
GFS2 is in technical preview in OEL5U2/RHEL5U2 and fully supported since OEL5U3/RHEL5U3.

You can now get that RHEL cluster up and running in production.