On Thursday, March 3, 2016 at 11:27:13 AM UTC-8, Ryan Snodgrass wrote:
>
> I'd like to have a way to pass in `Execute as User` for raw SQL queries
> for tinytds
>
> I can sort of hack my way through it-
>
> *Behavior I get*
> 2.1.8p440 :001 > a = Sequel.connect(
> "tinytds://#{ENV['SQL_SERVER_development_USERNAME']}:#{ENV['SQL_SERVER_development_PASSWORD']}@#{ENV['SQL_SERVER_development_HOST']}:#{ENV['SQL_SERVER_development_PORT']}/#{ENV['SQL_SERVER_development_DB']}"
> )
> => #<Sequel::TinyTDS::Database:
> "tinytds://user:password@host:port/database">
>
> ## First thing we do is connect to the database, then run a query.
>
> 2.1.8p440 :002 > d = a['SELECT * FROM [secure].[Employee Snapshot Fact]
> [Employee Snapshot Fact]']
> => #<Sequel::TinyTDS::Dataset: "SELECT * FROM [secure].[Employee Snapshot
> Fact] [Employee Snapshot Fact]">
> 2.1.8p440 :003 > d.first
> => nil
> 2.1.8p440 :004 > d.count
> => 0
>
> ## We see that the connection doesn't work without the user passing through
>
> 2.1.8p440 :002 > d = a["EXECUTE AS USER='ADND\\user'; SELECT * FROM
> [secure].[Employee Snapshot Fact] [Employee Snapshot Fact]"]
> => #<Sequel::TinyTDS::Dataset: "EXECUTE AS USER='ADND\\user'; SELECT *
> FROM [secure].[Employee Snapshot Fact] [Employee Snapshot Fact]">
>
> 2.1.8p440 :003 > d.first
> => {:"employee key"=>126, :"job key"=>0, :"active faculty key"=>21,
> :"faculty
> appointment key"=>6, :"date key"=>20020630}
>
> ## With the Execute as User - We get a result object back working normally
>
> 2.1.8p440 :004 > d.count
> Sequel::DatabaseError: TinyTds::Error: Incorrect syntax near the keyword
> 'EXECUTE'.
> from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/
> adapters/tinytds.rb:220:in `fields'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/tinytds.rb:220:in
>
> `block in fetch_rows'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/tinytds.rb:65:in
>
> `block in execute'
> from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/
> database/connecting.rb:251:in `block in synchronize'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/connection_pool/threaded.rb:105:in
>
> `hold'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/database/connecting.rb:251:in
>
> `synchronize'
> from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/
> adapters/tinytds.rb:34:in `execute'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:952:in
>
> `execute'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/tinytds.rb:219:in
>
> `fetch_rows'
> from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset
> /actions.rb:835:in `with_sql_each'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:843:in
>
> `with_sql_first'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:661:in
>
> `single_record!'
> from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset
> /actions.rb:649:in `single_record'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:671:in
>
> `single_value'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:249:in
>
> `get'
> from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset
> /actions.rb:105:in `count'
> from (irb):4
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/railties-4.0.13/lib/rails/commands/console.rb:90:in
>
> `start'
> from
> /usr/local/rvm/gems/ruby-2.1.8/gems/railties-4.0.13/lib/rails/commands/console.rb:9:in
>
> `start'
> from /usr/local/rvm/gems/ruby-2.1.8/gems/railties-4.0.13/lib/rails/
> commands.rb:62:in `<top (required)>'
> from bin/rails:4:in `require'
>
> ## but the count method doesn't work
>
>
> 2.1.8p440 :005 > d = a['SELECT * FROM [secure].[Employee Snapshot Fact]
> [Employee Snapshot Fact]']
> => #<Sequel::TinyTDS::Dataset: "SELECT * FROM [secure].[Employee
> Snapshot Fact] [Employee Snapshot Fact]">
>
> ## We try again, this time WITHOUT the Execute as User statement
>
> 2.1.8p440 :006 > d.first
> => {:"employee key"=>126, :"job key"=>0, :"active faculty key"=>21,
> :"faculty
> appointment key"=>6, :"date key"=>20020630}
>
> 2.1.8p440 :007 > d.count
> => 4167860
>
> ## Somehow the Execute as User statement has been saved? in the query
> object and we are able to get back the row count. But then why didn't it
> work the first time?
> ## Is that the ruby object counting or is it returning from the database?
> I think it comes from the database because it only takes about a second for
> it to compute as 4 million rows in ruby is a lot to compute.
> ## Maybe my sql query isn't 100% correct
>
> *Behavior I want*
> What I'd like to have is a `.count` method that works with Execute as User
> statement, that would maybe tack on the Execute as User statement. Or just
> have the original statement work.
>
> d.count("EXECUTE AS USER='ADND\\user';")
> => 12345678
>
Submitting multiple statements is unsupported by Sequel. That doesn't mean
it won't work (it depends on the adapter), but the behavior is undefined.
In any case, you certainly don't want to be making changes that affect the
connection without undoing those changes before returning the connection to
the pool.
You probably want something like:
def DB.execute_as(user)
synchronize do |conn|
begin
log_connection_execute(conn, "EXECUTE AS USER=#{literal(user)}")
yield
ensure
log_connection_execute(conn, "REVERT")
end
end
end
end
Then you can can execute a block as another user:
DB.execute_as('ADND\\user') do
ds = DB[:"secure__Employee Snapshot Fact___Employee Snapshot Fact"]
ds.first
ds.count
end
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
