Limiting User Commands, Roles & Sessions!! One Session / User.

There are a number of options available for limiting user access to the database. You can grant/restrict access of a specific user based on IP address as discussed in the article Tricky Oracle Access by IP Address!! However sometimes we need to limit user access instead of restricting it completely. In this article we will discuss some great tips that will help you limit your user access to the database.

Oracle PRODUCT_PROFILE Table:

Oracle PRODUCT_PROFILE table can be used for restricting a user or group of users from running a specific command. You can use it to disable roles and report the current restrictions in the profile table. If PRODUCT_PROFILE does not exist, then you can create it by running the PUBBLD.SQL script.

Limiting User from running Specific Commands:

Consider a scenario where you own a large database of some hundred tables and you do not want Schema Owner to run the DELETE command on any of the tables. One solution is to use the GRANT / REVOKE commands. However the simple solution is to insert a row in the PRODUCT_PROFILE table. Below command will restrict user SCOTT from running DELETE command on any table.

INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value) VALUES('SQL*Plus','SCOTT','DELETE','DISABLED');

You can re-enable the command by simply deleting that row from the PRODUCT_PROFILE table.

DELETE FROM PRODUCT_PROFILE WHERE (product='SQLPlus' AND userid='SCOTT' AND attribute=' DELETE ' AND char_value=' DISABLED')

Note that you must be logged on as SYSTEM in order to delete any row from the PRODUCT_PROFILE table.

Limiting User Roles:

Sometimes it happens that we want to assign some specific role to user for the purpose of running application but we don't want user to have that role while running ad-hoc commands from SQLPlus. However you can achieve this goal by inserting below row into the PRODUCT_PROFILE table.

INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value) VALUES ('SQLPlus','SCOTT','ROLES','PAYROLL_ADMINISTRATOR');

You can re-enable the command by simply deleting that row from the PRODUCT_PROFILE table.

DELETE FROM PRODUCT_PROFILE WHERE (product='SQLPlus' AND userid='SCOTT' AND command='ROLES' AND char_value='PAYROLL_ADMINISTRATOR')

Assigning only one Oracle session to User:

Sometimes our requirement is to allow only one session to each user in the database. One cumbersome solution to this table is to run a batch job that analyzes V$SESSION table and kills sessions of those users who have more than 1 session. Another approach is to create a LOGIN table and insert a row into it whenever user logs in and remove the row with log out. However this approach has lots of problems especially when a row does not gets removed due to application crash. A simple solution to this problem is described below whereby any user with the ONE_SESSION profile can log on only once.

SQL> CREATE PROFILE ONE_SESSION LIMIT SESSIONS_PER_USER 1;
PROFILE CREATED.

SQL> ALTER USER SCOTT PROFILE ONE_SESSION;
USER ALTERED.

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
SYSTEM ALTERED. Read Again!!

 

 

Best regards,

Abdoul Rahaman

IT Consultant

Globe Marine Services Co

Jeddah

Mobile:966-507654847

 

Tricky Oracle Access by IP Address!!

Sometimes it happens that we want to grant access to our Oracle database filtered by IP address. A typical scenario can be where you want to allow a number of IP addresses to connect to your database or ban a number of IP addresses from log in to your database. In this article we will discuss the secrets of restricting access to your database through a list of IP addresses.

Oracle enables you to restrict database access based on IP address by modifying the SQLNET.ORA file. The SQLNET.ORA file is Oracle configuration file that typically resides $ORACLE_HOME/NETWORK/ADMIN directory on UNIX systems and ORACLE_HOME\network\admin directory on Windows systems. If SQLNET.ORA file is not found there then you will have to see if you have a TNS_ADMIN environment variable pointing to a different directory because SQLNET.ORA file can also be stored in the directory specified by the TNS_ADMIN environment variable.

Below steps can be used to authorize users from accessing Oracle database based on their IP Address.

1. Turn On Hostname/IP Checking for Listeners:

Open SQLNET.ORA file in a text editor and add below line

tcp.validnode_checking = yes

2. Supply lists of nodes to be Allowed/Denied:

Now you will have to use tcp.invited_nodes and tcp.excluded_nodes to supply a list of nodes that you want to allow or deny for getting access to your database. Make sure that you always enter localhost as an invited node. Also you must ensure that all node addresses come in one line and no wildcards are used. Remember the list of included nodes have higher precedence over the list of excluded nodes.

