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.
.png)
