Xsql Script Executor

[Melissa Russian]

Iwould have to say that this is indeed a good idea for development environment. We have been implementing this on our dev environment. I have to agree with Brian that the problem with this is, of course, keeping the permissions up-to-date. And I would like to share our own way of dealing with this. We have also created a SQL Server Agent for the script that grants permission on all stored procedure to a particular role but as a DBA, i didn't assign a schedule for it to run, instead, I created an alert for that when triggered, will then trigger the SQL Server Agent that runs the script. So when a developer creates or drop&re-create a stored procedure, all he has to do is to execute a raiserror to trigger the SQL Server Agent that grants execute permission on all stored procedure for that particular role.

That works well, too. You know, that technique solves an issue I've seen posted here a lot... how to give developers the ability to create objects as dbo but without giving them the ability to carte-blanche change security settings!

So if I'm creating a sproc called GetRecentTrades I will grant execute permissions to USER_EXECUTE and possibly ADMIN_EXECUTE. I don't care which users will be given permission to use that sproc later - that can be handled in production (ultimately). This means that I can manage permissions at the initial coding stage rather than in implementation to each successive environment - avoiding the risk that permissions for some action might be missed in live.

The NO_ACCESS role is standard and all permissions are always denied to every object (sprocs, tables, views etc.) - with CASCADE. This makes decommissioning selected user accounts easier. I'm sure many of you have experienced the problem where a user account may have more than one use - but not necessarily documented. I can add this user to NO_ACCESS in one or more databases and if something unexpectedly breaks it's relatively simple to take the user out of NO_ACCESS in the relevant database(s) - especially when compared to how you'd recover after deleting that user.

Typically, I recommend creating the permissions with the stored proc and then whatever change control mechanism you use, stored procedure and permissions are stored together to be run together. Eliminates a lot of error.

I am surprised by the number of of client's DBA's who are held responsible for the integrity of the databases under their jurisdiction - whilst at the same time have absolutely no say in the security/access models used by developers.

I'd also add to the comments about production and use of change management procedures. I'd never allow a procedure that automatically granted changed permissions to users to run in production (or for that matter test). Permissions to production data should be controlled, and in my view test should replicate the live environment - with the exception of the specific change that you are testing.

Enclosed is the code for a stored proc which incorporates the granting of permissions to stored procs, table functions and regular functions. By creating this procedure in master database, I can call it from any user database to maintain permissions for the db_executor role.

Liquibase normally processes database changes in changelogs over a JDBC connection. However, the JDBC connection (or executor) sometimes cannot process highly specialized SQL. In these cases, you might want to use a different executor.

A native executor is designed to run scripts in a way that is directly compatible with your target software environment. For example, if you use PostgreSQL's psql native executor to access your database, you may have written SQL scripts uniquely suited to psql. Liquibase uses the jdbc native executor by default, so some scripts written for another executor may have unexpected behavior.

However, Liquibase Pro lets you use runWith to call on the PSQL (PostgreSQL), SQL Plus (Oracle), and SQLCMD (MSSQL Server) native executor script runners when deploying changesets. This lets you continue using scripts that you wrote before installing Liquibase. If you use MongoDB, the mongosh native executor is required. You can also write a custom executor yourself.

So I have a SQL database project which tracks our application's database schemas, stored procedures, etc. It also has deployment script generation sections, including pre- and post-deployment scripts. Part of these scripts includes doing things like caching values of columns that have been removed so they can be moved elsewhere (usually normalized into its own table).

The problem is that the upgrade script process I have implemented, which automatically detects the version and what updates need to be applied at deploy-time, includes references to columns which no longer exist. This is fine when deploying to either an empty database (so the script executor doesn't know there's no column) or to a being-upgraded database, as it can see the column before the deployment. The script validator however doesn't like that I'm referencing columns from a table that it knows about, even if the section which references it will never get called because of the conditions around it.

My current solution is to store in a string all of the parts of the script which handle columns being removed, then running those sections dynamically using EXEC. This is a little icky to me (and EXEC is a code smell issue), though not the worst thing. This is code that's only run once per DB and is never accessible during production operation.

