locked
Revoke privilege from Application owner (Cannot edit table ..) ? Is that right ? RRS feed

  • General discussion

  • Hi Everyone ,
          I am A DBA , Today I was with a discussion with Development manager , He asked me to create new SQL Server database as production env, This is easy :-)
    but the point is not here as he ask me a weird thing , he need from me create user X for X database and keep password with me not with application admin .
        He think I should keep password with me even in web.config file , I should only the one use this user X for an application .
        He also think any changes on table like add column , or create new table on this database should be created by DBA as he having the user . I don't believe the DBA his job adding a column or create a table for application. I am ever heard like this  .
       The DBA can help on that whenever developer face an issue , This kind of work will kill a DBA and become like an operation guy ? I would like to hear from you if I am wrong .
       Thanks in advance .
    Sunday, October 2, 2011 1:12 PM

All replies

  • >>>>He also think any changes on table like add column , or create new table on this database should be created by DBA as he having the user .

    It depends on company's policy. Usually if a database is a production only DBA adding columns/rebuild indexes/create new tables and etc.. Developers test their application on dev.machine and the responsibility of DBA is to compare/test the data on Dev,machine and if everything is OK combine the structure. 

    Are you afraid that  you will be adding hundred of columns or creating thousands of tables day by day :-))))? 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 2, 2011 1:24 PM
  • A DBA typically provides many services, which ultimately provide control over production data. Exactly what a DBA does varies widely depending on the organization and skillset of individuals. DBA roles are sometimes categorized into job or role titles like Production DBA, Operational DBA, Development DBA or Application DBA. Exactly what is involved in each role is quite subjective but the first two roles focus on maintenace, operation and support while the last 2 more on the application development and support aspects.

    Following best practices, application development typically does not change production schema or data directly. It is the role of the DBA to at least review rollout scripts, if not help develop them. This practice provides the checks and balances needed to safeguard the production data and database environment. In some organizations, developers have no access to production at all. It is the role of the DBA to provide the necessary services.

    Regarding passwords for production application service accounts, these should not be known by development unless they are required to do their job. If the production environment is locked down, then the app dev team obviously should not know such passwords.

    Do you have a job description? I think that would be the authoritative source of what is expected of you. OTOH, the more roles you can perform as a DBA, the more valuable you will be to your organization as well as in the marketplace.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, October 2, 2011 3:05 PM
  • Thanks Uri & Dan .

     

    Actually my core experience with Oracle more than SQL which is really have different role. My point Thats right if we have many DBAs and everyone has responsible  for his part , but I'm only one so spend more time to do some operations to adding removing tables is not something for me but for organization, spending time to improve design and improve performance is giving the organization high value as well than others .

     

    So if problem happen who should call ? I apply many best practice for security for SQL Server but I didn't read anything about this . If Microsoft saying that as recommendation then it will be acceptable, otherwise I believe application should classified into categories Development and production , one person will be responsible to move changes to production .         

    Sunday, October 2, 2011 7:25 PM
  • There is not really any or wrong or right here. At least not in the general case. Maybe if we had been in your shoes, we would also found the request corny, because we would have realised that it does not fit with how the organistion is working.

    Certainly, for there to be any point to revoke permission for the application admin from creating tables, you will have to take an active part in the design. If you just act as a proxy, there is little point in it. And for you to be able to take active part in the table design, you should be a member of the project team. And have time allocated to this task.

    But, again, it has a lot to do with how your shop is organised.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 2, 2011 10:06 PM
  • OK , let us say it is depend on organisation . I am going keep user X with me that is the database owner for X database .

     

    In this case it is now not allowed to any one to create table to add a column , but the developer needs to do DML operation using coded application which I don't know about the code . 

     

    OK , Developers cannot alter or modify schema , but they can access to data and play with it based on code that the DBA cannot know what is it .

     

    USER X is database owner for database X 

    User Y has only db_datawriter,db_reader, which I will give them to developer .

     

    I am talking about application user who connect from application server .

    Monday, October 3, 2011 7:17 AM
  • >>OK , Developers cannot alter or modify schema , but they can access to data and play with it based on code >>>that the DBA cannot know what is it .

    They can perform any DML operations you gave to user Y, so if them try to add column SQL Server throws the error. What is your concern? If the application performs badly they will be in touch with to improve it but only you are responsible to modify schema


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 3, 2011 7:25 AM
  • OK , Developers cannot alter or modify schema , but they can access to data and play with it based on code that the DBA cannot know what is it .

     
    As I understand your original post, we are talking about a deveopment environment. Obviously, developers needs to be able to modify data in their own environment.

    Production is a different matter.

    I like to add that with proper version control of all code, the whole discussion becomes sort of moot. There needs to be a gatekeeper who reviews what is checked in, but it is the version-control system that is the master.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 3, 2011 8:50 AM