Granting User Privileges????

[Dimitrios Tanis]

I have setup an SQL MSDE instance on my PC and I currently finished working
on the BE in an access 2003 project. I believe it is now time to define
roles, users and grant each one its privileges, but how do I do that???
I know about GRANT, REVOKE etc, but
1. Where do I type these?
2. Is all this information encrypted?
3. When installing the MSDE I set a password what is this all about?? Will
it have any impact on user level security?
4. Is there a visual tool to handle user level security, such as in an
Access database?
5. As I indend to create a distributed database application is these
security scheme in accordance to that?
6 and final. How can I let the superuser (aka the superior user of the
client) grant privileges to other users, after granting him the option to
grant?? eg form?

 

[Andrew John]

Some answers inline

I have setup an SQL MSDE instance on my PC and I currently finished working on the BE in an access 2003 project. I
believe it is now time to define roles, users and grant each one its privileges, but how do I do that???
I know about GRANT, REVOKE etc, but
1. Where do I type these?

In a script, run with osql.exe against the database is one way.
Query Analyser from a developer pc or pc with sql server installed is another

2. Is all this information encrypted?

Not unless it is embedded in your application, and only run locally or through an encrypted connection.
Even if it was scripting the database will get this configuration type of information back. The encyption in
SQL Server 2000 / MSDE is for view, triggers and stored procedures - i.e. code, and is crackable anyway.

3. When installing the MSDE I set a password what is this all about?? Will it have any impact on user level security?

Depends on what authentication mode you use - with windows authentication, the only password you
have to worry about is the sa one on the install, and this is only a problem if someone at some stage
changes the security to SQL/Mixed.

4. Is there a visual tool to handle user level security, such as in an Access database?

If you map windows groups to the SQL database roles, then your user management is done under windows itself.
The beta for SQL 2005 Express includes a GUI tool, and there is a web based one for 2000.

http://www.microsoft.com/downloads/...62-EC54-4299-85FC-BA05C181ED55&displaylang=en
http://www.microsoft.com/downloads/...98-c57a-419e-acbc-2a332cb7f959&DisplayLang=en

Apart from them there is osql.exe for the masochistic, Enterprise Manager for the licensed, or your application
doing sql calls behind the scenes.

5. As I indend to create a distributed database application is these security scheme in accordance to that?

Well you are going to have to enable the network libraries, which immediatly gives you an order of magnitude
more security worries. For example a breach in your install of MSDE can grant full access to the PC (xp_cmdshell).
Integrated security is Microsoft's recommendation, but you still need to think about changing network ports and
firewalling.

6 and final. How can I let the superuser (aka the superior user of the client) grant privileges to other users, after
granting him the option to grant?? eg form?

see 4)

also suggest reading security related info from

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/distsql/distsql_84xl.asp

Regards
AJ