Re: security based on value of X inside a table!
From: Lars Grøtteland (lars_at_bas.no)
Date: 08/18/03
- Next message: Arnel Sinchongco: "Re: enterprise manager and query analyzer"
- Previous message: Mamdoh Al-Habeeb: "Limit Privilaege level of SQL Server Services"
- In reply to: Dejan Sarka: "Re: security based on value of X inside a table!"
- Next in thread: Lars Grøtteland: "Re: security based on value of X inside a table!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 18 Aug 2003 09:07:52 +0200
Thanks!
Have a question - EXEC sp_addlogin 'UserE', 'E'
What does 'E' do?
- Lars
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote in
message news:%23BVOq9$YDHA.1916@TK2MSFTNGP12.phx.gbl...
> Here are some examples.
>
>
> /* Adding demo logins and db users in TestDB */
> /* For demo purposes, I am using names that will help me filter rows in
> view;
> Otherwise, I would be limited to use a SP or a UDF with IF method only.
> Note that in a SP or in a UDF I could use the same echnique as in view
*/
> USE master
> EXEC sp_addlogin 'UserE','E'
> EXEC sp_addlogin 'UserH','H'
> GO
> USE TestDB
> EXEC sp_grantdbaccess 'UserE'
> EXEC sp_grantdbaccess 'UserH'
> GO
>
> /* SP with IFs */
> CREATE PROCEDURE dbo.TestProc
> AS
> IF USER_NAME()='UserE'
> SELECT *
> FROM dbo.BOTTOM_POST
> WHERE LEFT(sPostID,1)='E'
> ELSE
> SELECT *
> FROM dbo.BOTTOM_POST
> WHERE LEFT(sPostID,1)='H'
> GO
> GRANT EXECUTE ON dbo.TestProc TO UserE
> GRANT EXECUTE ON dbo.TestProc TO UserH
> GO
> /* View */
> CREATE VIEW dbo.TestView
> AS
> SELECT *
> FROM dbo.BOTTOM_POST
> WHERE LEFT(sPostID,1)=RIGHT(USER_NAME(),1)
> WITH CHECK OPTION
> GO
> GRANT SELECT ON dbo.TestView TO UserE
> GRANT SELECT ON dbo.TestView TO UserH
> GO
>
> /* Test */
> SETUSER 'UserE'
> SELECT *
> FROM dbo.TestView
> EXEC dbo.TestProc
> SELECT *
> FROM dbo.BOTTOM_POST
> SETUSER
> SETUSER 'UserH'
> SELECT *
> FROM dbo.TestView
> EXEC dbo.TestProc
> SELECT *
> FROM dbo.BOTTOM_POST
> SETUSER
>
> /* Clean-Up */
> USE TestDB
> DROP PROCEDURE dbo.TestProc
> DROP VIEW dbo.TestView
> GO
> EXEC sp_revokedbaccess 'UserE'
> EXEC sp_revokedbaccess 'UserH'
> GO
> USE master
> EXEC sp_droplogin 'UserE'
> EXEC sp_droplogin 'UserH'
> GO
>
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Lars Grøtteland" <lars@bas.no> wrote in message
> news:OQ84tUyYDHA.440@tk2msftngp13.phx.gbl...
> > OK here it comes!
> >
> >
> > CREATE TABLE [dbo].[BOTTOM_POST] (
> > [sPostID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> > [iPartID] [smallint] NOT NULL ,
> > [sPartName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [User_ID] [int] NULL ,
> > [Changes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CreateDate] [datetime] NULL
> > ) ON [PRIMARY]
> > GO
> >
> >
> > insert into BOTTOM_POST(sPostID, iPartID, sPartName) values ('E', 10,
> '103')
> > insert into BOTTOM_POST(sPostID, iPartID, sPartName) values ('E1.1.1',
1,
> > '103')
> > insert into BOTTOM_POST(sPostID, iPartID, sPartName) values ('H1', 10,
> > '103')
> > insert into BOTTOM_POST(sPostID, iPartID, sPartName) values ('H1.1', 10,
> > '103')
> >
> >
> > We have a 'directory' inside the database. E, E1, E1.1, E2, E2.1, E2.2
and
> > so on. H, H1, H2, H2.1, H2.1.1, H2.1.2, and so on!
> > I would like user 1 to be able to view everything that has H inside
> sPostID,
> > and not E.
> > I would like user 2 to be able to view everything that has E and not H.
> >
> > - Thanks!
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote
in
> > message news:OjO8V2vYDHA.1816@TK2MSFTNGP09.phx.gbl...
> > > Well... do you have any DDL and Insert statements & desired result?
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > > Please reply only to the newsgroups.
> > > PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > > "Lars Grøtteland" <IHATESPAM@spam.lars.at.bas.no.com> wrote in message
> > > news:#R3W$alYDHA.3768@tk2msftngp13.phx.gbl...
> > > > If it should be in a SP - could you please give me a little clue of
> how
> > I
> > > > should do this?
> > > >
> > > > -Lars
> > > >
> > > > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si>
> wrote
> > in
> > > > message news:%23JydVUlYDHA.2448@TK2MSFTNGP09.phx.gbl...
> > > > > Lars,
> > > > >
> > > > > > I was hoping it was possible to have security based on a value
> > inside
> > > a
> > > > > > table. Is this possible?
> > > > > > If the value inside Table1 is 4 - Some of the users should have
> > access
> > > > to
> > > > > > only table2 and nothing else. Is this possible?
> > > > >
> > > > > Only through views, user-defined functions and stored procedures.
> > There
> > > is
> > > > > no declarative permissions statement for your needs.
> > > > >
> > > > > --
> > > > > Dejan Sarka, SQL Server MVP
> > > > > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > > > > Please reply only to the newsgroups.
> > > > > PASS - the definitive, global community
> > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Arnel Sinchongco: "Re: enterprise manager and query analyzer"
- Previous message: Mamdoh Al-Habeeb: "Limit Privilaege level of SQL Server Services"
- In reply to: Dejan Sarka: "Re: security based on value of X inside a table!"
- Next in thread: Lars Grøtteland: "Re: security based on value of X inside a table!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