tcp.invited_nodes = (localhost,hostname1,hostname2)
tcp.excluded_nodes = (hostname1,hostname2)

One thing that you should keep in mind is that if you are only using the tcp.invited_nodes then only those specific nodes will be allowed to access your database and all other IP addresses will be denied from accessing your database.

Similarly if you are only using tcp.excluded_nodes then only those specific nodes will be denied from getting access your database and all other IP addresses will be allowed to access your database.

3. Restart Listeners:

Finally you will need to restart your listeners by running below commands.

$LSNRCTL STOP
$LSNRCTL START

SQLNET.ORA for Allowed IP Addresses:

Suppose you want to allow users from IP addresses 70.127.349.101 and 70.127.349.160 only to access your database. In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,70.127.349.101,70.127.349.160)

SQLNET.ORA for Banned IP Addresses:

Suppose you want to ban users from IP addresses 70.127.349.216, 192.176.420.301 and 70.127.349.191 from getting access to your database. In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.excluded_nodes = (70.127.349.216, 192.176.420.301, 70.127.349.191)

 

 

Best regards,

Abdoul Rahaman

IT Consultant

Globe Marine Services Co

Jeddah

Mobile:966-507654847

 

How to use index efficiently in SQL (Oracle)

SELECT /*+ INDEX(GMS_TSP_PROPOSE GMS_TSP_PROPOSE_IDX1)  */

        DISTINCT SUBSTR(TSP_BL_NUMBER,5) AS TSP_BL_NUMBER,

                        TSP_FINAL_DESTINATION,

                        TSP_REFNO,

                        TSP_BL_REFNO,

                        VESSEL_NAME,

                        TSP_VOYAGE,

                        TSP_PORT

        FROM GMS_TSP_PROPOSE, GMS_VESSEL_MASTER

 WHERE TSP_STATUS = 'P' AND

       TSP_Vessel = VESSEL_CODE

 

Note: Never use Table Alias in SQL with will not allow to use index.

Oracle User Expiry Lock

LOGON('SYSTEM','HTP'||'@'||:PARAMETER.TNSNAMES);

:GLOBAL.EXP :=    'N';

 

      DECLARE

              my_stmt  VARCHAR2(2000);

              my_stmt2 VARCHAR2(2000);           

              ABC VARCHAR(200);

              A VARCHAR(1);

      BEGIN

            my_stmt2 := 'ALTER USER '||CHR(34)||'HTP'||CHR(34)||'ACCOUNT UNLOCK';

 

            FORMS_DDL(MY_STMT2);

      END;

      LOGOUT;

      LOGON('HTP','HTP'||'@'||:PARAMETER.TNSNAMES);

 

IF to_date(SYSDATE,'DD/MM/RRRR') >= TO_DATE(:PARAMETER.EXP_DATE,'DD/MM/RRRR') THEN

      DECLARE

              my_stmt VARCHAR2(2000);

      BEGIN

            my_stmt := 'ALTER USER '||CHR(34)||'HTP'||CHR(34)||'ACCOUNT LOCK';

            FORMS_DDL(MY_STMT);

            message('Trail version Expired. Plz ');

            :GLOBAL.EXP :=    'Y';

      END;

ELSE

      LOGOUT;

LOGON('HTP','HTP'||'@'||:PARAMETER.TNSNAMES);

 

END IF;

/*

:global.location  :=    '';

:global.curr      :=    '';

:global.user_name :=    '';

:global.company_name    :=    '';

*/

Nokia Codes..

