On Tue, May 6, 2014 at 6:44 AM, Prabath Abeysekera <[email protected]>wrote:
> Hi Charitha, > > In this particular scenario, it looks like you haven't properly configured > the database user being used, with "SELECT" privileges. Maybe you did, but > there's a possibility that it got overridden by some other setting as well. > Let me analyse the set of permissions required for SQL Server scripts to > run and let you know. > Just noticed that the script used for SQLServer tries to access the database objects configured within the schema ,SYS. This seems to be something introduced as part of a mechanism used to check whether some database object (eg: a table) already exists, without really thinking about the required privileges that are required to execute those statements, etc. However, this shouldn't be a problem for someone like a database administrator (granted with some admin role as those pre/custom configured admin roles usually have read access to the system schemas), but can cause problems for ordinary users. > On a side note, IMO, -Dsetup is a useful feature when it comes to > deploying the products in some environment like "Dev" (to make it easy for > users to simply install the product, configure the schema and use it for > dev tasks), etc but, not when you deal with "Prod" kind of environments > where the access to resources are typically restricted. In other words, > it's usually the task of a database administrator to create databases, > create schema objects, users, other database entities in a properly managed > storage provisioning environment (even for environments like "Dev", etc > depending on the organizational policies). Therefore, generally, as it is > with any other production deployed entity, POLP (Principle of Least > Privileges) is practiced by those database administrators to restrict users > to get themselves involved in tasks like altering database schemas etc even > accidentally as human errors. So, the general practice is that, an > application should be able to survive with the basic set of permissions > required for performing CRUD operations like, SELECT, INSERT, UPDATE, > DELETE + other relevant permissions to execute procedures/routines, etc > depending on the requirement. Because, if some database application tries > to create/alter database schema objects, it usually is an indication of > poor separation of concerns and hence, has to be avoided. I've seen this in > some places such as registry, identity, etc related components which need > to be fixed. > > Cheers, > Prabath > > > On Tue, May 6, 2014 at 1:20 AM, Charitha Kankanamge <[email protected]>wrote: > >> Hi folks, >> I had to assign "sysadmin" server role to the registry/um DB user to >> start the carbon server with -Dsetup in MSSQL server. With lesser >> privileges, I'm getting the following error. >> >> java.lang.Exception: Error occurred while executing : IF NOT EXISTS >> (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = >> OBJECT_ID(N'[DBO].[REG_CLUSTER_LOCK]') AND TYPE IN (N'U')) CREATE TABLE >> REG_CLUSTER_LOCK ( REG_LOCK_NAME VARCHAR (20), REG_LOCK_STATUS VARCHAR >> (20), REG_LOCKED_TIME DATETIME, REG_TENANT_ID INTEGER DEFAULT 0, PRIMARY >> KEY (REG_LOCK_NAME) ) >> at >> org.wso2.carbon.utils.dbcreator.DatabaseCreator.executeSQL(DatabaseCreator.java:169) >> at >> org.wso2.carbon.utils.dbcreator.DatabaseCreator.executeSQLScript(DatabaseCreator.java:325) >> at >> org.wso2.carbon.utils.dbcreator.DatabaseCreator.createRegistryDatabase(DatabaseCreator.java:61) >> at >> org.wso2.carbon.user.core.common.DefaultRealmService.initializeDatabase(DefaultRealmService.java:278) >> ... 19 more >> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT >> permission was denied on the object 'objects', database >> 'mssqlsystemresource', schema 'sys'. >> at >> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown >> Source) >> at >> com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown >> Source) >> at >> com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown >> Source) >> at >> com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown >> Source) >> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) >> >> What are the minimum set of permissions that must be granted to the DB >> user when creating MSSQL database? Please advise. We should update [1] with >> the exact set of permissions. >> >> [1]https://docs.wso2.org/display/AM160/Setting+up+with+MS+SQL >> [2]https://wso2.org/jira/browse/DOCUMENTATION-801 >> >> Thanks! >> Charitha >> > > > > -- > Prabath Abeysekara > Associate Technical Lead, Data TG. > WSO2 Inc. > Email: [email protected] > Mobile: +94774171471 > -- Prabath Abeysekara Associate Technical Lead, Data TG. WSO2 Inc. Email: [email protected] Mobile: +94774171471
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
