TSQL: Database Permission Excercise for 70-462

Here’s the TSQL for an exercise, involving database permissions, from the 70-462. Explanatory comments are included.

CREATE DATABASE Saturn;
GO
 
USE Saturn;
GO
CREATE ROLE Moon_Table_Editors;
GO
 
-- Create tables
CREATE TABLE Mimas 
(
	id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
	column1 VARCHAR(100) NOT NULL
);
GO
CREATE TABLE Thethys 
(
	id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
	column1 VARCHAR(100) NOT NULL
);
GO
CREATE TABLE Hyperion
(
	id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
	column1 VARCHAR(100) NOT NULL
);
GO
 
CREATE SCHEMA Orbits;
GO
 
-- Add permissions to the tole
GRANT SELECT, REFERENCES, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SCHEMA::Orbits TO Moon_Table_Editors;
GO
 
-- Check these permissions
SELECT dp.name, pm.[permission_name]
FROM sys.database_principals dp
INNER JOIN sys.database_permissions pm 
	ON pm.grantee_principal_id = dp.principal_id 
WHERE dp.[name] = 'Moon_Table_Editors';
 
-- Now we want to modify these permissions to include only SELECT and REFERENCES
REVOKE INSERT, UPDATE, DELETE, VIEW DEFINITION ON SCHEMA::Orbits TO Moon_Table_Editors;
 
-- Check this has worked...
SELECT dp.name, pm.[permission_name]
FROM sys.database_principals dp
INNER JOIN sys.database_permissions pm 
	ON pm.grantee_principal_id = dp.principal_id 
WHERE dp.[name] = 'Moon_Table_Editors';
 
-- Now create a new role  called Moon_Table_Designers
CREATE ROLE Moon_Table_Designers;
GO
 
-- This role should be able to create tables in the Orbits schema only
GRANT CREATE TABLE TO Moon_Table_Designers;
GO
GRANT ALTER ON SCHEMA::Orbits TO Moon_Table_Designers;
GO
 
-- Create a user to test this
CREATE USER [user1] FOR LOGIN [user1];
GO
EXEC sp_addrolemember 'Moon_Table_Designers', 'user1';
GO
 
-- Impersonate this user
EXECUTE AS USER = 'user1';
GO
 
CREATE TABLE dbo.ShouldNotCreate
(
	id INTEGER NOT NULL PRIMARY KEY
);
GO
 
CREATE TABLE Orbits.TableShouldCreate
(
	id INTEGER NOT NULL PRIMARY KEY
);
GO
 
REVERT;
GO
 
-- Check which tables exist
SELECT *
FROM sys.tables
WHERE [name] IN ('ShouldNotCreate', 'TableShouldCreate');
GO

TSQL: Create SQL Logins using certificates and asymmetric keys 70-462

Here’s some TSQL for creating sql logins using certificates and asymmetric keys. Explanatory comments are included.

CREATE CERTIFICATE myCert 
ENCRYPTION BY PASSWORD = 'Secret2014'
WITH SUBJECT = 'My certificate for sql logins';
 
USE [master]
GO
 
-- create sql user with the cert
CREATE LOGIN [sql_user_a] FROM CERTIFICATE [myCert];
GO
 
-- create key
CREATE ASYMMETRIC KEY myKey
WITH ALGORITHM = RSA_2048 
ENCRYPTION BY PASSWORD = 'Secret2014'; 
GO
 
-- sql user with key
CREATE LOGIN [sql_user_b] FROM ASYMMETRIC KEY [myKey];
GO

TSQL: Partially Contained Databases 70-462

Here’s some TSQL for the Partially Contained Databases section of the 70-462. Explanatory comments are included.

-- enable show advanced options and view current config 
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure;
GO
-- enable the feature
EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
-- view changed options
EXEC sp_configure;
GO
 
-- create the partially contained database
CREATE DATABASE partial_containment_db
CONTAINMENT = PARTIAL;
GO
 
USE partial_containment_db;
GO
-- create a contained user from a windows account
CREATE USER [contso\contained_user_b];
GO 
-- create a contained user that uses sql auth
CREATE USER [contained_user_c] WITH PASSWORD = 'Pa$$w0rd';
GO

EFK: Free Alternative to Splunk Using Fluentd

Here is an updated version of the instructions given at Free Alternative to Splunk Using Fluentd. The installation was performed in CentOS 6.5. 1. Install ElasticSearch

mkdir /opt/src
cd /opt/src
wget https://download.elasticsearch.org/elasticsearch/elasticsearch/elasticsearch-1.2.1.noarch.rpm
rpm -ivh elasticsearch-1.2.1.noarch.rpm
/sbin/chkconfig --add elasticsearch
service elasticsearch start

# Move default file locations if required
mkdir /data/elasticsearch
mkdir /data/elasticsearch/data
mkdir /data/elasticsearch/tmp
mkdir /data/elasticsearch/logs
vi /etc/elasticsearch/elasticsearch.conf
chown -R elasticsearch:elasticsearch /data/elasticsearch/
service elasticsearch restart

# index status http://:9200/A/_status
# cluster health http://:9200/_cluster/health

2. Install Apache

yum install httpd
chkconfig httpd on
service httpd start

3. Install Kibana

cd /opt/src
wget https://download.elasticsearch.org/kibana/kibana/kibana-3.1.0.tar.gz
tar xvzf kibana-3.1.0.tar.gz 
mv kibana-3.1.0 kibana
mv kibana /var/www/html/

4. Install fluentd

- get script http://toolbelt.treasuredata.com/sh/install-redhat.sh
 chmod +x  /usr/bin/scripts/install-redhat.sh
-- Execute script
 /usr/bin/scripts/install-redhat.sh

yum install libcurl-devel # Run if you get this error: Error installing fluent-plugin-elasticsearch:
/usr/lib64/fluent/ruby/bin/fluent-gem install fluent-plugin-elasticsearch
vi /etc/td-agent/td-agent.conf
<pre>
<source>
  type syslog
  port 42185
  tag syslog
</source>

<match syslog.**>
  type elasticsearch
  logstash_format true
  flush_interval 10s # for testing
</match>
</pre>
# Start the agent
/etc/init.d/td-agent start

5. Forward rsyslog to fluentd

vi /etc/rsyslog.conf

Add the text…

*.* @127.0.0.1:42185

Restart syslog and check the log for activity…

service rsyslog restart
# inspect the log for td-agent
tail /var/log/td-agent/td-agent.log -n 50

If you browse to http://hostname/kibana you shoul dbe able to get started and view syslog data coming in.


TSQL: User-Defined Server Roles 70-462

Just a little TSQL for the User-Defined Server Roles exercise in the 70-462 training materials. Explanatory comments are included.

USE [master];
GO
 
-- Create server role
CREATE SERVER ROLE [Login_Manager];
GO
 
-- Assign permission to server role
GRANT ALTER ANY LOGIN TO [Login_Manager];
GO
 
-- Add login to role
ALTER SERVER ROLE [Login_Manager] ADD MEMBER [CONTSO\domain_group_b];
GO
 
-- Create server role
CREATE SERVER ROLE [Database_Creator];
GO
 
-- Allow role to create databases
GRANT CREATE ANY DATABASE TO [Database_Creator];
GO
 
-- Add login to this role
ALTER SERVER ROLE [Database_Creator] ADD MEMBER [CONTSO\domain_user_b];
GO