Tuesday, March 11, 2014

Protect unix/linux directory using .htaccess and .htpasswd

This is a note on protecting unix/linux directory using .htaccess and .htpasswd.
The article below is copied from [1].

References:
[1] HTPassword Tutorial
[2] Password Protection with .htaccess & .htpasswd
[3] Apache HTTP Server Tutorial: .htaccess files

Password Tutorial

If you would like to have a set of web pages that are protected, requiring a username/password to gain access, this tutorial will show you how to set it up. This is geared towards the Unix Apache httpd servers used on holly, lamar, and www.colostate.edu. If you are using another web server, you'll need to check that server's documentation to see how to do this.

Steps to Password-protect a Directory

First, create a subdirectory in your web area. For the sake of this tutorial, I have created the "protect" directory. Set the permissions on the directory so that the server has read/execute. I do this by using the local command chgrp-www to set the group to the www group. This is the group that the server runs under at Colorado State University for the lamar, holly and www servers. I have used the -sd flag which sets "set group id" for a directory. This will then force any files you create within the protect directory to the www group, so if you ftp files to this directory they will be automatically readable by the server but not by any other user on the system. I then cd into the protect directory.
cd ~ric/public_html
mkdir protect
chmod g+r,g+x,o-r,o-x protect
chgrp-www -sd protect
cd protect
Next you must create a .htaccess file inside the directory you want protected. You can use either the vi or pico editors on the supported systems mentioned above or ftp the file to this directory. If you are new to unix or know little about vi then I suggest you use the pico editor or ftp the .htaccess file. The command to edit with pico is "pico .htaccess". The .htaccess file should contain the following lines. The items in bold are things you will want to change depending on the location of the AuthUserFile and content of AuthName.
AuthUserFile /z/ric/secret/.htpasswd
AuthGroupFile /dev/null
AuthName "Ric's protected files"
AuthType Basic

<Limit GET>
require valid-user
</Limit>
The AuthName is what the user will see when they're prompted for a password - something to the effect of "Enter the username for Ric's Protected files". The AuthUserFile is location of the password file and should be not accessible with a url on the server for security reasons. This is a full unix path and the permissions should be set up like the "protect" directory using the chmod and chgrp-www commands above so the only one that can read this file is the owner and the server. To get the full path of a directory, cd to that directory and enter the command "pwd" to print the working directory path. Now you'll have to set up the password file. You'll need to use the htpasswd program. It is included with the Apache httpd server.
First cd to the directory that contains the password file. In this example the password file is called .htpasswd and is in the directory /z/ric/secret/ as indicated by the AuthUserFile file entry in the .htaccess file. For every username you want to add to the password file, enter the following. (the -c is only required the first time; it indicates that you want to create the .htpasswd file).

   cd
   mkdir secret
   cd secret
   htpasswd -c .htpasswd pumpkin
     [ you're prompted for the password for pumpkin]
     [ if you have other users enter the following. Don't use the -c]
   htpasswd .htpasswd user2
   htpasswd .htpasswd user3
Again, make sure the permissions are set up like the "protect" directory using the chmod and chgrp-www commands above so the only one that can read files in the "secret" directory is the owner and the server. Here is the protected page using the above setup to password protect this page. The username is "pumpkin" and password is "pie".

Thursday, March 6, 2014

DOS Batch file to get the number of rows in all the tables and views in a MSSQL database

Store the code below as a bat file, can obtain the number of rows in all the tables and views.--
--

 @echo off

  set db=Northwind

  FOR /F "skip=2" %%G IN ('sqlcmd -E -S localhost -d %db% -Q "set nocount on; set ansi_warnings off; select TABLE_NAME from INFORMATION_SCHEMA.TABLES order by table_name;"') DO (
    REM echo dump table %%G ..
    FOR /F "skip=2" %%X IN ('sqlcmd -E -S localhost -d %db% -Q "set nocount on; set ansi_nulls on; set ansi_warnings on; select count(*) from %%G;"  -s "," -W') DO (
        echo [%%G]: %%X
    )
  )

DOS batch file to dump all tables/views in a MSSQL database

Store the code below as a bat file: dump_csv.bat.
--

@ECHO OFF
REM This script dumps all tables in the given database in CSV format.
REM Author: X.C. 3/6/2014
CLS

set db=Northwind
set mode=%1

IF NOT "%mode%" == "all" set mode=test

echo -----------------------------------------------------------------
echo This script dumps all tables in the given database in CSV format.
echo Author: X.C. 3/6/2014
echo.
echo Usage: dump_csv.bat [mode]
echo   If mode = all, the entire tables are dumped.
echo   If mode = test, only first row of each table is dumped.
echo -----------------------------------------------------------------
echo.
echo Mode: %mode%
echo Database: %db%
echo.
echo ==Tables to dump==

FOR /F "skip=2" %%G IN ('sqlcmd -E -S localhost -d %db% -Q "set nocount on; set ansi_warnings off; select TABLE_NAME from INFORMATION_SCHEMA.TABLES order by table_name;"') DO Echo %%GG

echo.
choice /m "Do you want to continue "
if errorlevel 2 goto Lexit

echo.

IF "%mode%" == "test" (
  FOR /F "skip=2" %%G IN ('sqlcmd -E -S localhost -d %db% -Q "set nocount on; set ansi_nulls on; set ansi_warnings on; select TABLE_NAME from INFORMATION_SCHEMA.TABLES order by table_name;"') DO (
    echo dump table %%G ..
    sqlcmd -E -S localhost -d %db% -Q "set nocount on; set ansi_warnings off; select top 1 * from %%G;" -o output/%%G.txt -s "," -W
  )
)

IF "%mode%" == "all" (
  FOR /F "skip=2" %%G IN ('sqlcmd -E -S localhost -d %db% -Q "set nocount on; set ansi_nulls on; set ansi_warnings on; select TABLE_NAME from INFORMATION_SCHEMA.TABLES order by table_name;"') DO (
    echo dump table %%G ..
    sqlcmd -E -S localhost -d %db% -Q "set nocount on; set ansi_warnings off; select * from %%G;" -o output/%%G.txt -s "," -W
  )
)

:Lexit
echo.
pause


REM Note 1: If do "set ansi_nulls off; set ansi_warnings off", then view information cannot be retrieved.
REM Note 2: To obtain table values only, use: where TABLE_TYPE = 'BASE TABLE'
REM             To obtain view values only, use: where TABLE_TYPE = 'VIEW'


To dump just one table T_data from a database DB, use:

echo dump table T_data ..
sqlcmd -E -S localhost -d DB -Q "set nocount on; set ansi_warnings off; select * from T_data;" -o output/T_data.txt -s "," -W

To bulk insert data from generated dump files, use the command below. Note the "FIRSTROW" is 1-based and not 0-based. In the dump file, the first row will be column names, the second row is separator line "----", so data starts from the 3rd line.

USE [DB]
GO

BULK INSERT [T_data]
    FROM 'C:\\output\\T_data.txt'
    WITH
    (
        FIRSTROW = 3,
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a'
    )
GO


Blog Archive

Followers