March 31, 2017 at 6:24 am
Hi Experts,
I have couple of basic queries regarding the way SQL Server is designed/works:
1) Schemas in SS is bit different from that in Oracle. In Oracle, explicit privileges (GRANTs) need to be given to a schema's object in order to access it in another schema. Say, to access table "T" present in schema "X" from schema "T", it requires -
-- In schema "X"
GRANT SELECT on T to Y;
But in SS, whenever I connect to my database (AdventureWorks2014) I directly land in default DBO schema, which has access to all objects across schemas like Sales, Person, etc. Why is there no authentication mechanism between schemas ?
2) Is there any concept of Collections in SQL Server like in Oracle?
Collection is a data-structure in memory that help to temporarily store the data and do further computation on it. Can it be compared to local tables (say @table_x) in SQL Server?
In Oracle, we can fetch the data from a cursor, store it in the collection and loop around it to further computation to get the desired result.
Please help me understand these concepts. Help much appreciated.
ps. I have been working past 7 yrs with Oracle RDBMS and PL/SQL, and just started tinkering with MS SQL Server 2014 recently. And, this is my first post in this forum. Pardon me if it lacks any of the forum ettiquettes/protocols.
Regards,
Ranit
March 31, 2017 at 9:05 am
I have couple of basic queries regarding the way SQL Server is designed/works:
1) Schemas in SS is bit different from that in Oracle. In Oracle, explicit privileges (GRANTs) need to be given to a schema's object in order to access it in another schema. Say, to access table "T" present in schema "X" from schema "T", it requires -
-- In schema "X"
GRANT SELECT on T to Y;
But in SS, whenever I connect to my database (AdventureWorks2014) I directly land in default DBO schema, which has access to all objects across schemas like Sales, Person, etc. Why is there no authentication mechanism between schemas ?
2) Is there any concept of Collections in SQL Server like in Oracle?
Collection is a data-structure in memory that help to temporarily store the data and do further computation on it. Can it be compared to local tables (say @table_x) in SQL Server?
In Oracle, we can fetch the data from a cursor, store it in the collection and loop around it to further computation to get the desired result.
Please help me understand these concepts. Help much appreciated.
ps. I have been working past 7 yrs with Oracle RDBMS and PL/SQL, and just started tinkering with MS SQL Server 2014 recently. And, this is my first post in this forum. Pardon me if it lacks any of the forum ettiquettes/protocols.
Regards,
Ranit1
1) There is, but you have to set it up. You go straight to the DBO schema because that's what 's associated as the default for your login. Probably your'e logging in as SA, which means you get access to everything. That's going to make testing security harder. Go through the process of setting up a different account, look through all the options there, and you'll see that you can set up and control things by schema, define the default schema for a login (or a group, I prefer to control stuff that way).
2) I don't know Collections, but it sounds sort of like in-memory tables and natively compiled procedures? Not sure though. However, look those up and let me know.
Welcome to the forum. Don't sweat etiquette, etc. We try to be helpful. The biggest ding you'll get from anyone on here is when you ask a question along the lines of "make my query run faster" and you don't give us the query, the structure or the execution plans so we're just guessing at answers. That gets frustrating. As to the rest, detail what you want and people will dive in to help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2017 at 1:03 pm
2) Reg. Collections: yes they are something like in-memory tables but their scope ends when the procedure or execution block ends.
March 31, 2017 at 2:14 pm
Having come from an Oracle shop, I liken databases in SQL Server, to Schemas in Oracle. Also speaking from a personal perspective, I've yet to find a compelling reason to use schemas in SQL Server. Instead, we go with a naming standard to differentiate between sets of objects in the same database. Meaning, items working towards the same goal have the same first 3 letters, followed by an underscore, and so they all reside within the dbo schema (and even then, there are few applications we create which even need that level of separation).
--=cf
March 31, 2017 at 3:10 pm
The schema permission issue you describe in SQL Server may be related to what's called ownership chaining:
https://www.mssqltips.com/sqlservertip/1778/ownership-chaining-in-sql-server-security-feature-or-security-risk/
If the schemas have the same owner (many times DBO) then it's easier to reference objects across schemas. If the schemas have different owners, then you would need to have explicit permissions granted for objects in one schema to reference another schema.
In SQL Server, there really isn't any concept of arrays or nested tables such as in Oracle. Also, for SQL Server purposes, it's best to try to use set based operations and to avoid cursors, which operate in what's called RBAR (row by agonizing row). It is a bit of a shift in thinking from Oracle, but it's well worth understanding. I used to be an Oracle person myself, but have been exclusively SQL Server for the last 12 years and I don't really miss it.
March 31, 2017 at 5:04 pm
Thanks Chris.
Reg. RBAR - Same was also suggested by experts while working on Oracle dB. It also works best with set-based techniques.
Another thing I miss from Oracle is: the way transactions were defined. I find Oracle's implementation and way Commit/Rollbacks were used to mark a transaction is better than SS. Or might be too early for me to comment.
Collections like nested-table or Varrays were really powerful in pl/sql.
March 31, 2017 at 5:07 pm
chuck.forbes - Friday, March 31, 2017 2:14 PMHaving come from an Oracle shop, I liken databases in SQL Server, to Schemas in Oracle. Also speaking from a personal perspective, I've yet to find a compelling reason to use schemas in SQL Server. Instead, we go with a naming standard to differentiate between sets of objects in the same database. Meaning, items working towards the same goal have the same first 3 letters, followed by an underscore, and so they all reside within the dbo schema (and even then, there are few applications we create which even need that level of separation).--=cf
But the way Schemas work in Oracle is actually compartmentalizing the objects. It 's pretty good and secure.
IMHO, naming convention will be an extra overhead for the developer to stick to some protocols everytime an object is created in the DB.
April 3, 2017 at 3:53 am
ranitb - Friday, March 31, 2017 5:04 PMI find Oracle's implementation and way Commit/Rollbacks were used to mark a transaction is better than SS.
What do you mean?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2017 at 9:25 am
ranitb - Friday, March 31, 2017 5:07 PMchuck.forbes - Friday, March 31, 2017 2:14 PMHaving come from an Oracle shop, I liken databases in SQL Server, to Schemas in Oracle. Also speaking from a personal perspective, I've yet to find a compelling reason to use schemas in SQL Server. Instead, we go with a naming standard to differentiate between sets of objects in the same database. Meaning, items working towards the same goal have the same first 3 letters, followed by an underscore, and so they all reside within the dbo schema (and even then, there are few applications we create which even need that level of separation).--=cf
But the way Schemas work in Oracle is actually compartmentalizing the objects. It 's pretty good and secure.
IMHO, naming convention will be an extra overhead for the developer to stick to some protocols everytime an object is created in the DB.
I feel that if I don't mention something here, that it might come across as some level of affirmation, so here goes ....
I'm good with a set of lean standards, but IMO naming conventions seldom cause issues. They might become onerous within code if you have too many guidelines, although I still think something should be in place there as well. But naming standards on database objects are hardly overhead ... I mean, how long does it actually take? And when I go over to help someone at their desk, I'm immediately in the correct context as soon as I see object names on their screen. Then we also have naming standards for technical keys, foreign keys, and several maintenance columns on tables (create_date/id, mod_date/id, concurrency column). It really makes authoring a SQL statement easier when you can assume the same important fields are present.
April 4, 2017 at 6:30 am
chuck.forbes - Monday, April 3, 2017 9:25 AMI feel that if I don't mention something here, that it might come across as some level of affirmation, so here goes ....
I'm good with a set of lean standards, but IMO naming conventions seldom cause issues. They might become onerous within code if you have too many guidelines, although I still think something should be in place there as well. But naming standards on database objects are hardly overhead ... I mean, how long does it actually take? And when I go over to help someone at their desk, I'm immediately in the correct context as soon as I see object names on their screen. Then we also have naming standards for technical keys, foreign keys, and several maintenance columns on tables (create_date/id, mod_date/id, concurrency column). It really makes authoring a SQL statement easier when you can assume the same important fields are present.
Hi Chuck,
Guess I didn't make my point correctly. My bad.
Ofcourse, naming convention and standards should be in-place. But, what I meant was is that they shouldn't be meant for compartmentalizing db objects.
April 11, 2017 at 2:51 pm
ranitb - Friday, March 31, 2017 6:24 AM1)Why is there no authentication mechanism between schemas ?2) Is there any concept of Collections in SQL Server like in Oracle?
Collection is a data-structure in memory that help to temporarily store the data and do further computation on it. Can it be compared to local tables (say @table_x) in SQL Server?
2), That sounds like #temporary tables or @Table variables. Temporary tables can store data for further use, but are persisted and available only within the same session (query window in SSMS). Table variables can also store data for further use and are persisted and available only within the same batch(generally a query execution within a query window). Common Table Expressions (CTE) do not store data, but they are persisted and available within the same SQL statement. This is an oversimplification as there are a number of other important differences, but that would be too long for this response.
1) My Oracle understanding is limited, but if I recall, a Sales login owns the Sales schema and all of the objects within the sales schema. In SS, schemas are not coupled to logins. Most logins do not have a schema with the same name and are assigned the dbo schema by default. The default schema (and associated permissions) are up to the DBA. A schema now is another security container that can be used to enforce permissions above the object level.
I actually prefer using schemas to segregate objects because I find it easier to manage generic group permissions. For example, I can grant the entire sales team access to the entire Sales schema, and they'll automatically have access to all new objects added to the Sales schema. If using a dbo.sales_object naming convention, I have to manually grant the new dbo.sales_ objects to the sales roles(assuming sales does not have access to everything in the dbo schema).
Wes
(A solid design is always preferable to a creative workaround)
April 18, 2017 at 4:54 am
whenriksen - Tuesday, April 11, 2017 2:51 PMI actually prefer using schemas to segregate objects because I find it easier to manage generic group permissions. For example, I can grant the entire sales team access to the entire Sales schema, and they'll automatically have access to all new objects added to the Sales schema. If using a dbo.sales_object naming convention, I have to manually grant the new dbo.sales_ objects to the sales roles(assuming sales does not have access to everything in the dbo schema).
Yes, I am also of the same opinion. Oracle schemas offer this nicely, but, how to achieve this in SS ?
Please give some pointers, I will research the rest myself. 🙂
April 18, 2017 at 6:00 am
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON schema::Sales TO <database role>
That's the basic principal of it. Don't grant permissions to the database, don't use the db_owner fixed role (or the sysadmin fixed server role). Grant permissions on schemas to database roles (your own ones). Add roles to database users
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2017 at 6:23 am
GilaMonster - Tuesday, April 18, 2017 6:00 AMGRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON schema::Sales TO <database role>That's the basic principal of it. Don't grant permissions to the database, don't use the db_owner fixed role (or the sysadmin fixed server role). Grant permissions on schemas to database roles (your own ones). Add roles to database users
Thanks GilaMonster. I think, I have started getting this SS technique.
Let me tell you what I understood -
Each user (i.e. a person trying to access the database) will have their own credential (user_id and password). When the user logs in he will land on the configured default schema, and has access to only those schemas where he has been granted the privileges.
Ex. User "RB" logs into database and lands on schema "Sales", as configured by DBA. After that he will have access only to those schemas (i.e. objects present in it) which has been granted.
Is this understanding correct? Please rectify if wrong.
Oops I missed Roles. Where do they fit in ?
April 18, 2017 at 7:12 am
Here's an example based on an application I worked on recently to maybe help you understand. This just supported a small web application but similar ideas could be applied to a larger system.
I created the following schemas:
META - has stored procs and data that is fairly static lookup type data, and data controlled by an administrator
APP - has stored procs and data that is entered through this applications screens by regular users
EXT - has stored procs and data that is synchronized periodically from an external system, in this case our main OLTP system
I created the following roles:
ADMIN - has permissions to SELECT, INSERT, UPDATE on META and APP schemas, just SELECT on EXT schema.
It also has permissions to EXECUTE procs in any schema, and ALTER procs in META and APP
USERS - has permissions to SELECT, INSERT, UPDATE on APP schema, just SELECT on META and EXT schemas
it also has permissions to EXECUTE procs in any schema.
REPORTING - has only SELECT permissions on all schemas, EXECUTE procs in APP schema
So I could easily setup different environments, for example in development environment put the AD group for developers in the ADMIN role, but in the testing environment only the QA people's AD group would be in the ADMIN role, and in production, only those authorized to do deployments for this program would be in the ADMIN role. The login used for database connection pooling by the web application would be in the USERS role in all environments. Developers and QA people would only be members of REPORTING role in production.
In a more complex system, you could have many different APP schemas that different departments had different privileges in, so accounting people could only INSERT and UPDATE their own data, marketing people could only INSERT and UPDATE their own data, etc.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply