Short Description On Oracle Users and Security

In This part we discuss about Oracle users and security.  To perform the administrative tasks of an Oracle Database DBA, you need specific privileges within the database.

The SYSDBA and SYSOPER are two terms what will mandatory for oracle database. The unique thing about SYSDBA and SYSOPER privileges is that you must explicitly activate these privileges for your login session by specifying "AS SYSDBA" in the connection string BY THE FOLLOWING COMMAND:

SQL> CONNECT SYS AS SYSDBA

The default location of the password file in windows is 

‘%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora’.

HERE ‘%ORACLE_HOME%\database’ MEANS PATH DIRECTORY FOR PASSWORD

The following three steps show how to create a password file, how to enable remote login as SYSDBA, and how to grant SYSDBA access to a certain user:

Step 1: Create the password file using the ORAPWD utility:

CMD> ORAPWD FILE=‘%ORACLE_HOME%\DATABASE\PWDORCL.ORA ENTRIES=25

Step 2: you need to set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to ENABLE REMOTE LOGIN AS SYSDBA(In SPFILE or PFILE). To do this for SPFILE, execute the following command:

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

You must restart the database to make this change into effect.

Step 3: Grant SYSDBA or SYSOPER to HR user as follows:

SQL> GRANT DBA, SYSDBA to HR

Connecting Using Password File

SQL> CONNECT HR@ORCL AS SYSDBA

Verify who has the SYSDBA privilege in Database

SQL> SELECT * FROM V$PWFILE_USERS;

USERNAME             SYSDB        SYSOP

-------------------- ---------    ---------

SYS                  TRUE         TRUE

Creating and Managing Users, Roles, and Privileges

Creating a New User

You use the CREATE USER SQL statement to create a new user. The following example creates a new user JOHN whose password is JOHNPASS

SQL> CREATE USER JOHN  IDENTIFIED  BY JOHNPASS;

User created.

Following command give table space for JHON

SQL> SELECT  DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE  FROM  DBA_USERS WHERE USERNAME='JOHN';

The new user can’t connect to the database without privilege. So you should give  privileges to new User. Following Command do this:

SQL> GRANT CREATE SESSION TO JOHN;

Grant succeeded.

There is a new term QUOTA which is determines the maximum space that the user can use.

Following command show the command of create user, give table space & quota.

SQL>   CREATE USER JOHN IDENTIFIED BY JOHNPASS

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA 10M ON USERS;

User created

Altering a User: Using this statement, you can do the following work like Change a user’s password, Assign and modify tablespace quotas, Set and alter default and temporary tablespaces, Assign and modify a profile and default roles. Below, we show user ALTER USER command can be used to change previously created JOHN’s password. The new password will be JOHNNEWPASS

SQL> ALTER USER JOHN IDENTIFIED BY JOHNEWPASS;

User altered

In SQL*Plus, THIS command as :

SQL> PASSWORD Changing password for AHMED

Old password: *********

New password: *********

Retype new password: *********

Password changed SQL>

You can drop user with their objects by the Following Command

SQL> DROP USER JOHN CASCADE;

User Dropped.

If you aren’t sure whether you will need a user’s objects later, but you want to deny access.

SQL> REVOKE CREATE SESSION FROM JOHN;

Revoke succeeded. SQL>

Granting Roles and Privileges:

SQL> GRANT CREATE TABLE TO JOHN; Grant succeeded.

SQL> GRANT SELECT, UPDATE ON HR.EMPLOYEE TO JOHN; Grant succeeded.

Working with ROLES: We show the steps to create a role, assign privileges to the role, and then granting the role to a user by the following command:

SQL> CREATE ROLE APP; Role created.

Now, grant privileges to role APP.

SQL > GRANT CREATE SESSION TO APP; Grant succeeded.

SQL > GRANT CREATE TABLE TO APP;

Grant succeeded.

SQL > GRANT CREATE VIEW TO APP;

Grant succeeded.

SQL >

Now, you can grant the role APP to user JOHN. User JOHN will then receive all privileges assigned to the role APP.

SQL > GRANT APP TO JOHN;

Grant succeeded.

SQL >

SQL > GRANT DBA TO JOHN;

Grant succeeded.

SQL >

Creating and Using User Profiles: The Following Command Show How To Create Profile

SQL> CREATE PROFILE MISER  LIMIT     CONNECT_TIME 120     FAILED_LOGIN_ATTEMPTS 3     IDLE_TIME 60     SESSIONS_PER_USER 2;

Profile created.