Creating Noetix Query Users using Scripts
The scripts-based method of creating Noetix query users allows you to add multiple Noetix query users at the same time. You can add existing database users and Oracle E-Business Suite users and responsibilities as Noetix query users of Database User (Type U), Oracle E-Business Suite Authenticated User (Type A), and Oracle
E-Business Suite Authenticated Responsibility (Type R) types.
Using scripts, you can also create database users in the Oracle database and later add the users as Noetix query users of Database User (Type U) type.
Important: The scripts-based method does not allow you to set a translation language, assign security rules for data in views for Oracle General Ledger, and customize security for data in views for various Oracle E-Business Suite modules. To work with these features, use the Noetix Query User Maintenance tab of the Security Manager dialog box.
Add Existing Users as Noetix Query Users
The i create sm users.sql script available in NoetixViews allows you to create multiple Noetix query users based on existing database users and Oracle E-Business Suite users and responsibilities. These query users can be categorized into Database User (Type U), Oracle E-Business Suite Authenticated User (Type A), and Oracle E-Business Suite Authenticated Responsibility (Type R) types. The i create sm users.sql script cannot be used to create Noetix query users of the Oracle E-Business Suite Authenticated User (Type A) type who have no responsibility assigned to them in Oracle E-Business Suite.
Some features of the i create sm users.sql script include:
Support for adding multiple users by using wild card characters.
Support for assigning multiple roles by using wild card characters.
Support for assigning up to 30 role labels or role names to the selected users.
Note: For users of the Database User (Type U) type, only role names can be assigned.
Support for adding one or more users of the Oracle E-Business Suite Authenticated User (Type A) type corresponding to a specific Oracle E-Business Suite responsibility.
Support for selecting only active users. This feature is available for only users of the Oracle E-Business Suite Authenticated User (Type A) and Oracle E-Business Suite Authenticated Responsibility (Type R) types.
Support for automatically detecting only those roles based on the organizational unit settings available in Oracle E-Business Suite for a selected user. This feature is available for only users of the Oracle E-Business Suite Authenticated User (Type A) and Oracle E-Business Suite Authenticated Responsibility (Type R) types.
The following procedures explains how to use the i create sm users.sql script to create multiple Noetix query users based on existing users.
To add an Oracle E-Business Suite Authenticated User (Type A) type user as a Noetix query user
Depending on the operating system, do one of the following to start an SQL*Plus session:
On a Windows-based computer:
Log on to the NoetixViews Administrator.
On the Tools menu, click Support > SQL*Plus Prompt from the NoetixViews Administrator main window. The window for the SQL*Plus session will appear.
On a UNIX-based computer:
Change the present working directory to the server directory that contains the NoetixViews generation scripts.
To connect to the database as the Noetix System Administration User and to start an SQL*Plus session, run the following command at the command prompt.
sqlplus <username>/<password>@<dbconnectstring>
In the SQL*Plus session, run the i create sm users.sql script.
Answer the following prompts. To accept the default value, press ENTER; otherwise, type a value for each prompt, and press ENTER.
Please enter the type of query users you would like to create.
Valid values are A(EBS Authenticated User), R (EBS Authenticated Responsibility), or U (Database User).
default [A]:For an EBS Authenticated User (Type A), you can enter an EBS responsibility here. If you enter a responsibility name, the list of users will be derived from the users assigned to the responsibility specified. To derive the user list from the specified responsibility, enter its name here. You may use wildcard characters to specify multiple responsibilities.
Note: If you specify "%" here, then the responsibility user list will not be used.
*** Use the wildcard characters " " or "%" to specify multiple responsibilities. ***
default [%]:Important: The i create sm users.sql script cannot be used to create Noetix query users of the Oracle E-Business Suite Authenticated User (Type A) type who have no responsibility assigned to them in Oracle E-Business Suite.
Enter the name of the EBS Authenticated User (Type A) you would like to create.***
Use the wildcard characters " " or "%" to create multiple users. ***
default [%]:Important: Ensure that the user name is provided exactly as it appears in Oracle E-Business Suite.
Include only active users (those that have not been end dated.) Valid values are "Y" to include only activeusers or "N" to include all defined users.
default [Y]:For type "A" users, the default role assignment is handled automatically based on the organizational unit access that is detected for a particular user. In this mode, you are prompted to enter a set of role labels(templates).Based on your role label selections, a list of roles is generated for each user account as determined by each user's detected organizational unit access.
The other mode of operation is to explicitly grant a list roles to the specified user accounts, regardless of their organizational unit access.
Valid values are "Y" to specify roles automatically based on role label and organizational unit settings or "N" to specify an explicit list of roles.
default [Y]:What default mode of access for row-level security would you like?
For type "A" and "R" users, specify "ALL", "EBS", or "NONE".
For type "U" users, specify "ALL" or "NONE".
If the row-level security is not available, then the "EBS" access mode will be automatically changed to "NONE".
default [EBS]:What default mode of access for GL security would you like?
For type "A" users, specify "ALL", "EBS", “ROLLUP”, or "NONE".
For type "R" users, specify "ALL", "EBS", or "NONE".
For type "U" users, specify "ALL" or "NONE".
If the GL security is not available, the value specified is populated in the security manager, but is not used.
default [EBS]:Create query tool optimizing views?
This parameter is ignored if a user type other than "U" is specified.
default [N]:Do not type Y if the Noetix query user owns Oracle E-Business Suite tables. If you do so, you may encounter problems while assigning Noetix roles to the user. Also, do not type Y if the Noetix query users use any reporting tool, such as Oracle Discoverer, that accesses its own meta-layer because they do not benefit from this option.
Create query user synonyms for views?
This parameter is ignored if a user type other than "U" isspecified. default [N]:
Note: Do not create synonyms if the Noetix query users own Oracle E-Business Suite tables. If you do so, you may encounter problems while assigning Noetix roles to the users.
Press ENTER, when prompted, to see a list of role names or role labels. The list of available role names or role labels is displayed. This will allow you to assign a role or a set of roles to the specified users. Every time you run the script, the script provides you an option to assign a maximum of 30 role labels or role names for the selected users.
Note: By default, you will be prompted to view a list of role labels unless you have typed the value N in step e. If the list is long, you will be prompted to press ENTER more than once to see all the role names or labels.
To assign a single role name or role label, type the role name or role label, and press ENTER. To skip a role name or role label prompt, press ENTER.
When you assign a role label corresponding to an Oracle module to an Oracle E-Business Suite Authenticated User (Type A) type user for whom EBS access mode is specified, the roles corresponding to the role label are automatically assigned based on the user’s organizational unit access configured in Oracle E-Business Suite. If you assign role names instead of role labels for these user types, the roles are first verified against the corresponding organizational units and only those role names for which the user has organizational unit access are included.
Note: Use the percent symbol to select all Noetix role names or role labels that start with a prefix. For example, to select all the role names that start with GL, use GL% and to select all the role labels that start with HR, use HR%.
Verify the information that you entered.
Press ENTER, when prompted, to see the list of Noetix query users selected.
Press ENTER, when prompted, to see the list of role labels or role names assigned to the specified users. After this step, the i create sm users.sql script will be automatically generated that contains all the settings defined for the users or responsibilities.
Verify that all the information that you entered is correct. If the information is incorrect and you want to re-run the script or you want to edit the t create sm users.sql script and run it manually, exit the SQL*Plus session (usually by pressing CTRL+C). If the information is correct and you want to proceed, press ENTER.
Repeat steps In the SQL*Plus session, run the i create sm users.sql script. through Verify that all the information that you entered is correct. If the information is incorrect and you want to re-run the script or you want to edit the t create sm users.sql script and run it manually, exit the SQL*Plus session (usually by pressing CTRL+C). If the information is correct and you want to proceed, press ENTER. if you want to add more Oracle E-Business Suite Authenticated User (Type A) users as Noetix query users.
To add an Oracle E-Business Suite Authenticated Responsibility (Type R) type user as a Noetix query user
Depending on the operating system, do one of the following to start an SQL*Plus session:
On a Windows-based computer:
Log on to the NoetixViews Administrator.
On the Tools menu, click Support > SQL*Plus Prompt from the NoetixViews Administrator main window. The window for the SQL*Plus session will appear.
On a UNIX-based computer:
Change the present working directory to the server directory that contains the NoetixViews generation scripts.
To connect to the database as the Noetix System Administration User and to start an SQL*Plus session, run the following command at the command prompt.
sqlplus <username>/<password>@<dbconnectstring>
In the SQL*Plus session, run the i create sm users.sql script.
Answer the following prompts. To accept the default value, press ENTER; otherwise, type a value for each prompt, and press ENTER.
Please enter the type of query users you would like to create.Valid values are A (EBS Authenticated User), R (EBS Authenticated Responsibility), or U (Database User).default [A]:
Type R to specify the type of user as Oracle E-Business Suite responsibility.
Enter the name of the EBS Authenticated Responsibility (Type R) you would like to create.
*** Use the wildcard characters " " or "%" to create multiple users. ***
default [%]:Note: Ensure that the user name is provided exactly as it appears in Oracle E-Business Suite.
Include only active responsibilities (those that have not been end dated.)
Valid values are "Y" to include only active responsibilities or "N" to include all defined responsibilities.
default [Y]:For type "R" users, the default role assignment is handled automatically based on the organizational unit access that is detected for a particular user. In this mode, you are prompted to enter a set of role labels (templates). Based on your role label selections, a list of roles is generated for each user account as determined by each user's detected organizational unit access.
The other mode of operation is to explicitly grant a list roles to the specified user accounts, regardless of their organizational unit access.
Valid values are "Y" to specify roles automatically based on role label and organizational unit settings or "N" to specify an explicit list of roles.
default [Y]:What default mode of access for row-level security would you like?
For type "A" and "R" users, specify "ALL", "EBS", or "NONE".
For type "U" users, specify "ALL" or "NONE".
If the row-level security is not available, then the "EBS" access mode will be automatically changed to "NONE".
default [EBS]:What default mode of access for GL security would you like?
For type "A" and "R" users, specify "ALL", "EBS", or "NONE".
For type "U" users, specify "ALL" or "NONE".
If the GL security is not available, the value specified is populated in the security manager, but is not used.
default [EBS]:Create query tool optimizing views?
This parameter is ignored if a user type other than "U" is specified.
default [N]:Note: Do not type Y if the Noetix query user owns Oracle E-Business Suite tables. If you do so, you may encounter problems while assigning Noetix roles to the user. Also, do not type Y if the Noetix query users use any reporting tool, such as Oracle Discoverer, that accesses its own meta-layer because they do not benefit from this option.
Create query user
synonyms for views?
This parameter is ignored if a user type other than "U" is specified.
default [N]:Note: Do not create synonyms if the Noetix query users own Oracle E-Business Suite tables. If you do so, you may encounter problems while assigning Noetix roles to the users.
Press ENTER, when prompted, to see a list of role names or role labels. The list of available role names or role labels is displayed. This will allow you to assign a role or a set of roles to the specified users. Every time you run the script, the script provides you an option to assign a maximum of 30 role labels or role names for the selected users.
Note: By default, you will be prompted to view a list of role labels unless you have typed the value N in step d. If the list is long, you will be prompted to press ENTER more than once to see all the role names or labels.
To assign a single role name or role label, type the role name or role label, and press ENTER. To skip a role name or role label prompt, press ENTER.
When you assign a role label corresponding to an Oracle module to an Oracle E-Business Suite Authenticated User (Type A) or Oracle E-Business Suite Authenticated Responsibility (Type R) type user for whom EBS access mode is specified, the roles corresponding to the role label are automatically assigned based on the user’s organizational unit access configured in Oracle E-Business Suite. If you assign role names instead of role labels for these user types, the roles are first verified against the corresponding organizational units and only those role names for which the user has organizational unit access are included.
Note: Use the percent character to select all Noetix role names or role labels that start with a prefix. For example, to select all the role names that start with GL, use GL% and to select all the role labels that start with HR, use HR%.
Verify the information that you entered.
Press ENTER, when prompted, to see the list of Noetix query users selected.
Press ENTER, when prompted, to see the list of role labels or role names assigned to the specified users. After this step, the t create sm users.sql script will be automatically generated that contains all the settings defined for the users or responsibilities.
Verify that all the information that you entered is correct. If the information is incorrect and you want to re-run the script or you want to edit the t create sm users.sql script and run it manually, exit the SQL*Plus session (usually by pressing CTRL+C). If the information is correct and you want to proceed, press ENTER.
Repeat steps In the SQL*Plus session, run the i create sm users.sql script. through Verify that all the information that you entered is correct. If the information is incorrect and you want to re-run the script or you want to edit the t create sm users.sql script and run it manually, exit the SQL*Plus session (usually by pressing CTRL+C). If the information is correct and you want to proceed, press ENTER. if you want to add more Oracle E-Business Suite Authenticated Responsibility (Type R) type users as Noetix query users.
To add a Database User (Type U) user as a Noetix query user
Depending on the operating system, do one of the following to start an SQL*Plus session:
On a Windows-based computer:
Log on to the NoetixViews Administrator.
On the Tools menu, click Support > SQL*Plus Prompt from the NoetixViews Administrator main window. The window for the SQL*Plus session will appear.
On a UNIX-based computer:
Change the present working directory to the server directory that contains the NoetixViews generation scripts.
To connect to the database as the Noetix System Administration User and to start an SQL*Plus session, run the following command at the command prompt.
sqlplus <username>/<password>@<dbconnectstring>
In the SQL*Plus session, run the i create sm users.sql script.
Answer the following prompts. To accept the default value, press ENTER; otherwise, type a value for each prompt, and press ENTER.
Please enter the type of query users you would like to create.
Valid values are A (EBS Authenticated User), R (EBS Authenticated Responsibility), or U (Database User).
default [A]:Type U to specify the type of user as a database user.
Please enter the name of the database user you would like to create.
*** Use the wildcard characters " " or "%" to create multiple users. ***
default [%]:If the names of database users selected for adding as Noetix query users contain lower-case or mixed-case letters and special characters that are not supported by Oracle Database, the names should be provided within double quotation marks when the corresponding users log on to Oracle Discoverer Desktop.
What default mode of access for row-level security would you like?
For type "A" and "R" users, specify "ALL", "EBS", or "NONE".
For type "U" users, specify "ALL" or "NONE".
If the row-level security is not available, then the "EBS" access mode will be automatically changed to "NONE".
default [NONE]:What default mode of access for GL security would you like?
For type "A" and "R" users, specify "ALL", "EBS", or "NONE".
For type "U" users, specify "ALL" or "NONE".
If the GL security is not available, the value specified is populated in the security manager, but is not used.
default [NONE]:Create query tool optimizing views?
This parameter is ignored if a user type other than "U" is specified.
default [N]:Note: Do not type Y if the Noetix query user owns Oracle E-Business Suite tables. If you do so, you may encounter problems while assigning Noetix roles to the user. Also, do not type Y if the Noetix query users use any reporting tool, such as Oracle Discoverer, that accesses its own meta-layer because they do not benefit from this option.
Create query user synonyms for views?
This parameter is ignored if a user type other than "U" is specified.
default [Y]:Note: Do not create synonyms if the Noetix query users own Oracle E-Business Suite tables. If you do so, you may encounter problems while assigning Noetix roles to the users.
Press ENTER, when prompted, to see a list of role names or role labels. The list of available role names or role labels is displayed. This will allow you to assign a role or a set of roles to the specified users. Every time you run the script, the script provides you an option to assign a maximum of 30 role labels or role names for the selected users.
Note: If the list is long, you will be prompted to press ENTER more than once to see all the role names or labels.
To assign a single role name or role label, type the role name or role label, and press ENTER. To skip a role name or role label prompt, press ENTER.
Note: Use the percent character to select all Noetix role names or role labels that start with a prefix. For example, to select all the role names that start with GL, use GL% and to select all the role labels that start with HR, use HR%.
Verify the information that you entered.
Press ENTER, when prompted, to see the list of Noetix query users selected.
Press ENTER, when prompted, to see the list of role labels or role names assigned to the specified users. After this step, the t create sm users.sql script will be automatically generated that contains all the settings defined for the users or responsibilities.
Verify that all the information that you entered is correct. If the information is incorrect and you want to re-run the script or you want to edit the t create sm users.sql script and run it manually, exit the SQL*Plus session (usually by pressing CTRL+C). If the information is correct and you want to proceed, press ENTER.
Repeat steps In the SQL*Plus session, run the i create sm users.sql script. through Verify that all the information that you entered is correct. If the information is incorrect and you want to re-run the script or you want to edit the t create sm users.sql script and run it manually, exit the SQL*Plus session (usually by pressing CTRL+C). If the information is correct and you want to proceed, press ENTER. if you want to add more Database User (Type U) users as Noetix query users.
Create a Database User
This procedure allows you to create a database user in the Oracle database that you can add later as a Noetix query user of Database User (Type U) type.
To create database users
Depending on the operating system that you use, do one of the following to start an SQL*Plus session:
On a Windows-based computer, do the following:
Log on to the NoetixViews Administrator.
On the Tools menu, click Support > SQL*Plus Prompt from the NoetixViews Administrator main window. The window for the SQL*Plus session will appear.
On a UNIX-based computer, do the following:
Change the present working directory to the server directory that contains the NoetixViews generation scripts.
To connect to the database as the Noetix System Administration User and to start an SQL*Plus session, run the following command at the command prompt.
sqlplus <username>/<password>@<dbconnectstring>
Connect to the database as a user with DBA privileges.
Note: To create the database users, you need to use a user with DBA privileges.
On the SQL*Plus session, run the instuser.sql script.
Answer the following prompts. To accept the default value, press ENTER; otherwise, type a value for each item when prompted, and press ENTER.
Please enter the Oracle User Name for the NoetixViews Query User
Important: The user name must start with a letter; must not contain double quotation marks (") or single quotation marks ('); cannot exceed 30 bytes. Also, use only uppercase letters (A-Z) for the user name. Diacritical marks and characters for Western European languages can also be typed.
Please enter the Oracle User Password for the NoetixViews Query User
Please enter the Database Connect string
Please enter the Default Tablespace to create Noetix objects in
Please enter the Temporary Tablespace to assign the Noetix query user
Verify the information that you have entered. If the information is incorrect, exit the SQL*Plus session (usually by pressing CTRL+C), and start over. If the information is correct, press ENTER to continue.
Repeat these steps for each user that you want to create.