In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Thursday, May 27, 2010

Operating System Authentication to Communicate with the DATABASE.

To enable operating system authentication of an administrative user:
with and without OSOPER, OSDBA Groups granted to the operating system user.

1.Create an operating system account for the user.
2.Add the account to the OSDBA or OSOPER operating system defined groups

CREATE A USER 
[root@rac3 ~]# useradd -G dba,oper vivek
[root@rac3 ~]# passwd vivek
Changing password for user vivek.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

[root@rac3 ~]# su - vivek
[vivek@rac3 ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[vivek@rac3 ~]$ export ORACLE_PATH=$PATH:$ORACLE_HOME/bin
[vivek@rac3 ~]$ export ORACLE_SID=rev
[vivek@rac3 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/bin
[vivek@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:34:09 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

we can create a database user to allow an OS authenticated connection.
SQL> show parameter os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

SQL> create user ops$vivek identified externally;

User created.

SQL> grant connect,resource to ops$vivek;

Grant succeeded.


[vivek@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:35:00 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select username from user_users;

USERNAME
------------------------------
OPS$VIVEK

SQL> show user;
USER is "OPS$VIVEK"
SQL>

workshop 2:
 without adding osoper, osdba groups to the user.
here i faced some errors, i have posted it with errors and solutions.
[oracle@rac3 ~]$ su -
Password:
[root@rac3 ~]# useradd jay
[root@rac3 ~]# passwd jay
Changing password for user jay.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@rac3 ~]#

[root@rac3 ~]# su - jay
[jay@rac3 ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[jay@rac3 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[jay@rac3 ~]$ export ORACLE_SID=rev
[jay@rac3 ~]$

[jay@rac3 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/bin
[jay@rac3 bin]$ ./sqlplus /
./sqlplus: error while loading shared libraries: libsqlplus.so:
cannot open shared object file: No such file or directory

SQL> show parameter os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
SQL> create user ops$jay identified externally;

User created.

SQL> grant connect,resource to ops$jay;

Grant succeeded.

[jay@rac3 bin]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[jay@rac3 bin]$ export PATH=$PATH:$ORACLE_HOME/bin
[jay@rac3 bin]$ ./sqlplus /

./sqlplus: error while loading shared libraries: libsqlplus.so:
cannot open shared object file: No such file or directory

---to solve this problem libsqlplus.so relink oracle as follows

[root@rac3 ~]# chmod -R 755 /u01/app/oracle/product/10.2.0/db_1/

[jay@rac3 bin]$ $ORACLE_HOME/bin/relink all


[jay@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:15:38 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
Linux Error: 13: Permission denied


Enter user-name:

--to rectify this error , do the following steps
[jay@rac3 bin]$ ls -l $ORACLE_HOME/bin/oracle
-rwxr-xr-x 1 oracle oinstall 93300109 May 27 14:05 /u01/app/oracle/product/10.2.0/db_1/bin/oracle


[root@rac3 ~]# chmod -R 6751 /u01/app/oracle/product/10.2.0/db_1/

[jay@rac3 bin]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 93300109 May 27 14:05 /u01/app/oracle/product/10.2.0/db_1/bin/oracle
[jay@rac3 bin]$

[jay@rac3 bin]$ ./sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 27 14:19:09 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show user;
USER is "OPS$JAY"
SQL> select username from user_users;

USERNAME
------------------------------
OPS$JAY


Note:
1.Operating system authentication takes precedence over password file authentication.
If you meet the requirements for operating system authentication, then even if you use a password file, you will be authenticated by operating system authentication.
2.If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
3.If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.

regards,
Rajeshkumar Govindarajan.

reference:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#i1006534

No comments:

free counters
 
Share/Bookmark