How to create user and grant privileges in oracle in R12.2

Hi DBA-mates,

As we all know, creating a Schema or DB User is very easy and we all do it in DBA’s life. Here, How to create database user and grant privileges in oracle.

However, there is some important points, which we need to take care.

There is some points such as how to create User’s and Grant them the Privileges.

How to create User and grant the user:

1. Create DB User or Schema:

Note: Here username is ora-data.

$sqlplus '/as sysdba'

Before creating user, we should check either user is existing or not.

SQL>select USERNAME from DBA_USERS where USERNAME like '%ORA-DATA%';

Query:

CREATE USER user_name IDENTIFIED BY  'passsword' DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE tablespace_name PROFILE DEFAULT ACCOUNT UNLOCK;

Example:

SQL>CREATE USER ora-data IDENTIFIED BY ora-data DEFAULT TABLESPACE user TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;

Cross check:

SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED from dba_users where USERNAME='ora-data';

Providing GRANTS:

SQL>GRANT CONNECT, RESOURCE TO ora-data;

SQL>GRANT CREATE SESSION TO ora-data;

SQL>GRANT CONNECT TO ora-data;

SQL>GRANT SELECT ANY DICTIONARY to ora-data;

SQL>GRANT SELECT ANY TABLE to ora-data;

SQL>GRANT UNLIMITED TABLESPACE to ora-data;

SQL>ALTER USER ora-data ENABLE EDITIONS; (In R12.2 specially)

If you want to Grant only on any particular Table (tb1) then find the below details:

SQL>GRANT SELECT ON tb1 to ora-data;

Suppose, if you want to GRANT for any particular OWNER for particular TABLE then follow below steps:

Example: OWNER= xxabc, TABLE= tb1, USERNAME=ora-data

SQL>conn xxabc/password

SQL>show user

xxabc

SQL> GRANT SELECT ON tb1 TO ora-data;

How to Check the GRANT details:

1. DBA_SYS_PRIVS: To show information about user's privileges related to System (DBA).

2. DBA_TAB_PRIVS: To show information about user's privileges related to Tables.

3. DBA_ROLE_PRIVS: To show information about user's privileges related to Roles.

To know details:

SQL>desc DBA_SYS_PRIVS;

Some Column Name and details:

GRANTEE: It is the name, role, or user that was assigned the privilege.

PRIVILEGE: It is the privilege that is assigned.

ADMIN_OPTION: It indicates if the granted privilege also includes the ADMIN option.

SQL>desc DBA_TAB_PRIVS;

GRANTEE: It is the name of the user with granted access.

TABLE_NAME: It is the name of the object (table, index, sequence, etc).

PRIVILEGE: It is the privilege assigned to the GRANTEE for the associated object.

Hope this may useful and helpful. If there is any suggestions or  corrections, please let us know either in comment box or contact us @https://ora-data.blogspot.in/

Some more useful links:

AD Utilities for Oracle Apps DBA

How to add datafiles to Tablespace in realtime scenario

Oracle DBA Troubleshooting Tips and Guide

Oracle forms opening slowly troubleshooting

Regards,

ora-data Team