• *3370# This Nokia code activates Enhanced Full Rate Codec (EFR) - Your Nokia cell phone uses the best sound quality but talk time is reduced my approx. 5%
• #3370# Deactivate Enhanced Full Rate Codec (EFR) .
• *#4720# Activate Half Rate Codec - Your phone uses a lower quality sound but you should gain approx 30% more Talk Time.
• *#4720# With this Nokia code you can deactivate the Half Rate Codec.
• *#0000# Displays your phones software version, 1st Line : Software Version, 2nd Line : Software Release Date, 3rd Line : Compression Type .
• *#9999# Phones software version if *#0000# does not work.
• *#06# For checking the International Mobile Equipment Identity (IMEI Number) .
• #pw+1234567890+1# Provider Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols).
• #pw+1234567890+2# Network Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols) .
• #pw+1234567890+3# Country Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols).
• #pw+1234567890+4# SIM Card Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols) .
• *#147# This lets you know who called you last (Only vodofone).
• *#1471# Last call (Only vodofone) .
• *#21# This phone code allows you to check the number that "All Calls" are diverted to.
• *#2640# Displays phone security code in use .
• *#30# Lets you see the private number.
• *#43# Allows you to check the "Call Waiting" status of your cell phone.
• *#61# Allows you to check the number that "On No Reply" calls are diverted to .
• *#62# Allows you to check the number that "Divert If Unreachable (no service)" calls are diverted to .
• *#67# Allows you to check the number that "On Busy Calls" are diverted to
• *#67705646# Phone code that removes operator logo on 3310 & 3330
• *#73# Reset phone timers and game scores.
• *#746025625# Displays the SIM Clock status, if your phone supports this power saving feature "SIM Clock Stop Allowed", it means you will get the best standby time possible .
• *#7760# Manufactures code.
• *#7780# Restore factory settings.
• *#8110# Software version for the nokia 8110.
• *#92702689# Displays - 1.Serial Number, 2.Date Made, 3.Purchase Date, 4.Date of last repair (0000 for no repairs), 5.Transfer User Data. To exit this mode you need to switch your phone off then on again.
• *#94870345123456789# Deactivate the PWM-Mem.
• **21*number# Turn on "All Calls" diverting to the phone number entered.
• **61*number# Turn on "No Reply" diverting to the phone number entered .
• **67*number# Turn on "On Busy" diverting to the phone number entered .
• 12345 This is the default security code .
• press and hold # Lets you switch between lines.

 

Cold backup in Oracle
Making a Cold Backup
A cold backup requires the database to be shut down. That means, as opposed to a hot backup, users cannot do anything on the database. So, first, SHUTDOWN the database (not a shutdown abort). Then copy all necessary files to a safe place. Those files are: The datafiles:
select name from v$datafile
The control files:
select name from v$controlfile
The online redo logs: Note, the online redo logs need not be backed up.
select member from v$logfile
The parameter file init.ora or spfile (The parameter file cannot be found using dynamic performance views) and the password file (if used). Also, some network configuration files such as the listener.ora, tnsnames.ora and sqlnet.ora might be backed up. Some might also want to backup the Oracle Software along with the inventory, the oratab file and startup scripts. Here is a shell script that shuts down the database, copies the files of the database to a safe place and then starts up the database again. After we have copied these files to a safe place, we can safely delete the archived redo log files.
Simulating Media Failure
create some tables, and insert something into the tables. And then delete the datafiles
create table after_backup (a number, b varchar2(40));
insert into after_backup values (1, 'before commit');
insert into after_backup values (2, 'also before commit');
commit;
insert into after_backup values (3, 'after commit');
Make sure to not commit this session until you delete the datafiles. Open another session instead and create a user or something.
create user user_after_backup identified by pw;
grant dba to user_after_backup;
Now, crash the instance and delete the database. Open the init.ora file at the backuped place and modify its control_file initializtion parameter to point to the backed up control files. Then startup mount pfile=c:\oracle\ora81\admin\backup\initadpdb.ora the database. Note, I am using the backed up initadpdb.ora file. After you've mounted the database, rename the datafile (so that they point to the new files):
alter database rename file 'C:\ORACLE\ORA81\ADMIN\ADPDB\SYSTEM01.DBF'
to 'C:\ORACLE\ORA81\ADMIN\backup\SYSTEM01.DBF';
Note: Online redo log files should never be backed up

Data guard

When you configure your database for datagaurd the start the database only with that pfile which has the configuration. Then you can work smooth in the database or else it will hangs unnecessary.

How to convert Column to Rows using SYS_CONNECT_BY_PATH

SELECT DEL_REFNO ID,MAX(LTRIM(SYS_CONNECT_BY_PATH(DEL_DELIVERY_NO,','),',')) RES FROM (SELECT DEL_REFNO,DEL_DELIVERY_NO, ROW_NUMBER() OVER(PARTITION BY DEL_REFNO ORDER BY DEL_DELIVERY_NO) RN FROM HTP_DELIVERY_DETAIL WHERE DEL_REFNO = :Z_SHIPMENT_NO) START WITH RN = 1 CONNECT BY PRIOR RN = RN -1 AND PRIOR DEL_REFNO = DEL_REFNO GROUP BY DEL_REFNO;