The Ji Village News

季庄新闻–Haidong Ji’s Blog

I found out recently that a schema owner does not necessarily have right to create objects for its own schema, but can drop and grant permissions for its objects, which is kind of strange.

Try this:

1. Create 2 logins:

USE [master]
GO
CREATE LOGIN [Login1] WITH PASSWORD=N’xxx’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
CREATE LOGIN [Login2] WITH PASSWORD=N’xxx’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [AdventureWorks]
GO
CREATE USER [Login1] FOR LOGIN [Login1]
CREATE USER [Login2] FOR LOGIN [Login2]
GO

2. Grant create schema permissions to both Login1 and Login2.

use adventureworks
go
grant create schema to login1
grant create schema to login2
go

3. Connect to Sql Server as login1, do

create schema Login1Schema

4. With the same connection, do

create table Login1Schema.T1 (C1 int)

and it will fail. It is a little strange. Login1 is indeed the owner of the schema, yet it cannot create objects within the schema it owns.

5. Connect to Sql Server as sa, do

Use AdventureWorks
go

create table Login1Schema.TableCreatedBySa (C1 int)
go

grant create table to Login1
go

6. Connect to Sql server as Login1, do

create table Login1Schema.T1 (C1 int)
go

grant select on Login1Schema.T1 to Login2
grant select on Login1Schema.TableCreatedBySa to Login2
go

7. Connect to Sql server as Login2, do

select * from Login1Schema.T1
select * from Login1Schema.TableCreatedBySa
go

8. In the same session, do

create schema Login2Schema
go

9. Connect to Sql Server as sa, do

use AdventureWorks
go
create table Login2Schema.T1 (C1 int)
go

10. Connect to Sql Server as Login2, do

select * from Login2Schema.t1
go
drop table Login2Schema.T1
go
drop schema Login2Schema
go

So, what did our little test prove?

1. By default, a schema owner cannot create objects in its own schema;
2. By default, a schema owner can grant permissions to other users on objects it owns;
3. By default, a schema owner can drop objects it owns, even if it cannot create them in the first place;
4. By default, a schema owner can drop its own schema, provided it is empty.

To clean up, drop all those tables, users, schemas, and logins.

In Sql Server 2000, C2 auditing is a US government standard that monitors database security. When it is enabled, a trace will be created to collect all Security Audit related events, 21 or so of them. You can find what those events are by browsing through them in Profiler. By default, the trace file will be saved at your default data folder. It is named in the format of audittraceYYYYMMDDHHMMSS.trc. Its file size is 200 mb, and rolls over automatically when that limit is reached.

In Sql Server 2000, C2 is off by default and you cannot enable it using Enterprise Manager. You will have to do it using sp_configure and set the “c2 audit mode” property. It needs a server restart for it to take into effect.

C2 auditing still exists in Sql Server 2005, and you can enable it through Management Studio. Just right click on the server, pick properties, then go to Security tab to enable it. It works the same way as C2 in Sql Server 2000. A service restart is needed in order for it to take into effect. Although you cannot enable C2 for Sql Server 2000 in Enterprise Manager, you can enable it with Sql Server Management Studio.

Starting with Service Pack 2 for Sql Server 2005, in addition to C2, Sql Server 2005 can also use Common Criteria Compliance. You can pick this option by looking at the property page of the server, security tab. You can also do it with script by changing a new parameter using sp_configure. The new parameter is called “common criteria compliance enabled”. Again, a service restart is needed for it to take into effect. Common Criteria is a standard developed by a few countries and adapted by ISO.

Simply making the above change does not make the server Common Criteria compliant. You will also need to run a trace to audit security events, just like C2. The audit script is available here.

Frankie’s Deli is a quick deli store in Yorktown Center, Lombard, Illinois. It is close to my current work location. In my opinion, this deli store makes the best sandwiches at unbeatable prices. All ingredients are top quality. My favorites are:

1. Frankie’s special, with imported capicola, marinated eggplant, and optional provolone cheese. You get to pick mild or hot capicola. I always pick the hot one, which is not hot by my standard. I usually say no to the cheese, because I feel it sort of diminishes the delicious taste that the marinated eggplant and capicola create together;

2. Grilled muffuletta panini, with ham, salami, provolone cheese. After the olive oil is seeped into the flat muffuletta bread, together with olive spread on top of it, the whole thing imparts a wonderful flavor to die for;

3. Italian Sub with provolone, salami, capicola, mortadella, lettuce, tomato, onion, and oil. This staple sandwich won’t disappoint.

This store also offer great soup, and it complements the sandwiches very well. That’s all I order most days.

Here is a picture of Italian Sub:

Here is a picture of Cream of Asparagus soup:

Here is its address:

4 Convenience Center
Lombard, IL 60148
(630) 627-7977

I am going through some changes, dear reader: I have decided to quit my corporate day job and start my own consulting/training business, starting on April 9th, 2007. I could use your help here: I’d appreciate it greatly if you could offer me leads on Sql Server, Oracle, and MySQL consulting and training gigs. I will write more on my business plan in a future post.

I wrote here before about software I considered essential. With my fledging new business, I thought I need to get some cheap and reliable mobile communication services. Hence, this post.

1. Voice communication

Which cell phone company do you recommend? I have looked at all the major ones. At this point, I am leaning toward Cingular’s GoPhone Pay As You Go ($100, 25c/minute, valid for one year) and Virgin Mobile’s Pay By the Minute plan ($20 every 90 days, 18c/minute). Cingular seems to have better phones. The things I am not clear yet are coverage issues, and rates for web browsing when I need to check my email on the road.

