This post will guide to you what to do after you have forgotten you linux root password and help you how to login as root under in Red Hat Enterprise Linux 4.
The idea is you can log in using single-user mode and create a new root password.
Step 01: Using your machine restart button manually reboot your computer.
Step 02: If you use the default boot loader, GRUB, you can enter single user mode. To do so, at the boot loader menu, use the arrow keys to highlight the installation you want to edit and type [A] to enter into append mode.
Step 03: You are presented with a prompt that looks similar to the following:
grub append> ro root=LABEL=/
Step 04: Press the Spacebar once to add a blank space, then add the word single to tell GRUB to boot into single-user Linux mode. The result should look like the following:
ro root=LABEL=/ single
Step 05: Press [Enter] and GRUB will boot single-user Linux mode. After it finishes loading, you will be presented with a shell prompt similar to the following:
sh-2.05b#
Step 06: You can now change the root password by typing
passwd root
You will be asked to re-type the password for verification. Once you are finished, the password will be changed. You can then reboot by typing reboot at the prompt; then you can log in to root as you normally would.
Showing posts with label Security. Show all posts
Showing posts with label Security. Show all posts
Saturday, July 17, 2010
Difference between connecting to database as normal and sysdba/sysoper
As it is discussed in Database Administrator Authentication, it is said whenever you connect to database as sysdba privilege, you are connecting to SYS default schema. And whenever you are connecting to database as sysoper privilege, you are connecting to PUBLIC default schema.
Note that, sysdba and sysoper are special privilege and therefore certain types of operations can be performed whenever you assign sysdba/sysoper privilege to a user. A complete lists of operations that can be performed by the user who has sysdba/sysoper privilege are listed in the post SYSDBA and SYSOPER authorized operations. A very important thing to remember that, whenever you only assign these two privileges to a user and you don't assign any more privilege then user will not be able to do any schema/table level modification unless you specifically login as sysdba privilege.
With examples I will try to make you more clear between the differences.
sysdba privilege is not enough for a user to login to database unless he login as sysdba privilege
1. Login as sysdba.
E:\>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jul 17 15:12:05 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2. Create a user named test_sysdba with password test_sysdba.
SQL> create user test_sysdba identified by test_sysdba;
User created.
3. Change default tablespace to users of test_sysdba user.
SQL> alter user test_sysdba default tablespace users;
User altered.
4. Grant sysdba privilege to user test_sysdba.
SQL> grant sysdba to test_sysdba;
Grant succeeded.
As soon as we assign sysbda privilege under password file there will be an entry. By querying v$pwfile_users view we can see an entry.
SQL> conn test_sysdba/test_sysdba
ERROR:
ORA-01045: user TEST_SYSDBA lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
As we have not specify "as sysdba" while login so it does not permit test_sysdba to login to database even he has sysdba privilege.
If you don't login as SYSDBA privilege it will act as a normal user
1. Log in as sysdba
SQL> conn / as sysdba
Connected.
2. Grant create session privilege to test_sysdba.
SQL> grant create session to test_sysdba;
Grant succeeded.
3. Now try to login as test_sysdba privilege and try to create table.
SQL> conn test_sysdba/test_sysdba
Connected.
SQL> create table test(col1 number);
create table test(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
The "create table" statement fails as while login we did not specify "sysdba" privilege, and so user has connected to database as normal user.
4. Now connect as sysdba privilege and grant dba to test_sysdba user.
SQL> conn / as sysdba
Connected.
SQL> grant dba to test_sysdba;
Grant succeeded.
5. Connection will be successful as it has dba role but still it is normal test_sysdba user.
SQL> conn test_sysdba/test_sysdba
Connected.
SQL> create table test_sysdba_table1(col1 number);
Table created.
SQL> show user
USER is "TEST_SYSDBA"
Note that the user is TEST_SYSDBA.
Whenever we specify "sysdba privilege" while connecting the schema became SYS
1. Connect to database with test_sysdba user and using sysdba privilege.
SQL> conn test_sysdba/test_sysdba as sysdba
Connected.
SQL> show user
USER is "SYS"
Note that, now user became SYS as we specified "as sysdba" while login.
2. As it is SYS user and any table we create will go under SYS default "SYSTEM" tablespace whereas whenever we connect as normal test_sysdba user it would go under that user default schema.
SQL> create table test_sysdba_table2(col1 number);
Table created.
SQL> grant sysoper to test_sysdba;
Grant succeeded.
SQL> conn test_sysdba/test_sysdba as sysoper;
Connected.
SQL> show user
USER is "PUBLIC"
SQL> create table test_sysdba_table3(col1 number);
create table test_sysdba_table3(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
So user connecting as sysoper privilege will not be able to create table as PUBLIC user is not permitted so.
Note that, sysdba and sysoper are special privilege and therefore certain types of operations can be performed whenever you assign sysdba/sysoper privilege to a user. A complete lists of operations that can be performed by the user who has sysdba/sysoper privilege are listed in the post SYSDBA and SYSOPER authorized operations. A very important thing to remember that, whenever you only assign these two privileges to a user and you don't assign any more privilege then user will not be able to do any schema/table level modification unless you specifically login as sysdba privilege.
With examples I will try to make you more clear between the differences.
sysdba privilege is not enough for a user to login to database unless he login as sysdba privilege
1. Login as sysdba.
E:\>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jul 17 15:12:05 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2. Create a user named test_sysdba with password test_sysdba.
SQL> create user test_sysdba identified by test_sysdba;
User created.
3. Change default tablespace to users of test_sysdba user.
SQL> alter user test_sysdba default tablespace users;
User altered.
4. Grant sysdba privilege to user test_sysdba.
SQL> grant sysdba to test_sysdba;
Grant succeeded.
As soon as we assign sysbda privilege under password file there will be an entry. By querying v$pwfile_users view we can see an entry.
SQL> select * from v$pwfile_users ;5. Try to connect to database as test_sysdba
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
TEST_SYSDBA TRUE FALSE FALSE
SQL> conn test_sysdba/test_sysdba
ERROR:
ORA-01045: user TEST_SYSDBA lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
As we have not specify "as sysdba" while login so it does not permit test_sysdba to login to database even he has sysdba privilege.
If you don't login as SYSDBA privilege it will act as a normal user
1. Log in as sysdba
SQL> conn / as sysdba
Connected.
2. Grant create session privilege to test_sysdba.
SQL> grant create session to test_sysdba;
Grant succeeded.
3. Now try to login as test_sysdba privilege and try to create table.
SQL> conn test_sysdba/test_sysdba
Connected.
SQL> create table test(col1 number);
create table test(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
The "create table" statement fails as while login we did not specify "sysdba" privilege, and so user has connected to database as normal user.
4. Now connect as sysdba privilege and grant dba to test_sysdba user.
SQL> conn / as sysdba
Connected.
SQL> grant dba to test_sysdba;
Grant succeeded.
5. Connection will be successful as it has dba role but still it is normal test_sysdba user.
SQL> conn test_sysdba/test_sysdba
Connected.
SQL> create table test_sysdba_table1(col1 number);
Table created.
SQL> show user
USER is "TEST_SYSDBA"
Note that the user is TEST_SYSDBA.
Whenever we specify "sysdba privilege" while connecting the schema became SYS
1. Connect to database with test_sysdba user and using sysdba privilege.
SQL> conn test_sysdba/test_sysdba as sysdba
Connected.
SQL> show user
USER is "SYS"
Note that, now user became SYS as we specified "as sysdba" while login.
2. As it is SYS user and any table we create will go under SYS default "SYSTEM" tablespace whereas whenever we connect as normal test_sysdba user it would go under that user default schema.
SQL> create table test_sysdba_table2(col1 number);
Table created.
SQL> set lines 200Whenever we connect through sysoper privilege the schema is PUBLIC
SQL> select owner, table_name, tablespace_name from dba_tables where table_name like 'TEST_SYSDBA_TABLE%';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST_SYSDBA TEST_SYSDBA_TABLE1 USERS
SYS TEST_SYSDBA_TABLE2 SYSTEM
SQL> grant sysoper to test_sysdba;
Grant succeeded.
SQL> conn test_sysdba/test_sysdba as sysoper;
Connected.
SQL> show user
USER is "PUBLIC"
SQL> create table test_sysdba_table3(col1 number);
create table test_sysdba_table3(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
So user connecting as sysoper privilege will not be able to create table as PUBLIC user is not permitted so.
Monday, March 29, 2010
TNS-01169: The listener has not recognized the password
Problem Description
oracle:/databridge/db DBGEP> $ lsnrctl status LISTENER
LSNRCTL for HPUX: Version 9.2.0.8.0 - Production on 28-MAR-2010 21:19:33
Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ravel.gov)(PORT=1521)))
TNS-01169: The listener has not recognized the password
Cause of the Problem
The TNS-01169 error occurred because security feature is enabled for listener i.e password is set in listener.ora file but lsnrctl command is issued without proper authentication.
Solution of the Problem
After you enable listener password in Oracle 9i, you will now require a password whenever you wish to stop the listener or any other listener actions. However in Oracle database 10g, if you are not logged into the operating system with a privileged account i.e OS user is a member of dba group, you will have to enter a password while doing any operation to listener.
As we see from the message our listener version is 9.2.0.8.0 so follow the following steps to solve the problem.
A) If you remember listener password:
Step 01: Invoke lsnrctl command.
$lsnrctl
Step 02: Set the current_listener to the appropriate listener to which you want to do operation.
LSNRCTL> set current_listener {listener_name_here}
Note that you have to issue "set current_listener {listener_name_here}" if your listener name is not default name that is LISTENER.
Step03: Set password to the password that you previously set for listener.
LSNRCTL> set password {password_here}
or simply you can do it interactively as,
LSNRCTL> set password
Password: {enter_your_password_here}
Step 04: Issue your appropriate command.
LSNRCTL> status
or,
LSNRCTL> stop
B) If you forget listener password:
If password is set in plain text within listener.ora file
If you forget listener password then look for listener.ora and see if password is set in plain text. If it is in plain text then you can retrieve it easily and use that in "set password" command and then do operation as you wanted.
If password is encrypted within listener.ora file
Step 01:
If password is set in encrypted format, check the listener process by issuing,
$ps -ef |grep tns
and note down the process id.
Step 02:
Kill the listener process by,
$kill -9 {process_id}
Step 03:
Remove the line PASSWORDS_{listener_name} from the listener.ora file.
Step 04:
Start the listener
$lsnrctl start {listener_name}
and set the password if you want to set the password again. In order to set the password have a look at, How to set listener password
Related Documents
ORA-12518: TNS:listener could not hand off client connection
The listener supports no services
lsnrctl start fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:
Listener Hangs, Child listener process remains persistence
EM Daemon is not running
ORA-12541: TNS:no listener
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Login to Dbconsole, Authentication failed!null Returned
How to Enable Listener Logging and Tracing
oracle:/databridge/db DBGEP> $ lsnrctl status LISTENER
LSNRCTL for HPUX: Version 9.2.0.8.0 - Production on 28-MAR-2010 21:19:33
Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ravel.gov)(PORT=1521)))
TNS-01169: The listener has not recognized the password
Cause of the Problem
The TNS-01169 error occurred because security feature is enabled for listener i.e password is set in listener.ora file but lsnrctl command is issued without proper authentication.
Solution of the Problem
After you enable listener password in Oracle 9i, you will now require a password whenever you wish to stop the listener or any other listener actions. However in Oracle database 10g, if you are not logged into the operating system with a privileged account i.e OS user is a member of dba group, you will have to enter a password while doing any operation to listener.
As we see from the message our listener version is 9.2.0.8.0 so follow the following steps to solve the problem.
A) If you remember listener password:
Step 01: Invoke lsnrctl command.
$lsnrctl
Step 02: Set the current_listener to the appropriate listener to which you want to do operation.
LSNRCTL> set current_listener {listener_name_here}
Note that you have to issue "set current_listener {listener_name_here}" if your listener name is not default name that is LISTENER.
Step03: Set password to the password that you previously set for listener.
LSNRCTL> set password {password_here}
or simply you can do it interactively as,
LSNRCTL> set password
Password: {enter_your_password_here}
Step 04: Issue your appropriate command.
LSNRCTL> status
or,
LSNRCTL> stop
B) If you forget listener password:
If password is set in plain text within listener.ora file
If you forget listener password then look for listener.ora and see if password is set in plain text. If it is in plain text then you can retrieve it easily and use that in "set password" command and then do operation as you wanted.
If password is encrypted within listener.ora file
Step 01:
If password is set in encrypted format, check the listener process by issuing,
$ps -ef |grep tns
and note down the process id.
Step 02:
Kill the listener process by,
$kill -9 {process_id}
Step 03:
Remove the line PASSWORDS_{listener_name} from the listener.ora file.
Step 04:
Start the listener
$lsnrctl start {listener_name}
and set the password if you want to set the password again. In order to set the password have a look at, How to set listener password
Related Documents
ORA-12518: TNS:listener could not hand off client connection
The listener supports no services
lsnrctl start fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:
Listener Hangs, Child listener process remains persistence
EM Daemon is not running
ORA-12541: TNS:no listener
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Login to Dbconsole, Authentication failed!null Returned
How to Enable Listener Logging and Tracing
Friday, March 26, 2010
How to set oracle listener password
In general there are three ways by which you can set Oracle database listener password.
A. Editing the listener.ora file directly and setting the password within it.
B. Using lsnrctl utility.
C. Using GUI such as Oracle Net Manager or Enterprise Manager.
A. Editing the listener.ora file directly and setting the password within it:
Through this method password is stored in a plain text format inside listener.ora file without any encryption. Note that, through this way it is possible to set more than one listener password. Following steps demonstrate the procedure.
Step 01: Locate the listener.ora file. Based on the operating system and environmental settings the location varies. The default location is $ORACLE_HOME/network/admin or if you set TNS_ADMIN environmental variable then it overrides default location. Alternatively, if your listener is up you can issue "lsnrctl status" command to check the location of listener.ora file.
Step 02: After you locate listener.ora file check its contents.
For example, following is my listener.ora file contents.
From above contents you see my listener has the default name LISTENER from lines,
B. Using lsnrctl utility.
With the lsnrctl utility you can secure your password by making password encrypted. Setting password through lsnrctl utility is a recommended option. Step by step it is demonstrated.
Step 01: Invoke lsnrctl utility.
After invoking change_password it will prompt you for old password, if you have any old password set then type that. But if you don't have any old password then simply press Enter.
Then it will prompt you for the new password which you want to set and then press enter.
Then it will again prompt you to reenter the new password for confirmation and then press enter.
After this password will be changes for running instance or session of the listener.
Issue "set password" and "save_config" command if want it applicable for all the future instance or session. Issuing "save_config" command you save the configuration file after setting the password otherwise it will be lost.
After you set password using lsnrctl utility if you open listener.ora file you will see a new entry like below which is an encrypt one.
C. Using GUI such as Oracle Net Manager or Enterprise Manager.
Invoke netca or using Eneterprise Manager you can add listener password as directed by graphical window.
Related Documents
ORA-12518: TNS:listener could not hand off client connection
The listener supports no services
lsnrctl start fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:
Listener Hangs, Child listener process remains persistence
EM Daemon is not running
ORA-12541: TNS:no listener
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Login to Dbconsole, Authentication failed!null Returned
How to Enable Listener Logging and Tracing
A. Editing the listener.ora file directly and setting the password within it.
B. Using lsnrctl utility.
C. Using GUI such as Oracle Net Manager or Enterprise Manager.
A. Editing the listener.ora file directly and setting the password within it:
Through this method password is stored in a plain text format inside listener.ora file without any encryption. Note that, through this way it is possible to set more than one listener password. Following steps demonstrate the procedure.
Step 01: Locate the listener.ora file. Based on the operating system and environmental settings the location varies. The default location is $ORACLE_HOME/network/admin or if you set TNS_ADMIN environmental variable then it overrides default location. Alternatively, if your listener is up you can issue "lsnrctl status" command to check the location of listener.ora file.
E:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 27-MAR-2010 09:40:38
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 27-MAR-2010 09:36:06
Uptime 0 days 0 hr. 4 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:\oracle\product\10.2.0\db_2\network\admin\listener.ora
Listener Log File E:\oracle\product\10.2.0\db_2\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "a" has 1 instance(s).
Instance "a", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Step 02: After you locate listener.ora file check its contents.
For example, following is my listener.ora file contents.
# listener.ora Network Configuration File: E:\oracle\product\10.2.0\db_2\network\admin\listener.oraStep 03: Add a password line corresponding to the listener.
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=a)
(ORACLE_HOME=E:\oracle\product\10.2.0\db_1)
(SID_NAME=a)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
From above contents you see my listener has the default name LISTENER from lines,
LISTENER =It is the line starting LISTENER= . Within listener.ora file we need to add a line to this file with the following format:
(DESCRIPTION_LIST =
PASSWORDS_{LISTENER_NAME}=listener_password
Here the listener is called LISTENER then we need a add following lines within the listener.ora file.PASSWORDS_LISTENER=listener_passwordYou can also set multiple passwords as follows:
PASSWORDS_LISTENER=(password1,password2)For example I want to have my "LISTENER" listener password as "arjudba" so my listener.ora file will look like,
# listener.ora Network Configuration File: E:\oracle\product\10.2.0\db_2\network\admin\listener.oraNote that, After you set password in 9i, you will now require a password whenever you wish to stop the listener or any other "destructive" listener actions. However in Oracle database 10g, if you are not logged into the operating system with a privileged account, you will have to enter a password while doing any destructive operation to listener. Like,
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=a)
(ORACLE_HOME=E:\oracle\product\10.2.0\db_1)
(SID_NAME=a)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
PASSWORDS_LISTENER=arjudba
$ lsnrctl
LSNRCTL> set password
B. Using lsnrctl utility.
With the lsnrctl utility you can secure your password by making password encrypted. Setting password through lsnrctl utility is a recommended option. Step by step it is demonstrated.
Step 01: Invoke lsnrctl utility.
E:\>lsnrctlStep 02: Set the current listener to which you want to set password.
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 27-MAR-2010 11:24:26
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> set current_listener listenerStep 03: Invoke change_password command:
Current Listener is listener
After invoking change_password it will prompt you for old password, if you have any old password set then type that. But if you don't have any old password then simply press Enter.
Then it will prompt you for the new password which you want to set and then press enter.
Then it will again prompt you to reenter the new password for confirmation and then press enter.
After this password will be changes for running instance or session of the listener.
LSNRCTL> change_passwordStep 04: Set up password for the all future instance or session:
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Password changed for listener
The command completed successfully
Issue "set password" and "save_config" command if want it applicable for all the future instance or session. Issuing "save_config" command you save the configuration file after setting the password otherwise it will be lost.
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Saved LISTENER configuration parameters.
Listener Parameter File E:\oracle\product\10.2.0\db_2\network\admin\listener.ora
Old Parameter File E:\oracle\product\10.2.0\db_2\network\admin\listener.bak
The command completed successfully
After you set password using lsnrctl utility if you open listener.ora file you will see a new entry like below which is an encrypt one.
#----ADDED BY TNSLSNR 27-MAR-2010 11:37:15---
PASSWORDS_LISTENER = 44A81038BB249678
#--------------------------------------------
C. Using GUI such as Oracle Net Manager or Enterprise Manager.
Invoke netca or using Eneterprise Manager you can add listener password as directed by graphical window.
Related Documents
ORA-12518: TNS:listener could not hand off client connection
The listener supports no services
lsnrctl start fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:
Listener Hangs, Child listener process remains persistence
EM Daemon is not running
ORA-12541: TNS:no listener
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Login to Dbconsole, Authentication failed!null Returned
How to Enable Listener Logging and Tracing
Saturday, December 5, 2009
Audit and Profile Management Exercises in Oracle
In your lab for this week you are going to work with three different areas and processes within the Oracle Database that can be used to control data security. Each of these three processes has its own distinctive application to providing levels of security. In each case the individual processes deal with either limiting a users access to the database, limiting access to processes within the database, or keeping track of what the user is doing while in the database.
For the lab you will be using the SCOTT user which is already created in your instance. In Step 4 you will also be asked to shutdown you instance, make some edits to the init.ora file for your instance and then restart the instance.
 | L A B S T E P S |  |
