How to create a user and grant Different roles to that user in Oracle

  • To Create a user in Oracle run below command:

"CREATE USER User_Name IDENTIFIED BY MyPassword;"

  • To allow a user to connect to a Database run below command: (Connect is a Role)

 "GRANT CONNECT TO User_Name;"

  • To allow a user to connect to a Database run below command: (Create Session is a privilege)

"GRANT CREATE SESSION TO User_Name;"

  • To grant Select on a Table or all tables simply run below SQL Statement: 

"GRANT SELECT ON TableName TO User_Name;"

"GRANT SELECT ON ANY TABLE To User_Name;"

  • To grant Table Privileges a user simply run below SQL Statement: 

"GRANT SELECT,INSERT,UPDATE,DELETE ON Schema.Table TO User_Name;"

  • To grant DBA role to a user simply run below SQL Statement: 

"GRANT DBA to User_Name"

Database Administrator by profession.

Comments

Post a Comment

Popular posts from this blog

How to Enable / Disable a user in MS SQL Server

Image
To Enable or Disable a User in MS SQL Server: There are two ways to Enable / Disable a user in MS SQL Server 1. Using TSQL To Enable a User:  use master go ALTER LOGIN [UserName] ENABLE go To Disable a User:  use master go ALTER LOGIN [UserName] DISABLE go 2. Using SQL Server Management Studio GUI: Open SQL Server Management Studio, go to object explorer and expend logins folder as in the below image:  Double click on desired user / login or right click and go to properties as in the below image: Click on Status and Enable / Disable user / login as in the below image:
Read more

How to Create a Database in MS SQL Server

Image
There are more than one ways to create a database in MS SQL Server. You can do it by using GUI from SQL Server management studio or you can use T-SQL. In this blog I will demonstrate all possible options to create a database: TSQL:  CREATE DATABASE [DBName] CONTAINMENT = NONE ON PRIMARY (NAME = N'DBName', FILENAME = N'Path\DBName.mdf' , SIZE = 20480KB , FILEGROWTH = 51200KB) LOG ON (NAME = N'DBName_log', FILENAME = N'Path\DBName_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10240KB) Using GUI:    Using GUI is a simple method to create a Database in MS SQL Server, Below method is simple and basic for advanced option you will have the option to choose the option you need:       
Read more

How to Import / Export Data in Oracle

There are different Ways to Import / Export Database, Tablespace, Table, Schema in Oracle Database:   To Import Table from one Database to another follow the following steps:  1. First check Data Pump directory:  select Directory_Name, Directory_Path from dba_directories; 2. If directory is not created or you would like to change the location of directory run this:  CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/disk1/folder1'; " 3. Additionally you can grant read/ write access to the user.  GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO User; " 4. Export Table command (Run this command on Shell not on SQL Console) expdp SYSTEM TABLES=Schema.Table DIRECTORY=DUMP_DIR DUMPFILE=TableDumpfileName.dmp LOGFILE=Table_dumplog.log 5. To Import the dump command (Run this command on Shell not on SQL Console). This command will import the Data in same Table. Table definition on destination must be same as it is in source:  ...
Read more