Sunday, 14 January 2018

How to Create a Database User and Grant Permissions in Oracle



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.
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:






Thanks & Regards,
ora-data Team

Thursday, 28 December 2017

ADADMIN , ADCONFIG and ADMRGPCH




6.  How does ADADMIN know which form files to regenerate?

Answer
--------
How does adadmin know which forms files to regenerate?
---------------------------------------------------------------
With Release 11 adadmin, how does it determine which forms files need to be regenerated when using the Generate Forms Files option?

During the form generation process, adadmin and adaimgr (in this case adadmin) read a file named <prod>file.drv to build a list of objects to generate. 

For example if you are looking at the AZ module forms, there will be a file
called azfile.drv. This file is located in $PROD_TOP/admin/driver.
In your example this is $AZ_TOP/admin/driver.

NOTE - If a product not installed, adpatch does not regenerate forms for that
       product at all

You can test this by putting a dummy form name under entries for formsdev
and forms. This dummy form will be displayed when prompted to select forms for generation via adadmin.

How is this list of files in <prod>file.drv maintained?
-------------------------------------------------------
Any patches that install a new form or remove an existing form will
contain an appropriately updated version of <prod>file.drv.


adconfig:
A wrapper script that calls adconfig.pl. In earlier versions of AutoConfig adconfig.sh/ adconfig.cmd used to call the Java API to start AutoConfig.

AutoConfig Scripts:
On the application tier:
<AD_TOP>/bin
On the database tier:
<RDBMS ORACLE_HOME>/appsutil/bin

Log file created:
On the application tier:
<INST_TOP>/admin/log/<MMDDhhmm>/adconfig.log
On the database tier:
<RDBMS ORACLE_HOME>/appsutil/log/<CONTEXT_NAME>/<MMDDhhmm>/adconfig.log
 
Admrgpch:
Merges multiple patches into a single merged patch.
You can merge multiple patches into a single patch by using AD Merge Patch.
This AD utility is an executable located in AD_TOP/bin that merges multiple
AutoPatch compatible patches into a single, integrated patch. Once the merge is
complete, you use AutoPatch to apply the resulting patch in a single operation.
Using a merged patch reduces the time it takes to complete the patching process.
The command for merging patches is admrgpch.

AD Merge Patch reads the c<patchnum>.drv, d<patchnum>.drv, and g<patchnum>.drv
for each patch in the source directory and merges them to create a single set
of driver files (for example, cmerge99.drv, dmerge99.drv, and gmerge99.drv) in
the destination directory. It also merges the set of files contained in the
individual patches under the source directory according to file revision and
copies them to the destination directory. If a file exists in more than one
source patch, only the highest revision of the file is copied to the
destination directory.

Complete these steps:
1. Review the readme files carefully.
   Some patch readme files contain special instructions for applying merged
   patches. The patch may also require manual steps.
2. Create directories.
   In the patch top area, create a source directory and a destination directory.
   Choose any name for these directories.
3. Unzip patches.
   Copy all the patches to be merged into the source directory and unzip them.
4. Run AD Merge Patch.
   Run AD Merge Patch and supply the arguments for the destination directory
   name and the source directory name. You also need to specify the merged patch
   name, or accept the default.
5. Check AD Merge Patch log files.
   After AD Merge Patch runs, check the admrgpch.log file for errors. The file
   is located in the current working directory (where AD Merge Patch was run).
6. Run the merged patch.
   Once a merged patch is created, apply it just like a single patch.

AD Merge Patch cannot merge patches of different releases, different parallel
modes, or different platforms. However, it can merge patches for a specific
platform with a generic patch, or patches with different source character sets.
AD Merge Patch notifies you if you try to merge incompatible patches.

For example :
$ admrgpch /d01/patches/source /d01/patches/destination -merged_name mergedpatch

Where, mergedpatch is the name of the merged patch.


Thanks & Regards,
ora-data Team.