I downloaded Skype and tested it out today. It showed some promises. Skype sells a service called SkypeOut, $30 per year for unlimited phone calls to US and Canada. Combining that with a cheap cell phone, it looks like that should satisfy voice communication needs. Any alternatives on PC to phone service will be appreciated.

2. Laptop recommendations

I am in the market for a laptop. I’ve seen a HP dual core with 2 GB of memory selling around $1000 that I am interested. I need to look at that again, I do remember it got a built-in web cam and microphone, which can be nice. What kind of laptop(s) do you recommend?

3. Chat and video cam service

I’ve used both MSN chat and Google Talk, only for a handful of times, though. I like Google Talk’s simple and elegant interface (no software installation needed). What chat and video cam service do you recommend? I guess that can be a particularly good way to stay in touch with friends and family when one is on the road. What do you think of Skype’s chat and video conference functions? What other advice do you have?

4. I think I will be using GMail exclusively. Google Calendar and Applications are also interesting. What other advice do you have for a consultant on the road?

Here are a few handy tips I learned lately:

1. When using sp_configure, you don’t need to type the whole parameter string. You just need to type enough of it for Sql Server to be uniquely identify it. For example,

sp_configure ’show ad’

will resolve to

sp_configure ’show advanced’

2. In Query Analyzer or Management Studio, to get help on a keyword, system stored procedure, DDL/DML statements, etc, highlight them, then press Shift-F1;

3. When a process takes a long time to finish, sometimes you want to kill it. Killing a process will roll back all the changes. Keep in mind the roll back can take a long time also. To find out the roll back status, use

kill with statusonly

Todd Stroger, son of former Cook County Board President John Stroger, was elected to his father’s position recently. John had to resign due to a stroke last year, after serving tirelessly and heroically for 12 years, on behalf of good ‘ol tax-paying residents of Cook County, yours truly included. He left with 500 million dollars deficit in the county’s books in his wake.

Todd, part of the new generation to revitalize the political process, promised to make hard decisions and dramatic changes. Not too long after he was sworn in, he appointed his cousin, Donna Dunning, to be the county’s Chief Financial Officer.

Now Donna was County Budget Director before her new role, during John Stroger’s rein. (I think I mentioned that the county currently has a 500 million dollars budget hole.) Donna earned 142,840 dollars last year. She has promised she would keep this salary and wouldn’t give herself a raise. She has a degree in accounting from University of Arkansas and MBA from Northwestern University.

Todd said: “They have to be close to the president. The president has to trust them. There is no one I trust better than Donna Dunning. I don’t care what her name is or what her blood line is. She had great credentials.”

Donna said: “I am qualified for this job. And there will always be critics. My mother told me a long time ago that Jesus walked our earth and he had critics”

Soon afterwards, Chicago Mayor Richard M. Daley was re-elected for his 6th term. (Chicago is inside Cook County) He has served the City of Chicago since 1989 and will continue doing so until 2011, if he will not run again at the end of 2010. Richard M. Daley is the son of Richard J. Daley. Daley the elder was the longest-serving major, for 21 years, before the son beat daddy’s record in the last mayoral election. Richard M.’s little brother, Bill, served as Secretary of Commerce for President Bill Clinton, before George W. Bush took his daddy, George H. W. Bush’s job as president. (George H. W. Bush is a son of US senator) After Bill Daley left the revolving doors of Washington DC, he has been a high flying campaigner, big company executive, among other things. If memory serves, I think at least one of Richard M.’s other siblings/offsprings also serve in the Chicago City Council.

Meanwhile, ComEd, the dominant electric utility of Illinois, is fighting the state legislature for rate hikes. One of ComEd’s arguments is that cost really goes up. At the same time, ComEd’s CEO, John Rowe, took in 27 million dollars income last year alone. ComEd has contributed handsomely for state legislature and governor elections, so its chance shouldn’t be too bad.

In other news, the presidential election is running hot and getting hotter every day, since well more than 2 years before it actually happens. Experts have predicated that 2008 will be the most expensive presidential race ever, with campaign spending reaching a staggering 1 billion dollars. To be taken seriously by pundits and journalists, a candidate will need to raise at least $100 million by the end of 2007. With so many candidates fighting for positions and making headlines, I am tempted to jump into the race myself. I figure what I lack in money and connections, I more than make up with fame and charisma

In Oracle, there are 3 places that I know of that are important for monitoring: the bdump, where background process error is stored; udump, where user trace error is dumped; and cdump, the core dump, where Oracle internal error is dumped. cdump is in the binary format, you can use “strings -a” to look at things inside. All other trace and log files are text files that you can open up and read for yourself.

On Unix/Linux/Solaris based systems, these folders are located under:

$ORACLE_BASE/admin/$ORACLE_SID/bdump, udump, and cdump

To find out your $ORACLE_BASE and $ORACLE_SID, you can do:

echo $ORACLE_BASE and echo $ORACLE_SID

There is also the alert log, usually in the format of alert_$ORACLE_SID.log format, under the bdump folder.

There can be numerous trace and log files in these folders. To troubleshoot and correlate events with some problems, you can do:

ls -ltr

to sort files in ascending order according to date and time, and open up the file that is closest to the time when problem occurred. Those trace and log files may hold telltale signs of what you need to examine further.

For Sql Server, by default, the error log file is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. And that is the only place you need to worry about. If you turn on certain trace, the trace info will also be logged into error log.

Don’t confuse Oracle’s .trc file with Sql Server’s .trc file. Oracle’s trace file is in text format, generated when certain trace flags are turned on. Sql Server’s trace file are in binary format, and can be read using Sql Server Profiler.