Grant a user/role to Oracle tables and views

oracle_grant_role.rake

namespace :oracle do

  desc "Grant SELECT to ROLE 'SDD_READ' on all table and views in the schema rerefenced by RAILS_ENV"
  task :grant_selects => :environment do
    
    # Define Role (or User) to be granted 
    RoleToGrant = "your_role_or_user"
    
    begin
      db_objects = ActiveRecord::Base.connection.send(:select, "select object_name, object_type from user_objects where object_type in ('VIEW', 'TABLE')")
    rescue => e
      puts "Error executing grant statement statement. #{e.class}: #{e}"
      if Rails.configuration.database_configuration[Rails.env]["adapter"] !~ /.*oracle.*/ then
        $stderr.puts "Database might not be Oracle. Cant find 'to-be-granted objects' like this on other DBs - so maybe it's all OK ;-)"
      end
      exit(1)
    end

    grant_statement = "grant SELECT on %s to #{RoleToGrant}"
    db_objects.each do |db_object|
      object_type = db_object["object_type"]
      object_name = db_object["object_name"]

      raw_statement = sprintf(grant_statement, object_name)
      puts "-- [#{object_type.ljust(10)}] #{raw_statement}"
      begin
        ActiveRecord::Base.connection.execute(raw_statement)
      rescue => e
        $stderr.puts "Error executing grant statement. #{e.class}: #{e}"
      end
    end

  end
end