connect / as sysdba
They logged into the OS didn't they - they provided a password then!
"as sysdba" is extremely powerful, it uses OS AUTHENTICATION, it does not require database authentication (it is used to login before there is a "database" or even an "instance"!)
/ as sysdba - connect to LOCAL instance as specified in the environment variables ORACLE_HOME + ORACLE_SID
sys/a@fstest as sysdba - connect OVER A NETWORK to a remote database, using the credentials sys/a - eg: resolve (using the tnsnames.ora or whatever naming you are using) fstest to a REMOTE MACHINE and connect to it.
won't use the password file, it uses OS authentication.
orapwd - the password file used for remote authentication - won't help you here at all, it would be used over the network, but not for local connections.
"as sysdba" is extremely powerful, it uses OS AUTHENTICATION, it does not require database authentication (it is used to login before there is a "database" or even an "instance"!)
remove them from the DBA group (the ability to connect / as sysdba goes away)
grant them sysdba (this will put them into the password file - you need to have remote login password file set up - you might have to use orapwd to create an empty one if you don't have one yet).
When Oracle is installed on a Windows OS it creates a local group named 'ora_dba -- Oracle DBA Group' and the local user 'Administrator' with its member by default.The membership of this local group grants its members the privilege to connect as sysdba without password -- as they are authenticated at OS level.
When you connect as sysdba issuing a ‘CONNECT / AS SYSDBA’, Oracle checks if your account is a member of the os group ‘ORA_sid_DBA’ or ‘ORA_DBA’ group.
Operating System Group UNIX User Group Windows User Group
OSDBA dba ORA_DBA
OSOPER oper ORA_OPER
This is why REMOTE_OS_AUTHENT MUST always be set to FALSE otherwise anyone with his laptop can connect as a privileged user.
Password file:
initialization parameter file parameter REMOTE_LOGIN_PASSWORDFILE to exclusive, shared, or none.
exclusive specifies that only one instance can use the password file and that the password file contains names other than SYS.
shared. It specifies that multiple instances (for example, an Oracle RAC environment) can use the password file.
none specifies that Oracle Database ignores the password file.
=========
Let’s say we want to create a database user who logs in with their OS account. To do so, we will use the “external authentication” option offered by oracle.
Check the value of the os_authent_prefix parameter
SQL> show parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
The ops$ value is the prefix that oracle will use for each OS account.As we can see, our user is prefixed with ops$. Oracle uses the prefix to recognize OS users when they attempt to connect. It is also a way for Oracle to separate a database account and an OS account.
Let’s create the external user account in the database
SQL> create user ops$xxxx identified externally;
Give the necessary privileges
SQL> grant connect, resource, create session to ops$xxxx;
Let’s create the user named xxxx and his password.
[root@dbserver ~]# useradd -g oinstall -G dba,oper,asmdba xxxx
[root@dbserver ~]# passwd xxxx
Open a new session and connect with the new operating system (os) user created “xxxx”
login as: xxxx
xxxx@192.168.54.10's password
Load the database
[xxxx@dbserver ~]$ . oraenvoracle database 11
ORACLE_SID = [xxxx] ? achats
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID xxxx.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base has been set to /u01/app/oracle/product/12.1.0/db
Here, our target database is called achats
Connect to database
[xxxx@dbserver ~]$ sqlplus /
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 23 09:47:48 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL>
Now the os user named “xxxx” can connect to the database without being asked for a password.
No comments:
Post a Comment