• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2514
  • Last Modified:

how to block sqlplus '/as sysdba'

how to block  sqlplus '/as sysdba'?
I Don't want anybody to access Database using <sqlplus '/as sysdba'>, the user has to give the user name and password to access the database
0
umesh4exp
Asked:
umesh4exp
4 Solutions
 
TolomirAdministratorCommented:
sqlplus '/as sysdba' only works on the server itself, not remote.

So make certain the users don't have access to the oracle shell account and you are done (and the root account of cause)
0
 
umesh4expAuthor Commented:
I want to block sqlplus '/as sysdba' in  Server itself, what is the solution for this
0
 
TolomirAdministratorCommented:
I don't think this is a good approach.

Change the password for the oracle user and keep your root password hidden and you are done.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
PilouteCommented:
Logging in "/ as sysdba" can not be really unactivated.

It can, actually, be unactivated by changing a parameter in slqnet.ora (just set SQLNET.AUTHENTICATION_SERVICES = NONE), but anyone with some knowledge can reverse the settings, duplicate the file, etc...  

Better idea would be to restrict access to the system.
0
 
MikeOM_DBACommented:

Do as tolomir suggests.

1) Change oracle password.
2) Anyone needing to login to server should have personal (or network) account.
3) DBA's should also login to server with personal (or network) account and belong to DBA group
4) Login to Oracle account sould be by using sudo (nobody need to know the password)
0
 
schwertnerCommented:
Another idea is to allow logs only from a reliable set of machines

See here but change it to BEFORE LOGON

See also this:

SQL> SELECT SYS_CONTEXT('USERENV','CURRENT_USER') current_user from DUAL;

CURRENT_USER
--------------------------------------------------------------------------------
SYS

So you can encounter the user and to act appropriatly.

The snippet shows how to prevent DDLs from machines that are not granted this privilege.
CREATE OR REPLACE TRIGGER restrict_ddl
BEFORE ddl ON DATABASE
DECLARE
   machinename VARCHAR2(64);
   message     VARCHAR2(150) := 'You have no rights to execute DDL statements on DEVELOPBETA! Call the DBA! ';
BEGIN
   SELECT SYS_CONTEXT ('USERENV', 'host') INTO machinename FROM DUAL;
   IF machinename IS NULL then
   message := message || '   ' || 'NULL';
   else
   message := message || '   ' || machinename;
   end if;
  IF    INSTR(lower(machinename),'bg\shvertner',1) <> 0
     OR INSTR(lower(machinename),'bg\fed',1) <> 0
     OR INSTR(lower(machinename),'bg\plamen',1) <> 0
     OR INSTR(lower(machinename),'developbeta',1) <> 0
     OR INSTR(lower(machinename),'oralin1',1) <> 0
     OR machinename IS NULL
THEN
       null;
  ELSE
     RAISE_APPLICATION_ERROR (num => -20000,msg => message);
  END IF;
END;
/ 

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now