| STEP 1: Define a New Profile |
Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.
- To begin, log into your instance as the SYS user.
- Write SQL script that will create a new profile named DBM449_SCOTT_PROFILE that will do the following:
- Limit the number of failed login attempts to 3 in a row.
- Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).
- Allow a session to be idle no more than 1 hour.
- Change the password every 60 days.
- Allow the user 3 days to change the password after it expires.
- Not allow a previous password be reused before there have been three password changes.
- Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the DBM449_SCOTT_PROFILE parameters.
| STEP 2: Testing the New Profile |
Now that we have a new profile for the SCOTT user we need to verify that it works properly. For obvious reasons there are going to be parts of the profile that we cannot test within the confines of this lab due to time constraints, but we can test to verify that the SCOTT user is being controlled by the profile.
- The first things we need to do is assign the profile to the SCOTT user. While still logged into your instance as the SYS user write and execute the SQL command that will assign the new SBM449_SCOTT_PROFILE profile to the SCOTT user.
- Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@DB####.WORLD.
- There are several things that we can test related to the logging in and changing a password so here we go.
- You should now be successfully connect to the SCOTT user. Write the connect command again on this time use an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.
- Repeat the above process until you get the ORA-28000: the account is lockederror which will indicate that the profile is working here.
- At this point we need to get the account unlocked so you will need to login to your instance as the SYS user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.
- Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.
- Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.
- Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.
- Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.
| STEP 3: Using the PRODUCT_USER_PROFILE table |
As the owner of a schema a user has certain inherited privileges that would allow the user to pass access to his/her own objects on to other users. Often times this can open up data to scrutiny by individuals who probably do not need to have access to it. These types of decisions should always be made by the DBA in charge of the database. One mechanism the DBA has to keeping users from using these inherited privileges is by excluding those commands using the PRODUCT_USER_PROFILE (PUP) table. In this section of the lab we are going to do this to the SCOTT user by setting up the scenario that will prohibit him from giving the user GEORGE (created in lab 2) access to the EMP table.
- To begin, copy the pupbld.sql file from $ORACLE_HOME/rdbms/admin to the C drive of your local computer or place it in the local F drive of your Citrix environment. Once you have downloaded it you will need to open the file and make two edits to the login strings; one at the top and one at the bottom. The login at the top of the script is for the SYSTEM user. The password is already set to MANAGER which is correct. You need to change the reference to the database instance to match your instance name (do not add the AS SYSDBA to this connection string). The other connection string is at the bottom and you also need to change the instance name here to match yours. Once you have made these changes then save the file.
- Now login to your database instance as the SYS user. Run the PUPBLD.SQL script from the SQL> prompt (DO NOT copy and paste the script). Remember that at the end of the script you should be connect as the SYS user. You can test this by issuing a SHOW USER command.
- Now we need to limit SCOTT from being able to use the GRANT command.
- Insert the proper values into the PRODUCT_USER_PROFILE table that will keep the SCOTT user from using the GRANT command. Remember that some of the values in your insert statement must be in upper case and some will need to be in mixed case. Once you have done this then query the table to verify the insert (REMEMBER: you cannot query the table as the SYS user, only as the SYSTEM user).
- Now we need to test our above settings and make sure they are working.
- Connect to the SCOTT user (remember that you changed the password to LION).
- Write and execute the statement that would GRANT the user GEORGE the ability to write a select statement and see the data in the EMP table owned by SCOTT. You should receive the following message - SP2-0544: Command "grant" disabled in Product User Profile.
- This verifies that you have now disabled the ability of the SCOTT user to allow another user to access any of the data in his schema.
| STEP 4: Setting up the Database to use Auditing |
Being able to audit what, when and where people are doing things in the database can be a very enlightening thing for a DBA. It can also be a very important tool in working with Data Security. Oracle provides the ability to do various types of auditing, but it takes some special setting up of the environment for this to work. In this step we are going to make the necessary adjustments to the current Oracle instance so that we can enable auditing and make some tests. If you need to review the processes to be used here then refer to the iLab Manual in week 1.
- First you need to make sure that you are logged into your instance as the SYS user.
- At this point issue a SHUTDOWN IMMEDIATE command to shut down you database instance.
- Once the instance is shut down you need to go into your Citrix Windows Explorer application, find your database instance set of directory folders, drill down to the pfile directory folder and open your init.ora file found in that folder.
- Under the section titled "Security and Auditing" you need to add the parameter AUDIT_TRAIL and set the parameter to DB_EXTENDED. This will allow the SQL_TEXT column of the DBA_AUDIT_OBJECT view to be populated. Save and close the file and then go back to your SQL*Plus session.
- Now using the init.ora file, start your instance back up to an OPEN status. You can do this by issuing a STARTUP PFILE= statement and pointing to your init.ora file.
- Once you have completed this process you are ready to begin setting up the database to audit some activity.
| STEP 5: Creating an Audit Trail |
Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects. Again using the SCOTT user we are going to set up several scenarios for auditing what SCOTT does while in a session. NOTE: if you need to work through this process several times you can delete the values in the AUD$ base table by issuing the TRUNCATE TABLE AUD$ command while logged in as the SYS user.
- Make sure that you are connected as user SYS.
- Display value of the parameter AUDIT_TRAIL. For the VALUE column you should have a value of DB_EXTENDED.
- Now we can set up auditing to track what goes on in the database.
- Write SQL statements to audit successful and unsuccessful login attempts by SCOTT.
- Write SQL statement to audit any successful INSERT, UPDATE or DELETE performed on table DEPT in scott's schema.
- Now we need to test the audits to verify that they work.
- Log into the SCOTT user (remember that the password is LION) and perform the following:
- write and execute an UPDATE statement that will change the value in the LOC column of the DEPT table to MIAMI where the DEPTNO value is 10.
- Write and execute the INSERT statement that will in insert the following values into DEPT - (50, 'LEGAL', 'HOUSTON').
- Write and execute the DELETE statement that will delete the row from the DEPT table that was just inserted.
- Try to reconnect to the SCOTT user with an invalid password.
- Now connect back to the SYS user.
- While logged into your instance as the SYS user, query the DBA_AUDIT_OBJECT view of the data dictionary for the user name of the account (Not the OS), the object owner, the object name, the action name and the SQL command (text) from the DBA_AUDIT_OBJECT view in the Data Dictionary.
- Did you notice that the entries for successful logon and unsuccessful logon attempts were not there. Now query the user name, action name and return code values in the DBA_AUDIT_SESSION view. You should find that information here.
Related Documents
Basic Oracle Sql Exercise
Oracle Security Practices
Oracle, like many other databases, uses the combination of specific user definitions, privileges, and roles to control access to the data in the database. In turn, it provides various layers of security. When the database is first created, there are several users created for the purpose of not only installing various components of the database, but also to manage and administer the database functionality. You have already used the SYS and SYSTEM users in previous labs. In this lab you will be creating a series of different users, administering various privileges to those users, as well as exploring how Roles help provide additional functionality to the user picture.
VERY IMPORTANT:
Be sure that you start a spool session before you start executing your SQL code when working on the lab. Your SPOOL file name should be DATABASE_lab6. This will be the only way to capture the results of your work and will be required for grading. Remember that anytime you see the ### in the instructions (not the expected results), it indicates that you are supposed to replace the number signs with your instance number. Last, if you do the lab in several sessions, be sure that you use a different file name, so as not to overwrite the existing file (for example DATABASE_lab6, DATABASE_lab6a, DATABASE_lab6b etc.)
Environment Set Up:
Before starting this lab you will need to download two files from Doc Sharing and run them to make some changes to the environment. To make these change do the following:
- Download the pupbld.sql file from Doc Sharing to a directory on your computer.
- Open the file and edit the login information at the top for the SYSTEM user (change the DB### to your instance name).
- Now at the bottom of the file make the same change to the login string to go back to the SYS user.
- Now run the file in your Oracle enstance.
- Download the Lab6_support.SQL file from Doc Sharing to a directory on your computer.
- Open the file and edit the login information for the connection to SCOTT to reflect your instance number in the host string. This is the only change you should have to make to the script.
- When finished, save the file and then run the script file logged onto your instance as the SYS AS SYSDBA.
| L A B S T E P S | |
| Step 1: Creating a new user |
Your response will look similar to this:
User CreatedNow that the user is created, make sure that Bob can create a session, by granting the needed privilege.
Your answer will look similar to this:
Grant succeeded.
| Step 2: Finding information on users |
Hint: This information can be obtained by querying the DBA_USERS.Your response will look similar to this:
USERNAMEDEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ -----------------------
BOBUSERS01 TEMP01
| Step 3: Finding information about user storage |
Hint:This information can be obtained from DBA_TS_QUOTAS.Your answer will look similar to this:
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKSMAX_BLOCKS
--------------------- -------------------- ---------- ------------ ---------- ------------
USERS01 BOB 0 524288 0 64
| Step 4: Finding user privileges |
Hint: At the command line prompt enter CONNECT bob/along@host_string_goes_Your answer will look similar to this:here
Connected.While connected as Bob, query the USER_SYS_PRIVS data dictionary view to see what privileges Bob currently has. This view is accessible by any user and can be very helpful.
User altered.
Your answer will look similar to this:
USERNAME PRIVILEGE
---------------------- ------------------------
BOB CREATE SESSION
| Step 5: Changing user specifications |
Your answer will look similar to this:
Connected.Bob has forgotten his password. Assign him a password of OLINK and require that Bob change his password the next time he logs on.
User altered.
Your answer will look similar to this:
User altered.
| Step 6: Listing privileges associated with a Role |
Hint: The information is available from DBA_SYS_PRIVS.Your answer will look similar to this:
GRANTEEPRIVILEGE ADM
------------------------------ ---------------------------------------- ----
RESOURCECREATE CLUSTER NO
RESOURCECREATE INDEXTYPE NO
RESOURCECREATE OPERATOR NO
RESOURCECREATE PROCEDURE NO
RESOURCECREATE SEQUENCE NO
RESOURCECREATE TABLE NO
RESOURCECREATE TRIGGER NO
RESOURCECREATE TYPE NO
8 rows selected.
| Step 7: Creating a Role |
Create a role called DEV and assign privileges to the Role that will allow a user to:
- Create a session
- Create a table
- Create a view
- Enable a user select from Scott's EMP table.
Hint: Remember that a member assigned to this roll must be able to create the table in any tablespace. Also, you cannot assign System privileges and Object privileges in the same GRANT statement.You should get responses that look similar to this:
Role created.
Grant succeeded.
| Step 8: Verifying Role content |
Your response will look similar to this:
GRANTEEPRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DEVCREATE VIEW NO
DEVCREATE SESSION NO
DEVCREATE ANY TABLE NO
GRANTEE OWNERTABLE_NAME GRANTOR
---------------- ---------------------- ------------------- -------------------
DEV SCOTT EMP SCOTT
| Step 9: Assigning a user to a Role |
Your response will look similar to this:
User created.
Grant succeeded.
| Step 10: Using privileges in a Role |
Your answer will look similar to this:
Connected.
Table created.
| Step 11: Assigning multiple Roles to a user |
Hint: there is a system-level role that will accomplish this but you have to be connected as the correct user to assign it.Your answer will look similar to this:
Connected.
Grant succeeded.
| Step 12: Assigning a Default Role |
Hint: You will need to use the USER_ROLE_PRIVS view to find your information.Your answer will look similar to this:
Grant succeeded.
User altered.
Connected.
USERNAMEGRANTED_ROLE DEF
------------------------------ ------------------------------ ---
JACKDEV YES
JACKRESOURCE NO
JACKSELECT_CATALOG_ROLE NO
Subscribe to:
Posts (Atom)