MySQL Database User Permissions

MYSQL Server uses an internal database called mysql to store database user information. We can play around with several information tables liket the followings to get idea about user permissions and related information.

select * from mysql.user;

select * from mysql.db;

select * from mysql.tables_priv;

select * from mysql.columns_priv;

select * from mysql.procs_priv;

Followings are some formatted reports based upon these basic tables.

List of DATABASE Privileges:

SELECT md.host `Host`,

       md.user `User`,

       md.db `Database`,

       REPLACE(

          RTRIM(

             CONCAT(

                IF(md.Select_priv = 'Y', 'Select ', ''),

                IF(md.Insert_priv = 'Y', 'Insert ', ''),

                IF(md.Update_priv = 'Y', 'Update ', ''),

                IF(md.Delete_priv = 'Y', 'Delete ', ''),

                IF(md.Create_priv = 'Y', 'Create ', ''),

                IF(md.Drop_priv = 'Y', 'Drop ', ''),

                IF(md.Grant_priv = 'Y', 'Grant ', ''),

                IF(md.References_priv = 'Y', 'References ', ''),

                IF(md.Index_priv = 'Y', 'Index ', ''),

                IF(md.Alter_priv = 'Y', 'Alter ', ''),

                IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),

                IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),

                IF(md.Create_view_priv = 'Y', 'Create_view ', ''),

                IF(md.Show_view_priv = 'Y', 'Show_view ', ''),

                IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),

                IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),

                IF(md.Execute_priv = 'Y', 'Execute ', ''),

                IF(md.Event_priv = 'Y', 'Event ', ''),

                IF(md.Trigger_priv = 'Y', 'Trigger ', ''))),

          ' ',

          ', ')

          AS `Privileges`

  FROM mysql.db md

ORDER BY md.Host, md.User, md.Db

LIST OF GLOBAL PRIVILEGES:

SELECT mu.host `Host`,

       mu.user `User`,

       REPLACE(

          RTRIM(

             CONCAT(

                IF(mu.Select_priv = 'Y', 'Select ', ''),

                IF(mu.Insert_priv = 'Y', 'Insert ', ''),

                IF(mu.Update_priv = 'Y', 'Update ', ''),

                IF(mu.Delete_priv = 'Y', 'Delete ', ''),

                IF(mu.Create_priv = 'Y', 'Create ', ''),

                IF(mu.Drop_priv = 'Y', 'Drop ', ''),

                IF(mu.Reload_priv = 'Y', 'Reload ', ''),

                IF(mu.Shutdown_priv = 'Y', 'Shutdown ', ''),

                IF(mu.Process_priv = 'Y', 'Process ', ''),

                IF(mu.File_priv = 'Y', 'File ', ''),

                IF(mu.Grant_priv = 'Y', 'Grant ', ''),

                IF(mu.References_priv = 'Y', 'References ', ''),

                IF(mu.Index_priv = 'Y', 'Index ', ''),

                IF(mu.Alter_priv = 'Y', 'Alter ', ''),

                IF(mu.Show_db_priv = 'Y', 'Show_db ', ''),

                IF(mu.Super_priv = 'Y', 'Super ', ''),

                IF(mu.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),

                IF(mu.Lock_tables_priv = 'Y', 'Lock_tables ', ''),

                IF(mu.Execute_priv = 'Y', 'Execute ', ''),

                IF(mu.Repl_slave_priv = 'Y', 'Repl_slave ', ''),

                IF(mu.Repl_client_priv = 'Y', 'Repl_client ', ''),

                IF(mu.Create_view_priv = 'Y', 'Create_view ', ''),

                IF(mu.Show_view_priv = 'Y', 'Show_view ', ''),

                IF(mu.Create_routine_priv = 'Y', 'Create_routine ', ''),

                IF(mu.Alter_routine_priv = 'Y', 'Alter_routine ', ''),

                IF(mu.Create_user_priv = 'Y', 'Create_user ', ''),

                IF(mu.Event_priv = 'Y', 'Event ', ''),

                IF(mu.Trigger_priv = 'Y', 'Trigger ', ''))),

          ' ',

          ', ')

          AS `Privileges`

  FROM mysql.user mu

ORDER BY mu.Host, mu.User

List of privileges for TABLES:

