Roles in Oracle - Managing Users

An ORACLE role is a set of privileges (or the type of access that each user needs depending on his or her status and responsibilities)

Roles :

There are 3 Standard Roles

- CONNECT

- RESOURCE and

- DBA

The CONNECT Role

This has Privileges as follows :

 CREATE SESSION, ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, and CREATE VIEW.

Note: Users do not have the ability to create tables or clusters unless you grant them a quota on a tablespace.

You can also create your own role and grant privileges to that role :

Let s see this..

SQL > Create role APPLICATION_USER;

SQL > Grant CREATE SESSION to APPLICATION_USER;   - - - Grant privileges to Role

SQL > Grant APPLICATION_USER to username;  - - - Grant Role to a user

The RESOURCE Role

The RESOURCE role has the following system privileges:

CREATE CLUSTER, CREATE INDEX, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.

Users who have the RESOURCE role do not have the ability to create tables, indexes and clusters unless you first grant them a space quota in a tablespace.

Grant RESOURCE role to developers who will be creating PL/SQL objects such as procedures and triggers.

The DBA Role

The DBA role has all system privileges – including unlimited space quotas – and the ability to grant all privileges to other users

SYSTEM is for use by a DBA user

In ORACLE, the DBA is granted the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, which in turn have privileges necessary for exporting and importing the full Oracle database

Note:

An individual with the DBA role can revoke CONNECT, RESOURCE, DBA, or any other privilege or role from anyone, including another DBA.

How to add password to a role ?

To enable security for a role, use the identified keyword in the alter role command.

alter role APPLICATION_USERS  identified by password123 ;

User Management - II

This is the very basic task a DBA do in his initial stage.

Creating a User

SYSTEM and SYS are the  users that are created by default , and has the the passwords MANAGER and CHANGE_ON_INSTALL.

Example:

SQL >create user username identified (by password | externally);

SQL> create user Nikhil identified by Pass123 ;

To change a password of a user, use the alter user command as a dba user:

SQL > alter user Nikhil identified by newpassword ;

Granting Limited Resources

SQL > alter user Nikhil quota 100M on USERS;

A user’s space quota may be set when the user is created , via the create user command.

If there is no limit on the user’s space quota, then you can grant that user the UNLIMITED TABLESPACE system privilege.

SQL > alter user Nikhil quota unlimited on USERS;

                         

Password Management

Passwords can expire, and accounts may be locked due to repeated failed attempts to connect. When you change your password, a password history may be maintained in order to prevent reuse of previous passwords.

The expiration characteristics of your account’s password are determined by the profile assigned to your account. Profiles, which are created by the create profile command, are managed by the dba.

• The “lifetime” of your password, which determines how frequently you must change it

• The grace period following your password’s “expiration date” during which you can change the password

• The number of consecutive failed connect attempts allowed before the account is automatically “locked”

• The number of days the account will remain locked

• The number of days that must pass before you can reuse a password

• The number of password changes that must pass before you can reuse a password

Another way to change password: use password command

SQL> password

(hit enter)

This will prompt you your old and new password.

Invisible Index

To create an invisible index: 

Use the CREATE INDEX statement with the INVISIBLE keyword.

Example:

The following statement creates an invisible index named ename_index for the ename column of the emp table:

CREATE INDEX emp_index ON emp(ename)
      TABLESPACE empdata
      STORAGE (INITIAL 20K NEXT 20k)
      INVISIBLE;

 

 An invisible index is an index that is ignored by the optimizer.

     You have to explicitly set the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES  =  TRUE at the session or system level.

What is nohup command ? How to Run Datapump Jobs in Background with ‘’nohup ‘’ mode.

This is used to run Expdp / Impdp Jobs or any other long running jobs in Background.

Step 1: Create a parameter file for export or import

$ cat expdp.par

 

job_name=tab_export

directory=EXP_DIR

dumpfile=EMP_table_.dmp

logfile=export_EMP.log

reuse_dumpfiles=y

 

Step 2:  Create a shell script for your exp par file in nohup.

Also, Change the permission of this file  to execute this.

$ vi exp.sh

nohup expdp parfile=/home/oracle/st/exp.par &


[Esc]

!wq   [ENTER]


$ chmod 744 exp.sh

Step 3: Now run the shell script in nohup using below command.

This will release the prompt immediately and there will not be any running job in the prompt. 

$ nohup export.sh &

[1] 30221


$ nohup: appending output to `nohup.out'


[1]+  Done                    nohup export.sh

Note:

You can see the datapump job running in DBA_DATAPUMP_JOBS view.

See Also :  Invisible Index in Oracle

Oracle Release Number Format

-         Major Database Release Number
The first numeral is the most general identifier. It represents a major new version of the software that contains significant new functionality.

-         Database Maintenance Release Number
The second numeral represents a maintenance release level.  Some new features may also be included.

-         Fusion Middleware Release Number
The third numeral reflects the release level of Oracle Fusion Middleware.

-         Component- Specific Release Number
The fourth numeral identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

-         Platform-Specific Release Number
The fifth numeral identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this numeral will be the same across the affected platforms.

Housekeep : Quick Tips for DBA

Many a times , the DBA need to remove/housekeep the files on file system on Server so as to make the database running fine...!!

 
You can also remove files based on the age of the file. For example, say you determine that any trace files more than 2 days old can be safely deleted. Typically, the find command is used in conjunction with the rm command to accomplish this task. Before removing files, first display the results of the find command:

$ find . -type f -mtime +2 -name "*.trc*"

If you are satisfied with the list of files, then add the 'rm' command to remove them:

$ find . -type f -mtime +2 -name "*.trc*" | xargs rm

In the prior line of code, the results of the find command are piped to the xargs command, which executes the rm command for every file found by the find command. This is an efficient method for deleting files based on age. However, be very sure you know which files will be deleted.

Another file that sometimes consumes large amounts of space is the listener.log file. Because this file is actively written to by the listener process

$ cp listener.log /u01/backups

Next, use the cat command to replace (nullify) the contents of the listener.log with the /dev/null file (which contains zero bytes) :

$ cat /dev/null > listener.log

See also : Delete Old archives applied in Standby :
 

For Archivelog deletion, we can use various policies as below :

On Primary

RMAN>  CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

On Standby ( Depends upon where backup is preformed )

RMAN>  CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;