In many development shops, developers are not allowed to access the database schema directly, and are not allowed to create tables, indexes, views etc, instead are given access via a different schema that allows SELECT, UPDATE and DELETE access on data. The general reason is to avoid developers creating database objects without
In oracle this is implemented using a schema to hold all the objects, e.g. ecommercedb and the application (code, developers) get access to the ecommercedb schema using the ecommercedb_rw schema that has SELECT, UPDATE and DELETE privileges on the objects in the ecommercedb schema, in production developers get access to the ecommercedb schema via the ecommercedb_read schema, which has SELECT only access to the ecommercedb schema.
In the above setup, the database objects in the base schema ecommercedb can be accessed using the schema name as a prefix as shown below.
SELECT customer.id, customer.name, customer.status ....
FROM
ecommercedb.Customer as customer
The name of the schema owning the objects is spread all over the SQL being used in application and this is trouble as it does not provide the flexibility to change the schema names and it also makes the database environment not flexible to allow more than one application environment in the same database. Substituting the schema name with an variable is an acceptable solution, but does get more complex as more schemas are added to the mix.
A much more elegant solution is to use SYNONYMS, which allow to create alternate names to database objects that belong to other schemas or even to the same schema. In our ecommercedb example, create a synonym in ecommercedb_rw and ecommercedb_read schemas that are pointing to the ecommercedb schema as shown below
CREATE SYNONYM CUSTOMER for ecommercedb.CUSTOMER
After the above step, we can change our sql as shown below and we no longer need to reference the schema name in the SQL, thus allowing us to have as many application environments on the same database and also let each database server decide how they want to name the database schema.
SELECT customer.id, customer.name, customer.status ....
FROM
customer
Manually creating and maintaining all these SYNONYMS in version control is too much work and should be automated using a build task, an example using Rake is shown below
namespace :db do
desc 'Generate Synonyms for all tables'
task synonym: :environment do
base_schema = 'ECOMMERCEDB'
read_role = 'ECOMMERCEDB_READ'
read_write_role = 'ECOMMERCEDB_RW'
grants_dir = create_folder('grants')
synonym_dir = create_folder('synonyms')
grantsfile = File.open("#{grants_dir.getwd()}grants.sql", 'w')
synonymfile = File.open("#{synonym_dir.getwd()}synonym.sql",'w')
sql = 'SELECT table_name as tablename FROM user_tables'
tables = ActiveRecord::Base.connection.exec_query(sql)
tables.each do |table|
name = table['tablename']
grantsfile.puts "GRANT SELECT ON #{name} TO #{read_role}, #{read_write_role};"
grantsfile.puts "GRANT UPDATE,INSERT,DELETE ON #{name} TO #{read_write_role};"
synonymfile.puts "CREATE OR REPLACE SYNONYM #{name} FOR #{base_schema}.#{name};"
end
grantsfile.close()
synonymfile.close()
puts "Generated grants, synonyms for tables"
end
end
The synonym rake task above generates the GRANTS and the SYNONYMS for all the tables in the base ecommercedb, similar scripts can be generated for the rest of the database objects such as sequences, views, materialized views, functions and stored procedures