SELECT mt.host `Host`,

       mt.user `User`,

       CONCAT(mt.Db, '.', mt.Table_name) `Tables`,

       REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`

  FROM mysql.tables_priv mt

 WHERE mt.Table_name IN

          (SELECT DISTINCT t.table_name `tables`

             FROM information_schema.tables AS t

            WHERE    t.table_type IN

                        ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')

                  OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)

ORDER BY mt.Host,

         mt.User,

         mt.Db,

         mt.Table_name;

List of privileges for VIEWS:

SELECT mv.host `Host`,

       mv.user `User`,

       CONCAT(mv.Db, '.', mv.Table_name) `Views`,

       REPLACE(mv.Table_priv, ',', ', ') AS `Privileges`

  FROM mysql.tables_priv mv

 WHERE mv.Table_name IN (SELECT DISTINCT v.table_name `views`

                           FROM information_schema.views AS v)

ORDER BY mv.Host,

         mv.User,

         mv.Db,

         mv.Table_name;

List of privileges for TABLE COLUMNS:

SELECT mtc.host `Host`,

       mtc.user `User`,

       CONCAT(mtc.Db,

              '.',

              mtc.Table_name,

              '.',

              mtc.Column_name)

          `Tables Columns`,

       REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`

  FROM mysql.columns_priv mtc

 WHERE mtc.Table_name IN

          (SELECT DISTINCT t.table_name `tables`

             FROM information_schema.tables AS t

            WHERE    t.table_type IN

                        ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')

                  OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)

ORDER BY mtc.Host,

         mtc.User,

         mtc.Db,

         mtc.Table_name,

         mtc.Column_name;

List of privileges for VIEW COLUMNS:

SELECT mvc.host `Host`,

       mvc.user `User`,

       CONCAT(mvc.Db,

              '.',

              mvc.Table_name,

              '.',

              mvc.Column_name)

          `Views Columns`,

       REPLACE(mvc.Column_priv, ',', ', ') AS `Privileges`

  FROM mysql.columns_priv mvc

 WHERE mvc.Table_name IN (SELECT DISTINCT v.table_name `views`

                            FROM information_schema.views AS v)

ORDER BY mvc.Host,

         mvc.User,

         mvc.Db,

         mvc.Table_name,

         mvc.Column_name;

List of privileges for PROCEDURES:

SELECT mp.host `Host`,

       mp.user `User`,

       CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,

       REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`

  FROM mysql.procs_priv mp

 WHERE mp.Routine_type = 'PROCEDURE'

ORDER BY mp.Host,

         mp.User,

         mp.Db,

         mp.Routine_name;

List of privileges for FUNCTIONS:

SELECT mf.host `Host`,

       mf.user `User`,

       CONCAT(mf.Db, '.', mf.Routine_name) `Procedures`,

       REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`

  FROM mysql.procs_priv mf

 WHERE mf.Routine_type = 'FUNCTION'

ORDER BY mf.Host,

         mf.User,

         mf.Db,

         mf.Routine_name;

Oracle BI Discoverer 11g Installation

We are going to install OBI 11g with following sequences.

• Install Oracle database 11.2.0.3 standard edition database.
• Install JDK jdk 1.6 or 1.7
• Install Repository Creation Utility (RCU) 11.1.0.6 (ofm_rcu_win_11.1.1.6.0_disk1_1of1.zip)
• Weblogic Server 10.3.6 (wls1036_generic.jar)
• Oracle Portal, Reports,Forms & Discoverer 11g and patching with 11.1.6
• Run configuration for Discoverer

pfrd 11.1.1.2 Includes

V18772-01_1of4.zip

V18772-01_2of4.zip

V18772-01_3of4.zip

V18772-01_4of4.zip

pfrd 11.1.1.6 Includes

V29883-01.zip

./config.sh to configure 11.1.1.6

Environments:

Make sure the /etc/hosts are like this

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

#::1 localhost6.localdomain6 localhost6

192.168.10.11 biserver.domain.com biserver

Packages:

With an Oracle Database 11g installed, you need only to install one package - compat-db

Install OBI Repository Creation Utility (RCU)

Download the installation files from the http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html on your desktop.

(Note** It has to be the same version as BI software). The name of the installer would be fm_rcu_win_11.1.1.6.0_disk1_1of1.zip

Unzip the file, navigate to the bin directory and start the RCU => rcu.bat.

Welcome > Next

Accept default of Create. Click Next:

·         Leave default of Oracle Database:

·         Set hostname

·         Set port

·         Set service name

·         Set username

·         Set password

·         Leave Role as SYSDBA 

Select the Oracle Business Intelligence Component.This action automatically selects the Metadata Services (MDS) schema (under the AS Common Schemas group).

