Thursday, March 29, 2012
Granting User Privileges??
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?
Some answers inline
"Dimitrios Tanis" <jtanis@.mycosmos.gr> wrote in message news:O%23XXjhBnFHA.3608@.TK2MSFTNGP15.phx.gbl...
>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/d...displaylang=en
http://www.microsoft.com/downloads/d...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/de...stsql_84xl.asp
Regards
AJ
Tuesday, March 27, 2012
Grant Privileges
doesn't seem to work. I have a link table in Access 97. Some columns are
supposed to be read only and some columns are supposed to be read-only.
I use the script below
revoke all privileges on stores to shipdirect1
grant update on stores(creditlimit) to shipdirect1
What am I doing wrong?I havn't tested this but you cud always try removing the user and login and
adding it back.
"Arne" wrote:
> I am trying to give limited privileges to a login account on a table, but
it
> doesn't seem to work. I have a link table in Access 97. Some columns are
> supposed to be read only and some columns are supposed to be read-only.
> I use the script below
> revoke all privileges on stores to shipdirect1
> grant update on stores(creditlimit) to shipdirect1
> What am I doing wrong?
Monday, March 26, 2012
Grant on all tables
is there a way to grants object privileges on
all tables of database to an user?
like this:
grant select, insert, update, delete on <all tables> to usernamedb
go
thanks!!!!
No. You can add user to db_datareader and db_datawriter roles. This will however provide SELECT, INSERT, UPDATE and DELETE permission on tables/ views / table-valued functions etc. See Books Online for more details on the permissions / roles. Also, you should create a group/role and grant permissions to it instead of directly to the user. This is easier to manage and control.
Alternatively, you can write few lines of code that loops through the desired objects and grants necessary permissions on each object using dynamic SQL.
|||In 2005 you can grant access to a schema's set of objects:
grant select, insert, update, delete on schema::dbo to bob
For example, in the AdventureWorks DB:
use adventureWorks
go
create user bob without login
go
--first prove no accss
execute as user='bob'
go
select * from production.product --will error
go
revert
go
grant select, insert,update, delete on schema::production to bob
go
execute as user='bob'
go
select * from production.product --will work
go
revert
go
Note that this gives access to table valued user-defined functions also...
|||Very cool... thank uFriday, March 23, 2012
Grant and Revoke on a column
doesn't seem to work. I have a link table in Access 97 and my SQL server is
version 7. Some columns are supposed to be read only and some columns are
supposed to be read-only.
I use the script below
revoke all privileges on stores to shipdirect1
grant update on stores(creditlimit) to shipdirect1
What am I doing wrong?Arne,
SQL Server does not support column level permissions. Even if it did it is
doubtful Access supports it. You will be better off setting up a view with
the permitted columns and granting permissions to it to the user.
Ilya
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CC4D4275-A5EC-4C30-B875-8A5CB2626C65@.microsoft.com...
> I am trying to give limited privileges to a login account on a table, but
it
> doesn't seem to work. I have a link table in Access 97 and my SQL server
is
> version 7. Some columns are supposed to be read only and some columns are
> supposed to be read-only.
> I use the script below
> revoke all privileges on stores to shipdirect1
> grant update on stores(creditlimit) to shipdirect1
> What am I doing wrong?
>|||SQL Server 7.0 that is.
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:OdYOh6GFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Arne,
> SQL Server does not support column level permissions. Even if it did it is
> doubtful Access supports it. You will be better off setting up a view with
> the permitted columns and granting permissions to it to the user.
> Ilya
> "Arne" <Arne@.discussions.microsoft.com> wrote in message
> news:CC4D4275-A5EC-4C30-B875-8A5CB2626C65@.microsoft.com...
but
> it
> is
are
>|||Actually, SQL Server DOES support column-level permissions and has done so
since the earliest version.
The problem is probably related to linking to the Access table, if access
doesn't support this.
How do you know it doesn't work? Are you getting an error? What does it say?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:OdYOh6GFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Arne,
> SQL Server does not support column level permissions. Even if it did it is
> doubtful Access supports it. You will be better off setting up a view with
> the permitted columns and granting permissions to it to the user.
> Ilya
> "Arne" <Arne@.discussions.microsoft.com> wrote in message
> news:CC4D4275-A5EC-4C30-B875-8A5CB2626C65@.microsoft.com...
> it
> is
>|||Kalen,
When I update a column that is supposed to be read-only I don't the
exception that I expected.
However I found some MS Access properties that solved the problem for me.
Arne.
"Kalen Delaney" wrote:
> Actually, SQL Server DOES support column-level permissions and has done so
> since the earliest version.
> The problem is probably related to linking to the Access table, if access
> doesn't support this.
> How do you know it doesn't work? Are you getting an error? What does it sa
y?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
> news:OdYOh6GFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>
>
Grant all privileges to a user
regards,
Add the user to the db_owner database role of that database:
use databasename
go
sp_addrolemember db_owner,username
Grant Access Error
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change al
l
the logon ids.
Thanks for any assistance
/Leif S
--
Systems AnalystUse brackets as delimiters:
GRANT <privileges> on <table_name> TO [gh.om]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change
all
the logon ids.
Thanks for any assistance
/Leif S
--
Systems Analyst|||Thanks, Tom! Problem solved.
/Leif S.
--
Systems Analyst
"Tom Moreau" wrote:
> Use brackets as delimiters:
> GRANT <privileges> on <table_name> TO [gh.om]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leif S" <LeifS@.discussions.microsoft.com> wrote in message
> news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
> SQL Server returns an error when I try go grant privileges to a username
> containing '.' (dot). The statement goes like this:
> GRANT <privileges> on <table_name> TO gh.om
> The error message point to the '.' Is there a workaround?
> We use the format "sitename"."username" on quite a lot of
> serverconfigurations in our company and it will be quite a job to change
> all
> the logon ids.
> Thanks for any assistance
> /Leif S
> --
> Systems Analyst
>