What I want to know is, is there a better way of doing this? Can I somehow comment to the validator that "This won't run, please ignore it"? Is this whole process terrible? (I'm not that experienced of a DB admin at all, so any expertise here would be very helpful.) If I'm doing this all wrong, how do you handle one-off scripts for database updates? I've also considered doing one-off conditional compilations using SQLCMD declarations/labels, then automatically removing sections that aren't needed for a particular version-to-version upgrade using a script.

Code sent to SQL Server is validated, prior to execution, by SQL Server itself. The query processor checks object names for existence prior to running the query, without regard for any code paths that may or may not be taken.

The only way to avoid the issue you're experiencing is to us sp_executesql exactly as it sounds like you are doing now. Since you are doing this in an upgrade script that you control, there is virtually zero chance for a SQL injection problem, assuming you aren't taking non-validated input from a user (or via a config file the user has access to).

node-executor="jdbc-command" (optional) for executing Groovy SQL scripts using Rundeck 'Command' tab.

jdbc-command node executor also can be set in the project.properties file for using jdbc-command executor for all project nodes: service.NodeExecutor.default.provider=jdbc-command

Spark properties control most application settings and are configured separately for eachapplication. These properties can be set directly on aSparkConf passed to yourSparkContext. SparkConf allows you to configure some of the common properties(e.g. master URL and application name), as well as arbitrary key-value pairs through theset() method. For example, we could initialize an application with two threads as follows:

While numbers without units are generally interpreted as bytes, a few are interpreted as KiB or MiB.See documentation of individual configuration properties. Specifying units is desirable wherepossible.

The Spark shell and spark-submittool support two ways to load configurations dynamically. The first is command line options,such as --master, as shown above. spark-submit can accept any Spark property using the --conf/-cflag, but uses special flags for properties that play a part in launching the Spark application.Running ./bin/spark-submit --help will show the entire list of these options.

Any values specified as flags or in the properties file will be passed on to the applicationand merged with those specified through SparkConf. Properties set directly on the SparkConftake highest precedence, then flags passed to spark-submit or spark-shell, then optionsin the spark-defaults.conf file. A few configuration keys have been renamed since earlierversions of Spark; in such cases, the older key names are still accepted, but take lowerprecedence than any instance of the newer key.

The default value for number of thread-related config keys is the minimum of the number of cores requested forthe driver or executor, or, in the absence of that value, the number of cores available for the JVM (with a hardcoded upper limit of 8).

Server configurations are set in Spark Connect server, for example, when you start the Spark Connect server with ./sbin/start-connect-server.sh.They are typically set via the config file and command-line options with --conf/-c.

The advisory size in bytes of the shuffle partition during adaptive optimization (when spark.sql.adaptive.enabled is true). It takes effect when Spark coalesces small shuffle partitions or splits skewed shuffle partition.

Configures the maximum size in bytes for a table that will be broadcast to all worker nodes when performing a join. By setting this value to -1 broadcasting can be disabled. The default value is same with spark.sql.autoBroadcastJoinThreshold. Note that, this config is used only in adaptive framework.

When true and 'spark.sql.adaptive.enabled' is true, Spark will coalesce contiguous shuffle partitions according to the target size (specified by 'spark.sql.adaptive.advisoryPartitionSizeInBytes'), to avoid too many small tasks.

The initial number of shuffle partitions before coalescing. If not set, it equals to spark.sql.shuffle.partitions. This configuration only has an effect when 'spark.sql.adaptive.enabled' and 'spark.sql.adaptive.coalescePartitions.enabled' are both true.

When true, Spark does not respect the target size specified by 'spark.sql.adaptive.advisoryPartitionSizeInBytes' (default 64MB) when coalescing contiguous shuffle partitions, but adaptively calculate the target size according to the default parallelism of the Spark cluster. The calculated size is usually smaller than the configured target size. This is to maximize the parallelism and avoid performance regression when enabling adaptive query execution. It's recommended to set this config to false and respect the configured target size.

3a8082e126