|
(SELECT * from [db].[schema].[tbl] FOR XML PATH(''),ROOT(''))
|
|
|
|
|
Hello All,
When designing an application, desktop or web, what is the best way to manage users that must access a database?
Is it better to have a single group that all users belong to and let the application manage all the security?
Or setup different groups with different permissions on the database and then associate users with that and whatever security the database can't manage handle that in the application?
Or is there a third way that I am just not seeing?
Thanks,
Koshbert
|
|
|
|
|
You will get answers for both methods.
1) Create users and handle security in the database
2) Create users in the application and connect with one user to the database.
There are pluses and minuses for both situations, however, consider this ...
Given a website like Facebook, you can create an account and have some set of permissions.
Do you really think it is creating a database user on the backend? Hmmm ... Probably not.
The real answer is up to you. You can do it either way.
I personally create one user in the database and handle all the security in the application.
Good luck. 
|
|
|
|
|
I agree with David.
I usually create users/rights as structures in the database and use one SQL user/password to access the database.
I do break this rule when security is a one shot item -> You either have full access or you have zero access. In this case I won't go to the trouble to create user/rights structures and just use standard group security on the database.
|
|
|
|
|
So then you guys have a table with user ids and a hash for the password that is used for logging into the application and everyone is associated with the one database user id.
That makes the database management piece pretty easy.
So what is the downside to this approach?
Thanks,
Koshbert
|
|
|
|
|
The only downside is adding development complexity. You need to manage rights in the application which requires the development of objects manage users and thier rights.
|
|
|
|
|
Follow up question:
For desktop applications how do you keep the single user id / password in sync? Alot of people for good reason want to change the supplied default password so hard coding it would be a bad idea.
Is this kind of design pattern covered in a blog, article or book anywhere online?
Thanks,
Koshbert
|
|
|
|
|
I work in a closed environment and have no applications in third party hands so changing the db password is not an issue.
Not sure how I would handle an application at other sites. Even encrypting a connection string would require a hardcoded key somewhere along the line.
|
|
|
|
|
Hello
I am trying to execute this query:
Create Domain Email
As
Varchar(255) Check (Position('@', Value) > 1)
But the below error will occur:
Unknown object type 'Domain' used in a CREATE, DROP, or ALTER statement.
SignatureNotFoundException
|
|
|
|
|
..Sql Server does not know about "domains". It hosts tables, records, and you can create things like databases and tables.
How did you expect this to work? Simply throw in the word "domain" and pray that Sql knows what you want?
Computers don't work like that. You learn the computers language if you want things from it, not the other way around.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Sql Server does not know about "domains". It hosts tables, records, and you can create things like databases and tables.
A DOMAIN in SQL is defined as a set of values for a given attribute! So, of course SqlServer knows something about domains! It just called different!
In SqlServer you use ADD CONSTRAINT to simulate domains.
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
Goood morning 
It is, you're right, stupid mistake. ..and twice as bad given the tone.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I saw some samples for creating UDDTs using Create Domain... .
I didn't that word from myself.
SignatureNotFoundException
|
|
|
|
|
..now that I had my rant, let's try and find an answer to your problem. For that, we first need to get a clear description on the problem itself; as is know, I can only guess at what you are trying to do.
- Are you trying to store email-adresses? (creating them is done in another server)
- Create a login? (Google for "create login")
- Something else?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I am trying to create a UDDT. I know how to do it using "Create Type", but I have also saw some query like I have posted.
SignatureNotFoundException
|
|
|
|
|
Well, as I know, DOMAIN is not implemented in SqlServer but as a SQL-Language element it's a reserved word.
You can use ADD CONSTRAINT to check the values (google for sql server add constraint, there are lot of samples).
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
Perhaps an alias data type ?
http://msdn.microsoft.com/en-us/library/ms175007(v=sql.100).aspx
|
|
|
|
|
|
Hi guys, I need to update the data in local database from other database automatically, by date or suggest if there is other way thanks for the help 
|
|
|
|
|
Depends, on the connection, databases used, and your data.
Running a script on a timer is the obvious way, but you might want to take a look into materialized views, or indexed views depending on your flavour of RDBMS.
I don't necessarily recommend it, it depends.
|
|
|
|
|
Hi, Im using 2 oracle databases how this could be possibly done?
|
|
|
|
|
Start with creating a database link.
Here's[^] the reference. But as the Oracle reference sucks for learning, I suggest that you google it instead. There's a ton of info on the net.
When you have created the link you should be able to SELECT * FROM mytable@the_other_server.
If this works you can either run a script to update the tables on your server, or create a materialized view that does this automatically.
How to create a materialized view is a much to big subject to answer here, I suggest google again.
Just remember that there are a lot of conditions that needs to be met to get a MV to work.
|
|
|
|
|
More info on Advanced Replication[^]
It always helps having the right search term.
You can also find Advanced Replication setup in the Oracle Enterprise Manager under the Data Movement tab.
|
|
|
|
|
One option would be to use the Sync Framework[^].
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hi, can I use stored procedure to sync data from other database using DBLink? What are the possible querries that I need to do in this process?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
