Skip to Content

Some tips around setting up security privileges in central repository using SQL

The tables involved

1. AL_OBJ_PERMS – NORMNAME, OBJECT_TYPE, GROUP_ID, PERMISSION – All the permissions are stored in this table
2. AL_GROUPS – GROUP_ID, NAME, DESCRIPTION – The groups defined in Management console.
3. AL_LANG – Contains Job, Workflow, Dataflow, Datastore, flatfile format, XML definition, Transforms, E xcel workbooks, etc.

Object Type Description
0 Job or Workflow (Type = 0 Job, 1 Workflow)
1 Dataflow (Type – 0 Normal, 1 ABAP)
3 ABAP transforms
4 Flatfile formats
5 Datastores
36 Nested schemas (Type 0 – DTD, 1 – XML Schema)
68 Excel workbooks
73 Custom/Quality transforms
74 subvar datastore

4. AL_SCHEMA – Tables, SAP Tree {Object Type = 7}
5. AL_FUNCINFO – Functions {Object Type = 13}
6. AL_PROJECTS – Projects Object Type = 11}

Step 1 : Find the group id that needs to be setup from AL_GROUPS.
Step 2 : Identify the permission to be provided for the group
Step 3 : Insert or update AL_OBJ_PERMS table accordingly

The below scripts sets up a GROUP_ID (4) with Full privilege for all the objects

#Delete all the existing privileges
DELETE FROM AL_OBJ_PERMS WHERE GROUP_ID = 4
#AL_LANG objects such as Job, Workflow, Dataflow, etc
INSERT INTO AL_OBJ_PERMS SELECT DISTINCT NORMNAME,OBJECT_TYPE,4,2 FROM AL_LANG
   WHERE NORMNAME NOT IN ( N’CD_DS_D0CAFAE2′ , N’XML_TEMPLATE_FORMAT’ , N’CD_JOB_D0CAFAE2′ , N’CD_DF_D0CAFAE2′ , N’DI_JOB_AL_MACH_INFO’ , N’DI_DF_AL_MACH_INFO’ , N’DI_FF_AL_MACH_INFO’ )
#AL_SCHEMA – Tables
INSERT INTO AL_OBJ_PERMS SELECT DISTINCT NORMNAME,7,4,2 FROM AL_SCHEMA
#AL_FUNCINFO – Functions
INSERT INTO AL_OBJ_PERMS SELECT DISTINCT NORMNAME,13,4,2 FROM AL_FUNCINFO WHERE FUNC_TYPE=’User_Script_Function’ OR OWNER <> N’acta_owner’
#Projects
INSERT INTO AL_OBJ_PERMS SELECT DISTINCT NAME,11,4,2 FROM AL_PROJECTS

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply