Archives of the TeradataForum

Message Posted: Thu, 07 Jan 2010 @ 14:44:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here are some general recommendations and some insight into implementations from my experience.

Any other suggestions in managing these types of situations are welcome as I find this to be a somewhat difficult issue with many management strategies, none of which are great.

As for storing the created tables within the user containing the SPs, I don't understand that restriction, it may depend on the application requirements.

There are considerations of Database privileges for which it makes sense for the Owner and Creator of a SP to be the same use, and it is not so much a limitation as you described, but probably a best practice that you are being urged to follow. The following will help explain why.

When using a stored procedure to create tables (or any DDL), there are 2 situations in which database rights are checked.

1. DDL is explicitly in the Stored procedure. The rights of the owner are checked to see if the Owners can CREATE the table in the targeted database, whether that be in itself or others.

2. DDL is generated dynamically in the Stored procedure then executed with a Call to DBC.SYSEXESQL.

Since the DDL cannot be compiled (as it could be variable), it is compiled at execution time of the SP.

At execution time the rights are checked on the Owner and Creator, since they don't have to be the same. This is done as a security measure to ensure the creator which compiled the SP with Dynamic SQL has the rights to do anything that the Dynamic SQL may try to do. This helps to avoid mistakes and malicious code.

From the SP manual.

"The owner and creator do not have to be the same for stored procedures with dynamic SQL. At execution time, the creator's rights are checked when the owner's rights are checked. If the creator does not exist at execution time, the system returns an error."

It make is much more manageable to create SPs and store them in the same database as the creator/owner. This way the Owner/and creator will persist.

If a developer creates the SP (with Dynamic SQL) and compiles it, then his account is deleted b/c they left the company, then the SP will be broken. Often these removals are automated, and you don't have time to catch this until there is an outage on the app using the SP.

Alternatively, the SP can be compiled with an admin account that will never go away, however, sometimes this is BAD implementation as admin accounts may have more rights than you would want to have exposed to Dynamic SQL.

I have found that creating separate Users by application area to contain/create all the SPs and Macros is a good way to cleanly segregate the database rights and allow the Application Specific SPs to be managed easily and in a more secure manner.

Any other good strategies utilized out there that are much different from this?

Thanks,

Eric W. Barner

	<Prev	Next>		<<First	<Prev	Next>	Last>>