So at this point, Repository Creation succeded!!

Our next step is to Install Weblogic server & creating middleware home

Install Oracle WebLogic Server and Create the Middleware Home:

Environment Preperation:

We need to make sure that weblogic is using jdk1.7.0_17 while running installer.

SETTING JAVA HOME:

We have installed JDK1.7 into following directory:

Cd /usr/java/jdk1.7.0_17/bin

[oracle@test bin]$ ./java -version

java version "1.7.0_17"

Java(TM) SE Runtime Environment (build 1.7.0_17-b02)

Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

[oracle@test bin]$ cd..

[oracle@test jdk1.7.0_17]$ echo $PATH

/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/fnadvi/bin

[oracle@test jdk1.7.0_17]$ pwd

/usr/java/jdk1.7.0_17

[oracle@test jdk1.7.0_17]$ export JAVA_HOME=$PWD

[oracle@test jdk1.7.0_17]$  echo $JAVA_HOME

/usr/java/jdk1.7.0_17

[oracle@test jdk1.7.0_17]$ export PATH=$JAVA_HOME/bin:$PATH

[oracle@test jdk1.7.0_17]$ echo $PATH

/usr/java/jdk1.7.0_17/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/fnadvi/bin

[oracle@test jdk1.7.0_17]$ cd ../

[oracle@test java]$ java -version

java version "1.7.0_17"

Java(TM) SE Runtime Environment (build 1.7.0_17-b02)

Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

Install weblogic 10.3.6

Make sure that the weblogic is running using jdk1.7.0 java binaries by running it from java directory, as shown below:

[oracle@test distr]$ /usr/java/jdk1.7.0_17/bin/java -jar wls1036_generic.jar

Extracting 0%....................................................................................................100%

Now that the Weblogic server is installed. Our final step is installing Oracle Discoverer 11g.

Installing Forms,Reports & Discoverer 11.1

1.  We will be installing Oracle Portal, Reports, Forms & Discoverer 11.1.1.2 and then will pathc it with 11.1.1.6

NOTE: Select Install Software - DO NOT CONFIGURE****

We'll do that after applying patches.

Patching to 11.1.1.6:

Configure Discoverer under 11.1.1.6 patched home:

Once everything is installed and patched. Run configure Portal,Forms,Reports & Discoverer.
Configure CONFIG.sh 

cd /u01/app/oracle/middleware/as_1/bin

./config.sh

At this point.....Installation of Oracle Discoverer 11g has been successfully installed.


Just one additional task is, you need to copy the database tns file, (where EUL Schema resides) needs to be copied over to Middleware Instance Directory. Ie, for my case it's

/u01/app/oracle/middleware/asinst_1/config

The link to use Discover would be prety much like this:http://hostname:8888/discoverer/viewer OR 
http://hostname:8090/discoverer/viewer


Note that, even if webcache is down, you can still access Discoverer with the 8888 port!!!

Move SQL Server transaction log files to a different location via TSQL

To optimize I/O performance of a database, it's always a good idea to keep LOG file & DATA file in separate physical drives. 

The transaction log file records every data change and DML transaction executed in the database.  Writing to the transaction log file is sequential in nature as compared to the database files which are typically random I/O.  As such, placing the log file on separate physical disk from database will allow the disk to work in sequential manner and perform optimally.  

I'm going to show a demonstration of moving LOG files to another drive.

1: Capture database and transaction log file information

USE AdventureWorks
GO

sp_helpfile
GO

2: Set database to single user mode and detach database

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO

-- Detach the database
sp_detach_db 'AdventureWorks'
GO

*** Now the database is detached.  Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer.  Once this is completed, we can attach the database with SQL Server database log file at new location with the following script:

3: Attach database with log file at new location

USE master
GO

-- Now Attach the database
sp_attach_DB 'AdventureWorks',
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
GO

4. Validate the LOG moving

After the final attach command  transaction log file has been moved to new location and database is operational with log file on new location.  Verifying the new database transaction log location can be accomplished by re-running 

USE AdventureWorks
GO

sp_helpfile
GO

Bringing a database out of Restoring state in SQL Server

What if I restored a database with “NO RECOVERY” option keeping in mind that I’d keep continuing with another Differential backup set / Transactional backup set next. But then I’ve decided not to proceed further?

At this moment, database is in “Restoring” state and inaccesible.

I need to run following script bringing a database back from restoring state to normal.

RESTORE DATABASE

WITH RECOVERY